DbUtility中的方法ExecuteDataTableAsync()得到的是一个DataTable，而我们常见的情况下，我们需要的不是DataTable，而是List或IList，所以现在需要考虑把DataTable转成List或IList，目前暂时有三种方案：
方案1：用动软生成代码：
public static List<Roles> GetRoleses(DataTable dt)
{
List<Roles> list = new List<Roles>();
)
list.AddRange(from DataRow dataRow in dt.Rows select DataRowToModel(dataRow));
return list;
}
/// <summary>
/// 得到一个对象实体
/// </summary>
private static Roles DataRowToModel(DataRow row)
{
Roles model = new Roles();
if (row != null)
{
if (row["RoleID"] != null && row["RoleID"].ToString() != "")
{
model.RoleID = new Guid(row["RoleID"].ToString());
}
if (row["RoleName"] != null)
{
model.RoleName = row["RoleName"].ToString();
}
if (row["Description"] != null)
{
model.Description = row["Description"].ToString();
}
if (row["TaskMask"] != null)
{
model.TaskMask = row["TaskMask"].ToString();
}
if (row["RoleFlags"] != null && row["RoleFlags"].ToString() != "")
{
model.RoleFlags = int.Parse(row["RoleFlags"].ToString());
}
}
return model;
}
循环遍历100W次，大约用时：14460毫秒。
方案2：用MVC.Net提供的反射方案：
public static IList<T> ConvertToModel<T>(this DataTable dt) where T : class, new()
{
// 定义集合
IList<T> ts = new List<T>();
// 获得此模型的类型
Type type = typeof(T);
string tempName = "";
foreach (DataRow dr in dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
// 检查DataTable是否包含此列
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
ts.Add(t);
}
return ts;
}
反射100W次，大约用时：20350毫秒。
方案3：用Melas提供的反射方案：
public static IList<T> ConvertTo<T>(DataTable table)
{
if (table == null)
{
return null;
}
List<DataRow> rows = new List<DataRow>();
foreach (DataRow row in table.Rows)
{
rows.Add(row);
}
return ConvertTo<T>(rows);
}
public static IList<T> ConvertTo<T>(IList<DataRow> rows)
{
IList<T> list = null;
if (rows != null)
{
list = new List<T>();
foreach (DataRow row in rows)
{
T item = CreateItem<T>(row);
list.Add(item);
}
}
return list;
}
public static T CreateItem<T>(DataRow row)
{
T obj = default(T);
if (row != null)
{
obj = Activator.CreateInstance<T>();
foreach (DataColumn column in row.Table.Columns)
{
object value = row[column.ColumnName];
PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
try
{
if (value != DBNull.Value && prop != null)
prop.SetValue(obj, value, null);
}
catch
{
// You can log something here
throw;
}
}
}
return obj;
}
反射100W次，大约用时：20258毫秒。
数据库结构：
表名：Roles
|
原本想修改动软代码生成器的模板来生成改写过的DataRowToModel方法，想改成下面这样：
/// <summary>
/// 得到一个对象实体
/// </summary>
private static Roles DataRowToModel(DataRow row)
{
if (row != null)
{
Roles model = new Roles
{
RoleID = new Guid(row["RoleID"].ToString()),
RoleName = row["RoleName"].ToString(),
Description = row["Description"].ToString(),
TaskMask = row["TaskMask"].ToString(),
RoleFlags = int.Parse(row["RoleFlags"].ToString())
};
return model;
}
return null;
}
后来发现一个很悲剧的事，模板里不包含DataRowToModel方法，我看到了下面这个东西：
目前用到的代码生成，我用CodeSmith生成，下面贴出两个模板，可以直接用，已经测试通过。
实体层：
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Generates a very simple business object." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the object is based on." %>
<%@ Property Name="SingleFileMode" Type="System.Boolean" Category="Options" Default="True" Description="Generate content for a complete cs file instead of just a class." %>
<%@ Property Name="ClassNamespace" Type="System.String" Category="Options" Default="BusinessObjects" Description="Namespace your class is in. Only used if SingleFileMode is true!" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Map Name="SqlCSharp" Src="Sql-CSharp" Description="System to C# Type Map" %>
<%@ Map Name="DbDataReader" Src="DbType-DataReaderMethod" Description="DbType to DataReader Method Map" %>
<%@ Map Name="SqlNativeSqlDb" Src="SqlNativeType-SqlDbType" Description="SqlNativeType to SqlDbType Map" %>
<%@ Map Name="DbTypeCSharp" Src="DbType-CSharp" Description="DbType to CSharp Map" %>
<% if(this.SingleFileMode) { %>
using System;
namespace <%= this.ClassNamespace %>
{
<% } %>
/// <summary>
/// <%= GetClassName(SourceTable) %>:实体类(属性说明自动提取数据库字段的描述信息)
/// </summary>
[Serializable]
public class <%= GetClassName(SourceTable) %>
{
#region Private Properties
<% foreach (ColumnSchema column in SourceTable.Columns) { %>
<%= GetMemberVariableDeclarationStatement(column) %>
<% } %>
#endregion
#region Public Properties
<% ; i < SourceTable.Columns.Count; i++) { %>
/// <summary>
/// <%=SourceTable.Columns[i].Description %>
/// </summary>
public <%= GetCSharpVariableType(SourceTable.Columns[i]) %> <%= GetPropertyName(SourceTable.Columns[i]) %>
{
get {return <%= GetMemberVariableName(SourceTable.Columns[i]) %>;}
set {<%= GetMemberVariableName(SourceTable.Columns[i]) %> = value;}
}
<% ) Response.Write("\r\n"); %>
<% } %>
#endregion
}
<% if(this.SingleFileMode) { %>
}
<% } %>
<script runat="template">
public string GetMemberVariableDeclarationStatement(ColumnSchema column)
{
return GetMemberVariableDeclarationStatement("private", column);
}
public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
{
string statement = protectionLevel + " ";
statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column);
string defaultValue = GetMemberVariableDefaultValue(column);
if (defaultValue != "")
{
statement += " = " + defaultValue;
}
statement += ";";
return statement;
}
public string GetCamelCaseName(string value)
{
, ).ToLower() + value.Substring();
}
public string GetMemberVariableName(ColumnSchema column)
{
string propertyName = GetPropertyName(column);
string memberVariableName = "_" + GetCamelCaseName(propertyName);
return memberVariableName;
}
public string GetPropertyName(ColumnSchema column)
{
string propertyName = column.Name;
if (propertyName == column.Table.Name + "Name") return "Name";
if (propertyName == column.Table.Name + "Description") return "Description";
, propertyName.Length - );
return propertyName;
}
public string GetMemberVariableDefaultValue(ColumnSchema column)
{
switch (column.DataType)
{
case DbType.Guid:
{
return "Guid.Empty";
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
{
return "String.Empty";
}
default:
{
return "";
}
}
}
public string GetCSharpVariableType(ColumnSchema column)
{
if (column.Name.EndsWith("TypeCode")) return column.Name;
return DbTypeCSharp[column.DataType.ToString()];
}
public string GetClassName(TableSchema table)
{
return table.Name;
}
public override string GetFileName()
{
return this.GetClassName(this.SourceTable) + ".cs";
}
</script>
数据访问层：
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Generates a very simple business object." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the object is based on." %>
<%@ Property Name="SingleFileMode" Type="System.Boolean" Category="Options" Default="True" Description="Generate content for a complete cs file instead of just a class." %>
<%@ Property Name="ClassNamespace" Type="System.String" Category="Options" Default="BusinessObjects" Description="Namespace your class is in. Only used if SingleFileMode is true!" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Map Name="SqlCSharp" Src="Sql-CSharp" Description="System to C# Type Map" %>
<%@ Map Name="DbDataReader" Src="DbType-DataReaderMethod" Description="DbType to DataReader Method Map" %>
<%@ Map Name="SqlNativeSqlDb" Src="SqlNativeType-SqlDbType" Description="SqlNativeType to SqlDbType Map" %>
<%@ Map Name="DbTypeCSharp" Src="DbType-CSharp" Description="DbType to CSharp Map" %>
<% if(this.SingleFileMode) { %>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace <%= this.ClassNamespace %>
{
<% } %>
public class <%= GetClassName(SourceTable) %>DAL
{
/// <summary>
/// 把DataTable转成一个List集合
/// </summary>
public static List<<%= GetClassName(SourceTable) %>> GetRoleses(DataTable dt)
{
List<<%= GetClassName(SourceTable) %>> list = new List<<%= GetClassName(SourceTable) %>>();
)
list.AddRange(from DataRow dataRow in dt.Rows select DataRowToModel(dataRow));
return list;
}
/// <summary>
/// 得到一个对象实体
/// </summary>
private static <%= GetClassName(SourceTable) %> DataRowToModel(DataRow row)
{
if (row != null)
{
<%= GetClassName(SourceTable) %> model = new <%= GetClassName(SourceTable) %>
{
<% ; i < SourceTable.Columns.Count; i++) { %>
<%= GetReaderAssignmentStatement(SourceTable.Columns[i], i,SourceTable.Columns.Count-) %>
<%}%>
};
return model;
}
return null;
}
}
<% if(this.SingleFileMode) { %>
}
<% } %>
<script runat="template">
public string GetReaderAssignmentStatement(ColumnSchema column, int index,int count)
{
string statement = GetMemberVariableName(column) + " = ";
if (column.Name.EndsWith("TypeCode"))
statement += "(" + column.Name + ")";
statement += GetMemberVariableDefaultValue(column);
if(index<count)
statement+=",";
return statement;
}
public string GetMemberVariableName(ColumnSchema column)
{
return GetPropertyName(column);
}
public string GetPropertyName(ColumnSchema column)
{
string propertyName = column.Name;
if (propertyName == column.Table.Name + "Name") return "Name";
if (propertyName == column.Table.Name + "Description") return "Description";
, propertyName.Length - );
return propertyName;
}
public string GetMemberVariableDefaultValue(ColumnSchema column)
{
switch (column.DataType)
{
case DbType.Guid:
{
return "new Guid(row[\""+column.Name+"\"].ToString())";
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
{
return "row[\""+column.Name+"\"].ToString()";
}
case DbType.Byte:
return "Byte.Parse(row[\""+column.Name+"\"].ToString())";
case DbType.Int16:
return "Int16.Parse(row[\""+column.Name+"\"].ToString())";
case DbType.Int32:
return "Int32.Parse(row[\""+column.Name+"\"].ToString())";
case DbType.Int64:
return "Int64.Parse(row[\""+column.Name+"\"].ToString())";
default:
{
return "aaaa";
}
}
}
public string GetCSharpVariableType(ColumnSchema column)
{
if (column.Name.EndsWith("TypeCode")) return column.Name;
return DbTypeCSharp[column.DataType.ToString()];
}
public string GetClassName(TableSchema table)
{
return table.Name;
}
public override string GetFileName()
{
return this.GetClassName(this.SourceTable) + "DAL.cs";
}
</script>
上面的模板生成的是1楼评论中的代码。
后面经过MVC.Net的提醒，改用构造方法来做，具体做法是：
数据访问层就不需要了，只需要一个实体层模板：
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Generates a very simple business object." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the object is based on." %>
<%@ Property Name="SingleFileMode" Type="System.Boolean" Category="Options" Default="True" Description="Generate content for a complete cs file instead of just a class." %>
<%@ Property Name="ClassNamespace" Type="System.String" Category="Options" Default="BusinessObjects" Description="Namespace your class is in. Only used if SingleFileMode is true!" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Map Name="SqlCSharp" Src="Sql-CSharp" Description="System to C# Type Map" %>
<%@ Map Name="DbDataReader" Src="DbType-DataReaderMethod" Description="DbType to DataReader Method Map" %>
<%@ Map Name="SqlNativeSqlDb" Src="SqlNativeType-SqlDbType" Description="SqlNativeType to SqlDbType Map" %>
<%@ Map Name="DbTypeCSharp" Src="DbType-CSharp" Description="DbType to CSharp Map" %>
<% if(this.SingleFileMode) { %>
using System;
using System.Data;
namespace <%= this.ClassNamespace %>
{
<% } %>
/// <summary>
/// <%= GetClassName(SourceTable) %>:实体类(属性说明自动提取数据库字段的描述信息)
/// </summary>
[Serializable]
public class <%= GetClassName(SourceTable) %>
{
#region Private Properties
<% foreach (ColumnSchema column in SourceTable.Columns) { %>
<%= GetMemberVariableDeclarationStatement(column) %>
<% } %>
#endregion
#region Public Properties
public <%= GetClassName(SourceTable) %>(DataRow row)
{
<% ; i < SourceTable.Columns.Count; i++) { %>
<%=GetMemberVariableName(SourceTable.Columns[i])%> = <%= GetMemberVariableDefaultValue1(SourceTable.Columns[i])%>;
<%}%>
}
<% ; i < SourceTable.Columns.Count; i++) { %>
/// <summary>
/// <%=SourceTable.Columns[i].Description %>
/// </summary>
public <%= GetCSharpVariableType(SourceTable.Columns[i]) %> <%= GetPropertyName(SourceTable.Columns[i]) %>
{
get {return <%= GetMemberVariableName(SourceTable.Columns[i]) %>;}
set {<%= GetMemberVariableName(SourceTable.Columns[i]) %> = value;}
}
<% ) Response.Write("\r\n"); %>
<% } %>
#endregion
}
<% if(this.SingleFileMode) { %>
}
<% } %>
<script runat="template">
public string GetMemberVariableDeclarationStatement(ColumnSchema column)
{
return GetMemberVariableDeclarationStatement("private", column);
}
public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
{
string statement = protectionLevel + " ";
statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column);
string defaultValue = GetMemberVariableDefaultValue(column);
if (defaultValue != "")
{
statement += " = " + defaultValue;
}
statement += ";";
return statement;
}
public string GetCamelCaseName(string value)
{
, ).ToLower() + value.Substring();
}
public string GetMemberVariableName(ColumnSchema column)
{
string propertyName = GetPropertyName(column);
string memberVariableName = "_" + GetCamelCaseName(propertyName);
return memberVariableName;
}
public string GetPropertyName(ColumnSchema column)
{
string propertyName = column.Name;
if (propertyName == column.Table.Name + "Name") return "Name";
if (propertyName == column.Table.Name + "Description") return "Description";
, propertyName.Length - );
return propertyName;
}
public string GetMemberVariableDefaultValue(ColumnSchema column)
{
switch (column.DataType)
{
case DbType.Guid:
{
return "Guid.Empty";
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
{
return "String.Empty";
}
default:
{
return "";
}
}
}
public string GetMemberVariableDefaultValue1(ColumnSchema column)
{
switch (column.DataType)
{
case DbType.Guid:
{
return "new Guid(row[\""+column.Name+"\"].ToString())";
}
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
{
return "row[\""+column.Name+"\"].ToString()";
}
case DbType.Byte:
return "Byte.Parse(row[\""+column.Name+"\"].ToString())";
case DbType.Int16:
return "Int16.Parse(row[\""+column.Name+"\"].ToString())";
case DbType.Int32:
return "Int32.Parse(row[\""+column.Name+"\"].ToString())";
case DbType.Int64:
return "Int64.Parse(row[\""+column.Name+"\"].ToString())";
default:
{
return "aaaa";
}
}
}
public string GetCSharpVariableType(ColumnSchema column)
{
if (column.Name.EndsWith("TypeCode")) return column.Name;
return DbTypeCSharp[column.DataType.ToString()];
}
public string GetClassName(TableSchema table)
{
return table.Name;
}
public override string GetFileName()
{
return this.GetClassName(this.SourceTable) + ".cs";
}
</script>
生成的代码是：
using System;
using System.Data;
namespace TestDbUtilityConsole
{
/// <summary>
/// Roles:实体类(属性说明自动提取数据库字段的描述信息)
/// </summary>
[Serializable]
public class Roles
{
#region Private Properties
private Guid _roleID = Guid.Empty;
private string _roleName = String.Empty;
private string _description = String.Empty;
private string _taskMask = String.Empty;
private byte _roleFlags;
#endregion
#region Public Properties
public Roles(DataRow row)
{
_roleID = new Guid(row["RoleID"].ToString());
_roleName = row["RoleName"].ToString();
_description = row["Description"].ToString();
_taskMask = row["TaskMask"].ToString();
_roleFlags = Byte.Parse(row["RoleFlags"].ToString());
}
/// <summary>
/// 列1
/// </summary>
public Guid RoleID
{
get { return _roleID; }
set { _roleID = value; }
}
/// <summary>
/// 列2
/// </summary>
public string RoleName
{
get { return _roleName; }
set { _roleName = value; }
}
/// <summary>
/// 列3
/// </summary>
public string Description
{
get { return _description; }
set { _description = value; }
}
/// <summary>
/// 列4
/// </summary>
public string TaskMask
{
get { return _taskMask; }
set { _taskMask = value; }
}
/// <summary>
/// 列5
/// </summary>
public byte RoleFlags
{
get { return _roleFlags; }
set { _roleFlags = value; }
}
#endregion
}
}
调用方法：
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Threading.Tasks;
using DbUtility;
namespace TestDbUtilityConsole
{
class Program
{
static SqlDbUtility db = new SqlDbUtility("Data Source=(local);Initial Catalog=ReportServer;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False");
static void Main(string[] args)
{
DataTable dt = GetDataTable().Result;
Stopwatch timer = new Stopwatch();
timer.Start();
; i < ; i++)
{
List<Roles> list = new List<Roles>();
Roles roles = null;
foreach (DataRow dataRow in dt.Rows)
{
roles = new Roles(dataRow);
list.Add(roles);
}
}
timer.Stop();
Console.WriteLine("遍历100W次，共耗时{0}毫秒。", timer.ElapsedMilliseconds);
Console.ReadKey();
}
static async Task<DataTable> GetDataTable()
{
DataTable dt = await db.T("select * from Roles").ExecuteDataTableAsync();
return dt;
}
}
}
结果：遍历100W次，共耗时9568毫秒。