/// <summary> /// Copies the chunk. /// </summary> /// <param name="reader">The statistical reader.</param> /// <param name="bulkCopy">The bulk copy.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>An asynchronous completion token.</returns> private async Task CopyChunk(StatisticsDataReader reader, IBulkCopy bulkCopy, CancellationToken cancellationToken) { EventPublisher.Raise(new ChunkCopyingEvent { TargetName = this.target.Name, RowCount = reader.RowCount, ResultCount = reader.ResultCount, OperationId = this.operationId }); var chunkTimer = new Stopwatch(); chunkTimer.Start(); this.target.InitializeChunk(); await bulkCopy.WriteToServerAsync(reader, cancellationToken); chunkTimer.Stop(); this.target.ChunkComplete(); EventPublisher.Raise(new ChunkCopiedEvent { TargetName = this.target.Name, Duration = chunkTimer.Elapsed, RowCount = reader.RowCount, ResultCount = reader.ResultCount, OperationId = this.operationId }); }
/// <summary> /// Copies the data. /// </summary> /// <param name="sourceData">The source data.</param> /// <param name="bulkCopy">The bulk copy target.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>An asynchronous completion token.</returns> private async Task CopyData(IDataReader sourceData, IBulkCopy bulkCopy, CancellationToken cancellationToken) { var statisticalReader = new StatisticsDataReader(sourceData); do { try { await this.CopyChunk(statisticalReader, bulkCopy, cancellationToken); } catch (Exception ex) { var properties = ExtractErrorProperties(statisticalReader); EventPublisher.Raise(new OperationErrorEvent(this, ex, properties)); throw; } }while (!cancellationToken.IsCancellationRequested && statisticalReader.NextResult()); this.target.BatchComplete(); EventPublisher.Raise(new BatchCompleteEvent { Operation = this, Duration = this.timer.Elapsed, RowCount = statisticalReader.RowCount, ResultCount = statisticalReader.ResultCount }); }
public void TestBulkInsert_ExplicitDateTimeFormats(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyDateTestTable", new[] { new DatabaseColumnRequest("MyDate", new DatabaseTypeRequest(typeof(DateTime))) { AllowNulls = false }, }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("MyDate"); dt.Rows.Add("20011230"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; bulk.DateTimeDecider.Settings.ExplicitDateFormats = new [] { "yyyyMMdd" }; bulk.Upload(dt); } } var dtDown = tbl.GetDataTable(); Assert.AreEqual(new DateTime(2001, 12, 30), dtDown.Rows[0]["MyDate"]); }
public void TestBulkInsert_BadDecimalFormat_DecimalError(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Id", new DatabaseTypeRequest(typeof(int))) { IsAutoIncrement = true, IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Score", new DatabaseTypeRequest(typeof(decimal), null, new DecimalSize(2, 1))), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("age"); dt.Columns.Add("name"); dt.Columns.Add("score"); dt.Rows.Add(60, "Jamie", 1.2); dt.Rows.Add(30, "Frank", 1.3); dt.Rows.Add(11, "Toad", "."); //bad data dt.Rows.Add(100, "King"); dt.Rows.Add(10, "Frog"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; Exception ex = null; try { bulk.Upload(dt); } catch (Exception e) { ex = e; } Assert.IsNotNull(ex, "Expected upload to fail because value on row 2 is bad"); Assert.AreEqual("Failed to parse value '.' in column 'score'", ex.Message); Assert.IsNotNull(ex.InnerException, "Expected parse error to be an inner exception"); StringAssert.Contains("Could not parse string value '.' with Decider Type:DecimalTypeDecider", ex.InnerException.Message); } } }
public virtual void SubmitChunk(DataTable chunk, IDataLoadEventListener job) { _timer.Start(); if (_copy == null) { _copy = InitializeBulkCopy(chunk, job); AssessMissingAndIgnoredColumns(chunk, job); } _copy.Upload(chunk); _timer.Stop(); RaiseEvents(chunk, job); }
public void TestBulkInsert_Transaction(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("Name"); dt.Columns.Add("Age"); dt.Rows.Add("Dave", 50); dt.Rows.Add("Jamie", 60); using (var transaction = tbl.Database.Server.BeginNewTransactedConnection()) { using (IBulkCopy bulk = tbl.BeginBulkInsert(transaction.ManagedTransaction)) { bulk.Timeout = 30; bulk.Upload(dt); //inside transaction the count is 2 Assert.AreEqual(2, tbl.GetRowCount(transaction.ManagedTransaction)); dt.Rows.Clear(); dt.Rows.Add("Frank", 100); bulk.Upload(dt); //inside transaction the count is 3 Assert.AreEqual(3, tbl.GetRowCount(transaction.ManagedTransaction)); } transaction.ManagedTransaction.CommitAndCloseConnection(); } } //Transaction was committed final row count should be 3 Assert.AreEqual(3, tbl.GetRowCount()); }
public void TestBulkInsert_SpacedOutNames(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Na me", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("A ge", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("Na me"); dt.Columns.Add("A ge"); dt.Rows.Add("Dave", 50); dt.Rows.Add("Jamie", 60); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; bulk.Upload(dt); Assert.AreEqual(2, tbl.GetRowCount()); dt.Rows.Clear(); dt.Rows.Add("Frank", 100); bulk.Upload(dt); Assert.AreEqual(3, tbl.GetRowCount()); } } tbl.Insert(new Dictionary <string, object>() { { "Na me", "George" }, { "A ge", "300" } }); Assert.AreEqual(4, tbl.GetRowCount()); }
/// <summary> /// Initializes a new instance of the <see cref="SqlBulkRepositoryFacts"/> class. /// </summary> public SqlBulkRepositoryFacts() { this.fixture = new Fixture(); this.bulkCopy = Substitute.For <IBulkCopy>(); this.connectionFactory = Substitute.For <IDbConnectionFactory>(); this.columnMappings = new Dictionary <string, string>() { { "Id", "Id" }, { "Name", "Name" }, { "Created", "Created" } }; this.bulkRepository = new SqlBulkRepository <FakeEntity>( (c, o) => this.bulkCopy, TestTableName, this.connectionFactory, this.columnMappings); this.data = this.fixture.CreateMany <FakeEntity>(); this.context = Substitute.For <ITaskContext>(); }
public static async Task FlushAsync <T>(IEnumerable <T> list, string connStr, int batchSize = 500, string tableName = "", bool keepIdentity = false) where T : IBulkCopy { if (!list.Any()) { return; } IBulkCopy baseEntity = list.First(); dataTable = baseEntity.TableStructure(); Console.WriteLine($"BatchSize: {batchSize}"); _connStr = connStr; var countBatch = 1; foreach (IBulkCopy item in list) { DataRow row = dataTable.NewRow(); dataTable.Rows.Add(item.GetDataRow(row)); recordCount++; if (recordCount >= batchSize) { await WriteToDatabase(tableName, keepIdentity); Console.WriteLine($"Processed batch {countBatch}"); } countBatch++; } // write remaining records to the DB if (recordCount > 0) { await WriteToDatabase(tableName, keepIdentity); } }
public void TestBulkInsert_ColumnOrdinals(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("Age"); dt.Columns.Add("Name"); dt.Rows.Add("50", "David"); dt.Rows.Add("60", "Jamie"); Assert.AreEqual("Age", dt.Columns[0].ColumnName); Assert.AreEqual(typeof(string), dt.Columns[0].DataType); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; bulk.Upload(dt); Assert.AreEqual(2, tbl.GetRowCount()); } //columns should not be reordered Assert.AreEqual("Age", dt.Columns[0].ColumnName); Assert.AreEqual(typeof(int), dt.Columns[0].DataType); //but the data type was changed by HardTyping it } }
public DataTable ProcessPipelineData(DataTable toProcess, IDataLoadEventListener listener, GracefulCancellationToken cancellationToken) { if (toProcess == null) { return(null); } IDatabaseColumnRequestAdjuster adjuster = null; if (Adjuster != null) { var constructor = new ObjectConstructor(); adjuster = (IDatabaseColumnRequestAdjuster)constructor.Construct(Adjuster); } //work out the table name for the table we are going to create if (TargetTableName == null) { if (string.IsNullOrWhiteSpace(toProcess.TableName)) { throw new Exception("Chunk did not have a TableName, did not know what to call the newly created table"); } TargetTableName = QuerySyntaxHelper.MakeHeaderNameSane(toProcess.TableName); } ClearPrimaryKeyFromDataTableAndExplicitWriteTypes(toProcess); StartAuditIfExists(TargetTableName); if (_loggingDatabaseListener != null) { listener = new ForkDataLoadEventListener(listener, _loggingDatabaseListener); } EnsureTableHasDataInIt(toProcess); bool createdTable = false; if (_firstTime) { bool tableAlreadyExistsButEmpty = false; if (!_database.Exists()) { throw new Exception("Database " + _database + " does not exist"); } discoveredTable = _database.ExpectTable(TargetTableName); //table already exists if (discoveredTable.Exists()) { tableAlreadyExistsButEmpty = true; if (!AllowLoadingPopulatedTables) { if (discoveredTable.IsEmpty()) { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found table " + TargetTableName + " already, normally this would forbid you from loading it (data duplication / no primary key etc) but it is empty so we are happy to load it, it will not be created")); } else { throw new Exception("There is already a table called " + TargetTableName + " at the destination " + _database); } } if (AllowResizingColumnsAtUploadTime) { _dataTypeDictionary = discoveredTable.DiscoverColumns().ToDictionary(k => k.GetRuntimeName(), v => v.GetDataTypeComputer(), StringComparer.CurrentCultureIgnoreCase); } } else { listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Determined that the table name " + TargetTableName + " is unique at destination " + _database)); } //create connection to destination if (!tableAlreadyExistsButEmpty) { createdTable = true; if (AllowResizingColumnsAtUploadTime) { _database.CreateTable(out _dataTypeDictionary, TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster); } else { _database.CreateTable(TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster); } listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Created table " + TargetTableName + " successfully.")); } _managedConnection = _server.BeginNewTransactedConnection(); _bulkcopy = discoveredTable.BeginBulkInsert(_managedConnection.ManagedTransaction); if (Culture != null) { _bulkcopy.DateTimeDecider.Culture = Culture; } _firstTime = false; } try { if (AllowResizingColumnsAtUploadTime && !createdTable) { ResizeColumnsIfRequired(toProcess, listener); } //push the data swTimeSpentWritting.Start(); _affectedRows += _bulkcopy.Upload(toProcess); swTimeSpentWritting.Stop(); listener.OnProgress(this, new ProgressEventArgs("Uploading to " + TargetTableName, new ProgressMeasurement(_affectedRows, ProgressType.Records), swTimeSpentWritting.Elapsed)); } catch (Exception e) { _managedConnection.ManagedTransaction.AbandonAndCloseConnection(); if (LoggingServer != null) { _dataLoadInfo.LogFatalError(GetType().Name, ExceptionHelper.ExceptionToListOfInnerMessages(e, true)); } throw new Exception("Failed to write rows (in transaction) to table " + TargetTableName, e); } return(null); }
/// <summary> /// Initializes a new instance of the <see cref="SqlBulkRepositoryFacts"/> class. /// </summary> public SqlBulkRepositoryFacts() { this.fixture = new Fixture(); this.bulkCopy = Substitute.For<IBulkCopy>(); this.connectionFactory = Substitute.For<IDbConnectionFactory>(); this.columnMappings = new Dictionary<string, string>() { { "Id", "Id" }, { "Name", "Name" }, { "Created", "Created" } }; this.bulkRepository = new SqlBulkRepository<FakeEntity>( (c, o) => this.bulkCopy, TestTableName, this.connectionFactory, this.columnMappings); this.data = this.fixture.CreateMany<FakeEntity>(); this.context = Substitute.For<ITaskContext>(); }
private static int RunDatabaseTarget(TargetDatabase configDatabase, ProgramOptions opts) { var batchSize = Math.Max(1, configDatabase.Batches); //if we are going into a database we definitely do not need pixels! if (opts.NoPixels == false) { opts.NoPixels = true; } Stopwatch swTotal = new Stopwatch(); swTotal.Start(); string neverDistinct = "SOPInstanceUID"; if (!File.Exists(configDatabase.Template)) { Console.WriteLine($"Listed template file '{configDatabase.Template}' does not exist"); return(-1); } ImageTableTemplateCollection template; try { template = ImageTableTemplateCollection.LoadFrom(File.ReadAllText(configDatabase.Template)); } catch (Exception e) { Console.WriteLine($"Error reading yaml from '{configDatabase.Template}'"); Console.WriteLine(e.ToString()); return(-2); } ImplementationManager.Load <MySqlImplementation>(); ImplementationManager.Load <PostgreSqlImplementation>(); ImplementationManager.Load <OracleImplementation>(); ImplementationManager.Load <MicrosoftSQLImplementation>(); var server = new DiscoveredServer(configDatabase.ConnectionString, configDatabase.DatabaseType); try { server.TestConnection(); } catch (Exception e) { Console.WriteLine($"Could not reach target server '{server.Name}'"); Console.WriteLine(e); return(-2); } var db = server.ExpectDatabase(configDatabase.DatabaseName); if (!db.Exists()) { Console.WriteLine($"Creating Database '{db.GetRuntimeName()}'"); db.Create(); Console.WriteLine("Database Created"); } else { Console.WriteLine($"Found Database '{db.GetRuntimeName()}'"); } var creator = new ImagingTableCreation(db.Server.GetQuerySyntaxHelper()); Console.WriteLine($"Image template contained schemas for {template.Tables.Count} tables. Looking for existing tables.."); //setting up bulk inserters DiscoveredTable[] tables = new DiscoveredTable[template.Tables.Count]; DataTable[][] batches = new DataTable[batchSize][]; for (var i = 0; i < batches.Length; i++) { batches[i] = new DataTable[template.Tables.Count]; } IBulkCopy[][] uploaders = new IBulkCopy[batchSize][]; for (int i = 0; i < uploaders.Length; i++) { uploaders[i] = new IBulkCopy[template.Tables.Count]; } string[] pks = new string[template.Tables.Count]; for (var i = 0; i < template.Tables.Count; i++) { var tableSchema = template.Tables[i]; var tbl = db.ExpectTable(tableSchema.TableName); tables[i] = tbl; if (configDatabase.MakeDistinct) { var col = tableSchema.Columns.Where(c => c.IsPrimaryKey).ToArray(); if (col.Length > 1) { Console.WriteLine("MakeDistinct only works with single column primary keys e.g. StudyInstanceUID / SeriesInstanceUID"); } pks[i] = col.SingleOrDefault()?.ColumnName; if (pks[i] != null) { //if it is sop instance uid then we shouldn't be trying to deduplicate if (string.Equals(pks[i], neverDistinct, StringComparison.CurrentCultureIgnoreCase)) { pks[i] = null; } else { //we will make this a primary key later on col.Single().IsPrimaryKey = false; Console.WriteLine($"MakeDistinct will apply to '{pks[i]}' on '{tbl.GetFullyQualifiedName()}'"); } } } bool create = true; if (tbl.Exists()) { if (configDatabase.DropTables) { Console.WriteLine($"Dropping existing table '{tbl.GetFullyQualifiedName()}'"); tbl.Drop(); } else { Console.WriteLine($"Table '{tbl.GetFullyQualifiedName()}' already existed (so will not be created)"); create = false; } } if (create) { Console.WriteLine($"About to create '{tbl.GetFullyQualifiedName()}'"); creator.CreateTable(tbl, tableSchema); Console.WriteLine($"Successfully created create '{tbl.GetFullyQualifiedName()}'"); } Console.WriteLine($"Creating uploader for '{tbl.GetRuntimeName()}''"); for (int j = 0; j < batchSize; j++) { //fetch schema var dt = tbl.GetDataTable(); dt.Rows.Clear(); batches[j][i] = dt; uploaders[j][i] = tbl.BeginBulkInsert(); } } var tasks = new Task[batchSize]; IPersonCollection identifiers = GetPeople(opts, out Random r); for (int i = 0; i < batchSize; i++) { var batch = i; tasks[i] = new Task(() => // lgtm[cs/local-not-disposed] { RunBatch(identifiers, opts, r, batches[batch], uploaders[batch]); }); tasks[i].Start(); } Task.WaitAll(tasks); swTotal.Stop(); for (var i = 0; i < tables.Length; i++) { if (pks[i] == null) { continue; } Console.WriteLine($"{DateTime.Now} Making table '{tables[i]}' distinct (this may take a long time)"); var tbl = tables[i]; tbl.MakeDistinct(500000000); Console.WriteLine($"{DateTime.Now} Creating primary key on '{tables[i]}' of '{pks[i]}'"); tbl.CreatePrimaryKey(500000000, tbl.DiscoverColumn(pks[i])); } Console.WriteLine("Final Row Counts:"); foreach (DiscoveredTable t in tables) { Console.WriteLine($"{t.GetFullyQualifiedName()}: {t.GetRowCount():0,0}"); } Console.WriteLine("Total Running Time:" + swTotal.Elapsed); return(0); }
static Bulky() { BulkCopier = new SqlServerBulkCopy(); }
public void TestBulkInsert_SchemaTooNarrow_DecimalError(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Id", new DatabaseTypeRequest(typeof(int))) { IsAutoIncrement = true, IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Score", new DatabaseTypeRequest(typeof(decimal), null, new DecimalSize(2, 1))), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("age"); dt.Columns.Add("name"); dt.Columns.Add("score"); dt.Rows.Add(60, "Jamie", 1.2); dt.Rows.Add(30, "Frank", 1.3); dt.Rows.Add(11, "Toad", 111111111.11); //bad data dt.Rows.Add(100, "King"); dt.Rows.Add(10, "Frog"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; Exception ex = null; try { bulk.Upload(dt); } catch (Exception e) { ex = e; } Assert.IsNotNull(ex, "Expected upload to fail because value on row 2 is too long"); switch (type) { case DatabaseType.MicrosoftSQLServer: StringAssert.Contains("Failed to load data row 3 the following values were rejected by the database", ex.Message); StringAssert.Contains("Parameter value '111111111.1' is out of range", ex.Message); break; case DatabaseType.MySql: Assert.AreEqual("Out of range value for column 'Score' at row 3", ex.Message); break; case DatabaseType.Oracle: StringAssert.Contains("value larger than specified precision allowed for this column", ex.Message); break; case DatabaseType.PostgreSql: StringAssert.Contains("numeric field overflow", ex.Message); break; default: throw new ArgumentOutOfRangeException(nameof(type), type, null); } } } }
public void TestBulkInsert_SchemaTooNarrow_StringError(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Id", new DatabaseTypeRequest(typeof(int))) { IsAutoIncrement = true, IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("age"); dt.Columns.Add("name"); dt.Rows.Add(60, "Jamie"); dt.Rows.Add(30, "Frank"); dt.Rows.Add(11, "Toad"); dt.Rows.Add(50, new string('A', 11)); dt.Rows.Add(100, "King"); dt.Rows.Add(10, "Frog"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; Exception ex = null; try { bulk.Upload(dt); } catch (Exception e) { ex = e; } Assert.IsNotNull(ex, "Expected upload to fail because value on row 2 is too long"); switch (type) { case DatabaseType.MicrosoftSQLServer: StringAssert.Contains("BulkInsert failed on data row 4 the complaint was about source column <<name>> which had value <<AAAAAAAAAAA>> destination data type was <<varchar(10)>>", ex.Message); break; case DatabaseType.MySql: Assert.AreEqual("Data too long for column 'Name' at row 4", ex.Message); break; case DatabaseType.Oracle: StringAssert.Contains("NAME", ex.Message); StringAssert.Contains("maximum: 10", ex.Message); StringAssert.Contains("actual: 11", ex.Message); break; case DatabaseType.PostgreSql: StringAssert.Contains("value too long for type character varying(10)", ex.Message); break; default: throw new ArgumentOutOfRangeException(nameof(type), type, null); } } } }