C# 将来自数据读取器的行转换为类型化结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1202935/
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
Convert rows from a data reader into typed results
提问by Anthony
I'm using a third party library which returns a data reader. I would like a simple way and as generic as possible to convert it into a List of objects.
For example, say I have a class 'Employee' with 2 properties EmployeeId and Name, I would like the data reader (which contains a list of employees) to be converted into List< Employee>.
I guess I have no choice but to iterate though the rows of the data reader and for each of them convert them into an Employee object that I will add to the List. Any better solution? I'm using C# 3.5 and ideally I would like it to be as generic as possible so that it works with any classes (the field names in the DataReader match the property names of the various objects).
我正在使用返回数据读取器的第三方库。我想要一种简单且尽可能通用的方法将其转换为对象列表。
例如，假设我有一个具有 2 个属性 EmployeeId 和 Name 的类“Employee”，我希望将数据读取器（包含员工列表）转换为 List<Employee>。
我想我别无选择，只能遍历数据读取器的行，并为每个行将它们转换为我将添加到列表中的 Employee 对象。有什么更好的解决办法吗？我正在使用 C# 3.5，理想情况下我希望它尽可能通用，以便它可以与任何类一起使用（DataReader 中的字段名称与各种对象的属性名称匹配）。
采纳答案by Joel Coehoorn
Do you really need a list, or would IEnumerable be good enough?
你真的需要一个列表，或者 IEnumerable 是否足够好？
I know you want it to be generic, but a much more common pattern is to have a static Factory method on the target object type that accepts a datarow (or IDataRecord). That would look something like this:
我知道您希望它是通用的，但更常见的模式是在接受数据行（或 IDataRecord）的目标对象类型上使用静态工厂方法。这看起来像这样：
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public static Employee Create(IDataRecord record)
{
return new Employee
{
Id = record["id"],
Name = record["name"]
};
}
}
.
.
public IEnumerable<Employee> GetEmployees()
{
using (var reader = YourLibraryFunction())
{
while (reader.Read())
{
yield return Employee.Create(reader);
}
}
}
Then if you reallyneed a list rather than an IEnumerable you can call .ToList()on the results. I suppose you could also use generics + a delegate to make the code for this pattern more re-usable as well.
然后，如果您确实需要一个列表而不是 IEnumerable，则可以调用.ToList()结果。我想您还可以使用泛型 + 委托来使此模式的代码也更可重用。
Update:I saw this again today and felt like writing the generic code:
更新：我今天再次看到这个，感觉想写通用代码：
public IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> BuildObject)
{
try
{
while (reader.Read())
{
yield return BuildObject(reader);
}
}
finally
{
reader.Dispose();
}
}
//call it like this:
var result = GetData(YourLibraryFunction(), Employee.Create);
回答by Mehrdad Afshari
You could build an extension method like:
您可以构建一个扩展方法，如：
public static List<T> ReadList<T>(this IDataReader reader,
Func<IDataRecord, T> generator) {
var list = new List<T>();
while (reader.Read())
list.Add(generator(reader));
return list;
}
and use it like:
并使用它：
var employeeList = reader.ReadList(x => new Employee {
Name = x.GetString(0),
Age = x.GetInt32(1)
});
Joel's suggestion is a good one. You can choose to return IEnumerable<T>. It's easy to transform the above code:
乔尔的建议很好。你可以选择返回IEnumerable<T>。上面的代码很容易转换：
public static IEnumerable<T> GetEnumerator<T>(this IDataReader reader,
Func<IDataRecord, T> generator) {
while (reader.Read())
yield return generator(reader);
}
If you want to automatically map the columns to properties, the code idea is the same. You can just replace the generatorfunction in the above code with a function that interrogates typeof(T)and sets the properties on the object using reflection by reading the matched column. However, I personally prefer defining a factory method (like the one mentioned in Joel's answer) and passing a delegate of it into this function:
如果要自动将列映射到属性，代码思路是一样的。您可以generator将上述代码中的函数替换为一个函数，该函数typeof(T)通过读取匹配的列使用反射来询问和设置对象的属性。但是，我个人更喜欢定义一个工厂方法（就像 Joel 的回答中提到的那个）并将它的委托传递给这个函数：
var list = dataReader.GetEnumerator(Employee.Create).ToList();
回答by Dan Diplo
Whilst I wouldn't recommend this for production code, but you can do this automatically using reflection and generics:
虽然我不建议将其用于生产代码，但您可以使用反射和泛型自动执行此操作：
public static class DataRecordHelper
{
public static void CreateRecord<T>(IDataRecord record, T myClass)
{
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
for (int i = 0; i < record.FieldCount; i++)
{
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (propertyInfo.Name == record.GetName(i))
{
propertyInfo.SetValue(myClass, Convert.ChangeType(record.GetValue(i), record.GetFieldType(i)), null);
break;
}
}
}
}
}
public class Employee
{
public int Id { get; set; }
public string LastName { get; set; }
public DateTime? BirthDate { get; set; }
public static IDataReader GetEmployeesReader()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT EmployeeID As Id, LastName, BirthDate FROM Employees"))
{
cmd.Connection = conn;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
public static IEnumerable GetEmployees()
{
IDataReader rdr = GetEmployeesReader();
while (rdr.Read())
{
Employee emp = new Employee();
DataRecordHelper.CreateRecord<Employee>(rdr, emp);
yield return emp;
}
}
}
You can then use CreateRecord<T>()to instantiate any class from the fields in a data reader.
然后，您可以使用CreateRecord<T>()数据读取器中的字段实例化任何类。
<asp:GridView ID="GvEmps" runat="server" AutoGenerateColumns="true"></asp:GridView>
GvEmps.DataSource = Employee.GetEmployees();
GvEmps.DataBind();
回答by Airn5475
We have implemented the following solution and feel it works pretty well. It's pretty simple and requires a bit more wiring up then what a mapper would do. However, sometimes it is nice to have the manual control and honestly, you wire up once and you're done.
我们已经实施了以下解决方案，并且感觉效果很好。它非常简单，需要比映射器更多的接线。然而，有时手动控制很好，老实说，你连接一次就完成了。
In a nutshell:Our domain models implement an interface that has a method that takes in an IDataReaderand populates the model properties from it. We then use Generics and Reflection to create an instance of the model and call the Parsemethod on it.
简而言之：我们的领域模型实现了一个接口，该接口具有一个方法，该方法接收 anIDataReader并从中填充模型属性。然后我们使用泛型和反射来创建模型的实例并Parse在其上调用方法。
We considered using a constructor and passing IDataReaderto it, but the basicperformance checks we did seemed to suggest the interface was consistently faster (if only by a little). Also, the interface route provides instant feedback via compilation errors.
我们考虑过使用构造函数并将其传递IDataReader给它，但是我们所做的基本性能检查似乎表明接口始终更快（如果只是一点点）。此外，接口路由通过编译错误提供即时反馈。
One of the things I like, is that you can utilize private setfor properties like Agein the example below and set them straight from the database.
我喜欢的一件事是，您可以使用下面示例中的private set属性Age，并直接从数据库中设置它们。
public interface IDataReaderParser
{
void Parse(IDataReader reader);
}
public class Foo : IDataReaderParser
{
public string Name { get; set; }
public int Age { get; private set; }
public void Parse(IDataReader reader)
{
Name = reader["Name"] as string;
Age = Convert.ToInt32(reader["Age"]);
}
}
public class DataLoader
{
public static IEnumerable<TEntity> GetRecords<TEntity>(string connectionStringName, string storedProcedureName, IEnumerable<SqlParameter> parameters = null)
where TEntity : IDataReaderParser, new()
{
using (var sqlCommand = new SqlCommand(storedProcedureName, Connections.GetSqlConnection(connectionStringName)))
{
using (sqlCommand.Connection)
{
sqlCommand.CommandType = CommandType.StoredProcedure;
AssignParameters(parameters, sqlCommand);
sqlCommand.Connection.Open();
using (var sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
//Create an instance and parse the reader to set the properties
var entity = new TEntity();
entity.Parse(sqlDataReader);
yield return entity;
}
}
}
}
}
}
To call it, you simply provide the type parameter
要调用它，您只需提供类型参数
IEnumerable<Foo> foos = DataLoader.GetRecords<Foo>(/* params */)
回答by pimbrouwers
NOTE: This is .NET Core code
注意：这是 .NET Core 代码
A stupidly performant option, should you not mind an external dependency (the amazing Fast Membernuget package):
一个愚蠢的高性能选项，如果你不介意外部依赖（惊人的Fast Membernuget 包）：
public static T ConvertToObject<T>(this SqlDataReader rd) where T : class, new()
{
Type type = typeof(T);
var accessor = TypeAccessor.Create(type);
var members = accessor.GetMembers();
var t = new T();
for (int i = 0; i < rd.FieldCount; i++)
{
if (!rd.IsDBNull(i))
{
string fieldName = rd.GetName(i);
if (members.Any(m => string.Equals(m.Name, fieldName, StringComparison.OrdinalIgnoreCase)))
{
accessor[t, fieldName] = rd.GetValue(i);
}
}
}
return t;
}
To use:
使用：
public IEnumerable<T> GetResults<T>(SqlDataReader dr) where T : class, new()
{
while (dr.Read())
{
yield return dr.ConvertToObject<T>());
}
}
回答by Mohsen
The simplest Solution :
最简单的解决方案：
var dt=new DataTable();
dt.Load(myDataReader);
list<DataRow> dr=dt.AsEnumerable().ToList();
Then select them in order to map them to any type.
然后选择它们以将它们映射到任何类型。
回答by Tony Bourdeaux
For .NET Core 2.0:
对于 .NET Core 2.0：
Here is an extension method that works with .NET CORE 2.0 to execute RAW SQL and map results to LIST of arbitrary types:
这是一个与 .NET CORE 2.0 一起使用的扩展方法，用于执行 RAW SQL 并将结果映射到任意类型的 LIST：
USAGE:
用法：
var theViewModel = new List();
string theQuery = @"SELECT * FROM dbo.Something";
theViewModel = DataSQLHelper.ExecSQL(theQuery,_context);
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
public static List ExecSQL(string query, myDBcontext context)
{
using (context)
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
List<T> list = new List<T>();
T obj = default(T);
while (result.Read())
{
obj = Activator.CreateInstance<T>();
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
if (!object.Equals(result[prop.Name], DBNull.Value))
{
prop.SetValue(obj, result[prop.Name], null);
}
}
list.Add(obj);
}
return list;
}
}
}
}
回答by C.List
Like Magic
喜欢魔法
I personally HATE doing manual mapping in constructors, I'm also not a fan of doing my own reflection. So here's another solution courtesy of the wonderful (and fairly ubiquitous) Newtonsoft JSON lib.
我个人讨厌在构造函数中进行手动映射，我也不喜欢进行自己的反射。因此，这是由精彩的（并且相当普遍的）Newtonsoft JSON 库提供的另一个解决方案。
It will only work if your property names exactly match the datareader column names, but it worked well for us.
只有当您的属性名称与 datareader 列名称完全匹配时，它才会起作用，但它对我们来说效果很好。
...assumes you've got a datareader name "yourDataReader"...
...假设您有一个数据读取器名称“yourDataReader”...
var dt = new DataTable();
dt.Load(yourDataReader);
// creates a json array of objects
string json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
// this is what you're looking for right??
List<YourEntityType> list =
Newtonsoft.Json.JsonConvert
.DeserializeObject<List<YourEntityType>>(json);
