/// <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