使用SqlBulkCopy批量插入多条数据进入表中

public static int InsertSettlementRecord(SqlConnection connection, List<InventorySettlement> model) 2 { 3 DataTable dt = GetTableSchema(); 4 SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);//实例化SqlBulkCopy对象 5 bulkCopy.DestinationTableName = "Tuhu_shop.dbo.InventorySettlement";//需要插入的表的表名 6 bulkCopy.BatchSize = dt.Rows.Count; 7 string sql = "SELECT MAX(BatchNo) AS BatchNo FROM Tuhu_shop.dbo.InventorySettlement WITH (NOLOCK)"; 8 var maxBatchNo = SqlHelper.ExecuteDataTable(connection, CommandType.Text, sql).ConvertTo<InventorySettlement>().ToList();//获得当前表中的最大批次号，以后每次添加的一批数据都在当前批次上加1， 9 int? maxBatch = 0; 10 if (maxBatchNo[0].BatchNo == null)//对批次号的判断 11 { 12 maxBatch = 0; 13 } 14 else 15 { 16 maxBatch = maxBatchNo[0].BatchNo;//对批次号赋值 17 } 18 19 foreach (var item in model)//循环向datatable中插入数据 20 { 21 DataRow r = dt.NewRow(); 22 r[0] = item.ShopId; 23 r[1] = item.PID; 24 r[2] = item.PName; 25 r[3] = item.SettlementCount; 26 r[4] = item.SettlementPrice; 27 r[5] = item.SumMoney; 28 r[6] = item.SettlementStatus; 29 r[7] = maxBatch+1; 30 r[8] = DateTime.Now; 31 r[9] = item.Settlementor; 32 dt.Rows.Add(r); 33 34 } 35 foreach (DataColumn dc in dt.Columns) 36 { 37 bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//使列对应，这段代码很重要，自己亲测少了这一句会插入失败 38 } 39 40 if (dt != null && dt.Rows.Count != 0) 41 { 42 bulkCopy.WriteToServer(dt);//通过SqlBulkCopy一次性插入到数据库表中 43 return 1; 44 } 45 else 46 { 47 return 0; 48 } 49 } 50 51 使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。 52 来自 <http://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html> 53 public static DataTable GetTableSchema() 54 { 55 DataTable dt = new DataTable(); 56 dt.Columns.AddRange(new DataColumn[]{ 57 //new DataColumn("PKID",typeof(int)), 58 new DataColumn("ShopId",typeof(int)), 59 new DataColumn("PID",typeof(string)), 60 new DataColumn("PName",typeof(string)), 61 new DataColumn("SettlementCount",typeof(int)), 62 new DataColumn("SettlementPrice",typeof(decimal)), 63 new DataColumn("SumMoney",typeof(decimal)), 64 new DataColumn("SettlementStatus",typeof(bool)), 65 new DataColumn("BatchNo",typeof(int)), 66 new DataColumn("CreateDateTime",typeof(DateTime)), 67 new DataColumn("Settlementor",typeof(string)) 68 }); 69 70 return dt; 71 }

 

使用SqlBulkCopy批量插入多条数据进入表中

标签：