MSSQL使用sqlbulkcopy批量插入数据
具体代码如下：
1 /// <summary> 2 /// 批量插入数据到BayonetZipFailedPic表 3 /// </summary> 4 /// <param name="bayonetFailedPicList">含有多条拷贝失败的二次卡口图片数据的集合</param> 5 /// <returns>0-成功，-2-异常，其他-失败</returns> 6 public int BatchAddBayonetZipFailedPic(List<BayonetZipFailedPic> bayonetFailedPicList) 7 { 8 int result = 0; 9 try 10 { 11 DataTable dataTable = GetBayonetZipFailedPicTableSchema(); 12 foreach (BayonetZipFailedPic bayonetFailedPic in bayonetFailedPicList) 13 { 14 DataRow dataRow = dataTable.NewRow(); 15 dataRow[1] = bayonetFailedPic.ZipFileID; 16 dataRow[2] = bayonetFailedPic.FileOriName; 17 dataRow[3] = bayonetFailedPic.FileFullPath; 18 dataRow[4] = System.DateTime.Now;//bayonetPic.OperateTime; 19 20 dataTable.Rows.Add(dataRow); 21 } 22 return BatchAddBayonetZipFailedPic(dataTable, "BayonetZipFailedPic"); 23 } 24 catch (Exception exception) 25 { 26 logger.Error("批量插入BayonetZipFailedPic数据异常！", exception); 27 result = -2; 28 } 29 return result; 30 } 31 32 33 34 /// <summary> 35 /// 创建和BayonetPic表对应的DataTable对象 36 /// </summary> 37 /// <returns>DataTable对象</returns> 38 private static DataTable GetBayonetPicTableSchema() 39 { 40 DataTable dataTable = new DataTable(); 41 dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("ID"), new DataColumn("FolderID",typeof(System.Data.SqlTypes.SqlGuid)), new DataColumn("FileOriName"), 42 new DataColumn("FileName"), new DataColumn("FileFullPath"), new DataColumn("Longitude"),new DataColumn("Latitude"),new DataColumn("Address"),new DataColumn("Contacts"), new DataColumn("ContactWay"), new DataColumn("PicStartTime"), 43 new DataColumn("PicEndTime"), new DataColumn("OperateTime"), new DataColumn("Status"),new DataColumn("Width"),new DataColumn("Height")}); 44 45 return dataTable; 46 } 47 48 /// <summary> 49 /// 批量插入数据到BayonetPic表 50 /// </summary> 51 /// <param name="bayonetPicList">含有多条二次卡口图片数据的集合</param> 52 /// <returns>0-成功，-2-异常，其他-失败</returns> 53 public int BatchAddBayonetPic(List<BayonetPic> bayonetPicList) 54 { 55 int result = 0; 56 try 57 { 58 DataTable dataTable = GetBayonetPicTableSchema(); 59 foreach (BayonetPic bayonetPic in bayonetPicList) 60 { 61 DataRow dataRow = dataTable.NewRow(); 62 dataRow[1] = new Guid(bayonetPic.FolderID); 63 dataRow[2] = bayonetPic.FileOriName; 64 dataRow[3] = bayonetPic.FileName; 65 dataRow[4] = bayonetPic.FileFullPath; 66 dataRow[5] = bayonetPic.Longitude; 67 dataRow[6] = bayonetPic.Latitude; 68 dataRow[7] = bayonetPic.Address; 69 dataRow[8] = bayonetPic.Contacts; 70 dataRow[9] = bayonetPic.ContactWay; 71 dataRow[10] = bayonetPic.PicStartTime; 72 dataRow[11] = bayonetPic.PicEndTime; 73 dataRow[12] = System.DateTime.Now;//bayonetPic.OperateTime; 74 dataRow[13] = bayonetPic.Status; 75 dataRow[14] = bayonetPic.Width; 76 dataRow[15] = bayonetPic.Height; 77 dataTable.Rows.Add(dataRow); 78 } 79 return BatchAddBayonetPic(dataTable, "BayonetPic"); 80 } 81 catch (Exception exception) 82 { 83 logger.Error("批量插入BayonetPic数据异常！", exception); 84 result = -2; 85 } 86 return result; 87 } 88 89 private int BatchAddBayonetPic(DataTable dt, string tableName) 90 { 91 int result = 0; 92 DBManager dbManager = this.dbConnector.GetDbManager(ConUtil.CaseId); 93 if (dbManager == null) 94 { 95 logger.Error("数据库连接未建立！"); 96 result = -1; 97 } 98 else 99 { 100 dbManager.SqlBulkCopyInsert(this.ConnectionString, this.DbType, dt, tableName); 101 result = 0; 102 } 103 return result; 104 } 105 106 public static int SqlBulkCopyInsert(string connectionString, ProviderType providerType, DataTable dataTable, string tableName) 107 { 108 if (connectionString == null || connectionString.Length == 0) 109 { 110 throw new ArgumentNullException("connectionString is null or empty!"); 111 } 112 int result = 0; 113 using (DbConnection dbConnection = DbFactory.GetProvider(providerType).CreateConnection()) 114 { 115 SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); 116 sqlBulkCopy.DestinationTableName = tableName; 117 sqlBulkCopy.BatchSize = dataTable.Rows.Count; 118 dbConnection.ConnectionString = connectionString; 119 dbConnection.Open(); 120 if (dataTable != null && dataTable.Rows.Count != 0) 121 { 122 sqlBulkCopy.WriteToServer(dataTable); 123 } 124 sqlBulkCopy.Close(); 125 dbConnection.Close(); 126 } 127 return result; 128 }
注意：以上代码不能直接拷贝执行，只是用法示例
