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>(); }