Handling DBNull.Value

Every ASP.NET developer uses DbNull.Value when writing code to communicate with database. But it could sometimes be quite painful when you want to write with a null-coalescing or ?: operator. The same would be when getting something from database and type checking.

For example, in this code snippet, I wrote a function which stores a comment in database. As userWebsite is an optional parameter, so it could be null and if database expects null, then you need to send DBNull.Value. To make it adjustable in ?: operator, you just have to cast into an object. like:

Value = userWebsite != null ? userWebsite : (object)DBNull.Value

I tested this code in fw 4.0 only, so I’m not sure about older versions.

internal int SaveComment(string name, string email, string comment, string? userWebsite)
        {
            SqlCommand _command = new SqlCommand
            {
                Connection = new SqlConnection { ConnectionString = "connection string" },
                CommandType = CommandType.StoredProcedure,
                CommandText = "csp_comment_save",
            };
            _command.Parameters.AddRange(new SqlParameter[] { new SqlParameter 
            { ParameterName = "name", Value = name, SqlDbType = SqlDbType.VarChar, Size = 100 },
            new SqlParameter { ParameterName = "email", Value = email, 
            SqlDbType = SqlDbType.VarChar, Size = 50 },
            new SqlParameter { ParameterName = "comment", Value = comment, 
            SqlDbType = SqlDbType.VarChar, Size = 255 },
            new SqlParameter { ParameterName = "website", Value = userWebsite != null ? 
            userWebsite : (object)DBNull.Value, SqlDbType = SqlDbType.VarChar, Size = 20 }});
            _command.Connection.Open();
            int result=_command.ExecuteNonQuery();
            _command.Connection.Close();
            return result;
        }

CodeProject