SQL 如果值为空，如何将值返回给 sqldatareader？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1513438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I return a value to a sqldatareader if value is null?
提问by Cunners
I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below:
我目前正在使用 sql 数据阅读器（在 vb.net 中）通过存储的 proc 从 SQL Server 2008 数据库中提取文章对象。该对象的一部分包括如下所示的两个属性：
theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))
theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdinal("Relevance"))))
My problem is that the Truthfulness and Relevance may return a null value and this is causing the function to fall over.
我的问题是真实性和相关性可能会返回一个空值，这会导致函数失败。
I think I understand why. I am asking for an integer value (getin32) and because null is returned it fails.
我想我明白为什么了。我要求一个整数值 (getin32) 并且因为返回 null 它失败。
How do I accommodate the null value from the database so it does not fall over?
如何容纳数据库中的空值，使其不会倒塌？
回答by marc_s
You can check whether or not a given ordinal position is null using .IsDBNull()and then do something - e.g. set your value to -1 or something:
您可以检查给定的序数位置是否为空.IsDBNull()，然后执行某些操作 - 例如将您的值设置为 -1 或其他内容：
int myOrdinal = myReader.GetOrdinal("Truthfullness");
if(myReader.IsDBNull(myOrdinal))
{
theArticle.Truthfulness = -1;
}
else
{
theArticle.Truthfulness = myReader.GetInt32(myOrdinal);
}
As Mike Hofer points out in his answer, you could also wrap all this logic into an extension method:
正如 Mike Hofer 在他的回答中指出的那样，您还可以将所有这些逻辑包装到一个扩展方法中：
public static class SqlDataReaderExtensions
{
public static int SafeGetInt32(this SqlDataReader reader,
string columnName, int defaultValue)
{
int ordinal = reader.GetOrdinal(columnName);
if(!reader.IsDbNull(ordinal))
{
return reader.GetInt32(ordinal);
}
else
{
return defaultValue;
}
}
}
and then just use that "SafeGetInt32" method instead:
然后只需使用“SafeGetInt32”方法：
theArticle.Truthfulness = myReader.SafeGetInt32("Truthfullness", -1);
Marc
马克
回答by KM?n
Did you check, SqlDataReader.IsDBNull Method? Probably something like:
你检查了，SqlDataReader.IsDBNull 方法吗？大概是这样的：
if(myReader.IsDBNull(myReader.GetOrdinal("Truthfulness"))
theArticle.Truthfulness = string.Empty;
else
theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))
回答by Mike Hofer
You know, I deal with this all the time in Oracle. To clean the code up, I wrote a set of extension methods to simplify the operation:
你知道，我一直在 Oracle 中处理这个问题。为了清理代码，我写了一组扩展方法来简化操作：
using System.Data.OracleClient;
public static class OracleDataReaderExtensions
{
public static int GetInt32(this OracleDataReader reader, string columnName, int defaultValue)
{
return reader.GetInt32(reader.GetOrdinal(columnName)) != DbNull.Value ?
reader.GetInt32(reader.GetOrdinal(columnName)) :
defaultValue;
}
}
Create a separate overload for each type you want to return. I primarily work with string, int, date, and decimal. Remember YAGNI (you don't need to work with every type supported by the reader, only those you actually use.)
为要返回的每种类型创建单独的重载。我主要使用字符串、整数、日期和十进制。记住 YAGNI（您不需要使用阅读器支持的所有类型，只需要使用您实际使用的类型。）
An extension class like this for SQL Server is really easy to write, and will VASTLY simplify your work. Trust me on that.Would I lie to you? :)
像这样的 SQL Server 扩展类真的很容易编写，并且会极大地简化您的工作。相信我。我会骗你吗？:)
回答by cyberenergy
This generic version may be of use:
这个通用版本可能有用：
private T ValueOrDefault<T>(System.Data.IDataReader rdr, string columnName)
{
T vod = default(T);
try
{
int idx = rdr.GetOrdinal(columnName);
if (!rdr.IsDBNull(idx))
return (T)rdr[idx];
}
catch (IndexOutOfRangeException) { }
return vod;
}
Could be extended to catch InvalidCastException, or use Convert.ChangeType instead of casting?
是否可以扩展以捕获 InvalidCastException，或使用 Convert.ChangeType 而不是强制转换？
回答by Price Brattin
Here is what we use on SQLServer and it works like a charm:
这是我们在 SQLServer 上使用的内容，它的工作原理非常迷人：
...
Dim X as Object = pbDr("TotAmt") 'dr is dim'ed as a DataReader
...
Public Function pbDr(ByVal drName As String) As Object
Dim SQLError As SqlClient.SqlException
Dim IsNull As Boolean
Dim Ordinal, DispNbr As Integer
Try
Ordinal = dr.GetOrdinal(drName)
IsNull = dr.IsDBNull(Ordinal)
If IsNull Then
Dim Dbtype As String = dr.GetFieldType(Ordinal).ToString
If Dbtype = "System.String" Then
Return ""
ElseIf Dbtype = "System.Int32" _
OrElse Dbtype = "System.Double" _
OrElse Dbtype = "System.Decimal" _
OrElse Dbtype = "System.Int16" Then
Return 0
Else
MsgBox("Print This Screen And Send To Support" _
& "pbdr-Object = " & Dbtype, MsgBoxStyle.Critical)
Return ""
End If
Else
Return dr(Ordinal)
End If
Catch sqlerror
Call DispSQLError(SQLError, "pbDr")
pbDr = ""
End Try
End Function
回答by ZXX
IsDbNull(int) is usually much slower that using methods like GetSqlInt32 and then comparing to DBNull.Value or using it's own .IsNull Like:
IsDbNull(int) 通常比使用 GetSqlInt32 等方法然后与 DBNull.Value 比较或使用它自己的 .IsNull 慢得多，例如：
public static int Int32(this SqlDataReader r, int ord)
{
var t = r.GetSqlInt32(ord);
return t.IsNull ? default(int) : t.Value;
}
Tried a few template solutions but to no avail so far. The problem is that all Sql-types (SqlInt32 here) types are actually structs and while they all have .Value property C# doesn't have real templates to handle that. Also they have their own INullable interface which has only .IsNull and is not conpatible with Nyllable<>.
尝试了一些模板解决方案，但到目前为止都无济于事。问题是所有 Sql 类型（此处为 SqlInt32）类型实际上都是结构体，虽然它们都具有 .Value 属性，但 C# 没有真正的模板来处理它。他们也有自己的 INullable 接口，它只有 .IsNull 并且与 Nyllable<> 不兼容。
I suspect that one would need full set of Sql-types as C# templates or to add ICOnvertible to them in order to be able to have just one or two templated methods.
我怀疑需要一整套 Sql 类型作为 C# 模板或向它们添加 ICOnvertible 以便能够只有一两个模板化方法。
If someone has maybe an idea with a functional trick or two speak up :-)
如果有人可能有一个或两个功能技巧的想法，请说出来:-)
回答by Daniel Hilgarth
Nowadays, you probably want the null if the database returns it and as such you would use a Nullable<int>:
如今，如果数据库返回它，您可能需要 null，因此您将使用Nullable<int>：
public static class Extensions
{
public static int? GetNullableInt32(this SqlDataReader reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
return null;
return reader.GetInt32(ordinal);
}
public static long? GetNullableInt64(this SqlDataReader reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
return null;
return reader.GetInt64(ordinal);
}
}
