/// <summary>
/// Marks that the data load ended
/// </summary>
public void CloseAndMarkComplete()
{
lock (oLock)
{
//prevent double closing
if (_isClosed)
{
return;
}
_endTime = DateTime.Now;
using (var con = _server.BeginNewTransactedConnection())
{
try
{
DbCommand cmdUpdateToClosed =
_server.GetCommand("UPDATE DataLoadRun SET endTime=@endTime WHERE ID=@ID",
con);
_server.AddParameterWithValueToCommand("@endTime", cmdUpdateToClosed, DateTime.Now);
_server.AddParameterWithValueToCommand("@ID", cmdUpdateToClosed, ID);
int rowsAffected = cmdUpdateToClosed.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception(
"Error closing off DataLoad in database, the update command resulted in " +
rowsAffected + " rows being affected (expected 1) - will try to rollback");
}
con.ManagedTransaction.CommitAndCloseConnection();
_isClosed = true;
}
catch (Exception)
{
//if something goes wrong with the update, roll it back
con.ManagedTransaction.AbandonAndCloseConnection();
throw;
}
//once a record has been commited to the database it is redundant and no further attempts to read/change it should be made by anyone
foreach (TableLoadInfo t in this.TableLoads.Values)
{
//close any table loads that have not yet completed
if (!t.IsClosed)
{
t.CloseAndArchive();
}
}
}
}
}
private void CheckLookupTableIsCorrectlyPopulated(ICheckNotifier notifier, string valueColumnName, string tableName, Dictionary <int, string> expected)
{
//see what is in the database
var actual = new Dictionary <int, string>();
using (var conn = _server.GetConnection())
{
conn.Open();
var reader =
_server.GetCommand("SELECT ID, " + valueColumnName + " FROM " + tableName, conn).ExecuteReader();
while (reader.Read())
{
actual.Add(Convert.ToInt32(reader["ID"]), reader[valueColumnName].ToString().Trim());
}
reader.Close();
}
//now reconcile what is in the database with what we expect
Dictionary <int, string> missing;
Dictionary <int, string> collisions;
List <string> misnomers;
ExpectedLookupsValuesArePresent(expected, actual, out missing, out collisions, out misnomers);
if (!missing.Any() && !collisions.Any() && !misnomers.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs(tableName + " contains the correct lookup values", CheckResult.Success, null));
return;
}
//collisions cannot be resolved without manual intervention
if (collisions.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs(tableName + " there is a key collision between what we require and what is in the database, the mismatches are:" + Environment.NewLine +
collisions.Aggregate("", (s, n) => s + "Desired:(" + n.Key + ",'" + n.Value + "') VS Found:(" + n.Key + ",'" + actual[n.Key] + "')" + Environment.NewLine)
+ collisions, CheckResult.Fail, null));
return;
}
//misnomers cannot be resolved without manual intervention either
if (misnomers.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs(
tableName + " the following ID conflicts were found:" +
misnomers.Aggregate("", (s, n) => s + Environment.NewLine + n), CheckResult.Fail, null));
}
if (missing.Any())
{
//add missing values
if (notifier.OnCheckPerformed(new CheckEventArgs(tableName + " does not contain all the required lookup statuses",
CheckResult.Fail, null,
"Insert the missing lookups (" + missing.Aggregate("", (s, pair) => s + ", " + pair.Value) + ")")))
{
using (var c = _server.BeginNewTransactedConnection())
{
_server.GetCommand("SET IDENTITY_INSERT " + tableName + " ON ", c).ExecuteNonQuery();
foreach (var kvp in missing)
{
_server.GetCommand("INSERT INTO " + tableName + "(ID," + valueColumnName + ") VALUES (" + kvp.Key + ",'" + kvp.Value + "')", c).ExecuteNonQuery();
}
_server.GetCommand("SET IDENTITY_INSERT " + tableName + " OFF ", c).ExecuteNonQuery();
c.ManagedTransaction.CommitAndCloseConnection();
}
}
}
}
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);
}