vb.net SQL Data Reader - 如何优雅地处理空列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19133529/
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
SQL Data Reader - How to handle Null column values elegantly
提问by Robin G Brown
I'm using an SQLDataReader to retrieve values from a database which may be null. I've worked out how to handle Null string values but can't get the same trick to work with integers or booleans:
我正在使用 SQLDataReader 从可能为空的数据库中检索值。我已经研究出如何处理空字符串值，但无法使用相同的技巧来处理整数或布尔值：
Using cmd As DbCommand = store.GetStoredProcCommand("RetrievePOCO")
store.AddInParameter(cmd, "ID", DbType.Int32, ID)
Using reader As IDataReader = store.ExecuteReader(cmd)
If reader.Read() = True Then
Dim newPOCO As New POCO()
With newPOCO
'If the source column is null TryCast will return nothing without throwing an error
.StatusXML = TryCast(reader.GetString(reader.GetOrdinal("StatusXML")), String)
'How can a null integer or boolean be set elegantly?
.AppType = TryCast(reader.GetInt32(reader.GetOrdinal("AppType")), System.Nullable(Of Integer))
.Archived = TryCast(reader.GetBoolean(reader.GetOrdinal("Archived")), Boolean)
So how can a null integer or boolean be set elegantly? I've seen suggestions in C# but they don't translate correctly to VB giving a 'TryCast operand must be reference type, but integer? is a value type' compiler errors.
那么如何优雅地设置空整数或布尔值呢？我在 C# 中看到过一些建议，但它们没有正确转换为 VB，给出了“TryCast 操作数必须是引用类型，但整数？是值类型的编译器错误。
回答by dummy
I use the following function in this scenario:
我在这种情况下使用以下功能：
Public Shared Function NoNull(ByVal checkValue As Object, ByVal returnIfNull As Object) As Object
If checkValue Is DBNull.Value Then
Return returnIfNull
Else
Return checkValue
End If
End Function
Your code would look something like this:
你的代码看起来像这样：
With newPOCO
.StatusXML = NoNull(reader("StatusXML"), "")
.AppType = NoNull(reader("AppType"), -1)
.Archived = NoNull(reader("Archived"), False)
End With
Note that this function requires you to pass the value which should be used if the value is DbNUll as the second Parameter.
请注意，此函数要求您传递应在值为 DbNUll 时使用的值作为第二个参数。
回答by Steve
You can take advantage of the IsDBNullmethod of the SqlDataReader and use the VB.NET ternary operatorto assign a default value to your poco object
您可以利用SqlDataReader的IsDBNull方法并使用VB.NET 三元运算符为您的 poco 对象分配默认值
.StatusXML = If(reader.IsDBNull(reader.GetOrdinal("StatusXML")), _
"",reader.GetString(reader.GetOrdinal("StatusXML")))
It is just one line, not very elegant because you need to call two times the GetOrdinal method.
它只是一行，不是很优雅，因为您需要调用两次 GetOrdinal 方法。
回答by john rains
Public Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
If Value Is Nothing OrElse IsDBNull(Value) Then
Return DefaultValue
Else
Return Value
End If
End Function
