说明
尽管随着NoSQL的普及,数据库访问的性能已经非常关注的重点了(可以通过架构来解决这个瓶颈),所以有越来越多的项目使用了ORM来访问和操作数据库,在周公的博客上有一个系列的文章来比较ADO.NET和一些常见的ORM,实际上现在周公业余研究的一个项目中,周公也使用了MyBatisNet(由iBatisNet升级而来)。不过仍然有使用ADO.NET的场合,如果使用ADO.NET则免不了要写大量的将DataTable或者DataReader转换成对应的实体类的代码,经过了大约24小时的编码和测试(非连续的,累计的),周公尝试写了一个辅助工具,它可以将DataTable或者DataReader中的数据自动转换成实体类,这个辅助工具只有两个类,一个是负责转换的类,另一个是Attribute类,用以标识实体类的非静态属性与数据集中的数据列的对应关系。
为了便于使用,将所有代码写在了一个文件里,代码中有详尽的注释,所以在这里就不再介绍其原理和如何实现的了。完整的代码如下:
- using System;
-
using System.Collections.Generic;
-
using System.Data;
-
using System.Data.Common;
-
using System.Reflection;
-
-
-
-
-
-
-
-
-
-
-
public sealed class EntityReader
- {
-
private const BindingFlags BindingFlag = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
-
-
private static Dictionary<Type, Dictionary<string, PropertyInfo>> propertyMappings = new Dictionary<Type, Dictionary<string, PropertyInfo>>();
-
-
private static Dictionary<Type, Type> genericTypeMappings = new Dictionary<Type, Type>();
-
-
static EntityReader()
- {
-
genericTypeMappings.Add(typeof(Byte?), typeof(Byte));
-
genericTypeMappings.Add(typeof(SByte?), typeof(SByte));
-
genericTypeMappings.Add(typeof(Char?), typeof(Char));
-
genericTypeMappings.Add(typeof(Boolean?), typeof(Boolean));
-
genericTypeMappings.Add(typeof(Guid?), typeof(Guid));
-
genericTypeMappings.Add(typeof(Int16), typeof(Int16));
-
genericTypeMappings.Add(typeof(UInt16), typeof(UInt16));
-
genericTypeMappings.Add(typeof(Int32), typeof(Int32));
-
genericTypeMappings.Add(typeof(UInt32), typeof(UInt32));
-
genericTypeMappings.Add(typeof(Int64), typeof(Int64));
-
genericTypeMappings.Add(typeof(UInt64), typeof(UInt64));
-
genericTypeMappings.Add(typeof(Single), typeof(Single));
-
genericTypeMappings.Add(typeof(Double), typeof(Double));
-
genericTypeMappings.Add(typeof(Decimal), typeof(Decimal));
-
genericTypeMappings.Add(typeof(DateTime), typeof(DateTime));
-
genericTypeMappings.Add(typeof(TimeSpan), typeof(TimeSpan));
-
genericTypeMappings.Add(typeof(Enum), typeof(Enum));
-
- }
-
-
-
-
-
-
-
public static List<T> GetEntities<T>(DataTable dataTable) where T : new()
- {
-
if (dataTable == null)
- {
-
throw new ArgumentNullException("dataTable");
- }
-
-
if(typeof(T)==typeof(string)||typeof(T).IsValueType)
- {
-
return GetSimpleEntities<T>(dataTable);
- }
-
else
- {
-
return GetComplexEntities<T>(dataTable);
- }
- }
-
-
-
-
-
-
-
public static List<T> GetEntities<T>(DbDataReader reader) where T : new()
- {
-
List<T> list = new List<T>();
-
if (reader == null)
- {
-
throw new ArgumentNullException("reader");
- }
-
-
if (typeof(T) == typeof(string) || typeof(T).IsValueType)
- {
-
return GetSimpleEntities<T>(reader);
- }
-
else
- {
-
return GetComplexEntities<T>(reader);
- }
-
- }
-
-
-
-
-
-
-
private static List<T> GetSimpleEntities<T>(DataTable dataTable) where T : new()
- {
-
List<T> list = new List<T>();
-
foreach (DataRow row in dataTable.Rows)
- {
-
list.Add((T)GetValueFromObject(row[0], typeof(T)));
- }
-
return list;
- }
-
-
-
-
-
-
-
private static object GetValueFromObject(object value, Type targetType)
- {
-
if (targetType == typeof(string))
- {
-
return GetString(value);
- }
-
else if (targetType.IsGenericType)
- {
-
return GetGenericValueFromObject(value, targetType);
- }
-
else
- {
-
return GetNonGenericValueFromObject(value, targetType);
- }
- }
-
-
-
-
-
-
-
-
private static List<T> GetComplexEntities<T>(DataTable dataTable) where T : new()
- {
-
if (!propertyMappings.ContainsKey(typeof(T)))
- {
-
GenerateTypePropertyMapping(typeof(T));
- }
-
List<T> list = new List<T>();
-
Dictionary<string, PropertyInfo> properties = propertyMappings[typeof(T)];
-
- T t;
-
foreach (DataRow row in dataTable.Rows)
- {
-
t = new T();
-
foreach (KeyValuePair<string, PropertyInfo> item in properties)
- {
-
-
-
-
-
-
item.Value.SetValue(t, GetValueFromObject(row[item.Key], item.Value.PropertyType), null);
- }
- list.Add(t);
- }
-
return list;
- }
-
-
-
-
-
-
-
-
private static List<T> GetComplexEntities<T>(DbDataReader reader) where T : new()
- {
-
if (!propertyMappings.ContainsKey(typeof(T)))
- {
-
GenerateTypePropertyMapping(typeof(T));
- }
-
List<T> list = new List<T>();
-
Dictionary<string, PropertyInfo> properties = propertyMappings[typeof(T)];
-
- T t;
-
while (reader.Read())
- {
-
t = new T();
-
foreach (KeyValuePair<string, PropertyInfo> item in properties)
- {
-
-
-
-
-
-
item.Value.SetValue(t, GetValueFromObject(reader[item.Key], item.Value.PropertyType), null);
- }
- list.Add(t);
- }
-
return list;
- }
-
-
-
-
-
-
-
private static List<T> GetSimpleEntities<T>(DbDataReader reader)
- {
-
List<T> list = new List<T>();
-
while (reader.Read())
- {
-
list.Add((T)GetValueFromObject(reader[0], typeof(T)));
- }
-
return list;
- }
-
-
-
-
-
-
private static object GetString(object value)
- {
-
return Convert.ToString(value);
- }
-
-
-
-
-
-
-
-
private static object GetEnum(object value, Type targetType)
- {
-
return Enum.Parse(targetType, value.ToString());
- }
-
-
-
-
-
-
-
private static object GetBoolean(object value)
- {
-
if (value is Boolean)
- {
-
return value;
- }
-
else
- {
-
byte byteValue = (byte)GetByte(value);
-
if (byteValue == 0)
- {
-
return false;
- }
-
else
- {
-
return true;
- }
- }
- }
-
-
-
-
-
-
-
private static object GetByte(object value)
- {
-
if (value is Byte)
- {
-
return value;
- }
-
else
- {
-
return byte.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetSByte(object value)
- {
-
if (value is SByte)
- {
-
return value;
- }
-
else
- {
-
return SByte.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetChar(object value)
- {
-
if (value is Char)
- {
-
return value;
- }
-
else
- {
-
return Char.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetGuid(object value)
- {
-
if (value is Guid)
- {
-
return value;
- }
-
else
- {
-
return new Guid(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetInt16(object value)
- {
-
if (value is Int16)
- {
-
return value;
- }
-
else
- {
-
return Int16.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetUInt16(object value)
- {
-
if (value is UInt16)
- {
-
return value;
- }
-
else
- {
-
return UInt16.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetInt32(object value)
- {
-
if (value is Int32)
- {
-
return value;
- }
-
else
- {
-
return Int32.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetUInt32(object value)
- {
-
if (value is UInt32)
- {
-
return value;
- }
-
else
- {
-
return UInt32.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetInt64(object value)
- {
-
if (value is Int64)
- {
-
return value;
- }
-
else
- {
-
return Int64.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetUInt64(object value)
- {
-
if (value is UInt64)
- {
-
return value;
- }
-
else
- {
-
return UInt64.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetSingle(object value)
- {
-
if (value is Single)
- {
-
return value;
- }
-
else
- {
-
return Single.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetDouble(object value)
- {
-
if (value is Double)
- {
-
return value;
- }
-
else
- {
-
return Double.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetDecimal(object value)
- {
-
if (value is Decimal)
- {
-
return value;
- }
-
else
- {
-
return Decimal.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetDateTime(object value)
- {
-
if (value is DateTime)
- {
-
return value;
- }
-
else
- {
-
return DateTime.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
private static object GetTimeSpan(object value)
- {
-
if (value is TimeSpan)
- {
-
return value;
- }
-
else
- {
-
return TimeSpan.Parse(value.ToString());
- }
- }
-
-
-
-
-
-
-
-
private static object GetGenericValueFromObject(object value,Type targetType)
- {
-
if (value == DBNull.Value)
- {
-
return null;
- }
-
else
- {
-
- Type nonGenericType= genericTypeMappings[targetType];
-
return GetNonGenericValueFromObject(value, nonGenericType);
- }
- }
-
-
-
-
-
-
-
-
private static object GetNonGenericValueFromObject(object value, Type targetType)
- {
-
if (targetType.IsEnum)
- {
-
return GetEnum(value, targetType);
- }
-
else
- {
-
switch (targetType.Name)
- {
-
case "Byte": return GetByte(value);
-
case "SByte": return GetSByte(value);
-
case "Char": return GetChar(value);
-
case "Boolean": return GetBoolean(value);
-
case "Guid": return GetGuid(value);
-
case "Int16": return GetInt16(value) ;
-
case "UInt16": return GetUInt16(value);
-
case "Int32": return GetInt32(value);
-
case "UInt32": return GetUInt32(value);
-
case "Int64": return GetInt64(value);
-
case "UInt64": return GetUInt64(value);
-
case "Single": return GetSingle(value);
-
case "Double": return GetDouble(value);
-
case "Decimal": return GetDecimal(value);
-
case "DateTime": return GetDateTime(value);
-
case "TimeSpan": return GetTimeSpan(value);
-
default: return null;
- }
- }
- }
-
-
-
-
-
-
private static void GenerateTypePropertyMapping(Type type)
- {
-
if (type != null)
- {
- PropertyInfo[] properties = type.GetProperties(BindingFlag);
-
Dictionary<string, PropertyInfo> propertyColumnMapping = new Dictionary<string, PropertyInfo>(properties.Length);
-
string description = string.Empty;
-
Attribute[] attibutes = null;
-
string columnName = string.Empty;
-
bool ignorable = false;
-
foreach (PropertyInfo p in properties)
- {
-
ignorable = false;
-
columnName = string.Empty;
- attibutes = Attribute.GetCustomAttributes(p);
-
foreach (Attribute attribute in attibutes)
- {
-
-
if (attribute.GetType() == typeof(ColumnNameAttribute))
- {
- columnName = ((ColumnNameAttribute)attribute).ColumnName;
- ignorable = ((ColumnNameAttribute)attribute).Ignorable;
-
break;
- }
- }
-
-
if (p.CanWrite&&!ignorable)
- {
-
-
if (string.IsNullOrEmpty(columnName))
- {
- columnName = p.Name;
- }
- propertyColumnMapping.Add(columnName, p);
- }
- }
- propertyMappings.Add(type, propertyColumnMapping);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- }
-
-
-
-
public class ColumnNameAttribute : Attribute
- {
-
-
-
-
public string ColumnName { get; set; }
-
-
-
-
public bool Ignorable { get; set; }
-
-
-
-
-
public ColumnNameAttribute(string columnName)
- {
- ColumnName = columnName;
-
Ignorable = false;
- }
-
-
-
-
-
public ColumnNameAttribute(bool ignorable)
- {
- Ignorable = ignorable;
- }
-
-
-
-
-
-
public ColumnNameAttribute(string columnName, bool ignorable)
- {
- ColumnName = columnName;
- Ignorable = ignorable;
- }
- }
用法举例
这里以MySQL为例(因为公司不允许安装破解和盗版软件,所以我的电脑上只有免费的SQLite和MySQL社区版了)。
- MySqlConnection connection = new MySqlConnection("Server=localhost;Database=crawldb;Uid=root;Pwd=root;Port=3306;");
- MySqlCommand command = new MySqlCommand("SELECT * FROM TY_Content order by Id desc limit 0,200000", connection);
- MySqlDataAdapter adapter = new MySqlDataAdapter(command);
- DataTable data6 = new DataTable();
- adapter.Fill(data6);
- Console.WriteLine("MySQL");
- watch.Reset();
- watch.Start();
- List<TY_ContentInfo> list6 = EntityReader.GetEntities<TY_ContentInfo>(data6);
- watch.Stop();
- Console.WriteLine("Parse data in DataTable lasts ms:{0}", watch.ElapsedMilliseconds);
- Console.WriteLine("Data record Count:{0}", list6.Count);
- data6.Clear();
- data6.Dispose();
- list6.Clear();
- list6 = null;
性能测试数据
在SQLite上有表如下:
- CREATE TABLE TY_Content (
- Id integer NOT NULL PRIMARY KEY UNIQUE,
- ArticleId int not null,
- Content text not null,
- ContentHash varchar(32) not null,
- CreateAt datetime null default CURRENT_TIMESTAMP
- );
-
- CREATE UNIQUE INDEX IDX_ContentHash on TY_Content (
- ContentHash ASC
- );
里面共有数据128062条,采用DataTable和SQLiteDataReader的方式读取耗时分别为1582ms和11120ms。
在MySQL中也存在有上面结构的表,有数据175616条,采用DataTable和SQLiteDataReader的方式读取耗时分别为1958ms和9461ms。
在数据库记录条数百万级以下,使用它还可以可以的,实际上在真实的开发中不可能一次性读取数十万条数据的,因此还可以可用于一般的中小型网站。
未尽事宜
第一个未尽事宜就是我还在分析为什么从DataTable中读取比DataReader中读取要快,即使不同数据库都是如此,几乎相差一个数量级,这跟平时我们印象中“从DataReader中读数据要比DataTable中快”相冲突。
第二个未尽事宜是代码的优化问题,因为最近个人事情比较多,还有很多地方没有来得及优化,甚至我的另一种实现还没有比较,那就是采用分而治之的方式,针对每一种数据类型都写一种方法来处理,这样就可以去掉反射部分了,或许速度上还会有提高,但这还是我的预想,没有验证。
总结
上面是一个逻辑稍微有点复杂的例子,里面用到了反射、Attribute及ADO.NET的例子,可以作为想学习这方面知识的朋友一个范例。除此之外,还可以将这个类用于中小型系统开发中。
如果大家有什么好的意见或建议,或者发现了bug,请在博客下方留言或者到http://weibo.com/zhoufoxcn上留言。
本文转自周金桥51CTO博客,原文链接:http://blog.51cto.com/zhoufoxcn/614794 ,如需转载请自行联系原作者