C# 处理 DBNull 的最佳方法是什么

What is the best way to deal with DBNull's

提问by Mykroft

I frequently have problems dealing with DataRowsreturned from SqlDataAdapters. When I try to fill in an object using code like this:

我经常在处理DataRows从SqlDataAdapters. 当我尝试使用这样的代码填充对象时：

DataRow row = ds.Tables[0].Rows[0];
string value = (string)row;

What is the best way to deal with DBNull'sin this type of situation.

DBNull's在这种情况下处理的最佳方法是什么。

采纳答案by Dan Herbert

Nullable types are good, but only for types that are not nullable to begin with.

可空类型很好，但仅适用于一开始不可为空的类型。

To make a type "nullable" append a question mark to the type, for example:

要使类型“可为空”，请在类型后附加一个问号，例如：

int? value = 5;

I would also recommend using the "as" keyword instead of casting. You can only use the "as" keyword on nullable types, so make sure you're casting things that are already nullable (like strings) or you use nullable types as mentioned above. The reasoning for this is

我还建议使用“ as”关键字而不是强制转换。您只能在可空类型上使用“as”关键字，因此请确保您正在转换已经可以为空的内容（如字符串），或者您使用如上所述的可空类型。这样做的原因是

1. If a type is nullable, the "as" keyword returns nullif a value is DBNull.
2. It's ever-so-slightly faster than castingthough only in certain cases. This on its own is never a good enough reason to use as, but coupled with the reason above it's useful.

1. 如果类型可以为空，则“ as”关键字null在值为 时返回DBNull。
2. 尽管仅在某些情况下，它比铸造快得多。这本身从来都不是一个足够好的使用理由，但结合上面的原因它是有用的。as

I'd recommend doing something like this

我建议做这样的事情

DataRow row = ds.Tables[0].Rows[0];
string value = row as string;

In the case above, if rowcomes back as DBNull, then valuewill become nullinstead of throwing an exception. Be aware that if your DB query changes the columns/types being returned, using aswill cause your code to silently failand make values simple nullinstead of throwing the appropriate exception when incorrect data is returned so it is recommended that you have tests in place to validate your queries in other ways to ensure data integrity as your codebase evolves.

在上面的情况下，如果row返回 as DBNull，value则将变成null而不是抛出异常。请注意，如果您的数据库查询更改了返回的列/类型，使用as将导致您的代码静默失败并使值变得简单，null而不是在返回不正确的数据时抛出适当的异常，因此建议您进行适当的测试以进行验证随着代码库的发展，您以其他方式进行查询以确保数据完整性。

回答by Pascal Paradis

You can also test with Convert.IsDBNull (MSDN).

您还可以使用Convert.IsDBNull (MSDN) 进行测试。

回答by Keith

Add a reference to System.Data.DataSetExtensions, that adds Linq support for querying data tables.

添加对 的引用System.Data.DataSetExtensions，这增加了对查询数据表的 Linq 支持。

This would be something like:

这将是这样的：

string value = (
    from row in ds.Tables[0].Rows
    select row.Field<string>(0) ).FirstOrDefault();

回答by Manu

I usually write my own ConvertDBNull class that wraps the built-in Convert class. If the value is DBNull it will return null if its a reference type or the default value if its a value type. Example: - ConvertDBNull.ToInt64(object obj)returns Convert.ToInt64(obj)unless obj is DBNull in which case it will return 0.

我通常编写自己的 ConvertDBNull 类来包装内置的 Convert 类。如果值为 DBNull，如果它是引用类型，它将返回 null，如果它是值类型，则返回默认值。示例： -ConvertDBNull.ToInt64(object obj)返回，Convert.ToInt64(obj)除非 obj 是 DBNull，在这种情况下它将返回 0。

回答by Daniel Auger

If you aren't using nullable types, the best thing to do is check to see if the column's value is DBNull. If it is DBNull, then set your reference to what you use for null/empty for the corresponding datatype.

如果您不使用可为空类型，最好的做法是检查列的值是否为 DBNull。如果它是 DBNull，则将您的引用设置为您用于相应数据类型的 null/empty。

DataRow row = ds.Tables[0].Rows[0];
string value;

if (row["fooColumn"] == DBNull.Value)
{
   value = string.Empty;
}
else 
{
   value = Convert.ToString(row["fooColumn"]);
}

As Manu said, you can create a convert class with an overloaded convert method per type so you don't have to pepper your code with if/else blocks.

正如 Manu 所说，您可以创建一个 convert 类，每个类型都有一个重载的 convert 方法，这样您就不必在代码中添加 if/else 块。

I will however stress that nullable types is the better route to go if you can use them. The reasoning is that with non-nullable types, you are going to have to resort to "magic numbers" to represent null. For example, if you are mapping a column to an int variable, how are you going to represent DBNull? Often you can't use 0 because 0 has a valid meaning in most programs. Often I see people map DBNull to int.MinValue, but that could potentially be problematic too. My best advice is this:

但是，我要强调的是，如果可以使用可空类型，则是更好的选择。原因是对于不可为空的类型，您将不得不求助于“幻数”来表示空值。例如，如果您将一列映射到一个 int 变量，您将如何表示 DBNull？通常您不能使用 0，因为 0 在大多数程序中具有有效含义。我经常看到人们将 DBNull 映射到 int.MinValue，但这也可能存在问题。我最好的建议是这样的：

• For columns that can be null in the database, use nullable types.
• For columns that cannot be null in the database, use regular types.

• 对于数据库中可以为空的列，请使用可空类型。
• 对于数据库中不能为空的列，使用常规类型。

Nullable types were made to solve this problem. That being said, if you are on an older version of the framework or work for someone who doesn't grok nullable types, the code example will do the trick.

可空类型就是为了解决这个问题而产生的。话虽如此，如果您使用的是旧版本的框架或为不了解可空类型的人工作，那么代码示例就可以解决问题。

回答by Dillie-O

For some reason I've had problems with doing a check against DBNull.Value, so I've done things slightly different and leveraged a property within the DataRow object:

出于某种原因，我在检查 DBNull.Value 时遇到了问题，所以我做了一些稍微不同的事情，并利用了 DataRow 对象中的一个属性：

if (row.IsNull["fooColumn"])
{
   value = string.Empty();
}
{
else
{
   value = row["fooColumn"].ToString;
}

回答by Steve Schoon

If you have control of the query that is returning the results, you can use ISNULL() to return non-null values like this:

如果您可以控制返回结果的查询，则可以使用 ISNULL() 返回非空值，如下所示：

SELECT 
  ISNULL(name,'') AS name
  ,ISNULL(age, 0) AS age
FROM 
  names

If your situation can tolerate these magic values to substitute for NULL, taking this approach can fix the issue through your entire app without cluttering your code.

如果您的情况可以容忍这些魔法值代替 NULL，那么采用这种方法可以在整个应用程序中解决问题，而不会使您的代码混乱。

回答by Meff

I always found it clear, concise, and problem free using a version of the If/Else check, only with the ternary operator. Keeps everything on one row, including assigning a default value if the column is null.

使用 If/Else 检查的一个版本，我总是发现它清晰、简洁且没有问题，只有使用三元运算符。将所有内容保留在一行上，包括在列为空时分配默认值。

So, assuming a nullable Int32 column named "MyCol", where we want to return -99 if the column is null, but return the integer value if the column is not null:

因此，假设一个名为“MyCol”的可为空的 Int32 列，如果该列为空，我们希望返回 -99，但如果该列不为空，则返回整数值：

return row["MyCol"] == DBNull.Value ? -99 : Convert.ToInt32(Row["MyCol"]);

It is the same method as the If/Else winner above - But I've found if you're reading multiple columns in from a datareader, it's a real bonus having all the column-read lines one under another, lined up, as it's easier to spot errors:

它与上面的 If/Else 获胜者的方法相同 - 但我发现如果您从数据读取器中读取多列，将所有列读取的行排成一行，这是一个真正的奖励，因为它是更容易发现错误：

Object.ID = DataReader["ID"] == DBNull.Value ? -99 : Convert.ToInt32(DataReader["ID"]);
Object.Name = DataReader["Name"] == DBNull.Value ? "None" : Convert.ToString(DataReader["Name"]);
Object.Price = DataReader["Price"] == DBNull.Value ? 0.0 : Convert.ToFloat(DataReader["Price"]);

回答by Dejan

If you are concerned with getting DBNull when expecting strings, one option is to convert all the DBNull values in the DataTable into empty string.

如果您担心在需要字符串时获取 DBNull，一种选择是将 DataTable 中的所有 DBNull 值转换为空字符串。

It is quite simple to do it but it would add some overhead especially if you are dealing with large DataTables. Check this linkthat shows how to do it if you are interested

这样做很简单，但会增加一些开销，尤其是在处理大型数据表时。如果您有兴趣，请查看此链接，该链接显示了如何操作

回答by Simon

Brad Abrams posted something related just a couple of days ago http://blogs.msdn.com/brada/archive/2009/02/09/framework-design-guidelines-system-dbnull.aspx

布拉德艾布拉姆斯几天前发布了一些相关的内容 http://blogs.msdn.com/brada/archive/2009/02/09/framework-design-guidelines-system-dbnull.aspx

In Summary "AVOID using System.DBNull. Prefer Nullable instead."

总结“避免使用 System.DBNull。更喜欢 Nullable。”

And here is my two cents (of untested code :) )

这是我的两美分（未经测试的代码:)）

// Or if (row["fooColumn"] == DBNull.Value)
if (row.IsNull["fooColumn"])
{
   // use a null for strings and a Nullable for value types 
   // if it is a value type and null is invalid throw a 
   // InvalidOperationException here with some descriptive text. 
   // or dont check for null at all and let the cast exception below bubble  
   value = null;
}
else
{
   // do a direct cast here. dont use "as", "convert", "parse" or "tostring"
   // as all of these will swallow the case where is the incorect type.
   // (Unless it is a string in the DB and really do want to convert it)
   value = (string)row["fooColumn"];
}

And one question... Any reason you are not using an ORM?

还有一个问题......你有什么理由不使用 ORM？