如何将空变量从 C#.net 代码传递给 SQL 存储过程

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1207404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-06 10:56:00  来源:igfitidea点击:

How to pass a null variable to a SQL Stored Procedure from C#.net code

c#sqlsql-serverstored-procedures

提问by Irfy

Im calling a SQL stored procedure from a piece of C#.net code:

我从一段 C#.net 代码调用 SQL 存储过程:

SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters);

where the sqlParametersvariable is defined as:

其中sqlParameters变量定义为:

        SqlParameter[] sqlParameters = new SqlParameter[SQL_NUMBER_PARAMETERS];

        Log.Logger.Debug(string.Format("Running proc: {0} ", STORED_PROC_NAME));

        SqlParameters[0] = new SqlParameter("fieldID", SqlDbType.BigInt );
        SqlParameters[0].Value = fieldID;
        SqlParameters[0].Direction = ParameterDirection.Input;

I need to now pass in another two parameters to this Stored Proc, (both are of type SqlDateTime), which are going to NULLin this case.

我现在需要将另外两个参数传递给这个 Stored Proc,(两者都是 类型SqlDateTime),在这种情况下它们将变为NULL

Thanks,

谢谢,

IN

采纳答案by Justin Niessner

SqlParameters[1] = new SqlParameter("Date1", SqlDbType.SqlDateTime);
SqlParameters[1].Value = DBNull.Value;
SqlParameters[1].Direction = ParameterDirection.Input;

...then copy for the second.

...然后复制第二个。

回答by marc_s

You can pass the DBNull.Valueinto the parameter's .Value property:

您可以将 传递DBNull.Value到参数的 .Value 属性中:

    SqlParameters[0] = new SqlParameter("LedgerID", SqlDbType.BigInt );
    SqlParameters[0].Value = DBNull.Value;

Just adjust for your two DateTime parameters, obviously - just showing how to use the DBNull.Valueproperty value here.

显然,只需调整您的两个 DateTime 参数 - 仅展示如何在DBNull.Value此处使用属性值。

Marc

马克

回答by Dan Diplo

Use DBNull.ValueBetter still, make your stored procedure parameters have defaults of NULL. Or use a Nullable<DateTime>parameter if the parameter will sometimes be a valid DateTime object

使用DBNull.Value更好的是,使您的存储过程参数具有默认值 NULL。或者,Nullable<DateTime>如果参数有时是有效的 DateTime 对象,则使用参数

回答by Mike Z

    SQLParam = cmd.Parameters.Add("@RetailerID", SqlDbType.Int, 4)
    If p_RetailerID.Length = 0 Or p_RetailerID = "0" Then
        SQLParam.Value = DBNull.Value
    Else
        SQLParam.Value = p_RetailerID
    End If

回答by Doomsknight

I use a method to convert to DBNull if it is null

如果为空,我使用一种方法转换为 DBNull

    // Converts to DBNull, if Null
    public static object ToDBNull(object value)
    {
        if (null != value)
            return value;
        return DBNull.Value;
    }

So when setting the parameter, just call the function

所以在设置参数的时候,只要调用函数

    sqlComm.Parameters.Add(new SqlParameter("@NoteNo", LibraryHelper.ToDBNull(NoteNo)));

This will ensure any nulls, get changed to DBNull.Value, else it will stay the same.

这将确保任何空值都更改为 DBNull.Value,否则它将保持不变。

回答by GoAntonio

try this! syntax less lines and even more compact! don't forget to add the properties you want to add with this approach!

尝试这个!语法更少,更紧凑!不要忘记使用这种方法添加要添加的属性!

cmd.Parameters.Add(new SqlParameter{SqlValue=(object)username??DBNull.Value,ParameterName="user" }  );

回答by ablaze

Let's say the name of the parameter is "Id" in your SQL stored procedure, and the C# function you're using to call the database stored procedure is name of type int?. Given that, following might solve your issue :

假设参数的名称在您的 SQL 存储过程中是“Id”,而您用来调用数据库存储过程的 C# 函数是 name of type int?。鉴于此,以下可能会解决您的问题:

public void storedProcedureName(Nullable<int> id, string name)
{
    var idParameter = id.HasValue ?
                new SqlParameter("Id", id) :
                new SqlParameter { ParameterName = "Id", SqlDbType = SqlDbType.Int, Value = DBNull.Value };

    // to be continued...

回答by Lavamantis

Old question, but here's a fairly clean way to create a nullable parameter:

老问题,但这是创建可为空参数的一种相当干净的方法:

new SqlParameter("@note", (object) request.Body ?? DBNull.Value);

If request.Body has a value, then it's value is used. If it's null, then DbNull.Value is used.

如果 request.Body 有一个值,则使用它的值。如果它为空,则使用 DbNull.Value。