SqlBulkCopy 批量插入数据库
/// <summary> /// 批量插入 注:DT的tableName为要更新的数据库表名,DT的列名和数据库一致 /// </summary> /// <param name="dt"></param> /// <remarks></remarks> public void tsSqlBulkCopy(DataTable dt) { try { using (SqlBulkCopy sqlCopy = new SqlBulkCopy(this.connection, SqlBulkCopyOptions.FireTriggers, this.transaction)) { string sql = "select * from " + dt.TableName + " where 1=-1"; DataTable DBTable = ExecuteDataTable(sql); List<string> DBList = new List<string>(); for (int i = 0; i <= DBTable.Columns.Count - 1; i++) { DBList.Add(DBTable.Columns[i].ColumnName); } List<string> ParList = new List<string>(); for (int i = 0; i <= dt.Columns.Count - 1; i++) { ParList.Add(dt.Columns[i].ColumnName); } //添加对应列 foreach (string item in DBList) { if (ParList.Contains(item)) { sqlCopy.ColumnMappings.Add(item, item); } } sqlCopy.DestinationTableName = dt.TableName; sqlCopy.BatchSize = dt.Rows.Count; sqlCopy.WriteToServer(dt); } } catch (Exception ex) { throw ex; } }