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         }
注意：以上代码不能直接拷贝执行，只是用法示例