//---------------------------------------------------------------------------------------------------- /// <summary> /// Key in return Dictionary is lowercase index name. /// Does not include sub columns infos. /// </summary> private static Dictionary <string, Index> Index__GetAll_InConnection(int connectionId) { var rtn = new Dictionary <string, Index>(); string sql = SQLServer_SQLBuilders.BuildSql__Index_SelectAll(); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { var index = new Index() { ConnectionId = connectionId, Schema = reader.Get("Schema", ""), TableName = reader.Get("TableName", ""), IndexName = reader.Get("IndexName", ""), Columns = null, IsUnique = reader.Get("IsUnique", false) }; string key_IndexName = index.IndexName.ToLower(); if (!rtn.ContainsKey(key_IndexName)) { rtn.Add(key_IndexName, index); } } } } return(rtn); }
//---------------------------------------------------------------------------------------------------- public static TableDesignResponse Table__Create(TableStructure tableStructure) { AjaxService.ASPdatabaseService.GetSetVal(); var rtn = new TableDesignResponse() { ResponseType = TableDesignResponse.ResponseTypesEnum.CreateTable, ConnectionId = tableStructure.ConnectionId, Schema = tableStructure.Schema, TableName = tableStructure.TableName }; tableStructure.Columns = (from c in tableStructure.Columns where c.ColumnName.Length > 0 && c.DataType.Length > 0 orderby c.OrdinalPosition select c).ToArray(); var primaryKey = new PrimaryKey() { ConnectionId = tableStructure.ConnectionId, TableId = tableStructure.TableId, Schema = tableStructure.Schema, TableName = tableStructure.TableName }; var tmp_PrimaryKeyColumns = new List <PrimaryKeyColumn>(); int i = 1; foreach (var column in tableStructure.Columns) { if (column.IsPrimaryKey) { tmp_PrimaryKeyColumns.Add(new PrimaryKeyColumn() { ColumnName = column.ColumnName, OrdinalPosition = i++ }); } column.Fix_DefaultValue_ForSaving(); } primaryKey.Columns = tmp_PrimaryKeyColumns.ToArray(); string sql = SQLServer_SQLBuilders.BuildSql__Table_Create(tableStructure); ASPdb.Framework.Debug.WriteLine(sql); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } PrimaryKey__Create(primaryKey, false); rtn.TableId = -1; Memory.AppCache.Reset(); var allTables = DataAccess.SQLObjectsCRUD.ASPdb_Table__GetAll(tableStructure.ConnectionId, true); foreach (var table in allTables) { if (table.Schema.ToLower() == rtn.Schema.ToLower() && table.TableName.ToLower() == rtn.TableName.ToLower()) { rtn.TableId = table.TableId; } } return(rtn); }
//---------------------------------------------------------------------------------------------------- public static PrimaryKey PrimaryKey__Get(TableStructure tableStructure) { AjaxService.ASPdatabaseService.GetSetVal(); PrimaryKey rtn = null; var colDict = new Dictionary <string, Column>(); foreach (var item in tableStructure.Columns) { if (!colDict.ContainsKey(item.ColumnName.ToLower())) { colDict.Add(item.ColumnName.ToLower(), item); } } var primaryKeyColumns_List = new List <PrimaryKeyColumn>(); string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Select(tableStructure); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { if (rtn == null) { rtn = new PrimaryKey() { ConnectionId = tableStructure.ConnectionId, TableId = tableStructure.TableId, Schema = reader.Get("Schema", ""), TableName = reader.Get("TableName", ""), ConstraintName = reader.Get("ConstraintName", "") } } ; var primaryKeyItem = new PrimaryKeyColumn() { ColumnName = reader.Get("ColumnName", ""), OrdinalPosition = reader.Get("OrdinalPosition", 0) }; if (colDict.ContainsKey(primaryKeyItem.ColumnName.ToLower())) { primaryKeyItem.Identity = colDict[primaryKeyItem.ColumnName.ToLower()].Identity; } primaryKeyColumns_List.Add(primaryKeyItem); } } } if (rtn != null) { rtn.Columns = primaryKeyColumns_List.ToArray(); } return(rtn); }
//---------------------------------------------------------------------------------------------------- public static Index[] Index__Get(TableStructure tableStructure) { AjaxService.ASPdatabaseService.GetSetVal(); var rtn = new Dictionary <string, Index>(); // key : indexName_L string sql = SQLServer_SQLBuilders.BuildSql__Index_Select(tableStructure); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { string schema = reader.Get("Schema", ""); string tableName = reader.Get("TableName", ""); string indexName = reader.Get("IndexName", ""); string indexName_L = indexName.ToLower(); bool isUnique = reader.Get("IsUnique", false); int columnId = reader.Get("ColumnId", 0); string columnName = reader.Get("ColumnName", ""); bool isDescending = reader.Get("IsDescending", false); Index index; if (rtn.ContainsKey(indexName_L)) { index = rtn[indexName_L]; } else { index = new Index(); rtn.Add(indexName_L, index); index.ConnectionId = tableStructure.ConnectionId; index.Schema = schema; index.TableName = tableName; index.IndexName = indexName; index.IsUnique = isUnique; index.Columns = new IndexColumn[0]; } var indexColumnsList = index.Columns.ToList(); var indexColumn = new IndexColumn() { ColumnId = columnId, ColumnName = columnName, SortDirection = IndexColumn.E_SortTypes.Ascending }; if (isDescending) { indexColumn.SortDirection = IndexColumn.E_SortTypes.Descending; } indexColumnsList.Add(indexColumn); index.Columns = indexColumnsList.ToArray(); } } } return(rtn.Values.ToArray()); }
//---------------------------------------------------------------------------------------------------- public static void Column__Create(Column column) { AjaxService.ASPdatabaseService.GetSetVal(); string sql = SQLServer_SQLBuilders.BuildSql__Column_Create(column); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } }
//---------------------------------------------------------------------------------------------------- public static void Index__Delete(Index index) { AjaxService.ASPdatabaseService.GetSetVal(); string sql = SQLServer_SQLBuilders.BuildSql__Index_Drop(index); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(index.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } }
//---------------------------------------------------------------------------------------------------- public static void ForeignKey__Delete(ForeignKey foreignKey) { AjaxService.ASPdatabaseService.GetSetVal(); string sql = SQLServer_SQLBuilders.BuildSql__ForeignKey_Drop(foreignKey); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(foreignKey.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } }
//---------------------------------------------------------------------------------------------------- public static void Index__Create(Index index) { AjaxService.ASPdatabaseService.GetSetVal(); var allIndexes = Index__GetAll_InConnection(index.ConnectionId); string sql = SQLServer_SQLBuilders.BuildSql__Index_Create(index, true, allIndexes); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(index.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } }
//---------------------------------------------------------------------------------------------------- public static void PrimaryKey__Delete(PrimaryKey primaryKey) { AjaxService.ASPdatabaseService.GetSetVal(); string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Drop(primaryKey); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(primaryKey.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(primaryKey.TableId, false, true, true); }
//---------------------------------------------------------------------------------------------------- public static void ForeignKey__Create(ForeignKey foreignKey) { AjaxService.ASPdatabaseService.GetSetVal(); var allConstraints = Constraint__GetAll_InConnection(foreignKey.ConnectionId); string sql = SQLServer_SQLBuilders.BuildSql__ForeignKey_Create(foreignKey, true, allConstraints); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(foreignKey.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } }
//---------------------------------------------------------------------------------------------------- public static void PrimaryKey__Update(PrimaryKey primaryKey) { AjaxService.ASPdatabaseService.GetSetVal(); string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Update(primaryKey); ASPdb.Framework.Debug.WriteLine(sql); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(primaryKey.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } PrimaryKey__SeeIfIdentityChanged(primaryKey); var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(primaryKey.TableId, false, true, true); }
//---------------------------------------------------------------------------------------------------- private static Column[] Tables__Get__GetColumns(TableStructure tableStructure) { var rtn = new List <Column>(); string sql = SQLServer_SQLBuilders.BuildSql__Column_SelectAll(tableStructure); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { var column = new Column() { ConnectionId = tableStructure.ConnectionId, Schema = reader.Get("Schema", ""), TableName = reader.Get("TableName", ""), OrdinalPosition = reader.Get("OrdinalPosition", 0), ColumnName = reader.Get("ColumnName", ""), DataType_Name = reader.Get("DataType", ""), MaxLength = reader.Get("MaxLength", 0), Precision = reader.Get("Precision", 0), Scale = reader.Get("Scale", 0), AllowNulls = reader.Get("AllowNulls", "NO").ToUpper() == "YES", DefaultValue = reader.GetString_OrNullDefault("DefaultValue"), IsPrimaryKey = reader.Get("IsPrimaryKey", 0) == 1, IsIdentity = reader.Get("IsIdentity", 0) == 1, Identity = null }; if (column.IsIdentity) { column.Identity = new Identity() { ConnectionId = tableStructure.ConnectionId, Schema = reader.Get("Schema", ""), TableName = reader.Get("TableName", ""), ColumnName = reader.Get("ColumnName", ""), Seed = reader.Get("Identity_Seed", 0), Increment = reader.Get("Identity_Increment", 0), CurrentIdentity = reader.Get("Identity_CurrentIdentity", 0) } } ; column.Populate__DataType_FullString(); column.Fix_DefaultValue_ForDisplay(); rtn.Add(column); } } } return(rtn.ToArray()); }
//---------------------------------------------------------------------------------------------------- public static void PrimaryKey__Create(PrimaryKey primaryKey, bool seeIfIdentityChanged) { AjaxService.ASPdatabaseService.GetSetVal(); var allConstraints = Constraint__GetAll_InConnection(primaryKey.ConnectionId); string sql = SQLServer_SQLBuilders.BuildSql__PrimaryKey_Create(primaryKey, true, allConstraints, true); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(primaryKey.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } if (seeIfIdentityChanged) { PrimaryKey__SeeIfIdentityChanged(primaryKey); } var tableStructure_ResetCache = DbInterfaces.SQLServerInterface.Tables__Get(primaryKey.TableId, false, true, true); }
//---------------------------------------------------------------------------------------------------- public static TableDesignResponse Table__Delete(TableStructure tableStructure) { AjaxService.ASPdatabaseService.GetSetVal(); var rtn = new TableDesignResponse() { ResponseType = TableDesignResponse.ResponseTypesEnum.DeleteTable }; string sql = SQLServer_SQLBuilders.BuildSql__Table_Drop(tableStructure); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } Memory.AppCache.Reset(); return(rtn); }
//---------------------------------------------------------------------------------------------------- public static void Column__Delete(Column column) { AjaxService.ASPdatabaseService.GetSetVal(); string defaultConstraintName = null; var defaultConstraint = DefaultConstraint__Get(column); if (defaultConstraint != null) { defaultConstraintName = defaultConstraint.DefaultConstraintName; } string sql = SQLServer_SQLBuilders.BuildSql__Column_Drop(column, defaultConstraintName); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql)) { command.Command.ExecuteNonQuery(); } }
//---------------------------------------------------------------------------------------------------- public static DefaultConstraint DefaultConstraint__Get(Column column) { AjaxService.ASPdatabaseService.GetSetVal(); var dict = new Dictionary <string, DefaultConstraint>(); string sql = SQLServer_SQLBuilders.BuildSql__DefaultConstraint_Select(column); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(column.ConnectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { string columnName = reader.Get("ColumnName", ""); string columnName_L = columnName.ToLower(); var defaultConstraint = new DefaultConstraint() { ConnectionId = column.ConnectionId, Schema = column.Schema, TableName = column.TableName, ColumnName = reader.Get("ColumnName", ""), DefaultConstraintName = reader.Get("DefaultConstraintName", ""), DefaultDefinition = reader.Get("DefaultDefinition", "") }; if (!dict.ContainsKey(columnName_L)) { dict.Add(columnName_L, defaultConstraint); } } } } if (dict.ContainsKey(column.ColumnName.ToLower())) { return(dict[column.ColumnName.ToLower()]); } else { return(null); } }
//---------------------------------------------------------------------------------------------------- /// <summary> /// Key == lowercase ConstraintName. /// Value == ConstraintName. /// </summary> public static Dictionary <string, string> Constraint__GetAll_InConnection(int connectionId) { AjaxService.ASPdatabaseService.GetSetVal(); var rtn = new Dictionary <string, string>(); string sql = SQLServer_SQLBuilders.BuildSql__Constraint_SelectAll(); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(connectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { string constraintName = reader.Get("ConstraintName", ""); string constraintName_L = constraintName.ToLower(); if (!rtn.ContainsKey(constraintName_L)) { rtn.Add(constraintName_L, constraintName); } } } } return(rtn); }
//---------------------------------------------------------------------------------------------------- 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); }
//---------------------------------------------------------------------------------------------------- public static void ForeignKey__Get(TableStructure tableStructure, out ForeignKey[] fkSide_ForeignKeys, out ForeignKey[] pkSide_ForeignKeys) { AjaxService.ASPdatabaseService.GetSetVal(); string sql = SQLServer_SQLBuilders.BuildSql__ForeignKey_Select(tableStructure); var fkSide_ForeignKeysDict = new Dictionary <string, ForeignKey>(); var pkSide_ForeignKeysDict = new Dictionary <string, ForeignKey>(); using (DbConnectionCommand command = UniversalADO.OpenConnectionCommand(tableStructure.ConnectionId, sql)) { using (DbReaderWrapper reader = command.ExecuteReaderWrapper()) { while (reader.Read()) { string relationshipSide = reader.Get("RelationshipSide", "").ToUpper(); string constraintName = reader.Get("ConstraintName", ""); string constraintName_L = constraintName.ToLower(); int ordinalPosition = reader.Get("OrdinalPosition", 0); string fk_Schema = reader.Get("FK_Schema", ""); string fk_Table = reader.Get("FK_Table", ""); string fk_Column = reader.Get("FK_Column", ""); string pk_Schema = reader.Get("PK_Schema", ""); string pk_Table = reader.Get("PK_Table", ""); string pk_Column = reader.Get("PK_Column", ""); string updateRule = reader.Get("UpdateRule", ""); string deleteRule = reader.Get("DeleteRule", ""); var dict = fkSide_ForeignKeysDict; var eRelationshipSide = ForeignKey.E_RelationshipSides.ForeignKeySide; if (relationshipSide == "PK") { dict = pkSide_ForeignKeysDict; eRelationshipSide = ForeignKey.E_RelationshipSides.PrimaryKeySide; } ForeignKey foreignKey; if (dict.ContainsKey(constraintName_L)) { foreignKey = dict[constraintName_L]; } else { dict.Add(constraintName_L, new ForeignKey()); foreignKey = dict[constraintName_L]; foreignKey.ConnectionId = tableStructure.ConnectionId; foreignKey.ConstraintName = constraintName; foreignKey.RelationshipSide = eRelationshipSide; foreignKey.ForeignKey_Schema = fk_Schema; foreignKey.ForeignKey_TableName = fk_Table; foreignKey.PrimaryKey_Schema = pk_Schema; foreignKey.PrimaryKey_TableName = pk_Table; foreignKey.DeleteRule = ForeignKey__Get__GetCascadeOption(deleteRule); foreignKey.UpdateRule = ForeignKey__Get__GetCascadeOption(updateRule); foreignKey.Columns = new ForeignKeyColumn[0]; } var columnsList = foreignKey.Columns.ToList(); var foreignKeyColumn = new ForeignKeyColumn() { ForeignKey_ColumnName = fk_Column, PrimaryKey_ColumnName = pk_Column, OrdinalPosition = ordinalPosition }; columnsList.Add(foreignKeyColumn); foreignKey.Columns = columnsList.ToArray(); } } } fkSide_ForeignKeys = fkSide_ForeignKeysDict.Values.ToArray(); pkSide_ForeignKeys = pkSide_ForeignKeysDict.Values.ToArray(); }
//---------------------------------------------------------------------------------------------------- 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(); } } }