private void FillTable(ISql sql, TablePrescription tablePrescription)
{
_Owner._Logger.LogInformation(Messages.LOG_INF_STARTED_1, nameof(FillTable), tablePrescription.TableDescription.Name);
var columns = tablePrescription.ColumnPrescriptions.Select(_ => _.ColumnDescription.Name).ToArray();
var tableName = tablePrescription.TableName();
using var recordGenerator = _Owner._RecordGeneratorFactory.Create(tablePrescription, tablePrescription.Rows);
using var connection = sql.CreateConnection();
connection.Open();
using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, null)
{
BatchSize = BULKCOPY_BATCHSIZE,
BulkCopyTimeout = 0,
DestinationTableName = tableName,
EnableStreaming = true,
};
// skipping DF1, DF2.
for (var i = 0; i < recordGenerator.FieldCount; i++)
{
_ = bulkCopy.ColumnMappings.Add(i, i + 2);
}
bulkCopy.WriteToServer(recordGenerator);
_Owner._Logger.LogInformation(Messages.LOG_INF_FINISHED_1, nameof(FillTable), tablePrescription.TableDescription.Name);
}
private static void FillAuxiliaryColumns(ISql sql, TableDescription tableDescription)
{
using var connection = sql.CreateConnection();
connection.Open();
var tableName = tableDescription.TableName();
var command = "WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS ROWNUMBER, [@DF1] FROM {0}) UPDATE T2 SET T2.[@DF2] = T1.[@DF1] FROM {0} T2, CTE T1 WHERE T1.ROWNUMBER = T2.[@DF1]".FormatInvariant(tableName);
sql.NonQuery(connection, command);
}
private static void DropAuxiliaryColumns(ISql sql)
{
using var connection = sql.CreateConnection();
connection.Open();
var server = new Server(new ServerConnection(connection));
foreach (Table table in server.Databases[DEFAULT_DATABASENAME].Tables)
{
var indexes = table.Indexes.Cast <Index>();
var columns = table.Columns.Cast <Column>();
indexes.Single().Drop();
columns.Single(c => c.Name == "@DF1").Drop();
columns.Single(c => c.Name == "@DF2").Drop();
}
}
private static void WriteInsertions(Stream stream, ISql sql, TableDescription[] tableDescriptions)
{
using var connection = sql.CreateConnection();
var server = new Server(new ServerConnection(connection));
var database = server.Databases[DEFAULT_DATABASENAME];
foreach (var tableDescription in tableDescriptions)
{
bool Match(Table table) => table.Schema == tableDescription.Schema &&
table.Name == tableDescription.Name;
var table = database
.Tables
.Cast <Table>()
.First(Match);
var tableName = tableDescription.TableName();
stream.WriteLine("SET IDENTITY_INSERT {0} ON;".FormatInvariant(tableName));
WriteInsertions(stream, table);
stream.WriteLine("SET IDENTITY_INSERT {0} OFF;".FormatInvariant(tableName));
}
}
