/// <summary>/// 大批量插入数据(2000每批次)/// 已采用整体事物控制/// </summary>/// <param name="connString">数据库链接字符串</param>/// <param name="tableName">数据库服务器上目标表名</param>/// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable</param>public static void BulkCopy(string connString, string tableName, DataTable dt){ using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); using (SqlTransaction transaction = conn.BeginTransaction()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction)) { bulkCopy.BatchSize = 2000; bulkCopy.BulkCopyTimeout = _CommandTimeOut; bulkCopy.DestinationTableName = tableName; try { foreach (DataColumn col in dt.Columns) { bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulkCopy.WriteToServer(dt); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { conn.Close(); } } } }} |
|
|
/// <summary>/// 批量更新数据(每批次5000)/// </summary>/// <param name="connString">数据库链接字符串</param>/// <param name="table"></param>public static void Update(string connString, DataTable table){ SqlConnection conn = new SqlConnection(connString); SqlCommand comm = conn.CreateCommand(); comm.CommandTimeout = _CommandTimeOut; comm.CommandType = CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(comm); SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter); commandBulider.ConflictOption = ConflictOption.OverwriteChanges; try { conn.Open(); //设置批量更新的每次处理条数 adapter.UpdateBatchSize = 5000; adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务 if (table.ExtendedProperties["SQL"] != null) { adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString(); } adapter.Update(table); adapter.SelectCommand.Transaction.Commit();/////提交事务 } catch (Exception ex) { if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null) { adapter.SelectCommand.Transaction.Rollback(); } throw ex; } finally { conn.Close(); conn.Dispose(); }} |