protected override SqlBulkCopy CreateBulkCopy(IRelationalConnection connection)
{
var bulk = new SqlBulkCopy((SqlConnection)connection.DbConnection, this.SqlBulkCopyOptions, (SqlTransaction)connection.CurrentTransaction.GetDbTransaction());
bulk.BulkCopyTimeout = connection.CommandTimeout ?? 60;
bulk.DestinationTableName = _bulkTable;
InboundColumns.ForEach(p => bulk.ColumnMappings.Add(p.ColumnName, p.ColumnName));
_setup?.Invoke(bulk);
return(bulk);
}
protected override SqlCommand PrepareStatement(IRelationalConnection connection)
{
var columnNames = InboundColumns.Select(p => $"[{p.ColumnName}]");
var commandText = $"Select Top 0 {string.Join(", ", columnNames)} into {_bulkTable} FROM {_targetTableName}";
var command = connection.DbConnection.CreateCommand();
command.Transaction = connection.CurrentTransaction.GetDbTransaction();
command.CommandText = commandText;
command.CommandTimeout = connection.CommandTimeout ?? 60;
return((SqlCommand)command);
}
protected override SqlCommand CommitStatement(IRelationalConnection connection)
{
var writeColumns = string.Join(" AND ", InboundColumns.Select(p => $"t.[{p.ColumnName}] = tmp.[{p.ColumnName}]"));
var commandText = $"SET NOCOUNT ON;\r\n" +
$"DELETE t FROM {_targetTableName} t \r\n" +
$"INNER JOIN {_bulkTable} tmp ON {writeColumns};\r\n" +
$"Select @@ROWCOUNT;";
var command = connection.DbConnection.CreateCommand();
command.CommandText = commandText;
command.Transaction = connection.CurrentTransaction.GetDbTransaction();
command.CommandTimeout = connection.CommandTimeout ?? 60;
return((SqlCommand)command);
}
protected override SqlCommand CommitStatement(IRelationalConnection connection)
{
if (!OutboundColumns.Any())
{
return(null);
}
var writeColumns = string.Join(", ", InboundColumns.Select(p => $"[{p.ColumnName}]"));
var commandText = $"INSERT INTO {_targetTableName} ({writeColumns}) " +
$"OUTPUT {string.Join(", ", OutboundColumns.Select(p => $"inserted.[{p.ColumnName}]"))} " +
$"SELECT {writeColumns} FROM {_bulkTable}";
var command = connection.DbConnection.CreateCommand();
command.CommandText = commandText;
command.Transaction = connection.CurrentTransaction.GetDbTransaction();
command.CommandTimeout = connection.CommandTimeout ?? 60;
return((SqlCommand)command);
}
