public static List <Table> GetTables( DataTable columns, DataTable indexColumns, DataTable indexes, DataTable schemas, DataTable systables) { var tables = new List <Table>(); var skipList = TemplateConstants.TablesToSkip .Split(',') .Select(a => a.Trim().ToLower()) .ToList(); // get tables systables.Rows .OfType <DataRow>() .Where(a => !skipList.Any(s => Regex.IsMatch(a["name"].ToString(), @"^" + s + "$", RegexOptions.IgnoreCase))) .ToList() .ForEach(a => { var t = new Table { GeneratedName = EntityDesignUtil.Singularize( NamingUtil.GetTableName(a["name"].ToString())), ObjectId = (int)a["object_id"], SchemaName = GetSchemaNameById(schemas, (int)a["schema_id"]), TableName = a["name"].ToString() }; tables.Add(t); }); // get indexes for each table tables.ForEach(a => { // get indexes for table // for each index, create index indexes.Select("object_id = " + a.ObjectId + " AND type IN(1, 2)" + " AND is_primary_key = 0", "index_id ASC") .ToList() .ForEach(b => { var index = new Index { IndexId = Convert.ToInt32(b["index_id"]), Name = b["name"].ToString() }; indexColumns .Select("object_id = " + a.ObjectId + " AND index_id = " + index.IndexId, "index_column_id ASC") .ToList() .ForEach(c => { index.Columns.Add( columns .Select("object_id = " + a.ObjectId + " AND column_id = " + c["column_id"].ToString()) .Select(d => d["name"].ToString()) .FirstOrDefault()); }); a.Indexes.Add(index); }); a.Indexes = a.Indexes .OrderBy(b => b.Name) .ToList(); }); return(tables .OrderBy(a => a.TableName) .ToList()); }
public static string GenerateTableClassFileContent( Table t, string modelNamespace) { var sb = new StringBuilder(); // TODO: move using statements to AddUsings //var hasDateColumns = // t.Columns.Any(a => CreateDateFieldNames.ContainsKey(a.Name)) // || t.Columns.Any(a => ModifiedDateFieldNames.ContainsKey(a.Name)); //var hasDateRangeColumns = // t.Columns.Any(a => StartDateFieldNames.ContainsKey(a.Name)) // || t.Columns.Any(a => EndDateFieldNames.ContainsKey(a.Name)); //var hasIsActiveColumns = // t.Columns.Any(a => IsActiveFieldNames.ContainsKey(a.Name)); //var hasDeactivatedDateColumns = // t.Columns.Any(a => DeactivatedDateFieldNames.ContainsKey(a.Name)); var hasKeyColumns = t.PrimaryKeyColumns != null && t.PrimaryKeyColumns.Count > 0; var hasMultipleKeyColumns = t.PrimaryKeyColumns != null && t.PrimaryKeyColumns.Count > 1; var interfaces = new List <string>(); //if (hasDateColumns && !ExcludePopulateDatesInterface.ContainsKey(t.TableName)) // interfaces.Add("IPopulateDates"); //if (hasDateRangeColumns && hasDeactivatedDateColumns // && TemplateConstants // .ExcludeDateRangeInterface // .Split(',') // .All(x => x != t.TableName)) //{ // interfaces.Add("IDateRange"); //} //if (!hasDateRangeColumns && hasIsActiveColumns // && TemplateConstants // .ExcludeDateRangeInterface // .Split(',') // .All(x => x != t.TableName)) //{ // interfaces.Add("IDeletable"); //} var subClass = interfaces.Contains("IDateRange") ? TemplateConstants.ModelsSubclassEffDate : TemplateConstants.ModelsSubclass; var needsColumnAttribute = t.Columns .Any(a => a.GeneratedName != a.Name || hasMultipleKeyColumns || a.Type == "varchar"); var needsKeyAttribute = hasKeyColumns; var needsMaxLengthAttribute = t.Columns .Any(a => a.ClrType == "string" && a.Type != "text" && a.Length != -1); var needsModelTableAttribute = t.Columns .Any(a => ExcludeFieldsFromModelTable.ContainsKey( $"{t.TableName}.{a.Name}")); var needsRequiredAttribute = t.Columns .Any(a => a.ClrType == "string" && !a.IsNullable); var needsTableAttribute = t.TableName != t.GeneratedName; // ReSharper disable once InconsistentNaming var needsUsingDGDeanModelsBase = !TablesToOptOutOfSubClass .ContainsKey(t.TableName) && subClass != TemplateConstants.ModelsSubclassEffDate; // ReSharper disable once InconsistentNaming var needsUsingDGDeanModelsAttributes = !string.IsNullOrWhiteSpace(t.ActiveColumnName) && subClass != TemplateConstants.ModelsSubclassEffDate; const bool needsUsingNewtonsoftJson = false; var needsUsingModelsAttributes = needsModelTableAttribute; var needsUsingModelsInterfaces = interfaces.Count > 0; var needsUsingSystem = t.Columns .Where(a => subClass != TemplateConstants.ModelsSubclassEffDate || !ModelsSubclassEffDateExcludedColumns.ContainsKey(a.Name)) .Any(a => GetClrType(a.Type) == "DateTime" || GetClrType(a.Type) == "DateTimeOffset" || GetClrType(a.Type) == "Guid" || GetClrType(a.Type) == "Int16" || GetClrType(a.Type) == "Int64"); var needsUsingSystemCollectionsGeneric = t.NavigationProperties .Any(a => a.Type.StartsWith("ICollection<")) // required for metadata properties || true; var needsUsingSystemComponentModelDataAnnotations = needsKeyAttribute || needsMaxLengthAttribute || needsRequiredAttribute || t.Columns.Any(); var needsUsingSystemComponentModelDataAnnotationsSchema = needsColumnAttribute // || needsDatabaseGeneratedAttribute // || needsForeignKeyAttribute // || needsInversePropertyAttribute // || needsNotMappedAttribute || needsTableAttribute; const bool needsUsingSystemRuntimeSerialization = false; var needsUsingModelsDbCustom = interfaces.Contains("IDateRange"); AddGeneratedDisclaimer(sb); AddUsing( needsUsingDGDeanModelsAttributes, sb, "DGDean.Models.Attributes"); AddUsing( needsUsingDGDeanModelsBase, sb, "DGDean.Models.Base"); AddUsing( needsUsingNewtonsoftJson, sb, "Newtonsoft.Json"); AddUsing( needsUsingModelsAttributes, sb, "DB.Models.Core.Attributes"); AddUsing( needsUsingModelsInterfaces, sb, "DB.Models.Core.Interfaces"); AddUsing( needsUsingModelsDbCustom, sb, "DB.Models.Core.DB.Custom"); AddUsing(needsUsingSystem, sb, "System"); AddUsing( needsUsingSystemCollectionsGeneric, sb, "System.Collections.Generic"); AddUsing( needsUsingSystemComponentModelDataAnnotations, sb, "System.ComponentModel.DataAnnotations"); AddUsing( needsUsingSystemComponentModelDataAnnotationsSchema, sb, "System.ComponentModel.DataAnnotations.Schema"); AddUsing( needsUsingSystemRuntimeSerialization, sb, "System.Runtime.Serialization"); var className = EntityDesignUtil.Singularize( NamingUtil.GetTableName(t.TableName)); //var createDateProperty = hasDateColumns // ? t.Columns // .FirstOrDefault(a => CreateDateFieldNames.ContainsKey(a.Name)) // ?.Name ?? "" // : ""; //var modifiedDateProperty = hasDateColumns // ? t.Columns // .FirstOrDefault(a => ModifiedDateFieldNames.ContainsKey(a.Name)) // ?.Name ?? "" // : ""; //var startDateProperty = hasDateRangeColumns // ? t.Columns // .FirstOrDefault(a => StartDateFieldNames.ContainsKey(a.Name)) // ?.Name ?? "" // : ""; //var endDateProperty = hasDateRangeColumns // ? t.Columns // .FirstOrDefault(a => EndDateFieldNames.ContainsKey(a.Name)) // ?.Name ?? "" // : ""; //var dateRangeModelName = // hasDateRangeColumns // && TemplateConstants // .ExcludeDateRangeInterface // .Split(',') // .All(x => x != t.TableName) // ? Regex.Replace(className, @"Model$", "") // : ""; sb.Append( "\r\n" + "namespace " + modelNamespace + "\r\n" + "{\r\n" + GetClassAttributes(t, className) + GetClassStatement( t.TableName, className, subClass, interfaces, //createDateProperty, //modifiedDateProperty, //startDateProperty, //endDateProperty, //dateRangeModelName, GetNormalProperties(t, subClass), GetNavigationProperties(t)) + "}\r\n"); return(sb.ToString()); }
private static string GetNormalPropertyAttributes( Table t, Column c) { // need to support the following attributes // Display (needed for automatically generated form field labels) // ForeignKey (needed if foreign key column name doesn't match nav prop name + "Id") // Index (needed if index that's not primary key exists) // Inverse (needed for multiple relationships between classes) // NotMapped (needed if any nav properties) var keyNeeded = c.IsKey; var key = keyNeeded ? "Key" : ""; var commaNeeded = keyNeeded; var activeNeeded = !string.IsNullOrWhiteSpace(t.ActiveColumnName) && c.Name == t.ActiveColumnName; var active = activeNeeded ? (commaNeeded ? ", " : "") + "Active(" + (t.ActiveColumnValue ? "true" : "false") + ")" : ""; commaNeeded = commaNeeded || activeNeeded; var columnMultipleKeyCols = c.IsKey && t.PrimaryKeyColumns.Count > 1; var columnNameMismatch = c.GeneratedName != c.Name; var columnVarchar = c.Type == "varchar"; var columnNeeded = columnMultipleKeyCols || columnNameMismatch || columnVarchar; var column = columnNeeded ? (commaNeeded ? ", " : "") + "Column(" + // name (columnNameMismatch ? "\"" + c.Name + "\"" : "") + // order (columnMultipleKeyCols ? (columnNameMismatch ? ", " : "") + "Order = " + (t.PrimaryKeyColumns.IndexOf(c.Name) + 1) : "") + // typename (columnVarchar ? (columnNameMismatch || columnMultipleKeyCols ? ", " : "") + "TypeName = \"varchar\"" : "") + ")" : ""; commaNeeded = commaNeeded || columnNeeded; var databaseGeneratedNeeded = c.IsIdentity || c.IsComputed; var databaseGenerated = databaseGeneratedNeeded ? (commaNeeded ? ", " : "") + "DatabaseGenerated(DatabaseGeneratedOption." + (c.IsIdentity ? "Identity" : "Computed") + ")" : ""; commaNeeded = commaNeeded || databaseGeneratedNeeded; // for now, generate the display attribute for every field var display = (commaNeeded ? ", " : "") + "Display(Name = \"" + NamingUtil.GetDisplayName(c.Name) + "\")"; commaNeeded = true; var editableNeeded = EditableColumns.ContainsKey($"{t.TableName}.{c.Name}"); var editable = editableNeeded ? ", " + "Editable(true)" : ""; var fk = keyNeeded ? t.ForeignKeys .FirstOrDefault(a => a.Columns.Any(b => b.SourceColumn == c.Name)) : null; var foreignKeyNeeded = keyNeeded && fk != null; var srcCol = fk != null ? fk.Columns[0].SourceColumn : ""; var type = fk != null ? EntityDesignUtil.Singularize( NamingUtil.GetTableName(fk.DestinationTableName)) : ""; // TODO: need to support list of source columns var foreignKey = foreignKeyNeeded ? ", " + "ForeignKey(\"" + (SourceNavPropNames.TryGetValue( fk.SourceTableName + "." + string.Join("|", fk.Columns.Select(a => a.SourceColumn)), out var sourcePropName) ? sourcePropName : srcCol.ToLower().EndsWith("id") && srcCol.Length > 2 ? srcCol.Substring(0, srcCol.Length - 2) : type) + "\")" : ""; var indexNeeded = t.Indexes .Any(a => a.Columns.Contains(c.Name)); var index = ""; t.Indexes .Where(a => a.Columns.Contains(c.Name)) .ToList() .ForEach(a => { index += (string.IsNullOrWhiteSpace(index) ? "" : ", ") + "Index(\"" + a.Name + "\"" + (a.Columns.Count > 1 ? ", " + (a.Columns.IndexOf(c.Name) + 1).ToString() : "") + ")"; }); index = string.IsNullOrWhiteSpace(index) ? "" : (commaNeeded ? ", " : "") + index; commaNeeded = commaNeeded || indexNeeded; var maxLengthNeeded = c.ClrType == "string" && c.Type != "text" && c.Length > -1; var maxLength = maxLengthNeeded ? (commaNeeded ? ", " : "") + "MaxLength(" + (c.Type == "varchar" || c.Type == "char" ? c.Length : c.Length / 2) + ")" : ""; commaNeeded = commaNeeded || maxLengthNeeded; var modelTableNeeded = ExcludeFieldsFromModelTable.ContainsKey( $"{t.TableName}.{c.Name}"); var modelTable = modelTableNeeded ? (commaNeeded ? ", " : "") + "ModelTable(true)" : ""; commaNeeded = commaNeeded || modelTableNeeded; var requiredNeeded = c.ClrType == "string" && !c.IsNullable && c.DefaultValue == null; var required = requiredNeeded ? (commaNeeded ? ", " : "") + "Required(AllowEmptyStrings = true)" : ""; var attrNeeded = commaNeeded || requiredNeeded; var attr = attrNeeded ? "[" + key + active + column + databaseGenerated + display + editable + foreignKey + index + maxLength + modelTable + required + "]" : ""; return(attr); }
private static IEnumerable <NavigationProperty> GenerateNavigationProperties( DataTable columnsTable, DataTable foreignKeyColumnsTable, DataTable foreignKeysTable, DataTable tablesTable, Table table, IReadOnlyCollection <Table> tables) { var props = new List <NavigationProperty>(); // get foreign keys var foreignKeys = DbUtil.GetForeignKeysByTableId( columnsTable, foreignKeysTable, foreignKeyColumnsTable, tablesTable, table.ObjectId, table.TableName); // for each foreign key, foreignKeys.ForEach(a => { var isDestinationForeignKeyAllNonNullable = IsDestinationNullable(a, columnsTable); var isSourceForeignKeyAlsoPrimaryKey = IsSourcePrimaryKey(table, a); var type = EntityDesignUtil.Singularize( NamingUtil.GetTableName(a.DestinationTableName)); var srcCol = a.Columns[0].SourceColumn; var prop = new NavigationProperty { DestinationMultiplicity = // Zero: if any source foreign key columns are nullable !isDestinationForeignKeyAllNonNullable ? Multiplicity.Zero // One: not Zero : Multiplicity.One, ForeignKey = a, // *-1, 1-0/1 Name = SourceNavPropNames.TryGetValue( a.SourceTableName + "." + string.Join("|", a.Columns.Select(b => b.SourceColumn)), out var sourcePropName) ? sourcePropName : srcCol.ToLower().EndsWith("id") && srcCol.Length > 2 ? srcCol.Substring(0, srcCol.Length - 2) : type, SourceMultiplicity = isSourceForeignKeyAlsoPrimaryKey ? Multiplicity.One : Multiplicity.Many, Type = type }; // only add if not in NavigationPropertiesToSkip if (!NavPropsToSkip.ContainsKey(table.GeneratedName + "." + prop.Name)) { props.Add(prop); } // get destination table if (tables == null) { return; } { var destTable = tables .FirstOrDefault(b => b.TableName == a.DestinationTableName); type = EntityDesignUtil.Singularize( NamingUtil.GetTableName(a.SourceTableName)); var destProp = new NavigationProperty { DestinationMultiplicity = prop.SourceMultiplicity, ForeignKey = a, InverseProperty = prop.Name, Name = DestNavPropNames.TryGetValue( a.SourceTableName + "." + a.Columns[0].SourceColumn, out var destPropName) ? destPropName : prop.SourceMultiplicity == Multiplicity.Many ? EntityDesignUtil.Pluralize(type) : type, SourceMultiplicity = prop.DestinationMultiplicity, Type = prop.SourceMultiplicity == Multiplicity.Many ? "ICollection<" + type + ">" : type }; if (destTable != null && !NavPropsToSkip.ContainsKey(destTable.GeneratedName + "." + destProp.Name)) { destTable.NavigationProperties.Add(destProp); } } }); // var isManyToMany = AreAllColumnsForeignKeys // The relationship is many-to-many only if the // relationship table only contains primary keys // of both entities, and no other fields. // 0, 1, *: 0-1, 1-0, 1->1, 1<-1, 0-*, 1-*, *-0, *-1, *-* // if 0-1, // add single, non-nullable navigation property // of foreign key destination table class, // and ensure foreign key destination table class // has non-required navigation property to current table class // if 1-0, // do nothing, as non-required navigation property will be added // when processing other side of foreign key relationship // if 1->1, // add single, non-nullable navigation property // of foreign key destination table class, // and ensure foreign key destination table class // has required navigation property to current table class // if 1<-1, // required navigation property will be added // when processing other side of foreign key relationship // if 0-*, // add public virtual ICollection of // foreign key destination table class, // and ensure foreign key destination table class // has non-required navigation property to current table class // if 1-*, // add public virtual ICollection // of foreign key destination table class, // and ensure foreign key destination table class // has required navigation property to current table class // if *-0, // do nothing, as non-required navigation property will be added // when processing other side of foreign key relationship // if *-1, // do nothing, as required navigation property will be added // when processing other side of foreign key relationship // if *-*, // well, there are no such tables, according to the // Entity Framework definition, which is that all columns // in the join table being both in the join table primary key, // and a foreign key pointing from the join table to one of the // joined tables. return(props); }