.net 检查 DBNull 然后分配给变量的最有效方法？

Most efficient way to check for DBNull and then assign to a variable?

提问by ilitirit

This question comes up occasionally, but I haven't seen a satisfactory answer.

这个问题偶尔会出现，但我还没有看到满意的答案。

A typical pattern is (row is a DataRow):

一个典型的模式是（行是一个DataRow）：

 if (row["value"] != DBNull.Value)
 {
      someObject.Member = row["value"];
 }

My first question is which is more efficient (I've flipped the condition):

我的第一个问题是哪个更有效（我已经改变了条件）：

  row["value"] == DBNull.Value; // Or
  row["value"] is DBNull; // Or
  row["value"].GetType() == typeof(DBNull) // Or... any suggestions?

Thisindicates that .GetType() should be faster, but maybe the compiler knows a few tricks I don't?

这表明 .GetType() 应该更快，但也许编译器知道一些我不知道的技巧？

Second question, is it worth caching the value of row["value"] or does the compiler optimize the indexer away anyway?

第二个问题，值得缓存 row["value"] 的值还是编译器优化索引器？

For example:

例如：

  object valueHolder;
  if (DBNull.Value == (valueHolder = row["value"])) {}

Notes:

笔记：

1. row["value"] exists.
2. I don't know the column index of the column (hence the column name lookup).
3. I'm asking specifically about checking for DBNull and then assignment (not about premature optimization, etc.).

1. 行[“值”] 存在。
2. 我不知道列的列索引（因此列名查找）。
3. 我特别询问检查 DBNull 然后分配（不是关于过早优化等）。

I benchmarked a few scenarios (time in seconds, 10,000,000 trials):

我对几个场景进行了基准测试（时间以秒为单位，10,000,000 次试验）：

row["value"] == DBNull.Value: 00:00:01.5478995
row["value"] is DBNull: 00:00:01.6306578
row["value"].GetType() == typeof(DBNull): 00:00:02.0138757

Object.ReferenceEquals has the same performance as "=="

Object.ReferenceEquals 具有与“==”相同的性能

The most interesting result? If you mismatch the name of the column by case (for example, "Value" instead of "value", it takes roughly ten times longer (for a string):

最有趣的结果？如果按大小写不匹配列的名称（例如，“值”而不是“值”，则需要大约十倍的时间（对于字符串）：

row["Value"] == DBNull.Value: 00:00:12.2792374

The moral of the story seems to be that if you can't look up a column by its index, then ensure that the column name you feed to the indexer matches the DataColumn's name exactly.

这个故事的寓意似乎是，如果您不能通过索引查找列，那么请确保您提供给索引器的列名称与 DataColumn 的名称完全匹配。

Caching the value also appears to be nearly twiceas fast:

缓存值的速度似乎也快了两倍：

No Caching: 00:00:03.0996622
With Caching: 00:00:01.5659920

So the most efficient method seemsto be:

所以最有效的方法似乎是：

 object temp;
 string variable;
 if (DBNull.Value != (temp = row["value"]))
 {
      variable = temp.ToString();
 }

回答by Dan Tao

I must be missing something. Isn't checking for DBNullexactly what the DataRow.IsNullmethod does?

我肯定错过了什么。不检查DBNull该DataRow.IsNull方法的确切作用吗？

I've been using the following two extension methods:

我一直在使用以下两种扩展方法：

public static T? GetValue<T>(this DataRow row, string columnName) where T : struct
{
    if (row.IsNull(columnName))
        return null;

    return row[columnName] as T?;
}

public static string GetText(this DataRow row, string columnName)
{
    if (row.IsNull(columnName))
        return string.Empty;

    return row[columnName] as string ?? string.Empty;
}

Usage:

用法：

int? id = row.GetValue<int>("Id");
string name = row.GetText("Name");
double? price = row.GetValue<double>("Price");

If you didn't want Nullable<T>return values for GetValue<T>, you could easily return default(T)or some other option instead.

如果您不想为Nullable<T>返回值GetValue<T>，您可以轻松地返回default(T)或其他一些选项。

---

---

On an unrelated note, here's a VB.NET alternative to Stevo3000's suggestion:

在一个不相关的说明中，这是 Stevo3000 建议的 VB.NET 替代方案：

oSomeObject.IntMember = If(TryConvert(Of Integer)(oRow("Value")), iDefault)
oSomeObject.StringMember = If(TryCast(oRow("Name"), String), sDefault)

Function TryConvert(Of T As Structure)(ByVal obj As Object) As T?
    If TypeOf obj Is T Then
        Return New T?(DirectCast(obj, T))
    Else
        Return Nothing
    End If
End Function

回答by Jon Grant

You should use the method:

您应该使用以下方法：

Convert.IsDBNull()

Considering it's built-in to the Framework, I would expect this to be the most efficient.

考虑到它是框架内置的，我希望这是最有效的。

I'd suggest something along the lines of:

我会建议一些类似的东西：

int? myValue = (Convert.IsDBNull(row["column"]) ? null : (int?) Convert.ToInt32(row["column"]));

And yes, the compiler should cache it for you.

是的，编译器应该为你缓存它。

回答by Marc Gravell

The compiler won't optimise away the indexer (i.e. if you use row["value"] twice), so yes it is slightlyquicker to do:

编译器不会优化索引器（即，如果您使用 row["value"] 两次），所以是的，这样做会稍微快一点：

object value = row["value"];

and then use value twice; using .GetType() risks issues if it is null...

然后使用 value 两次；如果 .GetType() 为空，则使用 .GetType() 有风险...

DBNull.Valueis actually a singleton, so to add a 4th option - you could perhaps use ReferenceEquals - but in reality, I think you're worrying too much here... I don't think the speed different between "is", "==" etc is going to be the cause of any performance problem you are seeing. Profile your entire codeand focus on something that matters... it won't be this.

DBNull.Value实际上是一个单例，所以添加第四个选项 - 你也许可以使用 ReferenceEquals - 但实际上，我认为你在这里担心太多......我不认为“是”，“==”之间的速度不同" 等将成为您所看到的任何性能问题的原因。分析您的整个代码并专注于重要的事情......它不会是这个。

回答by stevehipwell

I would use the following code in C# (VB.NETis not as simple).

我会在 C# 中使用以下代码（VB.NET不是那么简单）。

The code assigns the value if it is not null/DBNull, otherwise it asigns the default which could be set to the LHS value allowing the compiler to ignore the assign.

如果该值不是 null/DBNull，则代码分配该值，否则它分配默认值，该默认值可以设置为 LHS 值，允许编译器忽略分配。

oSomeObject.IntMemeber = oRow["Value"] as int? ?? iDefault;
oSomeObject.StringMember = oRow["Name"] as string ?? sDefault;

回答by nawfal

I feel only very few approaches here doesn't risk the prospect OP the most worry (Marc Gravell, Stevo3000, Richard Szalay, Neil, Darren Koppand) and most are unnecessarily complex. Being fully aware this is useless micro-optimization, let me say you should basically employ these:

我觉得这里只有很少的方法不会让潜在的 OP 最担心（Marc Gravell、Stevo3000、Richard Szalay、Neil、Darren Koppand），而且大多数方法都不必要地复杂。充分意识到这是无用的微优化，让我说你应该基本上使用这些：

1) Don't read the value from DataReader/DataRow twice - so either cache it before null checks and casts/conversions or even better directly pass your record[X]object to a custom extension method with appropriate signature.

1) 不要从 DataReader/DataRow 读取值两次 - 所以要么在空检查和强制转换/转换之前缓存它，要么更好地直接将您的record[X]对象传递给具有适当签名的自定义扩展方法。

2) To obey the above, do not use built in IsDBNullfunction on your DataReader/DataRow since that calls the record[X]internally, so in effect you will be doing it twice.

2) 为了遵守上述规定，不要在IsDBNullDataReader/DataRow 上使用内置函数，因为它会调用record[X]内部函数，因此实际上您将执行两次。

3) Type comparison will be always slower than value comparison as a general rule. Just do record[X] == DBNull.Valuebetter.

3) 作为一般规则，类型比较总是比值比较慢。只要record[X] == DBNull.Value做得更好。

4) Direct casting will be faster than calling Convertclass for converting, though I fear the latter will falter less.

4）直接转换将比调用Convert类进行转换更快，尽管我担心后者会少一些。

5) Lastly, accessing record by index rather than column name will be faster again.

5）最后，通过索引而不是列名访问记录将再次更快。

---

---

I feel going by the approaches of Szalay, Neil and Darren Koppand will be better. I particularly like Darren Koppand's extension method approach which takes in IDataRecord(though I would like to narrow it down further to IDataReader) and index/column name.

我觉得遵循 Szalay、Neil 和 Darren Koppand 的方法会更好。我特别喜欢 Darren Koppand 的扩展方法方法，它采用IDataRecord（尽管我想进一步缩小到IDataReader）和索引/列名称。

Take care to call it:

注意调用它：

record.GetColumnValue<int?>("field");

and not

并不是

record.GetColumnValue<int>("field");

in case you need to differentiate between 0and DBNull. For example, if you have null values in enum fields, otherwise default(MyEnum)risks first enum value being returned. So better call record.GetColumnValue<MyEnum?>("Field").

如果您需要区分0和DBNull。例如，如果您在枚举字段中default(MyEnum)有空值，否则可能会返回第一个枚举值。所以最好打电话record.GetColumnValue<MyEnum?>("Field")。

Since you're reading from a DataRow, I would create extension method for both DataRowand IDataReaderby DRYingcommon code.

由于您正在阅读DataRow，因此我将为两者创建扩展方法DataRow并IDataReader通过DRYing公共代码。

public static T Get<T>(this DataRow dr, int index, T defaultValue = default(T))
{
    return dr[index].Get<T>(defaultValue);
}

static T Get<T>(this object obj, T defaultValue) //Private method on object.. just to use internally.
{
    if (obj.IsNull())
        return defaultValue;

    return (T)obj;
}

public static bool IsNull<T>(this T obj) where T : class 
{
    return (object)obj == null || obj == DBNull.Value;
} 

public static T Get<T>(this IDataReader dr, int index, T defaultValue = default(T))
{
    return dr[index].Get<T>(defaultValue);
}

So now call it like:

所以现在称之为：

record.Get<int>(1); //if DBNull should be treated as 0
record.Get<int?>(1); //if DBNull should be treated as null
record.Get<int>(1, -1); //if DBNull should be treated as a custom value, say -1

I believe this is how it should have been in the framework (instead of the record.GetInt32, record.GetStringetc methods) in the first place - no run-time exceptions and gives us the flexibility to handle null values.

我相信这就是它应该在框架中（而不是record.GetInt32, record.GetStringetc 方法）首先的方式 - 没有运行时异常，并为我们提供了处理空值的灵活性。

From my experience I had less luck with one generic method to read from the database. I always had to custom handle various types, so I had to write my own GetInt, GetEnum, GetGuid, etc. methods in the long run. What if you wanted to trim white spaces when reading string from db by default, or treat DBNullas empty string? Or if your decimal should be truncated of all trailing zeroes. I had most trouble with Guidtype where different connector drivers behaved differently that too when underlying databases can store them as string or binary. I have an overload like this:

根据我的经验，使用一种通用方法从数据库中读取数据的运气较差。我总是不得不自定义处理各种类型的，所以我不得不写我自己的GetInt，GetEnum，GetGuid等从长远看方法。如果您想在默认情况下从 db 读取字符串时修剪空格，或者将其DBNull视为空字符串怎么办？或者，如果您的小数点应该被所有尾随零截断。Guid当底层数据库可以将它们存储为字符串或二进制时，我在类型方面遇到了最大的麻烦，其中不同的连接器驱动程序的行为也不同。我有这样的重载：

static T Get<T>(this object obj, T defaultValue, Func<object, T> converter)
{
    if (obj.IsNull())
        return defaultValue;

    return converter  == null ? (T)obj : converter(obj);
}

With Stevo3000's approach, I find the calling a bit ugly and tedious, and it will be harder to make a generic function out of it.

使用 Stevo3000 的方法，我发现调用有点丑陋和乏味，而且很难用它来制作通用函数。

回答by Saleh Najar

There is the troublesome case where the object could be a string. The below extension method code handles all cases. Here's how you would use it:

有一个麻烦的情况是对象可能是一个字符串。下面的扩展方法代码处理所有情况。以下是您将如何使用它：

    static void Main(string[] args)
    {
        object number = DBNull.Value;

        int newNumber = number.SafeDBNull<int>();

        Console.WriteLine(newNumber);
    }



    public static T SafeDBNull<T>(this object value, T defaultValue) 
    {
        if (value == null)
            return default(T);

        if (value is string)
            return (T) Convert.ChangeType(value, typeof(T));

        return (value == DBNull.Value) ? defaultValue : (T)value;
    } 

    public static T SafeDBNull<T>(this object value) 
    { 
        return value.SafeDBNull(default(T)); 
    } 

回答by Dylan Beattie

I personally favour this syntax, which uses the explicit IsDbNull method exposed by IDataRecord, and caches the column index to avoid a duplicate string lookup.

我个人喜欢这种语法，它使用由 公开的显式 IsDbNull 方法IDataRecord，并缓存列索引以避免重复的字符串查找。

Expanded for readability, it goes something like:

为提高可读性而扩展，它类似于：

int columnIndex = row.GetOrdinal("Foo");
string foo; // the variable we're assigning based on the column value.
if (row.IsDBNull(columnIndex)) {
  foo = String.Empty; // or whatever
} else { 
  foo = row.GetString(columnIndex);
}

Rewritten to fit on a single line for compactness in DAL code - note that in this example we're assigning int bar = -1if row["Bar"]is null.

重写以适应 DAL 代码的紧凑性以适合一行 - 请注意，在此示例中，我们分配int bar = -1ifrow["Bar"]为 null。

int i; // can be reused for every field.
string foo  = (row.IsDBNull(i  = row.GetOrdinal("Foo")) ? null : row.GetString(i));
int bar = (row.IsDbNull(i = row.GetOrdinal("Bar")) ? -1 : row.GetInt32(i));

The inline assignment can be confusing if you don't know it's there, but it keeps the entire operation on one line, which I think enhances readability when you're populating properties from multiple columns in one block of code.

如果您不知道它的存在，内联分配可能会令人困惑，但它将整个操作保留在一行上，我认为当您在一个代码块中从多列填充属性时，这会增强可读性。

回答by Richard Szalay

Not that I've done this, but you could get around the double indexer call and still keep your code clean by using a static / extension method.

并不是说我已经这样做了，但是您可以通过使用静态/扩展方法绕过双索引器调用并仍然保持代码清洁。

Ie.

IE。

public static IsDBNull<T>(this object value, T default)
{
    return (value == DBNull.Value)
        ? default
        : (T)value;
}

public static IsDBNull<T>(this object value)
{
    return value.IsDBNull(default(T));
}

Then:

然后：

IDataRecord record; // Comes from somewhere

entity.StringProperty = record["StringProperty"].IsDBNull<string>(null);
entity.Int32Property = record["Int32Property"].IsDBNull<int>(50);

entity.NoDefaultString = record["NoDefaultString"].IsDBNull<string>();
entity.NoDefaultInt = record["NoDefaultInt"].IsDBNull<int>();

Also has the benefit of keeping the null checking logic in one place. Downside is, of course, that it's an extra method call.

还有一个好处是将空检查逻辑保留在一个地方。当然，缺点是它是一个额外的方法调用。

Just a thought.

只是一个想法。

回答by bdukes

I try to avoid this check as much as possible.

我尽量避免这种检查。

Obviously doesn't need to be done for columns that can't hold null.

显然不需要对不能容纳的列进行null。

If you're storing in a Nullable value type (int?, etc.), you can just convert using as int?.

如果您存储在 Nullable 值类型（int?等）中，则可以使用as int?.

If you don't need to differentiate between string.Emptyand null, you can just call .ToString(), since DBNull will return string.Empty.

如果您不需要区分string.Empty和null，则可以直接调用.ToString()，因为 DBNull 将返回string.Empty。

回答by Stefan

if in a DataRow the row["fieldname"] isDbNull replace it with 0 otherwise get the decimal value:

如果在 DataRow 中 row["fieldname"] isDbNull 将其替换为 0 否则获取十进制值：

decimal result = rw["fieldname"] as decimal? ?? 0;