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
 
序号 列名 数据类型 长度 小数位 标识 主键 允许空 默认值 说明
1 RoleID uniqueidentifier 16 0     否    
2 RoleName nvarchar 260 0     否    
3 Description nvarchar 512 0     否    
4 TaskMask nvarchar 32 0     否    
5 RoleFlags tinyint 1 0     否    

原本想修改动软代码生成器的模板来生成改写过的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毫秒。

最新文章

  1. myeclipse下构建maven web项目
  2. 洛谷P1126机器人搬重物[BFS]
  3. jquey ajax 无刷新提交form
  4. Ubuntu下安装eclipse及PyDev插件注意事项
  5. ImageMagick的使用
  6. Sencha Touch id 和 itemId
  7. CenOS搭建FTP服务器
  8. java新手笔记11 类的静态属性、方法(单例）
  9. jQuery随记
  10. python 中函数参数传递形式
  11. bat调用带参数存储过程
  12. 史上最全的IntelliJIdea快捷键
  13. c++ 重点随记
  14. 【小o地图Excel插件版】不止能做图表，还能抓58、大众点评网页数据...
  15. Android Studio 学习（六）内容提供器
  16. 基于CentOS体验万象优图鉴黄服务
  17. javascript 高级程序设计 二
  18. TensorFlow-Python：创建空列表list与append的用法
  19. mysql中,什么是视图,视图的作用是什么?
  20. Oracle 空间查询, 数据类型为 sdo_geometry

热门文章

  1. Java的多线程(上)
  2. PHPExcell单元格中某些时间格式的内容不能正确获得的处理办法
  3. mvc知识应用
  4. linux性能分析命令和性能观察工具
  5. 第一篇！in和exists性能比较和使用
  6. [转] postgresql常用命令
  7. Linux随笔
  8. 检查主机是否存活的shell脚本
  9. SQL Server 数据库邮件 配置
  10. java SSH整合配置