//---------------------------------------------------------------------------------------------------- private static void PrimaryKey__SeeIfIdentityChanged(PrimaryKey primaryKey) { bool saveIsNeeded = false; var currentTableStructure = Tables__Get(primaryKey.TableId, true); Identity currentIdentity = null; foreach (var col in currentTableStructure.Columns) { if (col.Identity != null) { currentIdentity = col.Identity; } } Identity newIdentity = null; foreach (var col in primaryKey.Columns) { if (col.Identity != null) { newIdentity = col.Identity; } } if (currentIdentity == null && newIdentity == null) { saveIsNeeded = false; } else if (currentIdentity != null && newIdentity == null) { saveIsNeeded = true; } else if (currentIdentity == null && newIdentity != null) { saveIsNeeded = true; } else { if (currentIdentity.ColumnName.ToLower() != newIdentity.ColumnName.ToLower()) { saveIsNeeded = true; } else if (currentIdentity.Seed != newIdentity.Seed) { saveIsNeeded = true; } else if (currentIdentity.Increment != newIdentity.Increment) { saveIsNeeded = true; } } if (saveIsNeeded) { var newTableStructure = Tables__Get(primaryKey.TableId, true); foreach (var column in newTableStructure.Columns) { column.IsIdentity = false; column.Identity = null; if (newIdentity != null && column.ColumnName.ToLower() == newIdentity.ColumnName.ToLower()) { column.IsIdentity = true; column.Identity = newIdentity; } } string sql = SQLServer_SQLBuilders.BuildSql__Table_UpdateWithRecreate(newTableStructure, currentTableStructure); ASPdb.Framework.Debug.WriteLine(sql); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(currentTableStructure.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } } }
//---------------------------------------------------------------------------------------------------- public static TableDesignResponse Table__Update(TableStructure newTableStructure) { AjaxService.ASPdatabaseService.GetSetVal(); var rtn = new TableDesignResponse() { ResponseType = TableDesignResponse.ResponseTypesEnum.UpdateTable }; var currentTableStructure = Tables__Get(newTableStructure.TableId, true); var currentColumnsDict = new Dictionary <string, Column>(); foreach (var column in currentTableStructure.Columns) { if (!currentColumnsDict.ContainsKey(column.ColumnName.ToLower())) { currentColumnsDict.Add(column.ColumnName.ToLower(), column); } } var columnsList_All = new List <Column>(); var columnsList_Delete = new List <Column>(); var columnsList_Add = new List <Column>(); var columnsList_Update = new List <Column>(); bool reorder_Needed = false; int tmpOrdinalPos = 1; //var cols = (from c in newTableStructure.Columns orderby c.OrdinalPosition, c.ColumnName select c) //-------------------- foreach (var column in (from c in newTableStructure.Columns orderby c.OrdinalPosition, c.ColumnName select c)) { if (column.HasValidContent()) { column.Validate(Enums.ValidationTypes.NotSet); column.ConnectionId = currentTableStructure.ConnectionId; column.Schema = currentTableStructure.Schema; column.TableName = currentTableStructure.TableName; column.OrdinalPosition = tmpOrdinalPos++; column.Fix_DefaultValue_ForSaving(); columnsList_All.Add(column); switch (column.ChangeAction) { case Column.ChangeActionTypes.Delete: columnsList_Delete.Add(column); break; case Column.ChangeActionTypes.Update: if (Table__Update__IsColumnDifferent(column, currentColumnsDict)) { columnsList_Update.Add(column); } break; case Column.ChangeActionTypes.Add: columnsList_Add.Add(column); reorder_Needed = true; break; } } } newTableStructure.Columns = columnsList_All.ToArray(); //-------------------- foreach (var column in columnsList_Delete) { Column__Delete(column); } foreach (var column in columnsList_Update) { Column__Update(column); } foreach (var column in columnsList_Add) { Column__Create(column); } //-------------------- if (!reorder_Needed) { foreach (var column in newTableStructure.Columns) { if (currentColumnsDict.ContainsKey(column.ColumnName_Original.ToLower())) { var columnCurrent = currentColumnsDict[column.ColumnName_Original.ToLower()]; if (columnCurrent.OrdinalPosition != column.OrdinalPosition) { reorder_Needed = true; } } } } //-------------------- if (reorder_Needed) { string sql = SQLServer_SQLBuilders.BuildSql__Table_UpdateWithRecreate(newTableStructure, currentTableStructure); ASPdb.Framework.Debug.WriteLine("------------------------------"); ASPdb.Framework.Debug.WriteLine("SQL: " + sql); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(currentTableStructure.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } } Memory.AppCache.Reset(); var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(newTableStructure.TableId, false, true, true); return(rtn); }