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