若只是需要大批量插入數(shù)據(jù)使用bcp是最好的，若同時(shí)需要插入、刪除、更新建議使用SqlDataAdapter我測(cè)試過(guò)有很高的效率，一般情況下這兩種就滿足需求了
bcp方式
復(fù)制代碼 代碼如下:
/// summary>
/// 大批量插入數(shù)據(jù)(2000每批次)
/// 已采用整體事物控制
/// /summary>
/// param name="connString">數(shù)據(jù)庫(kù)鏈接字符串/param>
/// param name="tableName">數(shù)據(jù)庫(kù)服務(wù)器上目標(biāo)表名/param>
/// param name="dt">含有和目標(biāo)數(shù)據(jù)庫(kù)表結(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ù)庫(kù)鏈接字符串/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è)置批量更新的每次處理?xiàng)l數(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();
}
}
您可能感興趣的文章:- SQLServer批量更新兩個(gè)關(guān)聯(lián)表數(shù)據(jù)的方法
- SQLServer用存儲(chǔ)過(guò)程實(shí)現(xiàn)插入更新數(shù)據(jù)示例
- SQLSERVER 本地查詢更新操作遠(yuǎn)程數(shù)據(jù)庫(kù)的代碼
- 數(shù)據(jù)庫(kù)更新Sqlserver腳本總結(jié)
- sql server中批量插入與更新兩種解決方案分享(存儲(chǔ)過(guò)程)
- Sqlserver 2005使用XML一次更新多條記錄的方法
- SQL Server自動(dòng)更新統(tǒng)計(jì)信息的基本算法
- SQL Server 利用觸發(fā)器對(duì)多表視圖進(jìn)行更新的實(shí)現(xiàn)方法
- SQL Server實(shí)現(xiàn)顯示每個(gè)類別最新更新數(shù)據(jù)的方法
