【下载文档:  sql server中批量插入与更新两种解决方案分享(asp.net).txt 】

---

sql server中批量插入与更新两种解决方案分享(asp.net)
若只是需要大批量插入数据使用bcp是最好的，若同时需要插入、删除、更新建议使用SqlDataAdapter我测试过有很高的效率，一般情况下这两种就满足需求了 bcp方式 复制代码 代码如下: ///

/// 大批量插入数据(2000每批次) /// 已采用整体事物控制 ///

/// 数据库链接字符串 /// 数据库服务器上目标表名 /// 含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable 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(); } } } } } SqlDataAdapter 复制代码 代码如下: ///

/// 批量更新数据(每批次5000) ///

/// 数据库链接字符串 /// 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(); } }