public override void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
var syntax = discoveredTable.GetQuerySyntaxHelper();
string sql =
@"DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum
FROM {1}
) as f
where RowNum > 1";
string columnList = string.Join(",",
discoveredTable.DiscoverColumns().Select(c => syntax.EnsureWrapped(c.GetRuntimeName())));
string sqlToExecute = string.Format(sql, columnList, discoveredTable.GetFullyQualifiedName());
var server = discoveredTable.Database.Server;
using (var con = args.GetManagedConnection(server))
{
using (var cmd = server.GetCommand(sqlToExecute, con))
args.ExecuteNonQuery(cmd);
}
}
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 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 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 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 override void FillDataTableWithTopX(DatabaseOperationArgs args, DiscoveredTable table, int topX, DataTable dt)
{
using (var con = args.GetManagedConnection(table))
{
((OracleConnection)con.Connection).PurgeStatementCache();
var cols = table.DiscoverColumns();
//apparently * doesn't fly with Oracle DataAdapter
string sql = "SELECT " + string.Join(",", cols.Select(c => c.GetFullyQualifiedName()).ToArray()) + " FROM " + table.GetFullyQualifiedName() + " OFFSET 0 ROWS FETCH NEXT " + topX + " ROWS ONLY";
using (var cmd = table.Database.Server.GetCommand(sql, con))
using (var da = table.Database.Server.GetDataAdapter(cmd))
args.Fill(da, cmd, dt);
}
}
public virtual DataTable GetDataTable(DatabaseOperationArgs args, int topX = int.MaxValue, bool enforceTypesAndNullness = true)
{
var dt = new DataTable();
if (enforceTypesAndNullness)
{
foreach (DiscoveredColumn c in DiscoverColumns(args.TransactionIfAny))
{
var col = dt.Columns.Add(c.GetRuntimeName());
col.AllowDBNull = c.AllowNulls;
col.DataType = c.DataType.GetCSharpDataType();
}
}
Helper.FillDataTableWithTopX(args, this, topX, dt);
return(dt);
}
/// <inheritdoc/>
public virtual DiscoveredRelationship AddForeignKey(DatabaseOperationArgs args, Dictionary <DiscoveredColumn, DiscoveredColumn> foreignKeyPairs, bool cascadeDeletes, string constraintName = null)
{
var foreignTables = foreignKeyPairs.Select(c => c.Key.Table).Distinct().ToArray();
var primaryTables = foreignKeyPairs.Select(c => c.Value.Table).Distinct().ToArray();
if (primaryTables.Length != 1 || foreignTables.Length != 1)
{
throw new ArgumentException("Primary and foreign keys must all belong to the same table", nameof(foreignKeyPairs));
}
var primary = primaryTables[0];
var foreign = foreignTables[0];
if (constraintName == null)
{
constraintName = primary.Database.Helper.GetForeignKeyConstraintNameFor(foreign, primary);
}
string constraintBit = primary.Database.Helper.GetForeignKeyConstraintSql(foreign.GetRuntimeName(), primary.GetQuerySyntaxHelper(),
foreignKeyPairs
.ToDictionary(k => (IHasRuntimeName)k.Key, v => v.Value), cascadeDeletes, constraintName);
string sql = $@"ALTER TABLE {foreign.GetFullyQualifiedName()}
ADD " + constraintBit;
using (var con = args.GetManagedConnection(primary))
{
try
{
using (var cmd = primary.Database.Server.GetCommand(sql, con))
args.ExecuteNonQuery(cmd);
}
catch (Exception e)
{
throw new AlterFailedException("Failed to create relationship using SQL:" + sql, e);
}
}
return(primary.DiscoverRelationships(args.TransactionIfAny).Single(
r => r.Name.Equals(constraintName, StringComparison.CurrentCultureIgnoreCase)
));
}
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 override void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
{
try
{
using (var connection = args.GetManagedConnection(table))
{
var columnHelper = GetColumnHelper();
foreach (var col in discoverColumns.Where(dc => dc.AllowNulls))
{
var alterSql = columnHelper.GetAlterColumnToSql(col, col.DataType.SQLType, false);
using (var alterCmd = table.GetCommand(alterSql, connection.Connection, connection.Transaction))
args.ExecuteNonQuery(alterCmd);
}
}
}
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);
}
base.CreatePrimaryKey(args, table, discoverColumns);
}
public DiscoveredRelationship AddForeignKey(DiscoveredColumn foreignKey, DiscoveredColumn primaryKey, bool cascadeDeletes, string constraintName = null, DatabaseOperationArgs args = null)
{
return(AddForeignKey(new Dictionary <DiscoveredColumn, DiscoveredColumn> {
{ foreignKey, primaryKey }
}, cascadeDeletes, constraintName, args));
}
/// <summary>
///
/// </summary>
/// <param name="foreignKeyPairs">
/// Key is the foreign key column (and the table the constraint will be put on).
/// Value is the primary key table column (which the constraint reference points to)</param>
/// <param name="cascadeDeletes"></param>
/// <returns></returns>
public DiscoveredRelationship AddForeignKey(Dictionary <DiscoveredColumn, DiscoveredColumn> foreignKeyPairs,
bool cascadeDeletes, string constraintName = null, DatabaseOperationArgs args = null)
{
return(Helper.AddForeignKey(args ?? new DatabaseOperationArgs(), foreignKeyPairs, cascadeDeletes, constraintName));
}
/// <summary>
/// Deletes all EXACT duplicate rows from the table leaving only unique records. This is method may not be transaction/threadsafe
/// </summary>
/// <param name="timeoutInSeconds">The length of time to allow for the command to complete (See <see cref="DbCommand.CommandTimeout"/>)</param>
public void MakeDistinct(DatabaseOperationArgs args)
{
Helper.MakeDistinct(args, this);
}
public void CreatePrimaryKey(DatabaseOperationArgs args, params DiscoveredColumn[] discoverColumns)
{
Helper.CreatePrimaryKey(args, this, discoverColumns);
}
public void AddColumn(string name, string databaseType, bool allowNulls, DatabaseOperationArgs args)
{
Helper.AddColumn(args, this, name, databaseType, allowNulls);
}
/// <summary>
/// Creates and runs an ALTER TABLE SQL statement that adds a new column to the table
/// </summary>
/// <param name="name">The unqualified name for the new column e.g. "MyCol2"</param>
/// <param name="type">The data type for the new column</param>
/// <param name="allowNulls">True to allow null</param>
public void AddColumn(string name, DatabaseTypeRequest type, bool allowNulls, DatabaseOperationArgs args)
{
AddColumn(name, Database.Server.GetQuerySyntaxHelper().TypeTranslater.GetSQLDBTypeForCSharpType(type), allowNulls, args);
}
/// <summary>
/// Returns true if there are no rows in the table
/// </summary>
/// <param name="transaction">Optional - if set the query will be sent on the connection on which the current <paramref name="transaction"/> is open</param>
/// <returns></returns>
public bool IsEmpty(DatabaseOperationArgs args)
{
return(Helper.IsEmpty(args, this));
}
/// <summary>
/// Returns the estimated number of rows in the table. This may use a short cut e.g. consulting sys.partitions in Sql
/// Server (https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql?view=sql-server-2017)
/// </summary>
/// <param name="transaction">Optional - if set the query will be sent on the connection on which the current <paramref name="transaction"/> is open</param>
/// <returns></returns>
public int GetRowCount(DatabaseOperationArgs args)
{
return(Helper.GetRowCount(args, this));
}
public virtual bool IsEmpty(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
{
return(GetRowCount(args, discoveredTable) == 0);
}
