How do I Parameterize a null string with DBNull.Value clearly and quickly

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){
      开发者_JS百科  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.

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'".

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

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

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

You can avoid the explicit cast to object using SqlString.Null instead of 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:

 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);

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());

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

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

MiddleName ?? (object)DBNull.Value

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.

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.

I'd rather give you two totally different suggestions:

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

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:

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

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):

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);