/// <summary>
/// Data reader version.
/// </summary>
/// <param name="dataReader"></param>
/// <param name="destinationTableName"></param>
/// <param name="connection"></param>
/// <param name="sqlServerBulkInsertOptions"></param>
/// <returns></returns>
public static SqlBulkCopy GetSqlBulkCopy(
this IDataReader dataReader,
string destinationTableName,
SqlConnection connection,
SqlServerBulkInsertOptions sqlServerBulkInsertOptions = null)
{
//if (Database.TableExists(connection, destinationTableName) == false)
// throw new Exception(
// $"Table {destinationTableName} does not exist using connection {connection.ConnectionString}");
sqlServerBulkInsertOptions = sqlServerBulkInsertOptions ?? new SqlServerBulkInsertOptions();
var bulkCopy = new SqlBulkCopy(connection, sqlServerBulkInsertOptions.SqlBulkCopyOptions, sqlServerBulkInsertOptions.SqlTransaction)
{
BulkCopyTimeout = sqlServerBulkInsertOptions.BulkCopyTimeout,
BatchSize = sqlServerBulkInsertOptions.BatchSize,
DestinationTableName = destinationTableName,
NotifyAfter = sqlServerBulkInsertOptions.BatchSize,
EnableStreaming = true
};
if (sqlServerBulkInsertOptions.RowsCopiedEventHandler != null)
{
bulkCopy.SqlRowsCopied += (e, i) => sqlServerBulkInsertOptions.RowsCopiedEventHandler(i.RowsCopied);
}
PopulateBulkCopyMappings(bulkCopy, dataReader, destinationTableName, connection, sqlServerBulkInsertOptions.UseOrdinals);
return(bulkCopy);
}
/// <summary>
/// Bulk insert data table using connection string.
/// </summary>
/// <param name="data"></param>
/// <param name="destinationTableName"></param>
/// <param name="connectionString"></param>
/// <param name="sqlServerBulkInsertOptions"></param>
public static void BulkInsertSqlServer(
this DataTable data,
string connectionString,
string destinationTableName,
SqlServerBulkInsertOptions sqlServerBulkInsertOptions = null)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
BulkInsertSqlServer(data, connection, destinationTableName, sqlServerBulkInsertOptions);
}
}
/// <summary>
/// Bulk insert data table.
/// </summary>
/// <param name="data"></param>
/// <param name="destinationTableName"></param>
/// <param name="connection"></param>
/// <param name="sqlServerBulkInsertOptions"></param>
public static void BulkInsertSqlServer(
this DataTable data,
SqlConnection connection,
string destinationTableName,
SqlServerBulkInsertOptions sqlServerBulkInsertOptions = null)
{
var bulkCopy = GetSqlBulkCopy(data, destinationTableName, connection, sqlServerBulkInsertOptions);
using (bulkCopy)
{
bulkCopy.WriteToServer(data);
}
}
/// <summary>
/// Bulk upload enumerable using a connection string.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="items"></param>
/// <param name="connection"></param>
/// <param name="destinationTable"></param>
/// <param name="sqlServerBulkInsertOptions"></param>
/// <param name="members"></param>
public static void BulkUploadSqlServer <T>(
this IEnumerable <T> items,
SqlConnection connection,
string destinationTable,
SqlServerBulkInsertOptions sqlServerBulkInsertOptions = null,
IEnumerable <string> members = null
)
{
members ??= GetMembers <T>();
var uploadReader = items.ToDataReader(members?.ToArray());
uploadReader.BulkInsertSqlServer(connection, destinationTable, sqlServerBulkInsertOptions ?? new SqlServerBulkInsertOptions());
}
/// <summary>
/// Data table version.
/// </summary>
/// <param name="data"></param>
/// <param name="destinationTableName"></param>
/// <param name="connection"></param>
/// <param name="sqlServerBulkInsertOptions"></param>
/// <returns></returns>
public static SqlBulkCopy GetSqlBulkCopy(
DataTable data,
string destinationTableName,
SqlConnection connection,
SqlServerBulkInsertOptions sqlServerBulkInsertOptions = null)
{
sqlServerBulkInsertOptions = sqlServerBulkInsertOptions ?? new SqlServerBulkInsertOptions();
var bulkCopy = new SqlBulkCopy(connection, sqlServerBulkInsertOptions.SqlBulkCopyOptions, sqlServerBulkInsertOptions.SqlTransaction)
{
BulkCopyTimeout = sqlServerBulkInsertOptions.BulkCopyTimeout,
BatchSize = sqlServerBulkInsertOptions.BatchSize,
DestinationTableName = destinationTableName,
NotifyAfter = sqlServerBulkInsertOptions.BatchSize
};
PopulateBulkCopyMappings(bulkCopy, data, destinationTableName, connection, sqlServerBulkInsertOptions.UseOrdinals);
return(bulkCopy);
}
/// <summary>
/// Bulk insert data reader synchronously.
/// </summary>
/// <param name="dataReader"></param>
/// <param name="connection"></param>
/// <param name="destinationTableName"></param>
/// <param name="sqlServerBulkInsertOptions"></param>
public static void BulkInsertSqlServer(this IDataReader dataReader, SqlConnection connection, string destinationTableName, SqlServerBulkInsertOptions sqlServerBulkInsertOptions = null)
{
sqlServerBulkInsertOptions = sqlServerBulkInsertOptions ?? new AsyncSqlServerBulkInsertOptions();
var bulkCopy = GetSqlBulkCopy(dataReader, destinationTableName, connection, sqlServerBulkInsertOptions);
using (bulkCopy)
{
try
{
bulkCopy.WriteToServer(dataReader);
}
catch (SqlException ex)
{
var errmsgbcp = "";
if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
{
errmsgbcp += GetBulkCopyDetailedExceptionMessage(ex, bulkCopy);
throw new Exception(errmsgbcp, ex);
}
throw;
}
}
}
