Exception ReplaceException(Exception ex, SqlPreCommandSimple command) { //if (ex is Npgsql.PostgresException se) //{ // switch (se.Number) // { // case -2: return new TimeoutException(ex.Message, ex); // case 2601: return new UniqueKeyException(ex); // case 547: return new ForeignKeyException(ex); // default: return ex; // } //} //if (ex is SqlTypeException ste && ex.Message.Contains("DateTime")) //{ // var mins = command.Parameters.Where(a => DateTime.MinValue.Equals(a.Value)); // if (mins.Any()) // { // return new ArgumentOutOfRangeException("{0} {1} not initialized and equal to DateTime.MinValue".FormatWith( // mins.CommaAnd(a => a.ParameterName), // mins.Count() == 1 ? "is" : "are"), ex); // } //} return(ex); }
Exception ReplaceException(Exception ex, SqlPreCommandSimple command) { if (ex is SqlException se) { switch (se.Number) { case -2: return(new TimeoutException(ex.Message, ex)); case 2601: return(new UniqueKeyException(ex)); case 547: return(new ForeignKeyException(ex)); default: return(ex); } } if (ex is SqlTypeException ste && ex.Message.Contains("DateTime")) { var mins = command.Parameters.Where(a => DateTime.MinValue.Equals(a.Value)); if (mins.Any()) { return(new ArgumentOutOfRangeException("{0} {1} not initialized and equal to DateTime.MinValue".FormatWith( mins.CommaAnd(a => a.ParameterName), mins.Count() == 1 ? "is" : "are"), ex)); } } return(ex); }
protected internal override DataTable ExecuteDataTable(SqlPreCommandSimple preCommand, CommandType commandType) { return(EnsureConnectionRetry(con => { using (NpgsqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql())) { try { NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); DataTable result = new DataTable(); da.Fill(result); return result; } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } } })); }
protected internal override object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType) { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { object result = cmd.ExecuteScalar(); if (result == null || result == DBNull.Value) { return(null); } return(result); } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } } }
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType) { return(EnsureConnectionRetry(con => { using (NpgsqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql())) { try { int result = cmd.ExecuteNonQuery(); return result; } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } } })); }
protected internal override object?ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType) { return(EnsureConnectionRetry(con => { using (NpgsqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql())) { try { object?result = cmd.ExecuteScalar(); if (result == null || result == DBNull.Value) { return null; } return result; } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } } })); }
protected internal override DataTable ExecuteDataTable(SqlPreCommandSimple preCommand) { using (SqlCeConnection con = EnsureConnection()) using (SqlCeCommand cmd = NewCommand(preCommand, con)) using (HeavyProfiler.Log("SQL", () => cmd.CommandText)) { try { SqlCeDataAdapter da = new SqlCeDataAdapter(cmd); DataTable result = new DataTable(); da.Fill(result); return(result); } catch (SqlCeException ex) { var nex = HandleException(ex); if (nex == ex) { throw; } throw nex; } } }
SqlCeCommand NewCommand(SqlPreCommandSimple preCommand, SqlCeConnection overridenConnection) { SqlCeCommand cmd = new SqlCeCommand(); int?timeout = Connector.ScopeTimeout ?? CommandTimeout; if (timeout.HasValue) { cmd.CommandTimeout = timeout.Value; } if (overridenConnection != null) { cmd.Connection = overridenConnection; } else { cmd.Connection = (SqlCeConnection)Transaction.CurrentConnection; cmd.Transaction = (SqlCeTransaction)Transaction.CurrentTransaccion; } cmd.CommandText = preCommand.Sql; if (preCommand.Parameters != null) { foreach (SqlCeParameter param in preCommand.Parameters) { cmd.Parameters.Add(param); } } Log(preCommand); return(cmd); }
protected internal override DataSet ExecuteDataSet(SqlPreCommandSimple preCommand, CommandType commandType) { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet result = new DataSet(); da.Fill(result); return(result); } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } } }
public Exception HandleException(Exception ex, SqlPreCommandSimple command) { var nex = ReplaceException(ex, command); nex.Data["Sql"] = command.PlainSql(); return(nex); }
public static SqlPreCommand CreateIndex(Index index) { string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", "); if (index is PrimaryClusteredIndex) { return(new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED({2})".FormatWith( index.Table.Name, index.IndexName, columns))); } if (index is UniqueIndex uIndex && uIndex.ViewName != null) { ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName); SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}" .FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where)) { GoBefore = true, GoAfter = true }; SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})" .FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", "))); return(SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql)); }
SqlCeCommand NewCommand(SqlPreCommandSimple preCommand, SqlCeConnection overridenConnection) { SqlCeCommand cmd = new SqlCeCommand(); int? timeout = Connector.ScopeTimeout ?? CommandTimeout; if (timeout.HasValue) cmd.CommandTimeout = timeout.Value; if (overridenConnection != null) cmd.Connection = overridenConnection; else { cmd.Connection = (SqlCeConnection)Transaction.CurrentConnection; cmd.Transaction = (SqlCeTransaction)Transaction.CurrentTransaccion; } cmd.CommandText = preCommand.Sql; if (preCommand.Parameters != null) { foreach (SqlCeParameter param in preCommand.Parameters) { cmd.Parameters.Add(param); } } Log(preCommand); return cmd; }
public SqlPreCommand CreateTableSql(ITable t, ObjectName?tableName = null, bool avoidSystemVersioning = false) { var primaryKeyConstraint = t.PrimaryKey == null || t.SystemVersioned != null && tableName != null && t.SystemVersioned.TableName.Equals(tableName) ? null : isPostgres ? "CONSTRAINT {0} PRIMARY KEY ({1})".FormatWith(PrimaryKeyIndex.GetPrimaryKeyName(t.Name).SqlEscape(isPostgres), t.PrimaryKey.Name.SqlEscape(isPostgres)) : "CONSTRAINT {0} PRIMARY KEY CLUSTERED ({1} ASC)".FormatWith(PrimaryKeyIndex.GetPrimaryKeyName(t.Name).SqlEscape(isPostgres), t.PrimaryKey.Name.SqlEscape(isPostgres)); var systemPeriod = t.SystemVersioned == null || IsPostgres || avoidSystemVersioning ? null : Period(t.SystemVersioned); var columns = t.Columns.Values.Select(c => this.ColumnLine(c, GetDefaultConstaint(t, c), isChange: false, forHistoryTable: avoidSystemVersioning)) .And(primaryKeyConstraint) .And(systemPeriod) .NotNull() .ToString(",\r\n"); var systemVersioning = t.SystemVersioned == null || avoidSystemVersioning || IsPostgres ? null : $"\r\nWITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {t.SystemVersioned.TableName.OnDatabase(null)}))"; var result = new SqlPreCommandSimple($"CREATE {(IsPostgres && t.Name.IsTemporal ? "TEMPORARY " : "")}TABLE {tableName ?? t.Name}(\r\n{columns}\r\n)" + systemVersioning + ";"); if (!(IsPostgres && t.SystemVersioned != null)) { return(result); } return(new[]
public SqlPreCommand DeleteSqlSync(Entity ident, string comment = null) { var pre = OnPreDeleteSqlSync(ident); var collections = (from tml in this.TablesMList() select new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}" .FormatWith(tml.Name, tml.BackReference.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()))).Combine(Spacing.Simple); var main = new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}" .FormatWith(Name, this.PrimaryKey.Name.SqlEscape(), ident.Id, comment ?? ident.ToString())); return SqlPreCommand.Combine(Spacing.Simple, pre, collections, main); }
public SqlPreCommand DeleteSqlSync(IdentifiableEntity ident, string comment = null) { var pre = OnPreDeleteSqlSync(ident); var collections = (from m in this.Fields let ml = m.Value.Field as FieldMList where ml != null select new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}" .Formato(ml.TableMList.Name, ml.TableMList.BackReference.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()))).Combine(Spacing.Simple); var main = new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}" .Formato(Name, "Id", ident.Id, comment ?? ident.ToString())); return SqlPreCommand.Combine(Spacing.Simple, pre, collections, main); }
protected static void Log(SqlPreCommandSimple pcs) { var log = logger.Value; if (log != null) { log.WriteLine(pcs.Sql); if (pcs.Parameters != null) log.WriteLine(pcs.Parameters .ToString(p => "{0} {1}: {2}".FormatWith( p.ParameterName, Connector.Current.GetSqlDbType(p), p.Value?.Let(v => CSharpRenderer.Value(v, v.GetType(), null))), "\r\n")); log.WriteLine(); } }
public static SqlPreCommand CreateIndex(Index index) { string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", "); if (!(index is UniqueIndex)) { return(new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".FormatWith( index.IndexName, index.Table.Name, columns))); } else { var uIndex = (UniqueIndex)index; if (string.IsNullOrEmpty(uIndex.Where)) { return(new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".FormatWith( uIndex is UniqueIndex ? "UNIQUE " : null, uIndex.IndexName, uIndex.Table.Name, columns))); } if (uIndex.ViewName != null) { ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName); SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}" .FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where)) { GoBefore = true, GoAfter = true }; SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})" .FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", "))); return(SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql)); } else { return(new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".FormatWith( uIndex.IndexName, uIndex.Table.Name, columns, uIndex.Where))); } } }
public static SqlPreCommand MoveRows(ObjectName oldTable, ObjectName newTable, IEnumerable <string> columnNames) { SqlPreCommandSimple command = new SqlPreCommandSimple( @"INSERT INTO {0} ({2}) SELECT {3} FROM {1} as [table]".FormatWith( newTable, oldTable, columnNames.ToString(a => a.SqlEscape(), ", "), columnNames.ToString(a => "[table]." + a.SqlEscape(), ", "))); return(SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("SET IDENTITY_INSERT {0} ON".FormatWith(newTable)), command, new SqlPreCommandSimple("SET IDENTITY_INSERT {0} OFF".FormatWith(newTable)))); }
public static SqlPreCommand AlterTableAlterColumn(ITable table, IColumn column, string?defaultConstraintName = null, ObjectName?forceTableName = null) { var alterColumn = new SqlPreCommandSimple("ALTER TABLE {0} ALTER COLUMN {1}".FormatWith(forceTableName ?? table.Name, CreateColumn(column, null, isChange: true))); if (column.Default == null) { return(alterColumn); } var defCons = GetDefaultConstaint(table, column) !; return(SqlPreCommand.Combine(Spacing.Simple, AlterTableDropConstraint(table.Name, defaultConstraintName ?? defCons.Name), alterColumn, AlterTableAddDefaultConstraint(table.Name, defCons) ) !); }
protected internal override DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple preCommand) { try { SqlCeCommand cmd = NewCommand(preCommand, null); return(cmd.ExecuteReader()); } catch (SqlCeException ex) { var nex = HandleException(ex); if (nex == ex) { throw; } throw nex; } }
protected static void Log(SqlPreCommandSimple pcs) { var log = logger.Value; if (log != null) { log.WriteLine(pcs.Sql); if (pcs.Parameters != null) { log.WriteLine(pcs.Parameters .ToString(p => "{0} {1}: {2}".FormatWith( p.ParameterName, Connector.Current.GetSqlDbType(p), p.Value?.Let(v => CSharpRenderer.Value(v, v.GetType(), null))), "\r\n")); } log.WriteLine(); } }
public static SqlPreCommand CreateIndex(Index index, Replacements?checkUnique) { if (index is PrimaryClusteredIndex) { var columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", "); return(new SqlPreCommandSimple($"ALTER TABLE {index.Table.Name} ADD CONSTRAINT {index.IndexName} PRIMARY KEY CLUSTERED({columns})")); } if (index is UniqueIndex uIndex) { if (uIndex.ViewName != null) { ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName); var columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", "); SqlPreCommandSimple viewSql = new SqlPreCommandSimple($"CREATE VIEW {viewName} WITH SCHEMABINDING AS SELECT {columns} FROM {uIndex.Table.Name.ToString()} WHERE {uIndex.Where}") { GoBefore = true, GoAfter = true }; SqlPreCommandSimple indexSql = new SqlPreCommandSimple($"CREATE UNIQUE CLUSTERED INDEX {uIndex.IndexName} ON {viewName}({columns})"); return(SqlPreCommand.Combine(Spacing.Simple, checkUnique != null ? RemoveDuplicatesIfNecessary(uIndex, checkUnique) : null, viewSql, indexSql) !); } else { return(SqlPreCommand.Combine(Spacing.Double, checkUnique != null ? RemoveDuplicatesIfNecessary(uIndex, checkUnique) : null, CreateIndexBasic(index, false)) !); } } else { return(CreateIndexBasic(index, forHistoryTable: false)); } }
protected internal override DbDataReaderWithCommand UnsafeExecuteDataReader(SqlPreCommandSimple preCommand, CommandType commandType) { try { var cmd = NewCommand(preCommand, null, commandType); var reader = cmd.ExecuteReader(); return(new DbDataReaderWithCommand(cmd, reader)); } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } }
internal static SqlPreCommand CopyData(ITable newTable, DiffTable oldTable, Replacements rep) { var selectColumns = newTable.Columns .Select(col => oldTable.Columns.TryGetC(col.Key)?.Name ?? GetDefaultValue(newTable, col.Value, rep)) .ToString(", "); var insertSelect = new SqlPreCommandSimple( $@"INSERT INTO {newTable.Name} ({newTable.Columns.Values.ToString(a => a.Name, ", ")}) SELECT {selectColumns} FROM {oldTable.Name}"); if (!newTable.PrimaryKey.Identity) { return(insertSelect); } return(SqlPreCommand.Combine(Spacing.Simple, SqlBuilder.SetIdentityInsert(newTable.Name, true), insertSelect, SqlBuilder.SetIdentityInsert(newTable.Name, false) )); }
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand) { using (SqlCeConnection con = EnsureConnection()) using (SqlCeCommand cmd = NewCommand(preCommand, con)) using (HeavyProfiler.Log("SQL", () => cmd.CommandText)) { try { int result = cmd.ExecuteNonQuery(); return(result); } catch (SqlCeException ex) { var nex = HandleException(ex); if (nex == ex) { throw; } throw nex; } } }
NpgsqlCommand NewCommand(SqlPreCommandSimple preCommand, NpgsqlConnection?overridenConnection, CommandType commandType) { NpgsqlCommand cmd = new NpgsqlCommand { CommandType = commandType }; int?timeout = Connector.ScopeTimeout ?? CommandTimeout; if (timeout.HasValue) { cmd.CommandTimeout = timeout.Value; } if (overridenConnection != null) { cmd.Connection = overridenConnection; } else { cmd.Connection = (NpgsqlConnection)Transaction.CurrentConnection !; cmd.Transaction = (NpgsqlTransaction)Transaction.CurrentTransaccion !; } cmd.CommandText = preCommand.Sql; if (preCommand.Parameters != null) { foreach (NpgsqlParameter param in preCommand.Parameters) { cmd.Parameters.Add(param); } } Log(preCommand); return(cmd); }
public static SqlPreCommand SynchronizeTablesScript(Replacements replacements) { Dictionary <string, ITable> model = Schema.Current.GetDatabaseTables().ToDictionaryEx(a => a.Name.ToString(), "schema tables"); HashSet <SchemaName> modelSchemas = Schema.Current.GetDatabaseTables().Select(a => a.Name.Schema).Where(a => !SqlBuilder.SystemSchemas.Contains(a.Name)).ToHashSet(); Dictionary <string, DiffTable> database = DefaultGetDatabaseDescription(Schema.Current.DatabaseNames()); HashSet <SchemaName> databaseSchemas = DefaultGetSchemas(Schema.Current.DatabaseNames()); if (SimplifyDiffTables != null) { SimplifyDiffTables(database); } replacements.AskForReplacements(database.Keys.ToHashSet(), model.Keys.ToHashSet(), Replacements.KeyTables); database = replacements.ApplyReplacementsToOld(database, Replacements.KeyTables); Dictionary <ITable, Dictionary <string, Index> > modelIndices = model.Values .ToDictionary(t => t, t => t.GeneratAllIndexes().ToDictionaryEx(a => a.IndexName, "Indexes for {0}".FormatWith(t.Name))); model.JoinDictionaryForeach(database, (tn, tab, diff) => { var key = Replacements.KeyColumnsForTable(tn); replacements.AskForReplacements(diff.Columns.Keys.ToHashSet(), tab.Columns.Keys.ToHashSet(), key); diff.Columns = replacements.ApplyReplacementsToOld(diff.Columns, key); diff.Indices = ApplyIndexAutoReplacements(diff, tab, modelIndices[tab]); }); Func <ObjectName, ObjectName> ChangeName = (ObjectName objectName) => { string name = replacements.Apply(Replacements.KeyTables, objectName.ToString()); return(model.TryGetC(name)?.Name ?? objectName); }; Func <ObjectName, SqlPreCommand> DeleteAllForeignKey = tableName => { var dropFks = (from t in database.Values from c in t.Columns.Values where c.ForeignKey != null && c.ForeignKey.TargetTable.Equals(tableName) select SqlBuilder.AlterTableDropConstraint(t.Name, c.ForeignKey.Name)).Combine(Spacing.Simple); if (dropFks == null) { return(null); } return(SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("---In order to remove the PK of " + tableName.Name), dropFks)); }; using (replacements.WithReplacedDatabaseName()) { SqlPreCommand createSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas", modelSchemas.ToDictionary(a => a.ToString()), databaseSchemas.ToDictionary(a => a.ToString()), (_, newSN) => SqlBuilder.CreateSchema(newSN), null, (_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.CreateSchema(newSN), Spacing.Double); //use database without replacements to just remove indexes SqlPreCommand dropStatistics = Synchronizer.SynchronizeScript(model, database, null, (tn, dif) => SqlBuilder.DropStatistics(tn, dif.Stats), (tn, tab, dif) => { var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet(); return(SqlBuilder.DropStatistics(tn, dif.Stats.Where(a => a.Columns.Any(removedColums.Contains)).ToList())); }, Spacing.Double); SqlPreCommand dropIndices = Synchronizer.SynchronizeScript(model, database, null, (tn, dif) => dif.Indices.Values.Where(ix => !ix.IsPrimary).Select(ix => SqlBuilder.DropIndex(dif.Name, ix)).Combine(Spacing.Simple), (tn, tab, dif) => { Dictionary <string, Index> modelIxs = modelIndices[tab]; var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet(); var changes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices, null, (i, dix) => dix.Columns.Any(removedColums.Contains) || dix.IsControlledIndex ? SqlBuilder.DropIndex(dif.Name, dix) : null, (i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlPreCommand.Combine(Spacing.Double, dix.IsPrimary ? DeleteAllForeignKey(dif.Name) : null, SqlBuilder.DropIndex(dif.Name, dix)) : null, Spacing.Simple); return(changes); }, Spacing.Double); SqlPreCommand dropForeignKeys = Synchronizer.SynchronizeScript( model, database, null, (tn, dif) => dif.Columns.Values.Select(c => c.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, c.ForeignKey.Name) : null) .Concat(dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name))).Combine(Spacing.Simple), (tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple, Synchronizer.SynchronizeScript( tab.Columns, dif.Columns, null, (cn, colDb) => colDb.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null, (cn, colModel, colDb) => colDb.ForeignKey == null ? null : colModel.ReferenceTable == null || colModel.AvoidForeignKey || !colModel.ReferenceTable.Name.Equals(ChangeName(colDb.ForeignKey.TargetTable)) ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null, Spacing.Simple), dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name)).Combine(Spacing.Simple)), Spacing.Double); SqlPreCommand tables = Synchronizer.SynchronizeScript( model, database, (tn, tab) => SqlBuilder.CreateTableSql(tab), (tn, dif) => SqlBuilder.DropTable(dif.Name), (tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple, !object.Equals(dif.Name, tab.Name) ? SqlBuilder.RenameOrMove(dif, tab) : null, Synchronizer.SynchronizeScript( tab.Columns, dif.Columns, (cn, tabCol) => SqlPreCommandSimple.Combine(Spacing.Simple, tabCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null, AlterTableAddColumnDefault(tab, tabCol, replacements)), (cn, difCol) => SqlPreCommandSimple.Combine(Spacing.Simple, difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, difCol.Name) : null, SqlBuilder.AlterTableDropColumn(tab, cn)), (cn, tabCol, difCol) => SqlPreCommand.Combine(Spacing.Simple, difCol.Name == tabCol.Name ? null : SqlBuilder.RenameColumn(tab, difCol.Name, tabCol.Name), difCol.ColumnEquals(tabCol, ignorePrimaryKey: true) ? null : SqlPreCommand.Combine(Spacing.Simple, tabCol.PrimaryKey && !difCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null, SqlBuilder.AlterTableAlterColumn(tab, tabCol), tabCol.SqlDbType == SqlDbType.NVarChar && difCol.SqlDbType == SqlDbType.NChar ? SqlBuilder.UpdateTrim(tab, tabCol) : null), difCol.DefaultEquals(tabCol) ? null : SqlPreCommand.Combine(Spacing.Simple, difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, tabCol.Name) : null, tabCol.Default != null ? SqlBuilder.AddDefaultConstraint(tab.Name, tabCol.Name, tabCol.Default) : null), UpdateByFkChange(tn, difCol, tabCol, ChangeName)), Spacing.Simple)), Spacing.Double); if (tables != null) { tables.GoAfter = true; } var tableReplacements = replacements.TryGetC(Replacements.KeyTables); if (tableReplacements != null) { replacements[Replacements.KeyTablesInverse] = tableReplacements.Inverse(); } SqlPreCommand syncEnums; try { syncEnums = SynchronizeEnumsScript(replacements); } catch (Exception e) { syncEnums = new SqlPreCommandSimple("-- Exception synchronizing enums: " + e.Message); } SqlPreCommand addForeingKeys = Synchronizer.SynchronizeScript( model, database, (tn, tab) => SqlBuilder.AlterTableForeignKeys(tab), null, (tn, tab, dif) => Synchronizer.SynchronizeScript( tab.Columns, dif.Columns, (cn, colModel) => colModel.ReferenceTable == null || colModel.AvoidForeignKey ? null : SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable), null, (cn, colModel, coldb) => { if (colModel.ReferenceTable == null || colModel.AvoidForeignKey) { return(null); } if (coldb.ForeignKey == null || !colModel.ReferenceTable.Name.Equals(ChangeName(coldb.ForeignKey.TargetTable))) { return(SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable)); } var name = SqlBuilder.ForeignKeyName(tab.Name.Name, colModel.Name); return(SqlPreCommand.Combine(Spacing.Simple, name != coldb.ForeignKey.Name.Name ? SqlBuilder.RenameForeignKey(coldb.ForeignKey.Name, name) : null, (coldb.ForeignKey.IsDisabled || coldb.ForeignKey.IsNotTrusted) && !replacements.SchemaOnly ? SqlBuilder.EnableForeignKey(tab.Name, name) : null)); }, Spacing.Simple), Spacing.Double); bool?createMissingFreeIndexes = null; SqlPreCommand addIndices = Synchronizer.SynchronizeScript(model, database, (tn, tab) => modelIndices[tab].Values.Where(a => !(a is PrimaryClusteredIndex)).Select(SqlBuilder.CreateIndex).Combine(Spacing.Simple), null, (tn, tab, dif) => { var columnReplacements = replacements.TryGetC(Replacements.KeyColumnsForTable(tn)); Func <IColumn, bool> isNew = c => !dif.Columns.ContainsKey(columnReplacements?.TryGetC(c.Name) ?? c.Name); Dictionary <string, Index> modelIxs = modelIndices[tab]; var controlledIndexes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices, (i, mix) => mix is UniqueIndex || mix.Columns.Any(isNew) || SafeConsole.Ask(ref createMissingFreeIndexes, "Create missing non-unique index {0} in {1}?".FormatWith(mix.IndexName, tab.Name)) ? SqlBuilder.CreateIndex(mix) : null, null, (i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlBuilder.CreateIndex(mix) : mix.IndexName != dix.IndexName ? SqlBuilder.RenameIndex(tab, dix.IndexName, mix.IndexName) : null, Spacing.Simple); return(SqlPreCommand.Combine(Spacing.Simple, controlledIndexes)); }, Spacing.Double); SqlPreCommand dropSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas", modelSchemas.ToDictionary(a => a.ToString()), databaseSchemas.ToDictionary(a => a.ToString()), null, (_, oldSN) => DropSchema(oldSN) ? SqlBuilder.DropSchema(oldSN) : null, (_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.DropSchema(oldSN), Spacing.Double); return(SqlPreCommand.Combine(Spacing.Triple, createSchemas, dropStatistics, dropIndices, dropForeignKeys, tables, syncEnums, addForeingKeys, addIndices, dropSchemas)); } }
protected internal override DataTable ExecuteDataTable(SqlPreCommandSimple preCommand) { using (SqlCeConnection con = EnsureConnection()) using (SqlCeCommand cmd = NewCommand(preCommand, con)) using (HeavyProfiler.Log("SQL", () => cmd.CommandText)) { try { SqlCeDataAdapter da = new SqlCeDataAdapter(cmd); DataTable result = new DataTable(); da.Fill(result); return result; } catch (SqlCeException ex) { var nex = HandleException(ex); if (nex == ex) throw; throw nex; } } }
protected internal override async Task <DbDataReaderWithCommand> UnsafeExecuteDataReaderAsync(SqlPreCommandSimple preCommand, CommandType commandType, CancellationToken token) { try { var cmd = NewCommand(preCommand, null, commandType); var reader = await cmd.ExecuteReaderAsync(token); return(new DbDataReaderWithCommand(cmd, reader)); } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } }
protected internal abstract int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType);
public static SqlPreCommand CreateIndex(Index index) { string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", "); if (!(index is UniqueIndex)) { return new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".Formato( index.IndexName, index.Table.Name, columns)); } else { var uIndex = (UniqueIndex)index; if (string.IsNullOrEmpty(uIndex.Where)) { return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".Formato( uIndex is UniqueIndex ? "UNIQUE " : null, uIndex.IndexName, uIndex.Table.Name, columns)); } if (uIndex.ViewName != null) { ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName); SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}" .Formato(viewName, columns, uIndex.Table.Name.ToStringDbo(), uIndex.Where)) { AddGo = true }; SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})" .Formato(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", "))); return SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql); } else { return new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".Formato( uIndex.IndexName, uIndex.Table.Name, columns, uIndex.Where)); } } }
protected internal abstract DataSet ExecuteDataSet(SqlPreCommandSimple sqlPreCommandSimple, CommandType commandType);
protected internal abstract DataTable ExecuteDataTable(SqlPreCommandSimple command, CommandType commandType);
public void ExecuteDataReaderDependency(SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action reconect, Action <FieldReader> forEach, CommandType commandType) { bool reconected = false; retry: try { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL-Dependency")) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { if (change != null) { SqlDependency dep = new SqlDependency(cmd); dep.OnChange += change; } using (SqlDataReader reader = cmd.ExecuteReader()) { FieldReader fr = new FieldReader(reader); int row = -1; try { while (reader.Read()) { row++; forEach(fr); } } catch (Exception ex) { FieldReaderException fieldEx = fr.CreateFieldReaderException(ex); fieldEx.Command = preCommand; fieldEx.Row = row; throw fieldEx; } } } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) { throw; } throw nex; } } } catch (InvalidOperationException ioe) { if (ioe.Message.Contains("SqlDependency.Start()") && !reconected) { reconect(); reconected = true; goto retry; } throw; } }
public void ExecuteDataReaderDependency(SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action reconect, Action<FieldReader> forEach, CommandType commandType) { bool reconected = false; retry: try { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL-Dependency")) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { if (change != null) { SqlDependency dep = new SqlDependency(cmd); dep.OnChange += change; } using (SqlDataReader reader = cmd.ExecuteReader()) { FieldReader fr = new FieldReader(reader); int row = -1; try { while (reader.Read()) { row++; forEach(fr); } } catch (Exception ex) { FieldReaderException fieldEx = fr.CreateFieldReaderException(ex); fieldEx.Command = preCommand; fieldEx.Row = row; throw fieldEx; } } } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) throw; throw nex; } } } catch (InvalidOperationException ioe) { if (ioe.Message.Contains("SqlDependency.Start()") && !reconected) { reconect(); reconected = true; goto retry; } throw; } }
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType) { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { int result = cmd.ExecuteNonQuery(); return result; } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) throw; throw nex; } } }
protected internal override object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType) { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { object result = cmd.ExecuteScalar(); if (result == null || result == DBNull.Value) return null; return result; } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) throw; throw nex; } } }
protected internal abstract object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType);
protected internal override object ExecuteScalar(SqlPreCommandSimple preCommand) { using (SqlCeConnection con = EnsureConnection()) using (SqlCeCommand cmd = NewCommand(preCommand, con)) using (HeavyProfiler.Log("SQL", () => cmd.CommandText)) { try { if (cmd.CommandText.EndsWith(selecctInsertedId)) { cmd.CommandText = cmd.CommandText.RemoveEnd(selecctInsertedId.Length); cmd.ExecuteNonQuery(); cmd.CommandText = selecctInsertedId; object result = cmd.ExecuteScalar(); if (result == null || result == DBNull.Value) return null; return result; } else if (cmd.CommandText.EndsWith(selectRowCount)) { cmd.CommandText = cmd.CommandText.RemoveEnd(selectRowCount.Length); cmd.ExecuteNonQuery(); cmd.CommandText = selectRowCount; object result = cmd.ExecuteScalar(); if (result == null || result == DBNull.Value) return null; return result; } else { object result = cmd.ExecuteScalar(); if (result == null || result == DBNull.Value) return null; return result; } } catch (SqlCeException ex) { var nex = HandleException(ex); if (nex == ex) throw; throw nex; } } }
Exception ReplaceException(Exception ex, SqlPreCommandSimple command) { var se = ex as SqlException; if (se != null) { switch (se.Number) { case -2: return new TimeoutException(ex.Message, ex); case 2601: return new UniqueKeyException(ex); case 547: return new ForeignKeyException(ex); default: return ex; } } var ste = ex as SqlTypeException; if (ste != null && ex.Message.Contains("DateTime")) { var mins = command.Parameters.Where(a => DateTime.MinValue.Equals(a.Value)); if (mins.Any()) { return new ArgumentOutOfRangeException("{0} {1} not initialized and equal to DateTime.MinValue".FormatWith( mins.CommaAnd(a => a.ParameterName), mins.Count() == 1 ? "is" : "are"), ex); } } return ex; }
protected internal abstract DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple sqlPreCommandSimple, CommandType commandType);
public static SqlPreCommand SynchronizeTablesScript(Replacements replacements) { Schema s = Schema.Current; Dictionary<string, ITable> model = s.GetDatabaseTables().Where(t => !s.IsExternalDatabase(t.Name.Schema.Database)).ToDictionaryEx(a => a.Name.ToString(), "schema tables"); HashSet<SchemaName> modelSchemas = model.Values.Select(a => a.Name.Schema).Where(a => !SqlBuilder.SystemSchemas.Contains(a.Name)).ToHashSet(); Dictionary<string, DiffTable> database = DefaultGetDatabaseDescription(s.DatabaseNames()); HashSet<SchemaName> databaseSchemas = DefaultGetSchemas(s.DatabaseNames()); if (SimplifyDiffTables != null) SimplifyDiffTables(database); replacements.AskForReplacements(database.Keys.ToHashSet(), model.Keys.ToHashSet(), Replacements.KeyTables); database = replacements.ApplyReplacementsToOld(database, Replacements.KeyTables); Dictionary<ITable, Dictionary<string, Index>> modelIndices = model.Values .ToDictionary(t => t, t => t.GeneratAllIndexes().ToDictionaryEx(a => a.IndexName, "Indexes for {0}".FormatWith(t.Name))); model.JoinDictionaryForeach(database, (tn, tab, diff) => { var key = Replacements.KeyColumnsForTable(tn); replacements.AskForReplacements(diff.Columns.Keys.ToHashSet(), tab.Columns.Keys.ToHashSet(), key); diff.Columns = replacements.ApplyReplacementsToOld(diff.Columns, key); diff.Indices = ApplyIndexAutoReplacements(diff, tab, modelIndices[tab]); }); Func<ObjectName, ObjectName> ChangeName = (ObjectName objectName) => { string name = replacements.Apply(Replacements.KeyTables, objectName.ToString()); return model.TryGetC(name)?.Name ?? objectName; }; Func<ObjectName, SqlPreCommand> DeleteAllForeignKey = tableName => { var dropFks = (from t in database.Values from c in t.Columns.Values where c.ForeignKey != null && c.ForeignKey.TargetTable.Equals(tableName) select SqlBuilder.AlterTableDropConstraint(t.Name, c.ForeignKey.Name)).Combine(Spacing.Simple); if (dropFks == null) return null; return SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("---In order to remove the PK of " + tableName.Name), dropFks); }; using (replacements.WithReplacedDatabaseName()) { SqlPreCommand createSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas", modelSchemas.ToDictionary(a => a.ToString()), databaseSchemas.ToDictionary(a => a.ToString()), (_, newSN) => SqlBuilder.CreateSchema(newSN), null, (_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.CreateSchema(newSN), Spacing.Double); //use database without replacements to just remove indexes SqlPreCommand dropStatistics = Synchronizer.SynchronizeScript(model, database, null, (tn, dif) => SqlBuilder.DropStatistics(tn, dif.Stats), (tn, tab, dif) => { var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet(); return SqlBuilder.DropStatistics(tn, dif.Stats.Where(a => a.Columns.Any(removedColums.Contains)).ToList()); }, Spacing.Double); SqlPreCommand dropIndices = Synchronizer.SynchronizeScript(model, database, null, (tn, dif) => dif.Indices.Values.Where(ix => !ix.IsPrimary).Select(ix => SqlBuilder.DropIndex(dif.Name, ix)).Combine(Spacing.Simple), (tn, tab, dif) => { Dictionary<string, Index> modelIxs = modelIndices[tab]; var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet(); var changes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices, null, (i, dix) => dix.Columns.Any(removedColums.Contains) || dix.IsControlledIndex ? SqlBuilder.DropIndex(dif.Name, dix) : null, (i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlPreCommand.Combine(Spacing.Double, dix.IsPrimary ? DeleteAllForeignKey(dif.Name) : null, SqlBuilder.DropIndex(dif.Name, dix)) : null, Spacing.Simple); return changes; }, Spacing.Double); SqlPreCommand dropForeignKeys = Synchronizer.SynchronizeScript( model, database, null, (tn, dif) => dif.Columns.Values.Select(c => c.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, c.ForeignKey.Name) : null) .Concat(dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name))).Combine(Spacing.Simple), (tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple, Synchronizer.SynchronizeScript( tab.Columns, dif.Columns, null, (cn, colDb) => colDb.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null, (cn, colModel, colDb) => colDb.ForeignKey == null ? null : colModel.ReferenceTable == null || colModel.AvoidForeignKey || !colModel.ReferenceTable.Name.Equals(ChangeName(colDb.ForeignKey.TargetTable)) ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null, Spacing.Simple), dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name)).Combine(Spacing.Simple)), Spacing.Double); SqlPreCommand tables = Synchronizer.SynchronizeScript( model, database, (tn, tab) => SqlBuilder.CreateTableSql(tab), (tn, dif) => SqlBuilder.DropTable(dif.Name), (tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple, !object.Equals(dif.Name, tab.Name) ? SqlBuilder.RenameOrMove(dif, tab) : null, Synchronizer.SynchronizeScript( tab.Columns, dif.Columns, (cn, tabCol) => SqlPreCommandSimple.Combine(Spacing.Simple, tabCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null, AlterTableAddColumnDefault(tab, tabCol, replacements)), (cn, difCol) => SqlPreCommandSimple.Combine(Spacing.Simple, difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, difCol.Name) : null, SqlBuilder.AlterTableDropColumn(tab, cn)), (cn, tabCol, difCol) => SqlPreCommand.Combine(Spacing.Simple, difCol.Name == tabCol.Name ? null : SqlBuilder.RenameColumn(tab, difCol.Name, tabCol.Name), difCol.ColumnEquals(tabCol, ignorePrimaryKey: true) ? null : SqlPreCommand.Combine(Spacing.Simple, tabCol.PrimaryKey && !difCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null, SqlBuilder.AlterTableAlterColumn(tab, tabCol), tabCol.SqlDbType == SqlDbType.NVarChar && difCol.SqlDbType == SqlDbType.NChar ? SqlBuilder.UpdateTrim(tab, tabCol) : null), difCol.DefaultEquals(tabCol) ? null : SqlPreCommand.Combine(Spacing.Simple, difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, tabCol.Name) : null, tabCol.Default != null ? SqlBuilder.AddDefaultConstraint(tab.Name, tabCol.Name, tabCol.Default) : null), UpdateByFkChange(tn, difCol, tabCol, ChangeName)), Spacing.Simple)), Spacing.Double); if (tables != null) tables.GoAfter = true; var tableReplacements = replacements.TryGetC(Replacements.KeyTables); if (tableReplacements != null) replacements[Replacements.KeyTablesInverse] = tableReplacements.Inverse(); SqlPreCommand syncEnums; try { syncEnums = SynchronizeEnumsScript(replacements); } catch(Exception e) { syncEnums = new SqlPreCommandSimple("-- Exception synchronizing enums: " + e.Message); } SqlPreCommand addForeingKeys = Synchronizer.SynchronizeScript( model, database, (tn, tab) => SqlBuilder.AlterTableForeignKeys(tab), null, (tn, tab, dif) => Synchronizer.SynchronizeScript( tab.Columns, dif.Columns, (cn, colModel) => colModel.ReferenceTable == null || colModel.AvoidForeignKey ? null : SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable), null, (cn, colModel, coldb) => { if (colModel.ReferenceTable == null || colModel.AvoidForeignKey) return null; if (coldb.ForeignKey == null || !colModel.ReferenceTable.Name.Equals(ChangeName(coldb.ForeignKey.TargetTable))) return SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable); var name = SqlBuilder.ForeignKeyName(tab.Name.Name, colModel.Name); return SqlPreCommand.Combine(Spacing.Simple, name != coldb.ForeignKey.Name.Name ? SqlBuilder.RenameForeignKey(coldb.ForeignKey.Name, name) : null, (coldb.ForeignKey.IsDisabled || coldb.ForeignKey.IsNotTrusted) && !replacements.SchemaOnly ? SqlBuilder.EnableForeignKey(tab.Name, name) : null); }, Spacing.Simple), Spacing.Double); bool? createMissingFreeIndexes = null; SqlPreCommand addIndices = Synchronizer.SynchronizeScript(model, database, (tn, tab) => modelIndices[tab].Values.Where(a => !(a is PrimaryClusteredIndex)).Select(SqlBuilder.CreateIndex).Combine(Spacing.Simple), null, (tn, tab, dif) => { var columnReplacements = replacements.TryGetC(Replacements.KeyColumnsForTable(tn)); Func<IColumn, bool> isNew = c => !dif.Columns.ContainsKey(columnReplacements?.TryGetC(c.Name) ?? c.Name); Dictionary<string, Index> modelIxs = modelIndices[tab]; var controlledIndexes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices, (i, mix) => mix is UniqueIndex || mix.Columns.Any(isNew) || SafeConsole.Ask(ref createMissingFreeIndexes, "Create missing non-unique index {0} in {1}?".FormatWith(mix.IndexName, tab.Name)) ? SqlBuilder.CreateIndex(mix) : null, null, (i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlBuilder.CreateIndex(mix) : mix.IndexName != dix.IndexName ? SqlBuilder.RenameIndex(tab, dix.IndexName, mix.IndexName) : null, Spacing.Simple); return SqlPreCommand.Combine(Spacing.Simple, controlledIndexes); }, Spacing.Double); SqlPreCommand dropSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas", modelSchemas.ToDictionary(a => a.ToString()), databaseSchemas.ToDictionary(a => a.ToString()), null, (_, oldSN) => DropSchema(oldSN) ? SqlBuilder.DropSchema(oldSN) : null, (_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.DropSchema(oldSN), Spacing.Double); return SqlPreCommand.Combine(Spacing.Triple, createSchemas, dropStatistics, dropIndices, dropForeignKeys, tables, syncEnums, addForeingKeys, addIndices, dropSchemas); } }
public static SqlPreCommand MoveRows(ObjectName oldTable, ObjectName newTable, IEnumerable<string> columnNames) { SqlPreCommandSimple command = new SqlPreCommandSimple( @"INSERT INTO {0} ({2}) SELECT {3} FROM {1} as [table]".Formato( newTable, oldTable, columnNames.ToString(a => a.SqlEscape(), ", "), columnNames.ToString(a => "[table]." + a.SqlEscape(), ", "))); return SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("SET IDENTITY_INSERT {0} ON".Formato(newTable)), command, new SqlPreCommandSimple("SET IDENTITY_INSERT {0} OFF".Formato(newTable))); }
protected internal override DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple preCommand, CommandType commandType) { try { SqlCommand cmd = NewCommand(preCommand, null, commandType); return cmd.ExecuteReader(); } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) throw; throw nex; } }
protected internal override DataSet ExecuteDataSet(SqlPreCommandSimple preCommand, CommandType commandType) { using (SqlConnection con = EnsureConnection()) using (SqlCommand cmd = NewCommand(preCommand, con, commandType)) using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql())) { try { SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet result = new DataSet(); da.Fill(result); return result; } catch (Exception ex) { var nex = HandleException(ex, preCommand); if (nex == ex) throw; throw nex; } } }
public Exception HandleException(Exception ex, SqlPreCommandSimple command) { var nex = ReplaceException(ex, command); nex.Data["Sql"] = command.PlainSql(); return nex; }
public static SqlPreCommand CreateIndex(Index index) { string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", "); if (index is PrimaryClusteredIndex) { return new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED({2})".FormatWith( index.Table.Name, index.IndexName, columns)); } if (!(index is UniqueIndex)) { return new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".FormatWith( index.IndexName, index.Table.Name, columns)); } var uIndex = (UniqueIndex)index; if (string.IsNullOrEmpty(uIndex.Where)) { return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".FormatWith( uIndex is UniqueIndex ? "UNIQUE " : null, uIndex.IndexName, uIndex.Table.Name, columns)); } if (uIndex.ViewName != null) { ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName); SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}" .FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where)) { GoBefore = true, GoAfter = true }; SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})" .FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", "))); return SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql); } else { return new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".FormatWith( uIndex.IndexName, uIndex.Table.Name, columns, uIndex.Where)); } }