/// 
    /// 将泛类型集合List类转换成DataTable    /// 
    /// 泛类型集合    /// 
         public static DataTable ListToDataTable
     
      (List
      
        entitys)    {        //检查实体集合不能为空        if (entitys == null || entitys.Count < 1)        {            throw new Exception("需转换的集合为空");        }        //取出第一个实体的所有Propertie        Type entityType = entitys[0].GetType();        PropertyInfo[] entityProperties = entityType.GetProperties();         //生成DataTable的structure        //生产代码中，应将生成的DataTable结构Cache起来，此处略        DataTable dt = new DataTable();        for (int i = 0; i < entityProperties.Length; i++)        {            //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);            dt.Columns.Add(entityProperties[i].Name);        }        //将所有entity添加到DataTable中        foreach (object entity in entitys)        {            //检查所有的的实体都为同一类型            if (entity.GetType() != entityType)            {                throw new Exception("要转换的集合元素类型不一致");            }            object[] entityValues = new object[entityProperties.Length];            for (int i = 0; i < entityProperties.Length; i++)            {                entityValues[i] = entityProperties[i].GetValue(entity, null);            }            dt.Rows.Add(entityValues);        }        return dt;    }
      
     

 

 

public static class DataTableHelper    {        public static DataTable ConvertTo
     
      (IList
      
        list)        {            DataTable table = CreateTable
       
        ();            Type entityType = typeof(T);            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);            foreach (T item in list)            {                DataRow row = table.NewRow();                foreach (PropertyDescriptor prop in properties)                    row[prop.Name] = prop.GetValue(item);                table.Rows.Add(row);            }            return table;        }         public static IList
        
          ConvertTo
         
          (IList
          
            rows) { IList
           
             list = null; if (rows != null) { list = new List
            
             (); foreach (DataRow row in rows) { T item = CreateItem
             
              (row); list.Add(item); } } return list; } public static IList
              
                ConvertTo
               
                (DataTable table) { if (table == null) return null; List
                
                  rows = new List
                 
                  (); foreach (DataRow row in table.Rows) rows.Add(row); return ConvertTo
                  
                   (rows); } //Convert DataRow into T Object public static T CreateItem
                   
                    (DataRow row) { string columnName; T obj = default(T); if (row != null) { obj = Activator.CreateInstance
                    
                     (); foreach (DataColumn column in row.Table.Columns) { columnName = column.ColumnName; //Get property with same columnName PropertyInfo prop = obj.GetType().GetProperty(columnName); try { //Get value for the column object value = (row[columnName].GetType() == typeof(DBNull)) ? null : row[columnName]; //Set property value if (prop.CanWrite) //判断其是否可写 prop.SetValue(obj, value, null); } catch { throw; //Catch whatever here } } } return obj; } public static DataTable CreateTable
                     
                      () { Type entityType = typeof(T); DataTable table = new DataTable(entityType.Name); PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType); foreach (PropertyDescriptor prop in properties) table.Columns.Add(prop.Name, prop.PropertyType); return table; } }
                     
                    
                   
                  
                 
                
               
              
             
            
           
          
         
        
       
      
     

 

private void DataTable2Db(DataTable dataTable)        {            using (IDbConnection dbConnection = new SqlConnection(Configurator.DbConnectionString))            {                if (dbConnection.State != ConnectionState.Open)                {                    dbConnection.Open();                }                using (var transaction = dbConnection.BeginTransaction())                {                    try                    {                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)dbConnection,                                                                                  SqlBulkCopyOptions.Default,                                                                                  (SqlTransaction)transaction))                        {                            bulkCopy.DestinationTableName = "Record";                            bulkCopy.WriteToServer(dataTable);                        }                        transaction.Commit();                    }                    catch (Exception exception)                    {                        transaction.Rollback();                                              throw new Exception("Record持久化异常", exception);                    }                }            }        }

#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中        /// 
        /// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中        /// 
        /// 数据库中对应的表名        /// 数据集        public void SqlBulkCopyInsert(string strTableName, DataTable dtData)        {            string ConStr = connectionString;// 数据库连接字符串              try            {                using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(ConStr))//引用SqlBulkCopy                {                    sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名                      sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据                      sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库                      sqlRevdBulkCopy.Close();//关闭连接                }            }            catch (Exception ex)            {                WriteErrorLog(ex.Message + "数据库处理出错654行。SqlBulkCopyInsert");                throw (ex);            }        }        #endregion//BCP copy      SqlConnection conn = new SqlConnection();      conn.ConnectionString = "server=.;uid=dmkj_hpc;pwd=#$wlh*&1110h%c;database=DMKJ_SMS";      conn.Open();        SqlTransaction sqlbulkTransaction = conn.BeginTransaction();        //请在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务      SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);          copy.DestinationTableName = "T_SMS_SendInfo";      foreach (DataColumn dc in dataTable.Columns)      {          copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);        }      try      {          copy.WriteToServer(dataTable);          sqlbulkTransaction.Commit();      }      catch (Exception ex)      {          sqlbulkTransaction.Rollback();          Console.WriteLine(ex.ToString());      }      finally      {          copy.Close();          conn.Close();      }

 

using (var conn = GetWriteDbConnection(true))            using (var trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))            {                var inserted = await conn.ExecuteAsync(sql, records, trans);                if (inserted != records.Count)                {                    trans.Rollback();                    return new List
     
      ();                }                trans.Commit();            }
     

 

使用SqlBulkCopy将DataTable中的数据批量插入数据库中