public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType) { try { if (destinationType == typeof(string)) { var column = (ColumnBase)context.Instance; if (column is Column) { _column = (Column)column; if (_column.ComputedColumn) return "computed"; } return column.DataType.ToString(); } else if (destinationType == typeof(System.Data.SqlDbType)) { return null; } } catch (Exception ex) { } return null; }
public static string GetSqlAddColumn(Column column, bool useComment) { if (column.ParentTable.TypedTable == TypedTableConstants.EnumOnly) return string.Empty; var sb = new StringBuilder(); var tName = column.ParentTable.DatabaseName; if (useComment) sb.AppendLine("--ADD COLUMN [" + tName + "].[" + column.DatabaseName + "]"); sb.AppendLine("if exists(select * from sys.objects where name = '" + tName + "' and type = 'U') AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + column.DatabaseName + "' and o.name = '" + tName + "')"); sb.AppendLine("ALTER TABLE [" + column.ParentTable.GetSQLSchema() + "].[" + tName + "] ADD " + AppendColumnDefinition(column, allowDefault: true, allowIdentity: true)); //if (!column.AllowNull) //{ // sb.AppendLine(); // sb.AppendLine("--THIS IS A NON-NULLABLE FIELD. AT THIS POINT IT IS NULLABLE. ADD DATA TO THIS FIELD BEFORE IT IS SET TO NON-NULLABLE."); // sb.AppendLine("ALTER TABLE [" + column.ParentTable.GetSQLSchema() + "].[" + tName + "] ADD " + AppendColumnDefinition(column, allowDefault: true, allowIdentity: true)); // sb.AppendLine(); //} return sb.ToString(); }
private void AddColumnNode(Reference reference, Column column) { var newItem = new ListViewItem(column.Name); var inRelation = false; var parentRelations = ((ModelRoot)this.Object.Root).Database.Relations.FindByChildTable((Table)column.ParentTableRef.Object); foreach (var relation in parentRelations) { foreach (ColumnRelationship columnRelationship in relation.ColumnRelationships) { if (columnRelationship.ChildColumnRef.Object == column) inRelation = true; } } if (column.PrimaryKey) newItem.ImageIndex = ImageHelper.GetImageIndex(TreeIconConstants.ColumnPrimaryKey); else if (inRelation) newItem.ImageIndex = ImageHelper.GetImageIndex(TreeIconConstants.ColumnForeignKey); else newItem.ImageIndex = ImageHelper.GetImageIndex(TreeIconConstants.Column); //There is not a reference for inherited columns if (reference != null) { newItem.Tag = reference; newItem.Name = reference.Key; } else { newItem.ImageIndex = ImageHelper.GetImageIndex(TreeIconConstants.ColumnInherit); newItem.ForeColor = SystemColors.GrayText; } newItem.SubItems.Add(column.CodeFacade); newItem.SubItems.Add(column.PrimaryKey.ToString()); newItem.SubItems.Add(column.AllowNull.ToString()); newItem.SubItems.Add(column.DataType.ToString().ToLower()); newItem.SubItems.Add(column.Length.ToString()); ControllerListView.Items.Add(newItem); }
public Table GetRelatedTypeTableByColumn(Column column, out string roleName) { return GetRelatedTypeTableByColumn(column, false, out roleName); }
public bool IsColumnInherited(Column column) { var allInheritedColumns = this.GetColumnsFullHierarchy(false); var q = from x in allInheritedColumns where x.PascalName == column.PascalName select x; return q.FirstOrDefault() != null; }
private void AppendPropertyEventDeclarations(Column column, string codeType) { //Table typetable = _item.GetRelatedTypeTableByColumn(column); //if (typetable != null) //{ // this.AppendPropertyEventDeclarations(typetable.PascalName, codeType); //} //else { this.AppendPropertyEventDeclarations(column.PascalName, codeType); } }
public static string GetSqlRenameColumn(Column oldColumn, Column newColumn) { return GetSqlRenameColumn(newColumn.ParentTable.DatabaseName, oldColumn.DatabaseName, newColumn.DatabaseName); }
public static string GetSqlDropColumnDefault(Column column, bool upgradeScript = false) { var sb = new StringBuilder(); if (upgradeScript) sb.AppendLine("DECLARE @defaultName varchar(max)"); //sb.AppendLine("SET @defaultName = (select top 1 o.name FROM sysconstraints sc left join sys.objects o on sc.constid = o.object_id where sc.id=OBJECT_ID('" + column.ParentTable.DatabaseName + "') AND COL_NAME(sc.id,sc.colid)='" + column.DatabaseName + "' AND OBJECTPROPERTY(sc.constid, 'IsDefaultCnst') = 1)"); sb.AppendLine("SET @defaultName = (SELECT d.name FROM sys.columns c inner join sys.default_constraints d on c.column_id = d.parent_column_id and c.object_id = d.parent_object_id inner join sys.objects o on d.parent_object_id = o.object_id where o.name = '" + column.ParentTable.DatabaseName + "' and c.name = '" + column.DatabaseName + "')"); sb.AppendLine("if @defaultName IS NOT NULL"); sb.AppendLine("exec('ALTER TABLE ["+column.ParentTable.DatabaseName+"] DROP CONSTRAINT ' + @defaultName)"); if (upgradeScript) sb.AppendLine("GO"); return sb.ToString(); }
public static string AppendColumnDefaultRemoveSql(Column column) { if (column.ParentTable.TypedTable == TypedTableConstants.EnumOnly) return string.Empty; var sb = new StringBuilder(); var table = column.ParentTable; var variableName = "@" + column.ParentTable.PascalName + "_" + column.PascalName; sb.AppendLine("--NOTE: IF YOU HAVE AN NON-MANAGED DEFAULT, UNCOMMENT THIS CODE TO REMOVE IT"); sb.AppendLine("--DROP CONSTRAINT FOR '[" + table.DatabaseName + "].[" + column.DatabaseName + "]' if one exists"); sb.AppendLine("--declare " + variableName + " varchar(500)"); sb.AppendLine("--set " + variableName + " = (select top 1 c.name from sys.all_columns a inner join sys.tables b on a.object_id = b.object_id inner join sys.default_constraints c on a.default_object_id = c.object_id where b.name='" + table.DatabaseName + "' and a.name = '" + column.DatabaseName + "')"); sb.AppendLine("--if (" + variableName + " IS NOT NULL) exec ('ALTER TABLE [" + table.DatabaseName + "] DROP CONSTRAINT [' + " + variableName + " + ']')"); return sb.ToString(); }
public static string AppendColumnDefaultCreateSQL(Column column, bool includeDrop = true) { if (column.ParentTable.TypedTable == TypedTableConstants.EnumOnly) return string.Empty; var sb = new StringBuilder(); var table = column.ParentTable; var defaultName = "DF__" + table.DatabaseName + "_" + column.DatabaseName; defaultName = defaultName.ToUpper(); var defaultClause = GetDefaultValueClause(column); if (!string.IsNullOrEmpty(column.Default)) { //We know a default was specified so render the SQL defaultName = defaultName.ToUpper(); if (!string.IsNullOrEmpty(defaultClause)) { if (includeDrop) { sb.AppendLine("--ADD/DROP CONSTRAINT FOR '[" + table.DatabaseName + "].[" + column.DatabaseName + "]'"); sb.AppendLine("if exists(select * from sys.objects where name = '" + defaultName + "' and type = 'D' and type_desc = 'DEFAULT_CONSTRAINT')"); sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] DROP CONSTRAINT [" + GetDefaultValueConstraintName(column) + "]"); sb.AppendLine(); } sb.AppendLine("if not exists(select * from sys.objects where name = '" + defaultName + "' and type = 'D' and type_desc = 'DEFAULT_CONSTRAINT')"); sb.AppendLine("ALTER TABLE [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ADD " + defaultClause + " FOR [" + column.DatabaseName + "]"); sb.AppendLine(); } } return sb.ToString(); }
private static void SetupDefault(Column field, string defaultvalue) { if (defaultvalue == null) defaultvalue = string.Empty; //This is some sort of default pointer, we do not handle this. if (defaultvalue.Contains("create default [")) defaultvalue = string.Empty; //Just in case some put 'null' in to the default field if (field.AllowNull && defaultvalue.ToLower() == "null") defaultvalue = string.Empty; if (field.IsNumericType || field.DataType == SqlDbType.Bit || field.IsDateType || field.IsBinaryType) { field.Default = defaultvalue.Replace("(", string.Empty).Replace(")", string.Empty); //remove any parens } else if (field.DataType == SqlDbType.UniqueIdentifier) { if (!string.IsNullOrEmpty(defaultvalue) && defaultvalue.Contains("newid")) field.Default = "newid"; if (!string.IsNullOrEmpty(defaultvalue) && defaultvalue.Contains("newsequentialid")) field.Default = "newsequentialid"; else field.Default = defaultvalue.Replace("(", string.Empty).Replace(")", string.Empty).Replace("'", string.Empty); //Format: ('000...0000') } else if (field.IsTextType) { while (defaultvalue.StartsWith("('")) defaultvalue = defaultvalue.Substring(2, defaultvalue.Length - 2); while (defaultvalue.EndsWith("')")) defaultvalue = defaultvalue.Substring(0, defaultvalue.Length - 2); field.Default = defaultvalue; } else field.Default = defaultvalue; }
public static IEnumerable<Column> GetTableDefinitionFromDatabase(string connectionString, string tableName, ModelRoot root) { try { var retval = new List<Column>(); //Columns var connection = DatabaseHelper.GetConnection(connectionString); var columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlColumnsForTable(tableName)); while (columnReader.Read()) { var columnName = columnReader["columnName"].ToString(); if (StringHelper.Match(columnName, root.Database.CreatedByColumnName) || StringHelper.Match(columnName, root.Database.CreatedDateColumnName)) { //Do Nothing } else if (StringHelper.Match(columnName, root.Database.ModifiedByColumnName) || StringHelper.Match(columnName, root.Database.ModifiedDateColumnName) || StringHelper.Match(columnName, "updated_by") || StringHelper.Match(columnName, "updated_date")) { //Do Nothing } else if (StringHelper.Match(columnName, root.Database.TimestampColumnName)) { //Do Nothing } else { var currentColumn = new Column(root); currentColumn.Name = columnName; if (!ValidationHelper.ValidDatabaseIdenitifer(currentColumn.Name)) { currentColumn.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentColumn.Name); } //currentColumn.ParentTableRef = currentTable.CreateRef(); currentColumn.AllowNull = bool.Parse(columnReader["allowNull"].ToString()); if (bool.Parse(columnReader["isIdentity"].ToString())) currentColumn.Identity = IdentityTypeConstants.Database; else currentColumn.Identity = IdentityTypeConstants.None; if (columnReader["isPrimaryKey"] != System.DBNull.Value) currentColumn.PrimaryKey = true; //currentColumn.PrimaryKey = bool.Parse(columnReader["isPrimaryKey"].ToString()); try { //string columnTypename = columnReader["datatype"].ToString(); //if (StringHelper.Match(columnTypename, "numeric", true)) // currentColumn.DataType = SqlDbType.Decimal; //else // currentColumn.DataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), columnTypename, true); currentColumn.DataType = DatabaseHelper.GetSQLDataType((SqlNativeTypes)int.Parse(columnReader["xtype"].ToString())); } catch { } var defaultvalue = columnReader["defaultValue"].ToString(); SetupDefault(currentColumn, defaultvalue); currentColumn.Length = (int)columnReader["length"]; if (CanUseExtendedProperty(connectionString)) { var columnAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connection, CommandType.Text, "SELECT name, value FROM ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + tableName + "', 'column', '" + currentColumn + "')"); while (columnAttributeReader.Read()) { var attribName = string.Empty; attribName = columnAttributeReader["name"].ToString(); if (attribName.ToLower().Equals("ms_description")) { currentColumn.Description = columnAttributeReader["value"].ToString(); } } columnAttributeReader.Close(); } retval.Add(currentColumn); } //Create New Column } columnReader.Close(); return retval; } catch (Exception ex) { throw; } }
private void AddColumnNode(Reference reference, Column column) { var tc = new ColumnController(column); var isBaseTablePK = false; if (reference != null) { tc.Node.Tag = reference; tc.Node.Name = reference.Key; } else { isBaseTablePK = column.PrimaryKey; tc.Node.Name = column.Key; tc.Node.ForeColor = SystemColors.GrayText; } tc.Node.Refresh(); tc.Node.Text = column.Name; if ((this.Nodes.Find(tc.Node.Name, false).Length == 0) && !isBaseTablePK) { this.Nodes.Add(tc.Node); } }
private void AddColumnNode(Column column) { this.AddColumnNode(null, column); }
private string GetPropertyNode(Column column) { var propertyString = new StringBuilder(); var parentType = column.ParentTableRef.Object as Table; var roleName = string.Empty; var typeTable = parentType.GetRelatedTypeTableByColumn(column, false, out roleName); //if (typeTable != null) //{ // //This is a related type table field so handle it differently // propertyString.AppendFormat(" <Property Name=\"{0}\" Type=\"{1}\" Nullable=\"false\" ", typeTable.PascalName, this.GetLocalNamespace() + ".Entity." + typeTable.PascalName + "Wrapper"); //} //else { //Attributes Name and Type propertyString.AppendFormat(" <Property Name=\"{0}\" Type=\"{1}\" ", column.PascalName, column.EFSqlCodeType()); //Attribute Nullable propertyString.Append("Nullable=\"" + (column.AllowNull ? "true" : "false") + "\" "); } if (typeTable == null) { //Attribute MaxLength if (!string.IsNullOrEmpty(column.EFGetCodeMaxLengthString())) { propertyString.AppendFormat("MaxLength=\"{0}\" ", column.EFGetCodeMaxLengthString()); } //Attribute Precision Scale if (column.EFSupportsPrecision()) { propertyString.AppendFormat("Precision=\"{0}\" Scale=\"{1}\" ", column.Length.ToString(), column.Scale); } //Attribute Unicode if (column.EFUnicode().HasValue) { var unicodeString = column.EFUnicode().Value ? "true" : "false"; propertyString.AppendFormat("Unicode=\"{0}\" ", unicodeString); } //Attribute FixedLength if (column.EFIsFixedLength().HasValue) { var isFixedLengthString = column.EFIsFixedLength().Value ? "true" : "false"; propertyString.AppendFormat("FixedLength=\"{0}\" ", isFixedLengthString); } } //Primary Key if (column.PrimaryKey) { //propertyString.Append("xmlns:a=\"http://schemas.microsoft.com/ado/2006/04/codegeneration\" "); if (column.IsIntegerType && column.Identity == IdentityTypeConstants.Database) { //propertyString.Append("a:SetterAccess=\"Private\" DefaultValue=\"-1\" annotation:StoreGeneratedPattern=\"Identity\" "); propertyString.Append("DefaultValue=\"-1\" annotation:StoreGeneratedPattern=\"Identity\" "); } else { //propertyString.Append("a:SetterAccess=\"Public\" "); } } propertyString.Append("/>").AppendLine(); return propertyString.ToString(); }
public static string CreateIndexName(Table table, Column column) { var indexName = "IDX_" + table.DatabaseName.ToUpper() + "_" + column.DatabaseName.ToUpper(); var sb = new StringBuilder(); foreach (var c in indexName) { if (ValidationHelper.ValidCodeChars.Contains(c)) sb.Append(c); else sb.Append("_"); } return sb.ToString(); }
public static string GetSqlCreateColumnDefault(ModelRoot model, Column column) { var sb = new StringBuilder(); if (!string.IsNullOrEmpty(column.GetSQLDefault())) { var defaultName = "DF__" + column.ParentTable.DatabaseName + "_" + column.DatabaseName; defaultName = defaultName.ToUpper(); sb.AppendLine("--DROP CONSTRAINT FOR '[" + column.ParentTable.DatabaseName + "].[" + column.DatabaseName + "]'"); sb.AppendLine("if exists (select * from sys.objects where name ='" + column.ParentTable.DatabaseName + "' and type ='U') and not exists(select constid FROM sysconstraints where id=OBJECT_ID('" + column.ParentTable.DatabaseName + "') AND COL_NAME(id,colid)='" + column.DatabaseName + "' AND OBJECTPROPERTY(constid, 'IsDefaultCnst') = 1)"); sb.AppendLine("ALTER TABLE [" + column.ParentTable.DatabaseName + "] ADD CONSTRAINT [" + defaultName + "] DEFAULT " + column.GetSQLDefault() + " FOR [" + column.DatabaseName + "]"); } return sb.ToString(); }
private static string AppendColumnDefinition(Column column, bool allowDefault, bool allowIdentity, bool forceNull, bool allowFormula) { return AppendColumnDefinition(column, allowDefault: allowDefault, allowIdentity: allowIdentity, forceNull: forceNull, allowFormula: true, allowComputed: true); }
public static string GetSqlDropColumn(ModelRoot model, Column column) { if (column.ParentTable.TypedTable == TypedTableConstants.EnumOnly) return string.Empty; var sb = new StringBuilder(); var t = column.ParentTable; #region Delete Defaults sb.AppendLine("--DELETE DEFAULT"); sb.Append("select 'ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] DROP CONSTRAINT ' + [name] as 'sql' "); sb.Append("into #t "); sb.Append("from sysobjects "); sb.Append("where id IN( "); sb.Append("select SC.cdefault "); sb.Append("FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id "); sb.Append("LEFT JOIN sys.default_constraints SM ON SC.cdefault = SM.parent_column_id "); sb.AppendLine("WHERE SO.xtype = 'U' and SO.NAME = '" + t.DatabaseName + "' and SC.NAME = '" + column.DatabaseName + "')"); sb.AppendLine("declare @sql [nvarchar] (1000)"); sb.AppendLine("SELECT @sql = MAX([sql]) from #t"); sb.AppendLine("exec (@sql)"); sb.AppendLine("drop table #t"); sb.AppendLine(); #endregion #region Delete Parent Relations for (var ii = t.ParentRoleRelations.Count - 1; ii >= 0; ii--) { var parentR = t.ParentRoleRelations[ii] as Relation; var parentT = parentR.ParentTable; var childT = parentR.ChildTable; if (parentR.ParentTableRef.Object == t) { var removeRelationship = false; foreach (var cr in parentR.ColumnRelationships.AsEnumerable()) { if (cr.ParentColumnRef.Object == column) removeRelationship = true; } if (removeRelationship) { var objectName = "FK_" + parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, childT) + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, parentT); objectName = objectName.ToUpper(); sb.AppendLine("--REMOVE FOREIGN KEY"); sb.AppendLine("if exists(select * from sys.objects where name = '" + objectName + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')"); sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]"); sb.AppendLine(); } } } #endregion #region Delete Child Relations for (var ii = t.ChildRoleRelations.Count - 1; ii >= 0; ii--) { var childR = t.ChildRoleRelations[ii] as Relation; var parentT = childR.ParentTable; var childT = childR.ChildTable; for (var jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--) { var parentR = parentT.ParentRoleRelations[jj] as Relation; if (parentR.ChildTableRef.Object == t) { var removeRelationship = false; foreach (var cr in childR.ColumnRelationships.AsEnumerable()) { if ((cr.ChildColumnRef.Object == column) || (cr.ParentColumnRef.Object == column)) removeRelationship = true; } if (removeRelationship) { var objectName = "FK_" + parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, childT) + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, parentT); objectName = objectName.ToUpper(); sb.AppendLine("--REMOVE FOREIGN KEY"); sb.AppendLine("if exists(select * from sys.objects where name = '" + objectName + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')"); sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]"); sb.AppendLine(); } } } } #endregion #region Delete if Primary Key var removePrimaryKey = false; foreach (var c in t.PrimaryKeyColumns.OrderBy(x => x.Name)) { if (c == column) removePrimaryKey = true; } if (removePrimaryKey) { var objectName = "PK_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, t); //Delete Primary Key sb.AppendLine("--DELETE PRIMARY KEY FOR TABLE [" + t.DatabaseName + "]"); sb.AppendLine("if exists(select * from sys.objects where name = '" + objectName + "' and type = 'PK' and type_desc = 'PRIMARY_KEY_CONSTRAINT')"); sb.AppendLine("ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]"); sb.AppendLine(); } #endregion #region Delete Indexes foreach (var c in t.GetColumns()) { if (string.Compare(column.DatabaseName, c.DatabaseName, true) == 0) { var indexName = "IX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", string.Empty); indexName = indexName.ToUpper(); sb.AppendLine("--DELETE UNIQUE CONTRAINT"); sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'UQ')"); sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]"); sb.AppendLine(); indexName = CreateIndexName(t, c); indexName = indexName.ToUpper(); sb.AppendLine("--DELETE INDEX"); sb.AppendLine("if exists (select * from sys.indexes where name = '" + indexName + "')"); sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + t.DatabaseName + "]"); sb.AppendLine(); } } #endregion #region Delete actual column sb.AppendLine("--DROP COLUMN"); sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + column.DatabaseName + "' and o.name = '" + t.DatabaseName + "')"); sb.AppendLine("ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] DROP COLUMN [" + column.DatabaseName + "]"); #endregion return sb.ToString(); }
private static string AppendColumnDefinition(Column column, bool allowDefault, bool allowIdentity, bool forceNull, bool allowFormula, bool allowComputed) { var sb = new StringBuilder(); if (!allowComputed || !column.ComputedColumn) { //Add column sb.Append("[" + column.DatabaseName + "] " + column.DatabaseType); ////Add length //if (ModelHelper.VariableLengthType(column.DataType)) //{ // if (column.DataType == SqlDbType.Decimal) // sb.Append(" (" + column.Length + ", " + column.Scale + ")"); // else // sb.Append(" (" + column.GetLengthString() + ")"); //} //Add Identity if (allowIdentity && (column.Identity == IdentityTypeConstants.Database)) { if (column.DataType == SqlDbType.UniqueIdentifier) sb.Append(" DEFAULT newid()"); else sb.Append(" IDENTITY (1, 1)"); } //Add collation if (column.IsTextType && !string.IsNullOrEmpty(column.Collate)) sb.Append(" COLLATE " + column.Collate); //Add NULLable if (!forceNull && !column.AllowNull) sb.Append(" NOT"); sb.Append(" NULL"); //Add default value var defaultValue = GetDefaultValueClause(column); if (allowDefault && defaultValue != null) sb.Append(" " + GetDefaultValueClause(column)); } else { sb.Append("[" + column.DatabaseName + "]"); if (allowFormula) { sb.Append(" AS (" + column.Formula + ")"); } } return sb.ToString(); }
public static string GetSqlModifyColumn(Column oldColumn, Column newColumn) { if (newColumn.ParentTable.TypedTable == TypedTableConstants.EnumOnly) return string.Empty; var sb = new StringBuilder(); var newTable = newColumn.ParentTable; var oldTable = oldColumn.ParentTable; var model = newColumn.Root as ModelRoot; #region Rename column if (newColumn.DatabaseName != oldColumn.DatabaseName) { //RENAME COLUMN sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(oldColumn, newColumn)); sb.AppendLine("GO"); sb.AppendLine(); //rename all indexes for this table (later we can select just for this column) foreach (var index in newTable.TableIndexList) { var oldIndex = oldTable.TableIndexList.FirstOrDefault(x => x.Key == index.Key); if (oldIndex != null) { var oldIndexName = GetIndexName(oldTable, oldIndex); var newIndexName = GetIndexName(newTable, index); if (oldIndexName != newIndexName) { sb.AppendLine("--RENAME INDEX [" + oldTable.DatabaseName + "].[" + oldIndexName + "]"); sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')"); sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';"); sb.AppendLine(); } } } } #endregion #region Delete Parent Relations for (var ii = oldTable.ParentRoleRelations.Count - 1; ii >= 0; ii--) { var parentR = oldTable.ParentRoleRelations[ii] as Relation; var parentT = parentR.ParentTable; var childT = parentR.ChildTable; //var childT = newColumn.ParentTable; if (parentR.ParentTableRef.Object == oldTable) { var removeRelationship = false; foreach (var cr in parentR.ColumnRelationships.AsEnumerable()) { if (cr.ParentColumnRef.Object == oldColumn) removeRelationship = true; } if (removeRelationship) { var objectName = "FK_" + parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot)oldTable.Root, childT) + "_" + Globals.GetTableDatabaseName((ModelRoot)oldTable.Root, parentT); objectName = objectName.ToUpper(); sb.AppendLine("--REMOVE FOREIGN KEY"); sb.AppendLine("if exists(select * from sysobjects where name = '" + objectName + "' and xtype = 'F')"); sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]"); sb.AppendLine(); } } } #endregion #region Delete Child Relations for (var ii = oldTable.ChildRoleRelations.Count - 1; ii >= 0; ii--) { var childR = oldTable.ChildRoleRelations[ii] as Relation; var parentT = childR.ParentTable; //var childT = childR.ChildTable; var childT = newColumn.ParentTable; for (var jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--) { var parentR = parentT.ParentRoleRelations[jj] as Relation; if (parentR.ChildTableRef.Object == oldTable) { var removeRelationship = false; foreach (var cr in childR.ColumnRelationships.AsEnumerable()) { if ((cr.ChildColumnRef.Object == oldColumn) || (cr.ParentColumnRef.Object == oldColumn)) removeRelationship = true; } if (removeRelationship) { var objectName = "FK_" + parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) oldTable.Root, childT) + "_" + Globals.GetTableDatabaseName((ModelRoot) oldTable.Root, parentT); objectName = objectName.ToUpper(); sb.AppendLine("--REMOVE FOREIGN KEY"); sb.AppendLine("if exists(select * from sys.objects where name = '" + objectName + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')"); sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]"); sb.AppendLine(); } } } } #endregion #region Delete Primary Key if (oldColumn.PrimaryKey) { //Drop the primary key so we can modify this column var pkName = "PK_" + newTable.DatabaseName.ToUpper(); sb.AppendLine("--DROP PK BECAUSE THE MODIFIED FIELD IS A PK COLUMN"); sb.AppendLine("if exists(select * from sys.objects where name = '" + pkName + "' and type = 'PK')"); sb.AppendLine("ALTER TABLE [" + newTable.GetSQLSchema() + "].[" + newTable.DatabaseName + "] DROP CONSTRAINT " + pkName); sb.AppendLine("GO"); sb.AppendLine(); } #endregion #region Delete Indexes //Only drop indexes if the datatype changed if (oldColumn.DataType != newColumn.DataType) { //Unique Constraint var indexName = "IX_" + newTable.Name.Replace("-", "") + "_" + newColumn.Name.Replace("-", string.Empty); indexName = indexName.ToUpper(); sb.AppendLine("--DELETE UNIQUE CONTRAINT"); sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'UQ')"); sb.AppendLine("ALTER TABLE [" + newTable.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]"); sb.AppendLine(); //Other Index indexName = CreateIndexName(newTable, newColumn); indexName = indexName.ToUpper(); sb.AppendLine("--DELETE INDEX"); sb.AppendLine("if exists (select * from sys.indexes where name = '" + indexName + "')"); sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + newTable.DatabaseName + "]"); sb.AppendLine(); } #endregion #region Delete Defaults //sb.AppendLine("--DELETE DEFAULT"); //sb.Append("select 'ALTER TABLE [" + newTable.GetSQLSchema() + "].[" + newTable.DatabaseName + "] DROP CONSTRAINT ' + [name] as 'sql' "); //sb.Append("into #t "); //sb.Append("from sysobjects "); //sb.Append("where id IN ("); //sb.Append("select SC.cdefault "); //sb.Append("FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id "); //sb.Append("LEFT JOIN sys.default_constraints SM ON SC.cdefault = SM.parent_column_id "); //sb.Append("WHERE SO.xtype = 'U' and SO.NAME = '" + newTable.DatabaseName + "' and SC.NAME = '" + newColumn.DatabaseName + "')"); //sb.AppendLine("declare @sql [nvarchar] (1000)"); //sb.AppendLine("SELECT @sql = MAX([sql]) from #t"); //sb.AppendLine("exec (@sql)"); //sb.AppendLine("drop table #t"); //sb.AppendLine(); sb.AppendLine(GetSqlDropColumnDefault(oldColumn, true)); sb.Append(AppendColumnDefaultRemoveSql(newColumn)); #endregion #region Update column //Only change if the column type, length, or nullable values have changed if (oldColumn.DataType != newColumn.DataType || oldColumn.Length != newColumn.Length || oldColumn.AllowNull != newColumn.AllowNull) { sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newColumn.DatabaseName + "' and o.name = '" + newTable.DatabaseName + "')"); sb.AppendLine("BEGIN"); sb.AppendLine(AppendColumnDefaultCreateSQL(newColumn)); if (newColumn.ComputedColumn) { sb.AppendLine("--DROP COLUMN"); sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newColumn.DatabaseName + "' and o.name = '" + newTable.DatabaseName + "')"); sb.AppendLine("ALTER TABLE [" + newTable.GetSQLSchema() + "].[" + newTable.DatabaseName + "] DROP COLUMN " + AppendColumnDefinition(newColumn, allowDefault: false, allowIdentity: false)); } else { //If the old column allowed null values and the new one does not //Add this line to set non-null values to the default if (!newColumn.AllowNull && oldColumn.AllowNull) { sb.AppendLine(); if (string.IsNullOrEmpty(newColumn.Default)) { //There is no default value so just inject a warning sb.AppendLine("--WARNING: IF YOU NEED TO SET NULL COLUMN VALUES TO A NON-NULL VALUE, DO SO HERE BEFORE MAKING THE COLUMN NON-NULLABLE"); } else { //There is a default value so add a comment and necessary SQL sb.AppendLine("--WARNING: IF YOU NEED TO SET NULL COLUMN VALUES TO THE DEFAULT VALUE, UNCOMMENT THE FOLLOWING LINE TO DO SO HERE BEFORE MAKING THE COLUMN NON-NULLABLE"); var dValue = newColumn.Default; if (ModelHelper.IsTextType(newColumn.DataType) || ModelHelper.IsDateType(newColumn.DataType)) dValue = "'" + dValue.Replace("'", "''") + "'"; sb.AppendLine("--UPDATE [" + newTable.GetSQLSchema() + "].[" + newTable.DatabaseName + "] SET [" + newColumn.DatabaseName + "] = " + dValue + " WHERE [" + newColumn.DatabaseName + "] IS NULL"); } sb.AppendLine(); } sb.AppendLine("--UPDATE COLUMN"); sb.AppendLine("ALTER TABLE [" + newTable.GetSQLSchema() + "].[" + newTable.DatabaseName + "] ALTER COLUMN " + AppendColumnDefinition(newColumn, allowDefault: false, allowIdentity: false)); sb.AppendLine(); } sb.AppendLine("END"); } #endregion if (newColumn.ComputedColumn) { sb.Append(GetSqlAddColumn(newColumn)); } return sb.ToString(); }
public static string GetDefaultValueConstraintName(Column column) { var table = column.ParentTableRef.Object as Table; var defaultName = "DF__" + table.DatabaseName + "_" + column.DatabaseName; defaultName = defaultName.ToUpper(); return defaultName; }
public Column GetBasePKColumn(Column column) { if (column == null) throw new Exception("The column cannot be null."); if (this.PrimaryKeyColumns.Count(x => x.Name == column.Name) == 0) throw new Exception("The column does not belong to this table."); var tList = new List<Table>(GetTableHierarchy()); tList.Add(this); return tList.First().PrimaryKeyColumns.FirstOrDefault(x => x.Name == column.Name); }
public static string GetDetailSQLValue(Column column) { var tempBuilder = new StringBuilder(); var defaultValue = column.Default + string.Empty; if ((column.DataType == System.Data.SqlDbType.DateTime) || (column.DataType == System.Data.SqlDbType.SmallDateTime)) { if (defaultValue.ToLower() == "getdate" || defaultValue.ToLower() == "getdate()" || defaultValue.ToLower() == "sysdatetime" || defaultValue.ToLower() == "sysdatetime()") { tempBuilder.Append("sysdatetime()"); } else if (defaultValue.ToLower() == "getutcdate" || defaultValue.ToLower() == "getutcdate()") { tempBuilder.Append("getutcdate()"); } else if (defaultValue.ToLower().StartsWith("getdate+") || defaultValue.ToLower().StartsWith("sysdatetime+")) { var br = defaultValue.IndexOf("+") + 1; var t = defaultValue.Substring(br, defaultValue.Length - br); var tarr = t.Split('-'); if (tarr.Length == 2) { if (tarr[1] == "day") tempBuilder.Append("DATEADD(DAY, " + tarr[0] + ", sysdatetime())"); else if (tarr[1] == "month") tempBuilder.Append("DATEADD(MONTH, " + tarr[0] + ", sysdatetime())"); else if (tarr[1] == "year") tempBuilder.Append("DATEADD(YEAR, " + tarr[0] + ", sysdatetime())"); } } } else if (column.DataType == SqlDbType.UniqueIdentifier) { if (defaultValue.ToLower() == "newid" || defaultValue.ToLower() == "newid()" || defaultValue.ToLower() == "newsequentialid" || defaultValue.ToLower() == "newsequentialid()" || column.Identity == IdentityTypeConstants.Database) { tempBuilder.Append(GetDefaultValue(defaultValue)); } else { var v = GetDefaultValue(defaultValue .Replace("'", string.Empty) .Replace("\"", string.Empty) .Replace("{", string.Empty) .Replace("}", string.Empty)); Guid g; if (Guid.TryParse(v, out g)) tempBuilder.Append("'" + g.ToString() + "'"); } } else if (column.DataType == SqlDbType.Bit) { var d = defaultValue.ToLower(); if ((d == "false") || (d == "0")) tempBuilder.Append("0"); else if ((d == "true") || (d == "1")) tempBuilder.Append("1"); } else if (column.IsBinaryType) { tempBuilder.Append(GetDefaultValue(defaultValue)); } else if (ModelHelper.DefaultIsString(column.DataType) && !string.IsNullOrEmpty(defaultValue)) { if (!column.DefaultIsFunc) tempBuilder.Append("'"); tempBuilder.Append(GetDefaultValue(defaultValue)); if (!column.DefaultIsFunc) tempBuilder.Append("'"); } else { tempBuilder.Append(GetDefaultValue(defaultValue)); } return tempBuilder.ToString(); }
/// <summary> /// Given a column in this table, determines if there is a relation to a type table based on it /// </summary> /// <param name="column"></param> /// <returns></returns> public bool IsColumnRelatedToTypeTable(Column column, out string roleName) { return (GetRelatedTypeTableByColumn(column, out roleName) != null); }
internal static string GetDefaultValueClause(Column column) { var sb = new StringBuilder(); var theValue = GetDetailSQLValue(column); if (!string.IsNullOrEmpty(theValue)) { //We know that something was typed in so create the default clause var table = column.ParentTableRef.Object as Table; var defaultName = GetDefaultValueConstraintName(column); sb.Append("CONSTRAINT [" + defaultName + "] "); var tempBuilder = new StringBuilder(); tempBuilder.Append("DEFAULT (" + theValue + ")"); sb.Append(tempBuilder.ToString()); } return sb.ToString(); }
public Table GetRelatedTypeTableByColumn(Column column, bool fullHierarchy, out string roleName) { roleName = string.Empty; foreach (var relation in this.GetRelationsWhereChild(fullHierarchy)) { var parentTable = relation.ParentTableRef.Object as Table; //Type tables have 1 PK if (parentTable.Generated && relation.ColumnRelationships.Count == 1) { var parentColumn = relation.ColumnRelationships[0].ParentColumnRef.Object as Column; var childColumn = relation.ColumnRelationships[0].ChildColumnRef.Object as Column; if ((column == childColumn) && parentTable.TypedTable != TypedTableConstants.None) { roleName = relation.PascalRoleName; return parentTable; } } } return null; }
public static string GetSqlAddColumn(Column column) { return GetSqlAddColumn(column, true); }