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;
}
}
}
public string GetForeignKeyConstraintNameFor(DiscoveredTable foreignTable, DiscoveredTable primaryTable)
{
return(GetForeignKeyConstraintNameFor(foreignTable.GetRuntimeName(), primaryTable.GetRuntimeName()));
}