public static void WriteDatabase(this DataTable dataTable, SqlConnection connection, SqlTransaction transaction, BulkCopyCallback bulkCopyCallback)
{
//const SqlBulkCopyOptions bulkCopyOptions = SqlBulkCopyOptions.KeepNulls |
// SqlBulkCopyOptions.KeepIdentity |
// SqlBulkCopyOptions.TableLock |
// SqlBulkCopyOptions.FireTriggers |
// SqlBulkCopyOptions.CheckConstraints;
const SqlBulkCopyOptions bulkCopyOptions = SqlBulkCopyOptions.KeepNulls |
SqlBulkCopyOptions.KeepIdentity |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.CheckConstraints;
//const SqlBulkCopyOptions bulkCopyOptions = SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity;
using (var sqlBulkCopy = new SqlBulkCopy(connection, bulkCopyOptions, transaction))
{
foreach (DataColumn dataColumn in dataTable.Columns)
{
sqlBulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
}
var rowsCount = dataTable.Rows.Count;
var tableName = dataTable.TableName;
sqlBulkCopy.DestinationTableName = tableName;
sqlBulkCopy.BulkCopyTimeout = 3600;
sqlBulkCopy.BatchSize = Math.Min(2500, rowsCount);
sqlBulkCopy.NotifyAfter = GetBulkCopyNotifyCount(rowsCount, sqlBulkCopy.BatchSize);
sqlBulkCopy.SqlRowsCopied += (sender, args) => DoBulkCopyCallback(sender, args, bulkCopyCallback);
sqlBulkCopy.WriteToServer(dataTable);
DoBulkCopyCallback(sqlBulkCopy, new SqlRowsCopiedEventArgs(rowsCount), bulkCopyCallback);
sqlBulkCopy.Close();
}
}
private static void DoBulkCopyCallback(Object sender, SqlRowsCopiedEventArgs args, BulkCopyCallback callback)
{
var sqlBulkCopy = sender as SqlBulkCopy;
if (sqlBulkCopy == null || callback == null || args == null || callback.Count == args.RowsCopied)
{
return;
}
callback.Name = sqlBulkCopy.DestinationTableName;
callback.Count = args.RowsCopied;
var result = callback.DoCallback();
if (result != null)
{
args.Abort = result.Value;
}
}
public static void WriteDatabase(this DataTable dataTable, SqlConnection connection, IsolationLevel isolationLevel, BulkCopyCallback bulkCopyCallback)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var transaction = connection.BeginTransaction(isolationLevel))
{
try
{
dataTable.WriteDatabase(connection, transaction, bulkCopyCallback);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public static void WriteDatabase(this DataTable dataTable, SqlConnection connection, BulkCopyCallback bulkCopyCallback)
{
dataTable.WriteDatabase(connection, IsolationLevel.Unspecified, bulkCopyCallback);
}
public static void WriteDatabase(this DataTable dataTable, String connectionString, IsolationLevel isolationLevel, BulkCopyCallback bulkCopyCallback)
{
using (var connection = new SqlConnection(connectionString))
{
dataTable.WriteDatabase(connection, isolationLevel, bulkCopyCallback);
}
}
public static void WriteDatabase(this DataSet dataSet, SqlConnection connection, SqlTransaction transaction, BulkCopyCallback bulkCopyCallback)
{
foreach (DataTable dataTable in dataSet.Tables)
{
if (dataTable.Rows.Count > 0)
{
dataTable.WriteDatabase(connection, transaction, bulkCopyCallback);
}
}
}
public static void WriteDatabase(this DataSet dataSet, String connectionString, BulkCopyCallback bulkCopyCallback)
{
dataSet.WriteDatabase(connectionString, IsolationLevel.Unspecified, bulkCopyCallback);
}
