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