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