Archive

Posts Tagged ‘DBNull.Value’

Handling DBNull.Value

October 20, 2014 2 comments

Every asp.net developer use 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;
        }