public virtual void AddColumn(DiscoveredTable table, DbConnection connection, string name, string dataType, bool allowNulls, int timeoutInSeconds)
{
var cmd = table.Database.Server.GetCommand("ALTER TABLE " + table.GetFullyQualifiedName() + " ADD " + name + " " + dataType + " " + (allowNulls ? "NULL" : "NOT NULL"), connection);
cmd.CommandTimeout = timeoutInSeconds;
cmd.ExecuteNonQuery();
}
public virtual void TruncateTable(DiscoveredTable discoveredTable)
{
var server = discoveredTable.Database.Server;
using (var con = server.GetConnection())
{
con.Open();
server.GetCommand("TRUNCATE TABLE " + discoveredTable.GetFullyQualifiedName(), con).ExecuteNonQuery();
}
}
public virtual void AddColumn(DatabaseOperationArgs args, DiscoveredTable table, string name, string dataType, bool allowNulls)
{
var syntax = table.GetQuerySyntaxHelper();
using (var con = args.GetManagedConnection(table))
{
using (var cmd = table.Database.Server.GetCommand("ALTER TABLE " + table.GetFullyQualifiedName() + " ADD " + syntax.EnsureWrapped(name) + " " + dataType + " " + (allowNulls ? "NULL" : "NOT NULL"), con))
args.ExecuteNonQuery(cmd);
}
}
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 CreatePrimaryKey(DiscoveredTable table, DiscoveredColumn[] discoverColumns, IManagedConnection connection, int timeoutInSeconds = 0)
{
try{
string sql = string.Format("ALTER TABLE {0} ADD PRIMARY KEY ({1})",
table.GetFullyQualifiedName(),
string.Join(",", discoverColumns.Select(c => c.GetRuntimeName()))
);
DbCommand cmd = table.Database.Server.Helper.GetCommand(sql, connection.Connection, connection.Transaction);
cmd.CommandTimeout = timeoutInSeconds;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("Failed to create primary key on table " + table + " using columns (" + string.Join(",", discoverColumns.Select(c => c.GetRuntimeName())) + ")", e);
}
}
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 virtual void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
{
var syntax = table.GetQuerySyntaxHelper();
using (var connection = args.GetManagedConnection(table))
{
try{
string sql = string.Format("ALTER TABLE {0} ADD PRIMARY KEY ({1})",
table.GetFullyQualifiedName(),
string.Join(",", discoverColumns.Select(c => syntax.EnsureWrapped(c.GetRuntimeName())))
);
using (DbCommand cmd = table.Database.Server.Helper.GetCommand(sql, connection.Connection, connection.Transaction))
args.ExecuteNonQuery(cmd);
}
catch (Exception e)
{
throw new AlterFailedException(string.Format(FAnsiStrings.DiscoveredTableHelper_CreatePrimaryKey_Failed_to_create_primary_key_on_table__0__using_columns___1__, table, string.Join(",", discoverColumns.Select(c => c.GetRuntimeName()))), e);
}
}
}
public virtual void DropTable(DbConnection connection, DiscoveredTable tableToDrop)
{
string sql;
switch (tableToDrop.TableType)
{
case TableType.Table:
sql = "DROP TABLE {0}"; break;
case TableType.View:
sql = "DROP VIEW {0}"; break;
case TableType.TableValuedFunction:
throw new NotSupportedException();
default:
throw new ArgumentOutOfRangeException("Unknown TableType");
}
using (var cmd = tableToDrop.GetCommand(string.Format(sql, tableToDrop.GetFullyQualifiedName()), connection))
cmd.ExecuteNonQuery();
}
public virtual int GetRowCount(DatabaseOperationArgs args, DiscoveredTable table)
{
using (IManagedConnection connection = args.GetManagedConnection(table))
{
using (var cmd = table.Database.Server.GetCommand("SELECT count(*) FROM " + table.GetFullyQualifiedName(), connection))
return(Convert.ToInt32(args.ExecuteScalar(cmd)));
}
}
