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); } }
/// <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); }