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);
}
