如何用DBNull参数化一个空字符串。价值明显和迅速

时间:2022-02-26 03:13:07

I got tired of writing the following code:

我厌倦了写下面的代码:

/* Commenting out irrelevant parts
public string MiddleName;
public void Save(){
    SqlCommand = new SqlCommand();
    // blah blah...boring INSERT statement with params etc go here. */
    if(MiddleName==null){
        myCmd.Parameters.Add("@MiddleName", DBNull.Value);
    }
    else{
        myCmd.Parameters.Add("@MiddleName", MiddleName);
    }
    /*
    // more boring code to save to DB.
}*/

So, I wrote this:

所以,我写这个:

public static object DBNullValueorStringIfNotNull(string value)
{
    object o;
    if (value == null)
    {
        o = DBNull.Value;
    }
    else
    {
        o = value;
    }
    return o;
}

// which would be called like:
myCmd.Parameters.Add("@MiddleName", DBNullValueorStringIfNotNull(MiddleName));

If this is a good way to go about doing this then what would you suggest as the method name? DBNullValueorStringIfNotNull is a bit verbose and confusing.

如果这是一种很好的方法,那么您认为方法名是什么?DBNullValueorStringIfNotNull有点冗长和混乱。

I'm also open to ways to alleviate this problem entirely. I'd LOVE to do this:

我也有办法完全缓解这个问题。我很乐意这样做:

myCmd.Parameters.Add("@MiddleName", MiddleName==null ? DBNull.Value : MiddleName);

but that won't work because the "Operator '??' cannot be applied to operands of type 'string and 'System.DBNull'".

但这行不通,因为“操作者”?'不能应用于'string和'System.DBNull'类型的操作数。

I've got C# 3.5 and SQL Server 2005 at my disposal if it matters.

如果有问题的话,我有c# 3.5和SQL Server 2005。

8 个解决方案

#1


50  

Cast either of your values to object and it will compile.

将你的任何一个值转换为对象,它将被编译。

myCmd.Parameters.Add("@MiddleName", MiddleName==null ? (object)DBNull.Value : MiddleName);

#2


15  

You can avoid the explicit cast to object using SqlString.Null instead of DBNull.Value:

您可以避免使用SqlString对对象进行显式转换。Null代替DBNull.Value:

MiddleName ?? SqlString.Null

There are corresponding types for int, datetime, and so forth. Here's a code snippet with a couple more examples:

int、datetime等等都有相应的类型。这里有一个代码片段,还有几个例子:

 cmd.Parameters.AddWithValue("@StartDate", StartDate ?? SqlDateTime.Null);
 cmd.Parameters.AddWithValue("@EndDate", EndDate ?? SqlDateTime.Null);
 cmd.Parameters.AddWithValue("@Month", Month ?? SqlInt16.Null);
 cmd.Parameters.AddWithValue("@FormatID", FormatID ?? SqlInt32.Null);
 cmd.Parameters.AddWithValue("@Email", Email ?? SqlString.Null);
 cmd.Parameters.AddWithValue("@ZIP", ZIP ?? SqlBoolean.Null);

#3


11  

Personally this is what I would do with an extension method (make sure this goes into a static class)

就我个人而言,这就是我对扩展方法所做的(确保它进入一个静态类)

public static object GetStringOrDBNull(this string obj)
{
    return string.IsNullOrEmpty(obj) ? DBNull.Value : (object) obj
}

Then you'd have

然后你会

myCmd.Parameters.Add("@MiddleName", MiddleName.GetStringOrDBNull());

#4


6  

myCmd.Parameters.Add("@MiddleName", MiddleName ?? (object)DBNull.Value);

#5


2  

@David Thanks for your suggestion. The following method works great!

谢谢你的建议。下面的方法非常有用!

MiddleName ?? (object)DBNull.Value

#6


1  

Yeap, we'd all love to do myCmd.Parameters.Add("@MiddleName", MiddleName ?? DBNull.Value);. Or better still, have the freakin' SqlClient layer understand that CLR null should be mapped to DBNull.Value when adding a parameter. Unfortunately the .Net type system closes the first alternative, and the implementation of SqlClient closes the second.

是的,我们都喜欢做mycmd .参数。Add(“@MiddleName MiddleName ? ?DBNull.Value);。或者更好的是,让freakin' SqlClient层理解CLR null应该映射到DBNull。添加参数时的值。不幸的是。net类型系统关闭了第一个选项,SqlClient的实现关闭了第二个选项。

I'd go with a well known function name, like Coalesce or IsNull. Any DB developer will recognize what they do in an instant, from the name alone.

我将使用一个众所周知的函数名,比如Coalesce或IsNull。任何DB开发人员都能一眼就认出他们所做的事情。

#7


1  

I'd rather give you two totally different suggestions:

我宁愿给你两个完全不同的建议:

  1. Use an ORM. There are plenty of non-intrusive ORM tools.

    使用ORM。有大量的非侵入性ORM工具。

  2. Write your own wrapper for building commands, with a cleaner interface. Something like:

    为构建命令编写自己的包装器,使用更简洁的接口。喜欢的东西:

    public class MyCommandRunner {
      private SqlCommand cmd;
    
      public MyCommandRunner(string commandText) {
        cmd = new SqlCommand(commandText);
      }
    
      public void AddParameter(string name, string value) {
        if (value == null)
         cmd.Parameters.Add(name, DBNull.Value);
        else
          cmd.Parameters.Add(name, value);
      }
    
      // ... more AddParameter overloads
    }
    

If you rename your AddParameter methods to just Add, you can use it in a very slick way:

如果您将AddParameter方法重命名为Add,您可以非常巧妙地使用它:

var cmd = new MyCommand("INSERT ...")
  {
    { "@Param1", null },
    { "@Param2", p2 }
  };

#8


1  

I would suggest using nullable properties instead of public fields and an 'AddParameter' method (don't know if this code is optimized or correct, just off the top of my head):

我建议使用nullable属性,而不是public字段和“AddParameter”方法(不知道这段代码是否经过了优化或正确,我不知道):


private string m_MiddleName;

public string MiddleName
{
  get { return m_MiddleName; }
  set { m_MiddleName = value; }
}

.
.
.

public static void AddParameter(SQLCommand cmd, string parameterName, SQLDataType dataType, object value)
{
  SQLParameter param = cmd.Parameters.Add(parameterName, dataType);

  if (value is string) { // include other non-nullable datatypes
    if (value == null) {
      param.value = DBNull.Value;
    } else {
      param.value = value;
    }
  } else { 

    // nullable data types
    // UPDATE: HasValue is for nullable, not object type
    if (value.HasValue) // {{{=====================================================
    {
          param.value = value;
    } else 
    {
          param.value = DBNull.Value;
    }
  }
}

.
.
.
AddParameter(cmd, "@MiddleName", SqlDbType.VarChar, MiddleName);

#1


50  

Cast either of your values to object and it will compile.

将你的任何一个值转换为对象,它将被编译。

myCmd.Parameters.Add("@MiddleName", MiddleName==null ? (object)DBNull.Value : MiddleName);

#2


15  

You can avoid the explicit cast to object using SqlString.Null instead of DBNull.Value:

您可以避免使用SqlString对对象进行显式转换。Null代替DBNull.Value:

MiddleName ?? SqlString.Null

There are corresponding types for int, datetime, and so forth. Here's a code snippet with a couple more examples:

int、datetime等等都有相应的类型。这里有一个代码片段,还有几个例子:

 cmd.Parameters.AddWithValue("@StartDate", StartDate ?? SqlDateTime.Null);
 cmd.Parameters.AddWithValue("@EndDate", EndDate ?? SqlDateTime.Null);
 cmd.Parameters.AddWithValue("@Month", Month ?? SqlInt16.Null);
 cmd.Parameters.AddWithValue("@FormatID", FormatID ?? SqlInt32.Null);
 cmd.Parameters.AddWithValue("@Email", Email ?? SqlString.Null);
 cmd.Parameters.AddWithValue("@ZIP", ZIP ?? SqlBoolean.Null);

#3


11  

Personally this is what I would do with an extension method (make sure this goes into a static class)

就我个人而言,这就是我对扩展方法所做的(确保它进入一个静态类)

public static object GetStringOrDBNull(this string obj)
{
    return string.IsNullOrEmpty(obj) ? DBNull.Value : (object) obj
}

Then you'd have

然后你会

myCmd.Parameters.Add("@MiddleName", MiddleName.GetStringOrDBNull());

#4


6  

myCmd.Parameters.Add("@MiddleName", MiddleName ?? (object)DBNull.Value);

#5


2  

@David Thanks for your suggestion. The following method works great!

谢谢你的建议。下面的方法非常有用!

MiddleName ?? (object)DBNull.Value

#6


1  

Yeap, we'd all love to do myCmd.Parameters.Add("@MiddleName", MiddleName ?? DBNull.Value);. Or better still, have the freakin' SqlClient layer understand that CLR null should be mapped to DBNull.Value when adding a parameter. Unfortunately the .Net type system closes the first alternative, and the implementation of SqlClient closes the second.

是的,我们都喜欢做mycmd .参数。Add(“@MiddleName MiddleName ? ?DBNull.Value);。或者更好的是,让freakin' SqlClient层理解CLR null应该映射到DBNull。添加参数时的值。不幸的是。net类型系统关闭了第一个选项,SqlClient的实现关闭了第二个选项。

I'd go with a well known function name, like Coalesce or IsNull. Any DB developer will recognize what they do in an instant, from the name alone.

我将使用一个众所周知的函数名,比如Coalesce或IsNull。任何DB开发人员都能一眼就认出他们所做的事情。

#7


1  

I'd rather give you two totally different suggestions:

我宁愿给你两个完全不同的建议:

  1. Use an ORM. There are plenty of non-intrusive ORM tools.

    使用ORM。有大量的非侵入性ORM工具。

  2. Write your own wrapper for building commands, with a cleaner interface. Something like:

    为构建命令编写自己的包装器,使用更简洁的接口。喜欢的东西:

    public class MyCommandRunner {
      private SqlCommand cmd;
    
      public MyCommandRunner(string commandText) {
        cmd = new SqlCommand(commandText);
      }
    
      public void AddParameter(string name, string value) {
        if (value == null)
         cmd.Parameters.Add(name, DBNull.Value);
        else
          cmd.Parameters.Add(name, value);
      }
    
      // ... more AddParameter overloads
    }
    

If you rename your AddParameter methods to just Add, you can use it in a very slick way:

如果您将AddParameter方法重命名为Add,您可以非常巧妙地使用它:

var cmd = new MyCommand("INSERT ...")
  {
    { "@Param1", null },
    { "@Param2", p2 }
  };

#8


1  

I would suggest using nullable properties instead of public fields and an 'AddParameter' method (don't know if this code is optimized or correct, just off the top of my head):

我建议使用nullable属性,而不是public字段和“AddParameter”方法(不知道这段代码是否经过了优化或正确,我不知道):


private string m_MiddleName;

public string MiddleName
{
  get { return m_MiddleName; }
  set { m_MiddleName = value; }
}

.
.
.

public static void AddParameter(SQLCommand cmd, string parameterName, SQLDataType dataType, object value)
{
  SQLParameter param = cmd.Parameters.Add(parameterName, dataType);

  if (value is string) { // include other non-nullable datatypes
    if (value == null) {
      param.value = DBNull.Value;
    } else {
      param.value = value;
    }
  } else { 

    // nullable data types
    // UPDATE: HasValue is for nullable, not object type
    if (value.HasValue) // {{{=====================================================
    {
          param.value = value;
    } else 
    {
          param.value = DBNull.Value;
    }
  }
}

.
.
.
AddParameter(cmd, "@MiddleName", SqlDbType.VarChar, MiddleName);