处理DBNull.Value [英] Dealing with DBNull.Value

问题描述

我经常不得不处理连接到网格控件的DataTables，自定义更新似乎总是产生大量与DBNull.Value相关的代码.我在这里看到了类似的问题，但认为必须有更好的答案:

处理DBNull的最佳方法是什么

我发现的是我倾向于将数据库更新封装在方法中，因此最终得到如下代码，在该代码中，我将DBNull.value移为可空类型，然后返回进行更新:

  private void UpdateRowEventHandler(object sender，EventArgs e){布尔值?requireSupport = null;如果(grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport)！= DBNull.Value)requireSupport =(bool)grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport);AdditionalSupport.UpdateASRecord(年，学生ID，RequirementsSupport)}内部静态void UpdateASRecord(字符串年份字符串studentID，笨蛋?requireSupport){列表< SqlParameter>参数=新列表< SqlParameter>();parameters.Add(new SqlParameter("@ year"，SqlDbType.Char，4){值=年});parameters.Add(新SqlParameter("@ student_id"，SqlDbType.Char，11){Value = studentID});如果(requiresSupport == null)parameters.Add(新SqlParameter("@ requires_support"，SqlDbType.Bit){值= DBNull.Value});别的parameters.Add(new SqlParameter("@ requires_support"，SqlDbType.Bit){值= requireSupport});//在此处执行sql查询以进行更新} 

那只是流程的一个示例，而不是有效的代码.我意识到我可以使用作为类型"将DBUll直接变为null来做传递对象或吞下潜在转换问题之类的事情，但对我来说，这两个都似乎隐藏了潜在的错误，我喜欢具有可空类型的方法的类型安全性./p>

是否有一种更清洁的方法可以在保持类型安全的同时做到这一点?

解决方案

几个(非常)简单的通用帮助器方法可能至少将测试集中在一段代码中:

 静态T FromDB< T>(对象值){返回值== DBNull.Value?default(T):(T)值;}静态对象ToDB T(T值){返回值== null?(对象)DBNull.Value:值；} 

然后可以在适当的地方使用这些方法:

  private void UpdateRowEventHandler(object sender，EventArgs e){AdditionalSupport.UpdateASRecord(年，学生ID，FromDB< Boolean?>(grdMainLevel1.GetFocusedRowCellValue(colASRequiresSupport)));}内部静态void UpdateASRecord(字符串年份字符串studentID，笨蛋?requireSupport){列表< SqlParameter>参数=新列表< SqlParameter>();parameters.Add(new SqlParameter("@ year"，SqlDbType.Char，4){值=年});parameters.Add(新SqlParameter("@ student_id"，SqlDbType.Char，11){Value = studentID});parameters.Add(new SqlParameter("@ requires_support"，SqlDbType.Bit){Value = ToDB(requiresSupport)});//在此处执行sql查询以进行更新} 

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?

解决方案

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
}

这篇关于处理DBNull.Value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！