处理 DBNull.Value

发布于 2024-09-14 20:00:16 字数 1544 浏览 11 评论 0原文

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

处理 DBNull 的最佳方法是什么

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

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
    }

这只是流程的示例,而不是工作代码。我意识到我可以做一些事情,比如传递对象或使用“as type”吞掉潜在的转换问题,使 DBUll 直接为 null,但这对我来说似乎隐藏了潜在的错误,我喜欢具有可为 null 类型的方法的类型安全性。

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

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

两相知 2024-09-21 20:00:17

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

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

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

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
}

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
}
旧时浪漫 2024-09-21 20:00:17

我不明白 as 转换和 null 合并有什么问题。

as-casting 用于读取:

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

null 合并用于写入:

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

这两者都是完全类型安全的,不会“隐藏”错误。

如果你真的愿意,你可以将它们包装到静态方法中,这样你最终会得到这样的阅读:

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

和写作:

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

静态方法代码在书写情况下稍微干净一些,但其意图是不太清楚(尤其是在阅读情况下)。

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

若无相欠,怎会相见 2024-09-21 20:00:17
parameters.Add("@requires_support", SqlDbType.Bit).Value = (object)requiresSupport ?? DBNull.Value;

相同

parameters.Add("@requires_support", SqlDbType.Bit).Value = (requiresSupport != null) ? (object)requiresSupport : DBNull.Value;

这意味着与or

if (requiresSupport != null)
    parameters.Add("@requires_support", SqlDbType.Bit).Value = requiresSupport 
else
    parameters.Add("@requires_support", SqlDbType.Bit).Value = DBNull.Value;

(需要对对象进行额外的转换以消除类型歧义)

parameters.Add("@requires_support", SqlDbType.Bit).Value = (object)requiresSupport ?? DBNull.Value;

which means the same as

parameters.Add("@requires_support", SqlDbType.Bit).Value = (requiresSupport != null) ? (object)requiresSupport : DBNull.Value;

or

if (requiresSupport != null)
    parameters.Add("@requires_support", SqlDbType.Bit).Value = requiresSupport 
else
    parameters.Add("@requires_support", SqlDbType.Bit).Value = DBNull.Value;

(additional cast to object is required to remove the type ambiguity)

计㈡愣 2024-09-21 20:00:17
public static object DbNullable<T>(T? value) where T : struct
{
    if (value.HasValue)
    {
        return value.Value;
    }
    return DBNull.Value;
}

public static object ToDbNullable<T>(this T? value) where T : struct
{
    return DbNullable(value);
}

这是我的 DBNULL 帮助器的实现。用法很简单:

new SqlParameter("Option1", option1.ToDbNullable())
public static object DbNullable<T>(T? value) where T : struct
{
    if (value.HasValue)
    {
        return value.Value;
    }
    return DBNull.Value;
}

public static object ToDbNullable<T>(this T? value) where T : struct
{
    return DbNullable(value);
}

This is my implementation of DBNULL helper. The usage is simple:

new SqlParameter("Option1", option1.ToDbNullable())
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文