/// <inheritdoc/>
public string ScriptTableCreation(DiscoveredTable table, bool dropPrimaryKeys, bool dropNullability, bool convertIdentityToInt, DiscoveredTable toCreateTable = null)
{
List <DatabaseColumnRequest> columns = new List <DatabaseColumnRequest>();
foreach (DiscoveredColumn c in table.DiscoverColumns())
{
string sqlType = c.DataType.SQLType;
if (c.IsAutoIncrement && convertIdentityToInt)
{
sqlType = "int";
}
bool isToDifferentDatabaseType = toCreateTable != null && toCreateTable.Database.Server.DatabaseType != table.Database.Server.DatabaseType;
//translate types
if (isToDifferentDatabaseType)
{
var fromtt = table.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
var tott = toCreateTable.Database.Server.GetQuerySyntaxHelper().TypeTranslater;
sqlType = fromtt.TranslateSQLDBType(c.DataType.SQLType, tott);
}
var colRequest = new DatabaseColumnRequest(c.GetRuntimeName(), sqlType, c.AllowNulls || dropNullability);
colRequest.IsPrimaryKey = c.IsPrimaryKey && !dropPrimaryKeys;
colRequest.IsAutoIncrement = c.IsAutoIncrement && !convertIdentityToInt;
colRequest.AllowNulls = colRequest.AllowNulls && !colRequest.IsAutoIncrement;
//if there is a collation
if (!string.IsNullOrWhiteSpace(c.Collation))
{
//if the script is to be run on a database of the same type
if (toCreateTable == null || !isToDifferentDatabaseType)
{
//then specify that the column should use the live collation
colRequest.Collation = c.Collation;
}
}
columns.Add(colRequest);
}
var destinationTable = toCreateTable ?? table;
string schema = toCreateTable != null ? toCreateTable.Schema : table.Schema;
return(table.Database.Helper.GetCreateTableSql(destinationTable.Database, destinationTable.GetRuntimeName(), columns.ToArray(), null, false, schema));
}
public virtual void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
var server = discoveredTable.Database.Server;
//if it's got a primary key they it's distinct! job done
if (discoveredTable.DiscoverColumns().Any(c => c.IsPrimaryKey))
{
return;
}
var tableName = discoveredTable.GetFullyQualifiedName();
var tempTable = discoveredTable.Database.ExpectTable(discoveredTable.GetRuntimeName() + "_DistinctingTemp").GetFullyQualifiedName();
using (var con = args.TransactionIfAny == null
? server.BeginNewTransactedConnection()
: //start a new transaction
args.GetManagedConnection(server)) //or continue the ongoing transaction
{
using (var cmdDistinct =
server.GetCommand(
string.Format("CREATE TABLE {1} AS SELECT distinct * FROM {0}", tableName, tempTable), con))
args.ExecuteNonQuery(cmdDistinct);
//this is the point of no return so don't cancel after this point
using (var cmdTruncate = server.GetCommand(string.Format("DELETE FROM {0}", tableName), con))
{
cmdTruncate.CommandTimeout = args.TimeoutInSeconds;
cmdTruncate.ExecuteNonQuery();
}
using (var cmdBack = server.GetCommand(string.Format("INSERT INTO {0} (SELECT * FROM {1})", tableName, tempTable), con))
{
cmdBack.CommandTimeout = args.TimeoutInSeconds;
cmdBack.ExecuteNonQuery();
}
using (var cmdDropDistinctTable = server.GetCommand(string.Format("DROP TABLE {0}", tempTable), con))
{
cmdDropDistinctTable.CommandTimeout = args.TimeoutInSeconds;
cmdDropDistinctTable.ExecuteNonQuery();
}
//if we opened a new transaction we should commit it
if (args.TransactionIfAny == null)
{
con.ManagedTransaction?.CommitAndCloseConnection();
}
}
}
public virtual void MakeDistinct(DiscoveredTable discoveredTable, int timeoutInSeconds)
{
var server = discoveredTable.Database.Server;
//note to future developers, this method has horrible side effects e.g. column defaults might be recalculated, foreign key CASCADE Deletes might happen
//to other tables we can help the user not make such mistakes with this check.
if (discoveredTable.DiscoverColumns().Any(c => c.IsPrimaryKey))
{
throw new NotSupportedException("Table " + discoveredTable + " has primary keys, why are you calling MakeDistinct on it!");
}
var tableName = discoveredTable.GetFullyQualifiedName();
var tempTable = discoveredTable.Database.ExpectTable(discoveredTable.GetRuntimeName() + "_DistinctingTemp").GetFullyQualifiedName();
using (var con = server.BeginNewTransactedConnection())
{
try
{
var cmdDistinct = server.GetCommand(string.Format("CREATE TABLE {1} AS SELECT distinct * FROM {0}", tableName, tempTable), con);
cmdDistinct.CommandTimeout = timeoutInSeconds;
cmdDistinct.ExecuteNonQuery();
var cmdTruncate = server.GetCommand(string.Format("DELETE FROM {0}", tableName), con);
cmdTruncate.CommandTimeout = timeoutInSeconds;
cmdTruncate.ExecuteNonQuery();
var cmdBack = server.GetCommand(string.Format("INSERT INTO {0} (SELECT * FROM {1})", tableName, tempTable), con);
cmdBack.CommandTimeout = timeoutInSeconds;
cmdBack.ExecuteNonQuery();
var cmdDropDistinctTable = server.GetCommand(string.Format("DROP TABLE {0}", tempTable), con);
cmdDropDistinctTable.CommandTimeout = timeoutInSeconds;
cmdDropDistinctTable.ExecuteNonQuery();
con.ManagedTransaction.CommitAndCloseConnection();
}
catch (Exception)
{
con.ManagedTransaction.AbandonAndCloseConnection();
throw;
}
}
}
/// <summary>
/// Updates <see cref="TargetTableColumns"/>. Call if you are making modifications to the <see cref="TargetTable"/> midway through a bulk insert.
/// </summary>
public void InvalidateTableSchema()
{
TargetTableColumns = TargetTable.DiscoverColumns(Connection.ManagedTransaction);
}