Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dealing with DBNull.Value

Tags:

c#

.net

dbnull

I frequently have to deal with DataTables connected to grid controls, custom updating always seems to produce a lot of code related to DBNull.Value. I saw a similar question here but think there must be a better answer:

What is the best way to deal with DBNull's

The thing I find is I tend to encapsulate my database updates in methods so I end up with code like below where I move the DBNull.value to a nullable type and then back for the update:

private void UpdateRowEventHandler(object sender, EventArgs e)
{
    Boolean? requiresSupport = null;
    if (grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport) != DBNull.Value)
        requiresSupport = (bool)grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport);

    AdditionalSupport.UpdateASRecord(year, studentID, requiresSupport)
}

internal static void UpdateASRecord(
        string year,
        string studentID,            
        bool? requiresSupport)
    {
        List<SqlParameter> parameters = new List<SqlParameter>();

        parameters.Add(new SqlParameter("@year", SqlDbType.Char, 4) { Value = year });
        parameters.Add(new SqlParameter("@student_id", SqlDbType.Char, 11) { Value = studentID });

        if (requiresSupport == null)
            parameters.Add(new SqlParameter("@requires_support", SqlDbType.Bit) { Value = DBNull.Value });
        else
            parameters.Add(new SqlParameter("@requires_support", SqlDbType.Bit) { Value = requiresSupport });

        //execute sql query here to do update
    }

That was just an example of the flow and not working code. I realize I could do things like pass objects or swallow potential casting problems using "as type" to get DBUll straight to null but both of these to me appear to hide potential errors, I like the type safety of the method with nullable types.

Is there a cleaner method to do this while maintaining type safety?

like image 627
PeteT Avatar asked Aug 17 '10 09:08

PeteT


People also ask

What does DBNull value mean?

The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column.

What is the difference between Null and DBNull?

Null is similar to zero pointer in C++. So it is a reference which not pointing to any value. DBNull. Value is completely different and is a constant which is returned when a field value contains NULL.

How do you handle database NULL values in C #?

Use of NULL Values in C#Any type is known as nullable if you can assign a value or null to this variable it means that the type will have no value. In C# all reference types like string are of a nullable type, but the other types like int32 are not nullable type. A nullable type has two members, HasValue.

What is DBNull value in VB net?

DBNull is a singleton class, which means only this instance of this class can exist. If a database field has missing data, you can use the DBNull. Value property to explicitly assign a DBNull object value to the field.


2 Answers

A couple of (very) simple generic helper methods might at least concentrate the test into one piece of code:

static T FromDB<T>(object value)
{
    return value == DBNull.Value ? default(T) : (T)value;
}

static object ToDB<T>(T value)
{
    return value == null ? (object) DBNull.Value : value;
}

These methods can then be used where appropriate:

private void UpdateRowEventHandler(object sender, EventArgs e)
{
    AdditionalSupport.UpdateASRecord(year, studentID, 
        FromDB<Boolean?>(grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport)));
}

internal static void UpdateASRecord(
        string year,
        string studentID,
        bool? requiresSupport)
{
    List<SqlParameter> parameters = new List<SqlParameter>();

    parameters.Add(new SqlParameter("@year", SqlDbType.Char, 4) { Value = year });
    parameters.Add(new SqlParameter("@student_id", SqlDbType.Char, 11) { Value = studentID });
    parameters.Add(new SqlParameter("@requires_support", SqlDbType.Bit) { Value = ToDB(requiresSupport) });

    //execute sql query here to do update
}
like image 50
Fredrik Mörk Avatar answered Nov 16 '22 01:11

Fredrik Mörk


I don't see what's wrong with as-casting and null coalescing.

as-casting is used for reading:

bool? requiresSupport =
  grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport) as bool?;
AdditionalSupport.UpdateASRecord(year, studentID, requiresSupport);

null coalescing is used for writing:

parameters.Add(new SqlParameter("@student_id", SqlDbType.Char, 11)
  { Value = studentID });
parameters.Add(new SqlParameter("@requires_support", SqlDbType.Bit)
  { Value = (object)requiresSupport ?? DBNull.Value });

Both of these are completely typesafe and do not "hide" errors.

If you really want, you can wrap these into static methods, so you end up with this for reading:

//bool? requiresSupport =
//  grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport) as bool?;
bool? requiresSupport = FromDBValue<bool?>(
  grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport));

and this for writing:

//parameters.Add(new SqlParameter("@requires_support", SqlDbType.Bit)
//  { Value = (object)requiresSupport ?? DBNull.Value });
parameters.Add(new SqlParameter("@requires_support", SqlDbType.Bit)
  { Value = ToDBValue(requiresSupport) });

The static method code is slightly cleaner in the writing case, but the intent is less clear (especially in the reading case).

like image 23
Stephen Cleary Avatar answered Nov 16 '22 00:11

Stephen Cleary