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 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)));
}
}
public virtual void FillDataTableWithTopX(DiscoveredTable table, int topX, DataTable dt, DbConnection connection, DbTransaction transaction = null)
{
string sql = GetTopXSqlForTable(table, topX);
var da = table.Database.Server.GetDataAdapter(sql, connection);
da.Fill(dt);
}
/// <summary>
/// Begins a new bulk copy operation in which one or more data tables are uploaded to the <paramref name="targetTable"/>. The API entrypoint for this is
/// <see cref="DiscoveredTable.BeginBulkInsert(IManagedTransaction)"/>.
///
/// </summary>
/// <param name="targetTable"></param>
/// <param name="connection"></param>
protected BulkCopy(DiscoveredTable targetTable, IManagedConnection connection)
{
TargetTable = targetTable;
Connection = connection;
InvalidateTableSchema();
AllowUnmatchedInputColumns = false;
DateTimeDecider = new DateTimeTypeDecider();
}
/// <summary>
/// Internal API constructor intended for Implementation classes, instead use <see cref="DiscoveredTable.DiscoverColumn"/> instead.
/// </summary>
/// <param name="table"></param>
/// <param name="name"></param>
/// <param name="allowsNulls"></param>
public DiscoveredColumn(DiscoveredTable table, string name, bool allowsNulls)
{
Table = table;
Helper = table.Helper.GetColumnHelper();
_name = name;
_querySyntaxHelper = table.Database.Server.GetQuerySyntaxHelper();
AllowNulls = allowsNulls;
}
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 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 RenameTable(DiscoveredTable discoveredTable, string newName, IManagedConnection connection)
{
if (discoveredTable.TableType != TableType.Table)
{
throw new NotSupportedException("Rename is not supported for TableType " + discoveredTable.TableType);
}
DbCommand cmd = discoveredTable.Database.Server.Helper.GetCommand(GetRenameTableSql(discoveredTable, newName), connection.Connection, connection.Transaction);
cmd.ExecuteNonQuery();
}
public virtual void FillDataTableWithTopX(DatabaseOperationArgs args, DiscoveredTable table, int topX, DataTable dt)
{
string sql = GetTopXSqlForTable(table, topX);
using (var con = args.GetManagedConnection(table))
{
using (var cmd = table.Database.Server.GetCommand(sql, con))
using (var da = table.Database.Server.GetDataAdapter(cmd))
args.Fill(da, cmd, dt);
}
}
public virtual void RenameTable(DiscoveredTable discoveredTable, string newName, IManagedConnection connection)
{
if (discoveredTable.TableType != TableType.Table)
{
throw new NotSupportedException(string.Format(FAnsiStrings.DiscoveredTableHelper_RenameTable_Rename_is_not_supported_for_TableType__0_, discoveredTable.TableType));
}
discoveredTable.GetQuerySyntaxHelper().ValidateTableName(newName);
using (DbCommand cmd = discoveredTable.Database.Server.Helper.GetCommand(GetRenameTableSql(discoveredTable, newName), connection.Connection, connection.Transaction))
cmd.ExecuteNonQuery();
}
public virtual int ExecuteInsertReturningIdentity(DiscoveredTable discoveredTable, DbCommand cmd, IManagedTransaction transaction = null)
{
cmd.CommandText += ";SELECT @@IDENTITY";
var result = cmd.ExecuteScalar();
if (result == DBNull.Value || result == null)
{
return(0);
}
return(Convert.ToInt32(result));
}
/// <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 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 bool IsEmpty(DbConnection connection, DiscoveredTable discoveredTable, DbTransaction transaction)
{
return(GetRowCount(connection, discoveredTable, transaction) == 0);
}
public abstract IBulkCopy BeginBulkInsert(DiscoveredTable discoveredTable, IManagedConnection connection);
public abstract DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database);
protected abstract string GetRenameTableSql(DiscoveredTable discoveredTable, string newName);
public abstract DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null);
public abstract IBulkCopy BeginBulkInsert(DiscoveredTable discoveredTable, IManagedConnection connection, CultureInfo culture);
public string GetForeignKeyConstraintNameFor(DiscoveredTable foreignTable, DiscoveredTable primaryTable)
{
return(GetForeignKeyConstraintNameFor(foreignTable.GetRuntimeName(), primaryTable.GetRuntimeName()));
}
public virtual bool IsEmpty(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
return(GetRowCount(args, discoveredTable) == 0);
}