大資料批量插入資料庫

標籤：

public void ExecuteBuklCopy(DataTable dt)
{
SqlConnection conn = new SqlConnection(connectionString );
if (dt == null || dt.Rows.Count <= 0 || String.IsNullOrEmpty ( dt.TableName ) )
{ return; }
if (conn.State != ConnectionState.Open)
{
conn.Open();
}

SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
//請在插入資料的同時檢查約束，如果發生錯誤調用sqlbulkTransaction事務
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);

copy.DestinationTableName = dt.TableName;
foreach (DataColumn dc in dt.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

}
try
{
//每次10000條
if (dt.Rows.Count > 10000)
{

System.Collections.Generic.List<DataRow> _list = new System.Collections.Generic.List<DataRow>();
int i = 0;
for (i = 0; i < dt.Rows.Count; i++)
{
if (i > 0 && i % 10000 == 0)
{
copy.WriteToServer(_list.ToArray());
sqlbulkTransaction.Commit();
_list.Clear();
}
//else
{
_list.Add(dt.Rows[i]);
}
}
if (_list.Count > 0)
{
copy.WriteToServer(_list.ToArray());
sqlbulkTransaction.Commit();
}
}
else
{
copy.WriteToServer(dt);
sqlbulkTransaction.Commit();
}
}
catch (Exception ex)
{
sqlbulkTransaction.Rollback();
Console.WriteLine(ex.ToString());
}
finally
{
copy.Close();
conn.Close();
}

}

大資料批量插入資料庫