list转datatable,SqlBulkCopy将DataTable中的数据批量插入数据库

/// <summary>
    /// 将泛类型集合List类转换成DataTable
    /// </summary>
    /// <param name="list">泛类型集合</param>
    /// <returns></returns>
    public static DataTable ListToDataTable<T>(List<T> 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<T>(IList<T> list)
        {
            DataTable table = CreateTable<T>();
            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<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 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);
        }
 
        //Convert DataRow into T Object
        public static T CreateItem<T>(DataRow row)
        {
            string columnName;
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();
                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<T>()
        {
            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中的数据批量插入数据库中  
      /// <summary>  
      /// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中  
      /// </summary>  
      /// <param name="strTableName">数据库中对应的表名</param>  
      /// <param name="dtData">数据集</param>  
      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<BarCode>();
                }
                trans.Commit();
            }

 

使用SqlBulkCopy将DataTable中的数据批量插入数据库中https://blog.csdn.net/u013938578/article/details/78886884