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