用于 List<T> 的通用 DbDataReader映射 [英] Generic DbDataReader to List<T> mapping
问题描述
我的属性绑定数据访问类有一个小问题(更像是一个烦恼).问题是当读取器中不存在类中对应属性的列时，映射失败.
I am having a slight issue (more like an annoyance) with my property binding data access classes. The problem is that the mapping fails when there exists no column in the reader for corresponding property in class.
这是映射器类:
// Map our datareader object to a strongly typed list
private static IList<T> Map<T>(DbDataReader dr) where T : new()
{
try
{
// initialize our returnable list
List<T> list = new List<T>();
// fire up the lamda mapping
var converter = new Converter<T>();
while (dr.Read())
{
// read in each row, and properly map it to our T object
var obj = converter.CreateItemFromRow(dr);
// add it to our list
list.Add(obj);
}
// reutrn it
return list;
}
catch (Exception ex)
{
return default(List<T>);
}
}
转换器类:
/// <summary>
/// Converter class to convert returned Sql Records to strongly typed classes
/// </summary>
/// <typeparam name="T">Type of the object we'll convert too</typeparam>
internal class Converter<T> where T : new()
{
// Concurrent Dictionay objects
private static ConcurrentDictionary<Type, object> _convertActionMap = new ConcurrentDictionary<Type, object>();
// Delegate action declaration
private Action<IDataReader, T> _convertAction;
// Build our mapping based on the properties in the class/type we've passed in to the class
private static Action<IDataReader, T> GetMapFunc()
{
var exps = new List<Expression>();
var paramExp = Expression.Parameter(typeof(IDataReader), "o7thDR");
var targetExp = Expression.Parameter(typeof(T), "o7thTarget");
var getPropInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(string) });
var _props = typeof(T).GetProperties();
foreach (var property in _props)
{
var getPropExp = Expression.MakeIndex(paramExp, getPropInfo, new[] { Expression.Constant(property.Name, typeof(string)) });
var castExp = Expression.TypeAs(getPropExp, property.PropertyType);
var bindExp = Expression.Assign(Expression.Property(targetExp, property), castExp);
exps.Add(bindExp);
}
// return our compiled mapping, this will ensure it is cached to use through our record looping
return Expression.Lambda<Action<IDataReader, T>>(Expression.Block(exps), new[] { paramExp, targetExp }).Compile();
}
internal Converter()
{
// Fire off our mapping functionality
_convertAction = (Action<IDataReader, T>)_convertActionMap.GetOrAdd(typeof(T), (t) => GetMapFunc());
}
internal T CreateItemFromRow(IDataReader dataReader)
{
T result = new T();
_convertAction(dataReader, result);
return result;
}
}
异常
System.IndexOutOfRangeException {"Mileage"}
堆栈跟踪
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at lambda_method(Closure , IDataReader , Typing )
at o7th.Class.Library.Data.Converter`1.CreateItemFromRow(IDataReader dataReader) in d:Backup FolderDevelopmento7th Web Designo7th.Class.Library.C-Sharpo7th.Class.LibraryData Access ObjectConverter.cs:line 50
at o7th.Class.Library.Data.Wrapper.Map[T](DbDataReader dr) in d:Backup FolderDevelopmento7th Web Designo7th.Class.Library.C-Sharpo7th.Class.LibraryData Access ObjectWrapper.cs:line 33
问题
如何修复它，以便在我有一个读者可能没有的额外属性作为列时它不会失败，反之亦然?当然，快速创可贴就是在示例中简单地将 NULL As Mileage 添加到此查询中，但是，这不是问题的解决方案 :)
Question
How can I fix it, so that it will not fail when I have an extra property that the reader may not have as column and vice versa? Of course the quick band-aid would be to simply add NULL As Mileage to this query in example, however, this is not a solution to the problem :)
这是使用反射的Map:
// Map our datareader object to a strongly typed list
private static IList<T> Map<T>(DbDataReader dr) where T : new()
{
try
{
// initialize our returnable list
List<T> list = new List<T>();
T item = new T();
PropertyInfo[] properties = (item.GetType()).GetProperties();
while (dr.Read()) {
int fc = dr.FieldCount;
for (int j = 0; j < fc; ++j) {
var pn = properties[j].Name;
var gn = dr.GetName(j);
if (gn == pn) {
properties[j].SetValue(item, dr[j], null);
}
}
list.Add(item);
}
// return it
return list;
}
catch (Exception ex)
{
// Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
_Msg += "Wrapper.Map Exception: " + ex.Message;
ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Map", _Msg);
// make sure this method returns a default List
return default(List<T>);
}
}
注意:这种方法比使用表达式树慢 63%...
Note: This method is 63% slower than using expression trees...
推荐答案
如评论中所述，问题在于读取器中不存在指定属性的列.这个想法是先循环读取 reader 的列名，然后检查是否存在匹配的属性.但是如何事先获得列名列表呢?
As noted in comments, the problem is that there exists no column in the reader for the specified property. The idea is to loop by the column names of reader first, and check to see if matching property exists. But how do one get the list of column names beforehand?
一个想法是使用表达式树本身从读取器构建列名称列表，并根据类的属性检查它.像这样
One idea is to use expression trees itself to build the list of column names from the reader and check it against properties of the class. Something like this
var paramExp = Expression.Parameter(typeof(IDataRecord), "o7thDR");
var loopIncrementVariableExp = Expression.Parameter(typeof(int), "i");
var columnNamesExp = Expression.Parameter(typeof(List<string>), "columnNames");
var columnCountExp = Expression.Property(paramExp, "FieldCount");
var getColumnNameExp = Expression.Call(paramExp, "GetName", Type.EmptyTypes,
Expression.PostIncrementAssign(loopIncrementVariableExp));
var addToListExp = Expression.Call(columnNamesExp, "Add", Type.EmptyTypes,
getColumnNameExp);
var labelExp = Expression.Label(columnNamesExp.Type);
var getColumnNamesExp = Expression.Block(
new[] { loopIncrementVariableExp, columnNamesExp },
Expression.Assign(columnNamesExp, Expression.New(columnNamesExp.Type)),
Expression.Loop(
Expression.IfThenElse(
Expression.LessThan(loopIncrementVariableExp, columnCountExp),
addToListExp,
Expression.Break(labelExp, columnNamesExp)),
labelExp));
相当于
List<string> columnNames = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columnNames.Add(reader.GetName(i));
}
人们可以继续最后的表达，但这里有一个问题，沿着这条线做任何进一步的努力都是徒劳的.每次调用最终委托时，上面的表达式树都会获取列名，在您的情况下，这是针对每个对象创建的，这违背了您的要求的精神.
One may continue with the final expression, but there is a catch here making any further effort along this line futile. The above expression tree will be fetching the column names every time the final delegate is called which in your case is for every object creation, which is against the spirit of your requirement.
另一种方法是让转换器类通过属性(参见示例)或通过维护一个静态字典，如 (Dictionary).虽然它提供了更大的灵活性，但另一方面是您的查询不需要总是包含表的所有列名，任何 reader[notInTheQueryButOnlyInTheTableColumn] 都会导致异常.
Another approach is to let the converter class have a pre-defined awareness of the column names for a given type, by means of attributes (see for an example) or by maintaining a static dictionary like (Dictionary<Type, IEnumerable<string>>). Though it gives more flexibility, the flip side is that your query need not always include all the column names of a table, and any reader[notInTheQueryButOnlyInTheTableColumn] would result in exception.
据我所知，最好的方法是从 reader 对象中获取列名，但只获取一次.我会重写这样的东西:
The best approach as I see is to fetch the column names from the reader object, but only once. I would re-write the thing like:
private static List<string> columnNames;
private static Action<IDataReader, T> GetMapFunc()
{
var exps = new List<Expression>();
var paramExp = Expression.Parameter(typeof(IDataRecord), "o7thDR");
var targetExp = Expression.Parameter(typeof(T), "o7thTarget");
var getPropInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(string) });
foreach (var columnName in columnNames)
{
var property = typeof(T).GetProperty(columnName);
if (property == null)
continue;
// use 'columnName' instead of 'property.Name' to speed up reader lookups
//in case of certain readers.
var columnNameExp = Expression.Constant(columnName);
var getPropExp = Expression.MakeIndex(
paramExp, getPropInfo, new[] { columnNameExp });
var castExp = Expression.TypeAs(getPropExp, property.PropertyType);
var bindExp = Expression.Assign(
Expression.Property(targetExp, property), castExp);
exps.Add(bindExp);
}
return Expression.Lambda<Action<IDataReader, T>>(
Expression.Block(exps), paramExp, targetExp).Compile();
}
internal T CreateItemFromRow(IDataReader dataReader)
{
if (columnNames == null)
{
columnNames = Enumerable.Range(0, dataReader.FieldCount)
.Select(x => dataReader.GetName(x))
.ToList();
_convertAction = (Action<IDataReader, T>)_convertActionMap.GetOrAdd(
typeof(T), (t) => GetMapFunc());
}
T result = new T();
_convertAction(dataReader, result);
return result;
}
这就引出了一个问题，为什么不将数据读取器直接传递给构造函数?那会更好.
Now that begs the question why not pass the data reader directly to constructor? That would be better.
private IDataReader dataReader;
private Action<IDataReader, T> GetMapFunc()
{
var exps = new List<Expression>();
var paramExp = Expression.Parameter(typeof(IDataRecord), "o7thDR");
var targetExp = Expression.Parameter(typeof(T), "o7thTarget");
var getPropInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(string) });
var columnNames = Enumerable.Range(0, dataReader.FieldCount)
.Select(x => dataReader.GetName(x));
foreach (var columnName in columnNames)
{
var property = typeof(T).GetProperty(columnName);
if (property == null)
continue;
// use 'columnName' instead of 'property.Name' to speed up reader lookups
//in case of certain readers.
var columnNameExp = Expression.Constant(columnName);
var getPropExp = Expression.MakeIndex(
paramExp, getPropInfo, new[] { columnNameExp });
var castExp = Expression.TypeAs(getPropExp, property.PropertyType);
var bindExp = Expression.Assign(
Expression.Property(targetExp, property), castExp);
exps.Add(bindExp);
}
return Expression.Lambda<Action<IDataReader, T>>(
Expression.Block(exps), paramExp, targetExp).Compile();
}
internal Converter(IDataReader dataReader)
{
this.dataReader = dataReader;
_convertAction = (Action<IDataReader, T>)_convertActionMap.GetOrAdd(
typeof(T), (t) => GetMapFunc());
}
internal T CreateItemFromRow()
{
T result = new T();
_convertAction(dataReader, result);
return result;
}
像这样称呼
List<T> list = new List<T>();
var converter = new Converter<T>(dr);
while (dr.Read())
{
var obj = converter.CreateItemFromRow();
list.Add(obj);
}
<小时>
不过，我可以提出一些改进建议.
There are a number of improvements that I can suggest, though.
The generic
new T()you're calling inCreateItemFromRowis slower, it uses reflection behind the scenes. You can delegate that part to expression trees as well which should be faster
现在 GetProperty 调用不区分大小写，这意味着您的列名称必须与属性名称完全匹配.我会使用那些 Bindings.Flag 之一使其不区分大小写.
Right now GetProperty call isn't case insensitive, meaning your column names will have to exactly match the property name. I would make it case insensitive using one of those Bindings.Flag.
我完全不知道你为什么在这里使用 ConcurrentDictionary 作为缓存机制.泛型类中的静态字段T 都是唯一的.通用字段本身可以充当缓存.另外，为什么 ConcurrentDictionary 的 Value 部分是 object 类型的?
I'm not sure at all why you are using a ConcurrentDictionary as a caching mechanism here. A static field in a generic class <T> will be unique for every T. The generic field itself can act as cache. Also why is the Value part of ConcurrentDictionary of type object?
正如我之前所说的，将类型和列名强行绑定并不是最好的方式(您通过为每个类型缓存一个特定的 Action 委托来实现这一点em>).即使对于相同的类型，您的查询也可能不同，选择不同的列集.最好让数据阅读器来决定.
As I said earlier, it's not the best to strongly tie a type and the column names (which you're doing by caching one particular Action delegate per type). Even for the same type your queries can be different selecting different set of columns. It's best to leave it to data reader to decide.
使用 Expression.Convert 而不是 Expression.TypeAs 进行 object 的值类型转换.
Use Expression.Convert instead of Expression.TypeAs for value type conversion from object.
另请注意，reader.GetOrdinal 是更快的方式执行数据读取器查找.
我会像这样重写整个事情:
I would re-write the whole thing like:
readonly Func<IDataReader, T> _converter;
readonly IDataReader dataReader;
private Func<IDataReader, T> GetMapFunc()
{
var exps = new List<Expression>();
var paramExp = Expression.Parameter(typeof(IDataRecord), "o7thDR");
var targetExp = Expression.Variable(typeof(T));
exps.Add(Expression.Assign(targetExp, Expression.New(targetExp.Type)));
//does int based lookup
var indexerInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(int) });
var columnNames = Enumerable.Range(0, dataReader.FieldCount)
.Select(i => new { i, name = dataReader.GetName(i) });
foreach (var column in columnNames)
{
var property = targetExp.Type.GetProperty(
column.name,
BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
if (property == null)
continue;
var columnNameExp = Expression.Constant(column.i);
var propertyExp = Expression.MakeIndex(
paramExp, indexerInfo, new[] { columnNameExp });
var convertExp = Expression.Convert(propertyExp, property.PropertyType);
var bindExp = Expression.Assign(
Expression.Property(targetExp, property), convertExp);
exps.Add(bindExp);
}
exps.Add(targetExp);
return Expression.Lambda<Func<IDataReader, T>>(
Expression.Block(new[] { targetExp }, exps), paramExp).Compile();
}
internal Converter(IDataReader dataReader)
{
this.dataReader = dataReader;
_converter = GetMapFunc();
}
internal T CreateItemFromRow()
{
return _converter(dataReader);
}
这篇关于用于 List<T> 的通用 DbDataReader映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
