public DatabaseColumnStructure[] GetDatabaseTableStructure(DatabaseContext dc, string tableName) { var primaryKeys = dc.ExecuteReader("SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects WHERE name = @table_name) AND colid in (SELECT SIK.colid FROM sysindexkeys SIK JOIN sysobjects SO ON SIK.id = so.id WHERE SIK.indid = 1 AND SO.name = @table_name);", tableName); var rows = dc.ExecuteStoredProcedure("sp_columns", "table_name", tableName); var result = new DatabaseColumnStructure[rows[0].Length]; for (var i = 0; i < rows[0].Length; i++) { var columnName = Convert.ToString(rows[0][i, 3]); var length = Convert.ToInt32(rows[0][i, 7]); var type = Convert.ToString(rows[0][i, 5]).Remove(" identity").ToUpper(); if (type == "NVARCHAR" || type == "NTEXT") { if (type == "NTEXT" && length == Int32.MaxValue - 1) { type = "NVARCHAR"; } length /= 2; if (length > 8000) { length = 8000; } } var identity = IsIdentity(primaryKeys, columnName); var nullable = Convert.ToInt32(rows[0][i, 10]) == 1; var defaultValue = rows[0][i, 12] == null ? null : Convert.ToString(rows[0][i, 12]).TrimStart('(').TrimEnd(')'); result[i] = new DatabaseColumnStructure(columnName, type, nullable, identity, 1, 1, defaultValue, length, identity); } return(result); }
public void ModifyColumnStructure(DatabaseContext dc, string tableName, DatabaseColumnStructure destinationDcs) { if (this == destinationDcs) { return; } // WARNING - This will recreate the original column //var def_value_str = DefaultValue.ConvertToString(); //var def_value = def_value_str.FirstChar() == '\'' && def_value_str.LastChar() == '\''; if (DefaultValue != destinationDcs.DefaultValue) //if (destination_dcs.DefaultValue != null) { var constraintNames = DatabaseUtils.GetDefaultConstraintNames(dc, tableName, ColumnName); foreach (var constraintName in constraintNames) { dc.ExecuteNonQuery($"ALTER TABLE {tableName} DROP CONSTRAINT {constraintName}"); } // Delete old column, create new with default values DeleteColumn(dc, tableName); CreateColumn(dc, tableName, destinationDcs.ToString()); } else // Modify column type { ModifyColumn(dc, tableName, destinationDcs); } }
public bool ModifyColumn(DatabaseContext dc, string tableName, DatabaseColumnStructure newColumnStructure) { if (ToString() != newColumnStructure.ToString()) { // FIXME - We want to add IDENTITY(1,1) property to column if (ToString().IndexOf("IDENTITY(1,1) ") == NotFound && newColumnStructure.ToString().IndexOf("IDENTITY(1,1)") > NotFound) { /*CREATE TABLE dbo.Tmp_Events (ID bigint NOT NULL IDENTITY (1, 1), language_element_id bigint NOT NULL, note nvarchar(200) NULL, checksum nvarchar(400) NULL) ON [PRIMARY] * ALTER TABLE dbo.Tmp_Events SET (LOCK_ESCALATION = TABLE) * SET IDENTITY_INSERT dbo.Tmp_Events ON * IF EXISTS(SELECT * FROM dbo.Events) * EXEC('INSERT INTO dbo.Tmp_Events (ID, language_element_id, note, checksum) * SELECT ID, language_element_id, note, checksum FROM dbo.Events WITH (HOLDLOCK TABLOCKX)') * SET IDENTITY_INSERT dbo.Tmp_Events OFF * DROP TABLE dbo.Events * EXECUTE sp_rename N'dbo.Tmp_Events', N'Events', 'OBJECT' * ALTER TABLE dbo.Events ADD CONSTRAINT PK__Events__3214EC2707020F21 PRIMARY KEY CLUSTERED (ID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/ return(false); //return dc.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT {0} ON"), table_name); //dc.ExecuteQuery(String.Format("ALTER TABLE {0} ALTER COLUMN {1};", table_name, new_column_structure.ToString())); } // FIXME - We want to remove IDENTITY(1,1) property from column if (ToString().IndexOf("IDENTITY(1,1) ") > NotFound && newColumnStructure.ToString().IndexOf("IDENTITY(1,1)") == NotFound) { /*CREATE TABLE dbo.Tmp_Events (ID bigint NOT NULL, language_element_id bigint NOT NULL, note nvarchar(200) NULL, checksum nvarchar(400) NULL) ON [PRIMARY] * ALTER TABLE dbo.Tmp_Events SET (LOCK_ESCALATION = TABLE) * IF EXISTS(SELECT * FROM dbo.Events) * EXEC('INSERT INTO dbo.Tmp_Events (ID, language_element_id, note, checksum) * SELECT ID, language_element_id, note, checksum FROM dbo.Events WITH (HOLDLOCK TABLOCKX)') * DROP TABLE dbo.Events * EXECUTE sp_rename N'dbo.Tmp_Events', N'Events', 'OBJECT' * ALTER TABLE dbo.Events ADD CONSTRAINT PK__Events__3214EC2707020F21 PRIMARY KEY CLUSTERED (ID) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]*/ // Create temporary column /*string column_specification = GetToString("_temporary_column_"); * * if (this.default_value != null) * { * if (this.type == "NVARCHAR") column_specification += String.Format(" DEFAULT '{0}'", this.default_value.ToString()); * else column_specification += String.Format(" DEFAULT {0}", this.default_value.ToString()); * } * * CreateColumn(dc, table_name, column_specification); * dc.ExecuteNonQuery(String.Format("UPDATE Events SET _temporary_column_ = {0};", this.column_name)); */ return(false); //return dc.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT {0} OFF", table_name)); } return(dc.ExecuteNonQuery($"ALTER TABLE {tableName} ALTER COLUMN {newColumnStructure};")); } return(false); }
public void ModifyTableStructure(DatabaseContext dc, DatabaseTableStructure destinationDts) { foreach (var destinationColumnStructure in destinationDts.Columns) { var dcs = this[destinationColumnStructure.ColumnName]; if (dcs == null) // This database column must be created { DatabaseColumnStructure.CreateColumn(dc, TableName, destinationColumnStructure.ToString()); } else // Check column structures { dcs.ModifyColumnStructure(dc, TableName, destinationColumnStructure); } } foreach (var columnStructure in Columns) { if (destinationDts[columnStructure.ColumnName] == null) // This database column does not exists anymore { columnStructure.DeleteColumn(dc, TableName); } } if (!NeedToRecreatePrimaryIds(destinationDts)) { return; } for (var i = 0; i < PrimaryIds.Count; i++) { dc.ExecuteNonQuery("DECLARE @CMD VARCHAR(MAX); SET @CMD = 'ALTER TABLE Options DROP CONSTRAINT ' + (SELECT constraint_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND TABLE_NAME = @C AND COLUMN_NAME = @C2); EXEC(@CMD);", TableName, PrimaryIds[i].ColumnName); } var primaryKeys = GetPrimaryIdsInStringFormat(destinationDts.PrimaryIds); var alterTable = $"ALTER TABLE {TableName} ADD PRIMARY KEY ({primaryKeys});"; dc.ExecuteNonQuery(alterTable); }
public static DatabaseTableStructure GetDatabaseTableStructure(string tableDescriptor) { if (String.IsNullOrEmpty(tableDescriptor)) { return(null); } //table_descriptor = table_descriptor.ToUpper(); var tableName = tableDescriptor.Substring("CREATE TABLE ", " ("); if (tableName.IndexOf('[') == 0 && tableName.IndexOf(']') == tableName.Length - 1) { tableName = tableName.Substring(1, tableName.Length - 2); } var columns = new List <DatabaseColumnStructure>(); var primaryKeys = tableDescriptor.Substring("PRIMARY KEY(", ")").Split(','); for (var i = 0; i < primaryKeys.Length; i++) { primaryKeys[i] = primaryKeys[i].Trim(); } var index = tableDescriptor.IndexOf('(') + 1; var end = false; while (!end) { var index2 = tableDescriptor.IndexOf(", ", index + 1); if (index2 == NotFound) { end = true; index2 = tableDescriptor.IndexOf(" PRIMARY KEY", index + 1); } if (index2 != NotFound) { var columnDescriptor = tableDescriptor.Substring(index, index2 - index); if (columnDescriptor.IndexOf(", ") == 0) { columnDescriptor = columnDescriptor.Substring(2); } var cs = DatabaseColumnStructure.GetDatabaseColumnStructure(columnDescriptor, primaryKeys); columns.Add(cs); } index = index2; } //var columnsArray = (DatabaseColumnStructure[])columns.ToArray(typeof(DatabaseColumnStructure)); var id = DatabaseColumnStructure.GetPrimaryId(columns); if (!id.Any()) { var ids = tableDescriptor.Substring("PRIMARY KEY(", ")").Split(','); for (var i = 0; i < ids.Length; i++) { for (var j = 0; j < columns.Count; j++) { if (!String.Equals(columns[j].ColumnName, ids[i].Trim(), StringComparison.CurrentCultureIgnoreCase)) { continue; } id.Add(columns[j]); // TODO: Why has only one key? break; } } } return(new DatabaseTableStructure(tableName, columns, id)); }
public IEnumerable <DatabaseColumnStructure> GetPrimaryId() { return(DatabaseColumnStructure.GetPrimaryId(Columns)); }