快捷導(dǎo)航

sql server中批量插入與更新兩種解決方案分享(asp.net)

更新時間：2012年05月10日 22:42:06 作者：

xml和表值函數(shù)的相對復(fù)雜些這里簡單貼一下bcp和SqlDataAdapter進行批量跟新插入方法，未經(jīng)整理還望見諒

若只是需要大批量插入數(shù)據(jù)使用bcp是最好的，若同時需要插入、刪除、更新建議使用SqlDataAdapter我測試過有很高的效率，一般情況下這兩種就滿足需求了
bcp方式

 
/// <summary> 
/// 大批量插入數(shù)據(jù)(2000每批次) 
/// 已采用整體事物控制 
/// </summary> 
/// <param name="connString">數(shù)據(jù)庫鏈接字符串</param> 
/// <param name="tableName">數(shù)據(jù)庫服務(wù)器上目標(biāo)表名</param> 
/// <param name="dt">含有和目標(biāo)數(shù)據(jù)庫表結(jié)構(gòu)完全一致(所包含的字段名完全一致即可)的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(); 
} 
} 
} 
} 
} 

SqlDataAdapter

復(fù)制代碼代碼如下:

 
/// <summary> 
/// 批量更新數(shù)據(jù)(每批次5000) 
/// </summary> 
/// <param name="connString">數(shù)據(jù)庫鏈接字符串</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(); 
//設(shè)置批量更新的每次處理條數(shù) 
adapter.UpdateBatchSize = 5000; 
adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////開始事務(wù) 
if (table.ExtendedProperties["SQL"] != null) 
{ 
adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString(); 
} 
adapter.Update(table); 
adapter.SelectCommand.Transaction.Commit();/////提交事務(wù) 
} 
catch (Exception ex) 
{ 
if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null) 
{ 
adapter.SelectCommand.Transaction.Rollback(); 
} 
throw ex; 
} 
finally 
{ 
conn.Close(); 
conn.Dispose(); 
} 
} 