如何（有效地）将 SqlDataReader 字段转换（强制转换？）为其相应的 c# 类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2141575/
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 to (efficiently) convert (cast?) a SqlDataReader field to its corresponding c# type?
提问by Albireo
First, let me explain the current situation: I'm reading records from a database and putting them in an object for later use; today a question about the database type to C# type conversion (casting?) arose.
首先，让我解释一下当前的情况：我正在从数据库中读取记录并将它们放入一个对象中以备后用；今天出现了一个关于数据库类型到 C# 类型转换（转换？）的问题。
Let's see an example:
让我们看一个例子：
namespace Test
{
using System;
using System.Data;
using System.Data.SqlClient;
public enum MyEnum
{
FirstValue = 1,
SecondValue = 2
}
public class MyObject
{
private String field_a;
private Byte field_b;
private MyEnum field_c;
public MyObject(Int32 object_id)
{
using (SqlConnection connection = new SqlConnection("connection_string"))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "sql_query";
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
reader.Read();
this.field_a = reader["field_a"];
this.field_b = reader["field_b"];
this.field_c = reader["field_c"];
}
}
}
}
}
}
This is (obviously) failing because the three this.field_x = reader["field_x"];calls are throwing the Cannot implicitly convert type 'object' to 'xxx'. An explicit conversion exists (are you missing a cast?).compiler error.
这（显然）失败了，因为这三个this.field_x = reader["field_x"];调用都引发了Cannot implicitly convert type 'object' to 'xxx'. An explicit conversion exists (are you missing a cast?).编译器错误。
To correct this I currentlyknow of two ways (let's use the field_bexample): number one is this.field_b = (Byte) reader["field_b"];and number two is this.field_b = Convert.ToByte(reader["field_b"]);.
为了纠正这个问题，我目前知道两种方法（让我们使用field_b示例）：第一个是this.field_b = (Byte) reader["field_b"];，第二个是this.field_b = Convert.ToByte(reader["field_b"]);。
The problem with option number one is that DBNullfields are throwing exceptions as the cast is failing (even with nullable types as String), ant the problem with number two is that it's not preserving null values (the Convert.ToString(DBNull)yields a String.Empty), and I can't use them with enums too.
选项一的问题是DBNull字段在转换失败时抛出异常（即使可以为空类型String），第二个问题是它不保留空值（Convert.ToString(DBNull)产生 a String.Empty），我不能使用他们也有枚举。
So, after a couple of lookups on the internet and here at StackOverflow, what I came up with is:
因此，在互联网上和 StackOverflow 上进行了几次查找之后，我想到的是：
public static class Utilities
{
public static T FromDatabase<T>(Object value) where T: IConvertible
{
if (typeof(T).IsEnum == false)
{
if (value == null || Convert.IsDBNull(value) == true)
{
return default(T);
}
else
{
return (T) Convert.ChangeType(value, typeof(T));
}
}
else
{
if (Enum.IsDefined(typeof(T), value) == false)
{
throw new ArgumentOutOfRangeException();
}
return (T) Enum.ToObject(typeof(T), value);
}
}
}
This way I shouldhandle every case.
这样我应该处理每一种情况。
Question is:Am I missing something? Am I doing a WOMBAT (Waste Of Money, Brain And Time) as there's a quicker and cleaner way to do it? It's all correct? Profit?
问题是：我错过了什么吗？我是否在做 WOMBAT（浪费金钱、大脑和时间），因为有更快捷、更清洁的方法吗？都是对的吗？利润？
采纳答案by Dan Herbert
If a field allows nulls, don't use regular primitive types. Use the C# nullabletypeand the askeyword.
如果字段允许空值，请不要使用常规原始类型。使用C#nullable类型和as关键字.
int? field_a = reader["field_a"] as int?;
string field_b = reader["field_a"] as string;
Adding a ?to any non-nullable C# type makes it "nullable". Using the askeyword will attempt to cast an object to the specified type. If the cast fails (like it would if the type is DBNull), then the operator returns null.
向?任何不可为空的 C# 类型添加 a使其“可为空”。使用as关键字将尝试将对象强制转换为指定类型。如果转换失败（就像类型是 一样DBNull），则运算符返回null。
Note:Another small benefit of using asis that it is slightly fasterthan normal casting. Since it can also have some downsides, such as making it harder to track bugs if you try to cast as the wrong type, this shouldn't be considered a reason for always using asover traditional casting. Regular casting is already a fairly cheap operation.
注意：使用的另一个小好处as是它比普通铸造稍快。由于它也可能有一些缺点，例如如果您尝试将类型转换为错误的类型会使跟踪错误变得更加困难，因此不应将其视为始终使用as传统转换的原因。定期铸造已经是一个相当便宜的操作。
回答by Sam Holder
don't you want to use the reader.Get*methods? The only annoying thing is that they take column numbers so you have to wrap the accessor in a call to GetOrdinal()
你不想使用这些reader.Get*方法吗？唯一令人讨厌的是它们采用列号，因此您必须将访问器包装在对GetOrdinal()的调用中
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
reader.Read();
this.field_a = reader.GetString(reader.GetOrdinal("field_a"));
this.field_a = reader.GetDouble(reader.GetOrdinal("field_b"));
//etc
}
回答by Joel Mueller
You can make a set of extension methods, one pair per data type:
您可以创建一组扩展方法，每个数据类型一对：
public static int? GetNullableInt32(this IDataRecord dr, string fieldName)
{
return GetNullableInt32(dr, dr.GetOrdinal(fieldName));
}
public static int? GetNullableInt32(this IDataRecord dr, int ordinal)
{
return dr.IsDBNull(ordinal) ? null : (int?)dr.GetInt32(ordinal);
}
This gets a bit tedious to implement, but it's pretty efficient. In System.Data.DataSetExtensions.dll, Microsoft solved the same problem for DataSets with a Field<T>method, which generically handles multiple data types, and can turn DBNull into a Nullable.
这实现起来有点乏味，但它非常有效。在 System.Data.DataSetExtensions.dll 中，微软用一个Field<T>方法为 DataSets 解决了同样的问题，该方法一般处理多种数据类型，可以将 DBNull 变成 Nullable。
As an experiment, I once implemented an equivalent method for DataReaders, but I ended up using Reflector to borrow an internal class from DataSetExtensions (UnboxT) to do the actual type conversions efficiently. I'm not sure about the legality of distributing that borrowed class, so I probably shouldn't share the code, but it's pretty easy to look up for oneself.
作为一个实验，我曾经为 DataReaders 实现了一个等效的方法，但我最终使用 Reflector 从 DataSetExtensions (UnboxT) 借用了一个内部类来有效地进行实际的类型转换。我不确定分发那个借来的类的合法性，所以我可能不应该共享代码，但是查找自己很容易。
回答by BFree
This is how I've dealt with it in the past:
这是我过去处理它的方式：
public Nullable<T> GetNullableField<T>(this SqlDataReader reader, Int32 ordinal) where T : struct
{
var item = reader[ordinal];
if (item == null)
{
return null;
}
if (item == DBNull.Value)
{
return null;
}
try
{
return (T)item;
}
catch (InvalidCastException ice)
{
throw new InvalidCastException("Data type of Database field does not match the IndexEntry type.", ice);
}
}
Usage:
用法：
int? myInt = reader.GetNullableField<int>(reader.GetOrdinal("myIntField"));
回答by Ray
The generic hanlding code posted here is cool, but since the question title includes the word 'efficiently' I will post my less generic but (I hope) more efficient answer.
此处发布的通用处理代码很酷，但由于问题标题包含“有效”一词，我将发布我的通用性较低但（我希望）更有效的答案。
I suggest you use the getXXX methods that others have mentioned. To deal with the column number problem that bebop talks about, I use an enum, like this:
我建议您使用其他人提到的 getXXX 方法。为了处理 bebop 谈到的列号问题，我使用了一个枚举，如下所示：
enum ReaderFields { Id, Name, PhoneNumber, ... }
int id = sqlDataReader.getInt32((int)readerFields.Id)
It's a little extra typing, but then you don't need to call GetOrdinal to find the index for each column. And, instead of worrying about column names, you worry about column positions.
这是一个额外的输入，但是您不需要调用 GetOrdinal 来查找每列的索引。而且，您不必担心列名称，而是担心列位置。
To deal with nullable columns, you need to check for DBNull, and perhaps provide a default value:
要处理可为空的列，您需要检查 DBNull，并可能提供一个默认值：
string phoneNumber;
if (Convert.IsDBNull(sqlDataReader[(int)readerFields.PhoneNumber]) {
phoneNumber = string.Empty;
}
else {
phoneNumber = sqlDataReader.getString((int)readerFields.PhoneNumber);
}
