将空值分配给SqlParameter的最佳方法 [英] Best method of assigning NULL value to SqlParameter

问题描述

我在C#类方法中使用了许多可选的输入参数.由于可选语法在不使用参数时会创建一个'0'值，因此我在该方法中调用的SQL插入命令会像这样结束插入.但是，当不使用参数时，我需要命令插入NULL值而不是0.在不使用大量"if"语句的情况下实现此目标的最佳方法是什么?

I have a number of optional input parameters I am using in a C# class method. Since the optional syntax creates a value of '0' when the parameter is not used, the SQL insert command I call in the method winds up inserting as such. However, I need the command to insert a NULL value instead of a 0 when the parameter is not being used. What is the best way to accomplish this without using a large amount of 'if' statements?

下面是我所指的代码.是否有某种语法/某种命令可以让我在SqlParameter声明中指定NULL值?

Below is the code I am referring to. Is there syntax/a command of some kind that will allow me to specify a NULL value in the SqlParameter declaration?

public int batchInsert
(
    int id, 
    int outcome, 
    int input = 0, 
    int add = 0, 
    int update = 0,
    int delete = 0,
    int errors = 0, 
    int warnings = 0
)
{
    string sts;
    if (outcome == 0)
    {
        sts = "S";
    }
    else if (outcome == 1)
    {
        sts = "W";
    }
    else
    {
        sts = "E";
    }

    SqlConnection sqlConn = new SqlConnection(this.connString);
    SqlParameter runId = new SqlParameter("@runId", id);
    SqlParameter endTime = new SqlParameter("@endTime", DateTime.Now);
    SqlParameter status = new SqlParameter("@status", sts);
    SqlParameter sqlInput = new SqlParameter("@itemsRead", input);
    SqlParameter sqlAdd = new SqlParameter("@add", add);
    SqlParameter sqlUpdate = new SqlParameter("@update", update);
    SqlParameter sqlDelete = new SqlParameter("@delete", delete);
    SqlParameter sqlError = new SqlParameter("@errors", errors);
    SqlParameter sqlWarning = new SqlParameter("@warnings", warnings);
    SqlParameter result = new SqlParameter("@outcome", results[outcome]);
    SqlCommand sqlComm = new SqlCommand(insertCommand(), sqlConn);

推荐答案

是的，对于参数的值，只需使用DBNull.Value.例如:

Yes, for the value of the parameter, just use DBNull.Value. For example:

SqlParameter sqlError = 
    new SqlParameter("@errors", errors == 0 ? (object)DBNull.Value : errors);

或写一个小帮手:

private object ValueOrDBNullIfZero(int val) {
   if ( val == 0 ) return DBNull.Value;
   return val;
}

然后:

SqlParameter sqlError = 
    new SqlParameter("@errors", ValueOrDBNullIfZero(errors));

这篇关于将空值分配给SqlParameter的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！