/// <summary>
/// Creates a table in the database big enough to store the supplied DataTable with appropriate types.
/// </summary>
/// <param name="typeDictionary">The computers used to determine column types</param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <param name="explicitColumnDefinitions"></param>
/// <param name="createEmpty"></param>
/// <param name="adjuster"></param>
/// <returns></returns>
public DiscoveredTable CreateTable(out Dictionary <string, DataTypeComputer> typeDictionary, string tableName, DataTable dt, DatabaseColumnRequest[] explicitColumnDefinitions = null, bool createEmpty = false, IDatabaseColumnRequestAdjuster adjuster = null)
{
var args = new CreateTableArgs(this, tableName, null, dt, createEmpty)
{
Adjuster = adjuster,
ExplicitColumnDefinitions = explicitColumnDefinitions
};
var table = Helper.CreateTable(args);
if (!args.TableCreated)
{
throw new Exception("CreateTable completed but arguments were not Consumed");
}
typeDictionary = args.ColumnCreationLogic;
return(table);
}
/// <summary>
/// <para>Assembles and runs a CREATE TABLE sql statement based on the data/columns in <paramref name="dt"/> and returns the table created as a <see cref="DiscoveredTable"/>.</para>
///
/// <para>This will also INSERT the data in <paramref name="dt"/> into the table created unless <paramref name="createEmpty"/> is true</para>
/// </summary>
/// <param name="tableName">The unqualified name for the table you want to create e.g. "MyTable"</param>
/// <param name="dt">Data on which to base the CREATE statement on. Supports untyped (string) data e.g. "1", "101" "200" would create an int column</param>
/// <param name="createEmpty">True to bulk insert the Rows in the <paramref name="dt"/> after issuing CREATE. False to create only the empty schema</param>
/// <param name="adjuster">Last minute delegate class for modifying the table columns data types prior to executing SQL</param>
/// <param name="explicitColumnDefinitions">Optional - Override descisions made about columns in the <paramref name="dt"/> by specify an explicit type etc</param>
/// <returns>The table created</returns>
public DiscoveredTable CreateTable(string tableName, DataTable dt, DatabaseColumnRequest[] explicitColumnDefinitions = null, bool createEmpty = false, IDatabaseColumnRequestAdjuster adjuster = null)
{
return(CreateTable(new CreateTableArgs(this, tableName, null, dt, createEmpty)
{
ExplicitColumnDefinitions = explicitColumnDefinitions,
Adjuster = adjuster
}));
}
/// <summary>
/// Assembles and runs a CREATE TABLE sql statement and returns the table created as a <see cref="DiscoveredTable"/>.
/// </summary>
/// <param name="tableName">The unqualified name for the table you want to create e.g. "MyTable"</param>
/// <param name="columns">List of columns you want in your table</param>
/// <param name="adjuster">Last minute delegate class for modifying the <paramref name="columns"/> data types prior to executing SQL</param>
/// <param name="foreignKeyPairs">Creates a single foreign key between the table created (parent) and a child table. Columns in this parameter
/// must be a subset of <paramref name="columns"/></param>
/// <param name="cascadeDelete">True to set CASCADE DELETE on the foreign key created by <paramref name="foreignKeyPairs"/></param>
/// <returns>The table created</returns>
public DiscoveredTable CreateTable(string tableName, DatabaseColumnRequest[] columns, Dictionary <DatabaseColumnRequest, DiscoveredColumn> foreignKeyPairs, bool cascadeDelete, IDatabaseColumnRequestAdjuster adjuster = null)
{
return(CreateTable(new CreateTableArgs(this, tableName, null, foreignKeyPairs, cascadeDelete)
{
Adjuster = adjuster,
ExplicitColumnDefinitions = columns
}));
}
/// <summary>
/// Assembles and runs a CREATE TABLE sql statement and returns the table created as a <see cref="DiscoveredTable"/>.
/// </summary>
/// <param name="tableName">The unqualified name for the table you want to create e.g. "MyTable"</param>
/// <param name="columns">List of columns you want in your table</param>
/// <param name="schema">Optional - The schema (if supported by DBMS) to create in. This is NOT the database e.g. in [MyDb].[dbo].[MyTable] the schema is "dbo".
/// If in doubt leave blank</param>
/// <param name="adjuster">Last minute delegate class for modifying the <paramref name="columns"/> data types prior to executing SQL</param>
/// <returns>The table created</returns>
public DiscoveredTable CreateTable(string tableName, DatabaseColumnRequest[] columns, string schema = null, IDatabaseColumnRequestAdjuster adjuster = null)
{
return(CreateTable(new CreateTableArgs(this, tableName, schema)
{
Adjuster = adjuster,
ExplicitColumnDefinitions = columns
}));
}
/// <summary>
/// Creates a table in the database big enough to store the supplied DataTable with appropriate types.
/// </summary>
/// <param name="typeDictionary">The computers used to determine column types</param>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <param name="explicitColumnDefinitions"></param>
/// <param name="createEmpty"></param>
/// <param name="adjuster"></param>
/// <returns></returns>
public DiscoveredTable CreateTable(out Dictionary <string, Guesser> typeDictionary, string tableName, DataTable dt, DatabaseColumnRequest[] explicitColumnDefinitions = null, bool createEmpty = false, IDatabaseColumnRequestAdjuster adjuster = null)
{
var args = new CreateTableArgs(this, tableName, null, dt, createEmpty)
{
Adjuster = adjuster,
ExplicitColumnDefinitions = explicitColumnDefinitions
};
var table = Helper.CreateTable(args);
if (!args.TableCreated)
{
throw new Exception(FAnsiStrings.DiscoveredDatabase_CreateTableDidNotPopulateTableCreatedProperty);
}
typeDictionary = args.ColumnCreationLogic;
return(table);
}
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);
}
