public static void WriteTableToDataBase(DataTable table, SqlConnection conn, SqlRowsCopiedEventHandler eventHandler = null, params string[] columns)
{
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.SqlRowsCopied += (obj, arg) =>
{
SqlBulkCopyHelper.NotifyCount++;
};
if (eventHandler != null)
{
bulkCopy.SqlRowsCopied += eventHandler;
}
bulkCopy.NotifyAfter = SqlBulkCopyHelper.NotifyAfterCount;
bulkCopy.BatchSize = 20000;
bulkCopy.DestinationTableName = table.TableName;
//通过列映射，可以使外部的table顺序任意变化
foreach (var item in columns)
{
if (string.IsNullOrEmpty(item))
{
continue;
}
bulkCopy.ColumnMappings.Add(item, item);
}
bulkCopy.WriteToServer(table, DataRowState.Added);
}
TableCount++;
}
public void SqlInsertBatch(DataTable data, SqlRowsCopiedEventHandler onSqlRowsCopied = null, IDbTransaction transaction = null)
{
var tblName = data.TableName;
var tran = (SqlTransaction)transaction;
SqlConnection conn = null;
if (tran != null)
{
conn = tran.Connection;
}
else
{
conn = this.DbConnection() as SqlConnection;
}
using (conn)
{
conn.Open();
using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
{
bulkCopy.DestinationTableName = tblName;
for (int i = 0; i < data.Columns.Count; i++)
{
bulkCopy.ColumnMappings.Add(data.Columns[i].ColumnName, data.Columns[i].ColumnName);
}
bulkCopy.BatchSize = 1000;
bulkCopy.NotifyAfter = 1000;
bulkCopy.SqlRowsCopied += onSqlRowsCopied;
bulkCopy.WriteToServer(data);
}
}
}
/// <summary>
/// Sets callback method for sql bulk insert
/// </summary>
/// <param name="callback"></param>
/// <param name="interval">Numbers of rows processed before callback is invoked</param>
/// <returns></returns>
public BulkInsertOptions Callback(SqlRowsCopiedEventHandler callback, int interval)
{
CallbackMethod = callback;
NotifyAfterValue = interval;
return(this);
}
public static void BulkCopy(DataTable data,
string targetConnectionString, string targetTableName,
int timeOut = 30, bool keepIdentity = true, int batchSize = 2000,
SqlRowsCopiedEventHandler copiedEventHandler = null)
{
if (data == null || data.Rows.Count <= 0)
{
return;
}
DataTableReader sourceData = data.CreateDataReader();
var opn = keepIdentity ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default;
using (SqlBulkCopy bcp = new SqlBulkCopy(targetConnectionString, opn))
{
bcp.BulkCopyTimeout = timeOut;
if (copiedEventHandler != null)
{
bcp.SqlRowsCopied += copiedEventHandler; // 用于进度显示
}
bcp.BatchSize = batchSize;
bcp.NotifyAfter = batchSize;// 设置为1，状态栏提示比较准确，但是速度很慢
bcp.DestinationTableName = targetTableName;
// 设置同名列的映射,避免建表语句列顺序不一致导致无法同步的bug
List <string> arrColNames = GetColNames(sourceData);
foreach (string colName in arrColNames)
{
bcp.ColumnMappings.Add(colName, colName);
}
bcp.WriteToServer(sourceData);
}
}
/// <summary>
/// 把DataTable里的数据通过SqlBulkCopy复制到当前数据库的指定表中
/// </summary>
/// <param name="sourceData">数据源</param>
/// <param name="targetTableName">目标表名</param>
/// <param name="timeOut">超时之前操作完成所需的秒数</param>
/// <param name="keepIdentity">是否保留源标识值，如果为false，则由目标分配标识值</param>
/// <param name="batchSize">每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器</param>
/// <param name="copiedEventHandler">在每次处理完 batchSize条记录时触发此事件</param>
public void BulkCopy(DataTable sourceData, string targetTableName,
int timeOut = 30, bool keepIdentity = true, int batchSize = 2000,
SqlRowsCopiedEventHandler copiedEventHandler = null)
{
IDataReader reader = sourceData.CreateDataReader();
BulkCopy(reader, targetTableName, timeOut, keepIdentity, batchSize, copiedEventHandler);
}
private void OnRowsCopied(SqlRowsCopiedEventArgs value)
{
SqlRowsCopiedEventHandler handler = this._rowsCopiedEventHandler;
if (handler != null)
{
handler(this, value);
}
}
/// <summary>
/// Extends BeginInvoke so that when a state object is not needed, null does not need to be passed.
/// <example>
/// sqlrowscopiedeventhandler.BeginInvoke(sender, e, callback);
/// </example>
/// </summary>
public static IAsyncResult BeginInvoke(this SqlRowsCopiedEventHandler sqlrowscopiedeventhandler, Object sender, SqlRowsCopiedEventArgs e, AsyncCallback callback)
{
if (sqlrowscopiedeventhandler == null)
{
throw new ArgumentNullException("sqlrowscopiedeventhandler");
}
return(sqlrowscopiedeventhandler.BeginInvoke(sender, e, callback, null));
}
public SqlServerBulkCopyCommand(DbSession owner,
string tableName, DataTable data, Dictionary <string, string> mapping, SqlRowsCopiedEventHandler notifyHandler, int notifyAfter)
: base(owner, BeeDbCommandBehavior.NoQuery)
{
this.tableName = tableName;
this.data = data;
this.mapping = mapping;
this.notifyHandler = notifyHandler;
this.notifyAfter = notifyAfter;
}
/// <summary>
/// After every [notifyAfter] records, the event handler will be fired.
/// </summary>
/// <param name="eventHandler">The event handler.</param>
/// <param name="notifyAfter">The notify after.</param>
/// <returns>SqlServerInsertBulk.</returns>
public SqlServerInsertBulk WithNotifications(SqlRowsCopiedEventHandler eventHandler, int notifyAfter)
{
if (eventHandler == null)
{
throw new ArgumentNullException(nameof(eventHandler), $"{nameof(eventHandler)} is null.");
}
if (notifyAfter <= 0)
{
throw new ArgumentException($"{nameof(notifyAfter)} must be greater than 0.", nameof(notifyAfter));
}
m_EventHandler = eventHandler;
m_NotifyAfter = notifyAfter;
return(this);
}
internal static void InnerTest(SqlRowsCopiedEventHandler eventHandler)
{
bool hitException = false;
try
{
if (null == _tx || null == _tx.Connection)
{
_tx = _dstConn.BeginTransaction();
_dstcmd.Transaction = _tx;
_dstcmd.ExecuteNonQuery();
}
SqlBulkCopy bulkcopy;
using (bulkcopy = new SqlBulkCopy(_dstConn, SqlBulkCopyOptions.Default, _tx))
{
bulkcopy.SqlRowsCopied += eventHandler;
bulkcopy.DestinationTableName = _dstTable;
bulkcopy.NotifyAfter = 10;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "orderid");
ColumnMappings.Add(1, "customerid");
ColumnMappings.Add(4, "rdate");
ColumnMappings.Add(7, "freight");
ColumnMappings.Add(8, "shipname");
bulkcopy.WriteToServer(_dataTable);
bulkcopy.SqlRowsCopied -= eventHandler;
_tx.Commit();
}
}
catch (Exception e)
{
while (null != e.InnerException)
{
e = e.InnerException;
}
Assert.True(e is InvalidOperationException, "Unexpected exception type: " + e.GetType());
Assert.True(e.Message.Contains(expectedErrorMsg), string.Format("Incorrect error message. Expected: {0}. Actual: {1}.", expectedErrorMsg, e.Message));
_tx.Dispose();
hitException = true;
}
Assert.True(hitException, "Did not encounter expected exception.");
}
public static void WriteTableToDataBase(DataTable table, string conn, SqlRowsCopiedEventHandler eventHandler = null)
{
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.SqlRowsCopied += (obj, arg) =>
{
SqlBulkCopyHelper.NotifyCount++;
};
if (eventHandler != null)
{
bulkCopy.SqlRowsCopied += eventHandler;
}
bulkCopy.NotifyAfter = SqlBulkCopyHelper.NotifyAfterCount;
bulkCopy.BatchSize = 20000;
bulkCopy.DestinationTableName = table.TableName;
bulkCopy.WriteToServer(table);
}
TableCount++;
}
public override void BulkCopy(TableMapping tableMapping, IList list, Dictionary <string, object> bulkCopyConfig)
{
this.PrepareBulkCopy(tableMapping, list);
DataTable dataTable = this.GetFillTable(tableMapping, list);
if (bulkCopyConfig == null)
{
bulkCopyConfig = new Dictionary <string, object>();
}
SqlBulkCopyOptions options = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers;
if (bulkCopyConfig.ContainsKey("BulkCopyOptions"))
{
options = (SqlBulkCopyOptions)bulkCopyConfig["BulkCopyOptions"];
}
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DatabaseSession.Database.ConnectionString, options);
using (sqlBulkCopy)
{
SqlRowsCopiedEventHandler evtHandler = bulkCopyConfig.GetKeyValue <SqlRowsCopiedEventHandler>("SqlRowsCopied");
if (null != evtHandler)
{
sqlBulkCopy.SqlRowsCopied += evtHandler;
}
sqlBulkCopy.DestinationTableName = this.GetTableName(tableMapping.Name);
sqlBulkCopy.BulkCopyTimeout = bulkCopyConfig.GetKeyValue <int>("BulkCopyTimeout", 600);
sqlBulkCopy.NotifyAfter = bulkCopyConfig.GetKeyValue <int>("NotifyAfter", 0);
sqlBulkCopy.BatchSize = bulkCopyConfig.GetKeyValue <int>("BatchSize", 0);
sqlBulkCopy.WriteToServer(dataTable);
if (null != evtHandler)
{
evtHandler.Invoke(null, new SqlRowsCopiedEventArgs(list.Count));
}
}
}
/// <summary>
/// Advanced Settings for SQLBulkCopy class.
/// </summary>
/// <param name="bulkcopy">The bulkcopy.</param>
/// <param name="bulkCopyEnableStreaming">if set to <c>true</c> [bulk copy enable streaming].</param>
/// <param name="bulkCopyBatchSize">Size of the bulk copy batch.</param>
/// <param name="bulkCopyNotifyAfter">The bulk copy notify after.</param>
/// <param name="bulkCopyTimeout">The bulk copy timeout.</param>
/// <param name="eventHandler">The event handler for copied rows.</param>
private static void SetSettings(this SqlBulkCopy bulkcopy, bool bulkCopyEnableStreaming, int?bulkCopyBatchSize, int?bulkCopyNotifyAfter, int bulkCopyTimeout, SqlRowsCopiedEventHandler eventHandler)
{
bulkcopy.EnableStreaming = bulkCopyEnableStreaming;
if (bulkCopyBatchSize.HasValue)
{
bulkcopy.BatchSize = bulkCopyBatchSize.Value;
}
if (bulkCopyNotifyAfter.HasValue)
{
bulkcopy.NotifyAfter = bulkCopyNotifyAfter.Value;
}
bulkcopy.SqlRowsCopied += eventHandler;
bulkcopy.BulkCopyTimeout = bulkCopyTimeout;
}
/// <summary>
/// 把DataReader里的数据通过SqlBulkCopy复制到目标数据库的指定表中
/// </summary>
/// <param name="sourceData">数据源</param>
/// <param name="targetConnectionString">目标数据库连接字符串</param>
/// <param name="targetTableName">目标表名</param>
/// <param name="timeOut">超时之前操作完成所需的秒数</param>
/// <param name="keepIdentity">是否保留源标识值，如果为false，则由目标分配标识值</param>
/// <param name="batchSize">每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器</param>
/// <param name="copiedEventHandler">在每次处理完 batchSize条记录时触发此事件</param>
public static void BulkCopy(IDataReader sourceData,string targetConnectionString,string targetTableName,int timeOut = 30,bool keepIdentity = true,int batchSize = 2000,SqlRowsCopiedEventHandler copiedEventHandler = null)
{
SqlBulkCopyOptions opn = keepIdentity ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default;
using (SqlBulkCopy bcp = new SqlBulkCopy(targetConnectionString,opn)){
bcp.BulkCopyTimeout = timeOut;
if(copiedEventHandler != null){
bcp.SqlRowsCopied += copiedEventHandler;
}
bcp.BatchSize = batchSize;
bcp.NotifyAfter = batchSize;
bcp.DestinationTableName = targetTableName;
List<string> arrColNames = GetColNames(sourceData);
foreach(string colName in arrColNames){
bcp.ColumnMappings.Add(colName,colName);
}
bcp.WriteToServer(sourceData);
}
}
public int BulkToDB(DataTable Datatable, DbConnection Conn, int NotifyAfter = 0, SqlRowsCopiedEventHandler RowsCopied = null, bool UseTransaction = false)
{
SqlConnection theConn = GetConnection(Conn);
SqlTransaction theTrans = null;
if (UseTransaction)
{
theTrans = theConn.BeginTransaction();
}
SqlBulkCopy theBulkCopy = new SqlBulkCopy(theConn, SqlBulkCopyOptions.Default, theTrans);
theBulkCopy.DestinationTableName = Datatable.TableName;
theBulkCopy.BatchSize = Datatable.Rows.Count;
theBulkCopy.BulkCopyTimeout = 300;
if (RowsCopied != null)
{
theBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(RowsCopied);
}
if (NotifyAfter > 0)
{
theBulkCopy.NotifyAfter = NotifyAfter;
}
try
{
if (Datatable != null && Datatable.Rows.Count != 0)
{
theBulkCopy.WriteToServer(Datatable);
}
if (theTrans != null)
{
theTrans.Commit();
}
return(Datatable.Rows.Count);
}
catch (Exception ex)
{
if (theTrans != null)
{
theTrans.Rollback();
}
throw ex;
}
finally
{
theConn.Close();
if (theBulkCopy != null)
{
theBulkCopy.Close();
}
}
}
public static void WriteTableToDataBaseByReader(IDataReader reader, string tableName, string conn, SqlRowsCopiedEventHandler eventHandler = null, Dictionary <string, string> mapping = null)
{
Console.WriteLine("开始写数据库");
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.BulkCopyTimeout = 300;
bulkCopy.SqlRowsCopied += (obj, arg) =>
{
SqlBulkCopyHelper.NotifyCount++;
};
if (eventHandler != null)
{
bulkCopy.SqlRowsCopied += eventHandler;
}
bulkCopy.NotifyAfter = SqlBulkCopyHelper.NotifyAfterCount;
bulkCopy.BatchSize = 20000;
bulkCopy.DestinationTableName = tableName;
if (mapping != null)
{
foreach (var item in mapping)
{
bulkCopy.ColumnMappings.Add(item.Key, item.Value);
}
}
bulkCopy.WriteToServer(reader);
}
TableCount++;
Console.WriteLine("写数据库结束");
}
/// <summary>
/// 把DataTable里的数据通过SqlBulkCopy复制到目标数据库的指定表中
/// </summary>
/// <param name="sourceData">数据源</param>
/// <param name="targetConnectionString">目标数据库连接字符串</param>
/// <param name="targetTableName">目标表名</param>
/// <param name="timeOut">超时之前操作完成所需的秒数</param>
/// <param name="keepIdentity">是否保留源标识值，如果为false，则由目标分配标识值</param>
/// <param name="batchSize">每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器</param>
/// <param name="copiedEventHandler">在每次处理完 batchSize条记录时触发此事件</param>
public static void BulkCopy(DataTable sourceData,string targetConnectionString,string targetTableName,int timeOut = 30,bool keepIdentity = true,int batchSize = 2000,SqlRowsCopiedEventHandler copiedEventHandler = null)
{
IDataReader reader = sourceData.CreateDataReader();
BulkCopy(reader,targetConnectionString,targetTableName,timeOut,keepIdentity,batchSize,copiedEventHandler);
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="dataTable"></param>
/// <param name="mapping"></param>
/// <param name="notifyHandler"></param>
/// <param name="notifyAfter"></param>
public void SqlBulkCopy(string tableName, DataTable dataTable, Dictionary <string, string> mapping, SqlRowsCopiedEventHandler notifyHandler, int notifyAfter)
{
ThrowExceptionUtil.ArgumentNotNullOrEmpty(tableName, "tableName");
ThrowExceptionUtil.ArgumentConditionTrue(dataTable != null && dataTable.Rows.Count > 0, "dataTable", "data can not be null or empty");
SqlServerBulkCopyCommand command = new SqlServerBulkCopyCommand(this, tableName, dataTable, mapping, notifyHandler, notifyAfter);
command.Excute <int>();
}
