通用DbDataReader列出＆LT; T＆GT;制图 [英] 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 Folder\Development\o7th Web Design\o7th.Class.Library.C-Sharp\o7th.Class.Library\Data Access Object\Converter.cs:line 50
at o7th.Class.Library.Data.Wrapper.Map[T](DbDataReader dr) in d:\Backup Folder\Development\o7th Web Design\o7th.Class.Library.C-Sharp\o7th.Class.Library\Data Access Object\Wrapper.cs:line 33
问题
我怎样才能修复它，这样，当我有读者可能没有为列，反之亦然额外的属性就不会失败？当然，快速创可贴是简单地添加 NULL作为里程来此查询例子，但是，这并不是解决问题的办法：）
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 :)
下面的地图＆LT; T＆GT; 使用反射：
// 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>);
}
}
注意：
这种方法比使用前pression树木...
Note: This method is 63% slower than using expression trees...
推荐答案
正如评论指出，这个问题是不存在的读者为指定的属性没有列。这个想法是由读者的列名第一循环，并检查是否匹配的属性存在。但是，如何一送列名的名单事先？
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?
-
一个想法是使用前pression树木本身从阅读器打造的列名的列表，并检查它针对类的属性。事情是这样的。
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));
}
有人可能会继续与前最后pression，但这里有一个抓沿着这条线徒劳任何进一步的努力。上述前pression树将每次获取最终委托调用而你的情况是每个对象的创建，这是对你的要求的精神时间的列名。
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.
另一种方法是让转换器类具有给定类型的列名的pre定义的意识，通过属性的方式（<一个href=\"http://stackoverflow.com/questions/8999749/getting-property-value-based-on-its-column-attribute-value\">see的例子）或保持静态辞典像（词典＆LT;类型，IEnumerable的＆LT;串GT;＆GT; ）。虽然它提供了更多的灵活性，另一面是，您的查询不一定总是包括一个表的所有列名，任何阅读器[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.
最好的办法，因为我看到的是从读者对象获取的列名，但只有一次。我会重新写的东西，如：
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.
-
通用
新T（）你的CreateItemFromRow调用速度较慢，<一个href=\"http://stackoverflow.com/questions/367577/why-does-the-c-sharp-compiler-emit-activator-createinstance-when-calling-new-in\">it使用场景背后的反思。您可以委派部分前pression树木以及<一个href=\"http://stackoverflow.com/questions/6582259/fast-creation-of-objects-instead-of-activator-createinstancetype\">which应该快了
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 作为一个在这里缓存机制。 <一href=\"http://stackoverflow.com/questions/3037203/are-static-members-of-a-generic-class-tied-to-the-specific-instance\">A在泛型类＆LT静电场; T＆GT; 将是每一个 T 独特。通用字段本身可以作为高速缓存。另外，为什么是类型 ConcurrentDictionary 的值部分对象
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?
正如我刚才所说，这是不是最好的大力配合类型和列名（你被缓存每一个特定的动作委托做哪些的键入的）。即使是同一类型的查询可以有不同选择不同的一组列。这是最好的留给读者的数据来决定。
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.
使用防爆pression.Convert 而不是防爆pression.TypeAs 的从对象值类型转换。
Use Expression.Convert instead of Expression.TypeAs for value type conversion from object.
另外请注意，<一个href=\"http://stackoverflow.com/questions/1079366/why-use-the-getordinal-method-of-the-sqldatareader\">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);
}
这篇关于通用DbDataReader列出＆LT; T＆GT;制图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
