处理DBNull值的最好方法是什么

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

我经常遇到从sqldataadapter返回的DataRows的问题。当我试图用如下代码填充对象时:

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

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

在这种情况下，处理DBNull值的最佳方式是什么?

14 个解决方案

#1

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:

要使类型“nullable”附加一个问号到该类型，例如:

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”关键字而不是强制执行。您只能在nullable类型上使用“as”关键字，因此请确保您正在对已经可以为空的对象(如string)进行强制转换，或者使用上面提到的nullable类型。原因是

If a type is nullable, the "as" keyword returns null if a value is DBNull.
如果类型为nullable，则“as”关键字返回null，如果值为DBNull。
It's ever-so-slightly faster than casting though 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.
它比施法快得多，虽然只是在某些情况下。这本身并不是一个足够好的理由来使用它，但是加上上面的理由是有用的。

I'd recommend doing something like this

我建议做这样的事情

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

In the case above, if row comes back as DBNull, then value will become null instead of throwing an exception. Be aware that if your DB query changes the columns/types being returned, using as will cause your code to silently fail and make values simple null instead 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.

在上面的例子中，如果行返回为DBNull，那么值将变为null，而不是抛出异常。请注意,如果您的数据库查询更改列/返回类型,使用将导致代码默默地失败,使简单的零值而不是抛出相应的异常错误数据时返回,所以建议你在其他方面测试来验证您的查询以确保数据完整性作为你的代码库的发展。

#2

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:

但是，我将强调，如果可以使用的话，可以使用空类型是更好的方法。其理由是，对于不可空类型，您将不得不使用“神奇数字”来表示null。例如，如果您将一列映射到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.

为了解决这个问题，创建了可空类型。话虽如此，如果您使用的是框架的旧版本，或者为不理解可空类型的人工作，那么代码示例将发挥作用。

#3

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();

#4

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，但如果列不是null，则返回整数值:

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优胜者的方法相同——但我发现，如果您正在从datareader中读取多个列，那么将所有的列读行一个接一个地排列起来，这样更容易发现错误:

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"]);

#5

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，那么采用这种方法可以在整个应用程序中修复问题，而不会使代码混乱。

#6

DBNull implements .ToString() like everything else. No need to do anything. Instead of the hard cast, call the object's .ToString() method.

DBNull实现. tostring()和其他所有东西一样。没有必要做任何事。而不是硬类型转换，调用对象的. tostring()方法。

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

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

this becomes:

这就变成了:

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

DBNull.ToString() returns string.Empty

DBNull.ToString()返回string.Empty

I would imagine this is the best practice you're looking for

我想这是你在寻找的最佳实践

#7

It is worth mentioning, that DBNull.Value.ToString() equals String.Empty

值得一提的是，DBNull.Value.ToString()等于String.Empty

You can use this to your advantage:

你可以利用这个优势:

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

However, that only works for Strings, for everything else I would use the linq way or a extension method. For myself, I have written a little extension method that checks for DBNull and even does the casting via Convert.ChangeType(...)

但是，这只适用于字符串，对于其他所有我将使用linq方法或扩展方法的字符串。对于我自己，我编写了一个小的扩展方法，它检查DBNull，甚至通过Convert.ChangeType(…)进行转换。

int value = row.GetValueOrDefault<int>("count");
int value = row.GetValueOrDefault<int>("count", 15);

#8

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(对象obj)返回Convert.ToInt64(obj)，除非obj是DBNull，在这种情况下，它将返回0。

#9

Often when working with DataTables you have to deal with this cases, where the row field can be either null or DBNull, normally I deal with that like this:

通常，在处理datatable时，你必须处理这样的情况，即行字段可以为null或DBNull，通常我会这样处理:

string myValue = (myDataTable.Rows[i]["MyDbNullableField"] as string) ?? string.Empty;

The 'as' operator returns null for invalid cast's, like DBNull to string, and the '??' returns the term to the right of the expression if the first is null.

“as”操作符返回无效转换的null，比如DBNull到string，而“?”如果第一个表达式为空，则返回表达式右边的项。

#10

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

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

#11

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进行检查时遇到了问题。值，所以我做了一些稍微不同的事情在DataRow对象中使用了一个属性:

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

#12

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

就在几天前，Brad Abrams在http://blogs.msdn.com/brada/archive/2009/02/09框架-设计-指南-系统-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?

#13

You should also look at the extension methods. Here are some examples to deal with this scenerio.

您还应该查看扩展方法。下面是一些处理这个场景的例子。

#14

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 link that shows how to do it if you are interested

这样做很简单，但是会增加一些开销，尤其是在处理大型数据时。如果你感兴趣的话，可以查看这个链接，看看该如何做

#1