/// <summary>
/// Transfer data into a destination table.
/// </summary>
/// <param name="tableName">
/// The name of the table that will be written to.
/// If a query is not provided this will also be the name of the table that data is read from.</param>
/// <param name="sqlBulkCopyOptions">Options used by <see cref="SqlBulkCopy"/>.</param>
/// <param name="query">An optional query that will be used as the source of data.</param>
/// <param name="queryParams">Optional parameters that are used by the source data query.</param>
/// <param name="sqlTransaction">Optional <see cref="SqlTransaction"/> object that will be used to construct the <see cref="SqlBulkCopy"/>.</param>
/// <param name="sqlBulkCopyCustomizer">Optional delegate that can customize the <see cref="SqlBulkCopy"/> object.</param>
/// <param name="smartColumnMapping">
/// Passing true will cause source columns to be mapped to destination columns by name and computed columns to be ignored.
/// A value of false will result in ordinal based mapping of all columns. Defaults to true.
/// </param>
public void TransferData(string tableName, string query = null, SqlParameter[] queryParams = null, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default, SqlTransaction sqlTransaction = null, Action<SqlBulkCopy> sqlBulkCopyCustomizer = null, bool smartColumnMapping = true)
{
if (tableName == null)
throw new ArgumentNullException(nameof(tableName));
if (!tableName.Contains("["))
tableName = tableName.Split('.').Select(n => "[" + n + "]").Join(".");
using (var source = new SqlConnection(_sourceConnString))
using (var dest = new SqlConnection(_destConnString))
using (var bcp = new SqlBulkCopy(dest, sqlBulkCopyOptions, null))
using (var sourceCmd = source.CreateCommand())
using (source.Connect())
using (dest.Connect())
{
query = query.IsNullOrWhiteSpace() ? $"SELECT * FROM {tableName}" : query;
bcp.EnableStreaming = true;
bcp.DestinationTableName = tableName;
bcp.BulkCopyTimeout = (int)TransferTimeout.TotalSeconds;
sourceCmd.CommandType = CommandType.Text;
sourceCmd.CommandText = query;
sourceCmd.CommandTimeout = (int)TransferTimeout.TotalSeconds;
if (queryParams != null && queryParams.Any())
sourceCmd.Parameters.AddRange(queryParams);
using (var reader = sourceCmd.ExecuteReader())
{
if (smartColumnMapping)
{
var schema = reader.GetSchemaTable();
MapColumns(schema, bcp, dest);
}
if (sqlBulkCopyCustomizer != null)
sqlBulkCopyCustomizer(bcp);
bcp.WriteToServer(reader);
}
}
}