public static object ColumnasPorTabla(string valor, params object[] parametros) { using (ispDB db = new ispDB()) { LinqToDB.SchemaProvider.GetSchemaOptions GetSchemaOptions = new LinqToDB.SchemaProvider.GetSchemaOptions(); LinqToDB.SchemaProvider.TableSchema ts = new LinqToDB.SchemaProvider.TableSchema(); var sp = db.DataProvider.GetSchemaProvider(); LinqToDB.SchemaProvider.DatabaseSchema dbs = sp.GetSchema(db, GetSchemaOptions); List <ColumnaSis> cols = dbs.Tables.Where(x => x.TableName == parametros[0].ToString()).SingleOrDefault().Columns.Select(x => new ColumnaSis() { ColumnName = x.ColumnName, ColumnType = x.ColumnName, DataType = x.DataType, Description = x.Description, IsIdentity = x.IsIdentity, IsNullable = x.IsNullable, IsPrimaryKey = x.IsPrimaryKey, MemberName = x.MemberName, MemberType = x.MemberType, PrimaryKeyOrder = x.PrimaryKeyOrder, SystemType = x.SystemType, SkipOnInsert = x.SkipOnInsert, SkipOnUpdate = x.SkipOnUpdate, Table = x.Table }).ToList(); if (cols.Count > 0) { cols.Add(new ColumnaSis() { ColumnName = "modificado" }); } return(cols); } }
public void CargarTablas(ComboBox unCombo) { LinqToDB.SchemaProvider.GetSchemaOptions GetSchemaOptions = new LinqToDB.SchemaProvider.GetSchemaOptions(); LinqToDB.SchemaProvider.TableSchema ts = new LinqToDB.SchemaProvider.TableSchema(); using (ispDB dbcn = new ispDB()) { var sp = dbcn.DataProvider.GetSchemaProvider(); LinqToDB.SchemaProvider.DatabaseSchema db = sp.GetSchema(dbcn, GetSchemaOptions); unCombo.ValueMember = "TableName"; unCombo.DisplayMember = "TableName"; unCombo.DataSource = null; unCombo.DataSource = db.Tables.OrderBy(x => x.TableName).ToList(); } }
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema, GetSchemaOptions schemaOptions) { foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys.ToList()) { if (!key.KeyName.EndsWith("_BackReference")) { key.OtherTable.ForeignKeys.Add( key.BackReference = new ForeignKeySchema { KeyName = key.KeyName + "_BackReference", MemberName = key.MemberName + "_BackReference", AssociationType = AssociationType.Auto, OtherTable = t, ThisColumns = key.OtherColumns, OtherColumns = key.ThisColumns, CanBeNull = true, }); } } } foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys) { if (key.BackReference != null && key.AssociationType == AssociationType.Auto) { if (key.ThisColumns.All(_ => _.IsPrimaryKey)) { if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count) { key.AssociationType = AssociationType.OneToOne; } else { key.AssociationType = AssociationType.ManyToOne; } } else { key.AssociationType = AssociationType.ManyToOne; } key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable); } } foreach (var key in t.ForeignKeys) { string name = null; if (schemaOptions.GetAssociationMemberName != null) { name = schemaOptions.GetAssociationMemberName(key); if (name != null) { key.MemberName = ToValidName(name); } } if (name == null) { name = key.MemberName; if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id")) { name = key.ThisColumns[0].MemberName; name = name.Substring(0, name.Length - "id".Length); if (t.ForeignKeys.Select(_ => _.MemberName).Concat( t.Columns.Select(_ => _.MemberName)).Concat( new[] { t.TypeName }).All(_ => _ != name)) { name = key.MemberName; } } if (name == key.MemberName) { if (name.StartsWith("FK_")) { name = name.Substring(3); } if (name.EndsWith("_BackReference")) { name = name.Substring(0, name.Length - "_BackReference".Length); } name = string.Join("", name .Split('_') .Where(_ => _.Length > 0 && _ != t.TableName && (t.SchemaName == null || t.IsDefaultSchema || _ != t.SchemaName)) .ToArray()); var digitEnd = 0; for (var i = name.Length - 1; i >= 0; i--) { if (char.IsDigit(name[i])) { digitEnd++; } else { break; } } if (digitEnd > 0) { name = name.Substring(0, name.Length - digitEnd); } } if (string.IsNullOrEmpty(name)) { name = key.OtherTable.TableName; } if (t.ForeignKeys.Select(_ => _.MemberName).Concat( t.Columns.Select(_ => _.MemberName)).Concat( new[] { t.TypeName }).All(_ => _ != name)) { key.MemberName = ToValidName(name); } } } } return(databaseSchema); }
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { if (options == null) { options = new GetSchemaOptions(); } IncludedSchemas = GetHashSet(options.IncludedSchemas, options.StringComparer); ExcludedSchemas = GetHashSet(options.ExcludedSchemas, options.StringComparer); IncludedCatalogs = GetHashSet(options.IncludedCatalogs, options.StringComparer); ExcludedCatalogs = GetHashSet(options.ExcludedCatalogs, options.StringComparer); GenerateChar1AsString = options.GenerateChar1AsString; var dbConnection = (DbConnection)dataConnection.Connection; InitProvider(dataConnection); DataTypes = GetDataTypes(dataConnection); DataTypesDic = new Dictionary <string, DataTypeInfo>(DataTypes.Count, StringComparer.OrdinalIgnoreCase); foreach (var dt in DataTypes) { if (!DataTypesDic.ContainsKey(dt.TypeName)) { DataTypesDic.Add(dt.TypeName, dt); } } List <TableSchema> tables; List <ProcedureSchema> procedures; if (options.GetTables) { tables = ( from t in GetTables(dataConnection) where (IncludedSchemas.Count == 0 || IncludedSchemas.Contains(t.SchemaName)) && (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(t.SchemaName)) && (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(t.CatalogName)) && (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(t.CatalogName)) select new TableSchema { ID = t.TableID, CatalogName = t.CatalogName, SchemaName = t.SchemaName, TableName = t.TableName, Description = t.Description, IsDefaultSchema = t.IsDefaultSchema, IsView = t.IsView, TypeName = ToValidName(t.TableName), Columns = new List <ColumnSchema>(), ForeignKeys = new List <ForeignKeySchema>(), IsProviderSpecific = t.IsProviderSpecific } ).ToList(); var pks = GetPrimaryKeys(dataConnection); #region Columns var columns = from c in GetColumns(dataConnection) join pk in pks on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2 from pk in g2.DefaultIfEmpty() join t in tables on c.TableID equals t.ID orderby c.Ordinal select new { t, c, dt = GetDataType(c.DataType), pk }; foreach (var column in columns) { var dataType = column.c.DataType; var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); var isNullable = column.c.IsNullable; var columnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); column.t.Columns.Add(new ColumnSchema { Table = column.t, ColumnName = column.c.Name, ColumnType = columnType, IsNullable = isNullable, MemberName = ToValidName(column.c.Name), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale), ProviderSpecificType = GetProviderSpecificType(dataType), SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity, SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity, IsPrimaryKey = column.pk != null, PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1, IsIdentity = column.c.IsIdentity, Description = column.c.Description, Length = column.c.Length, Precision = column.c.Precision, Scale = column.c.Scale, }); } #endregion #region FK var fks = GetForeignKeys(dataConnection); foreach (var fk in fks.OrderBy(f => f.Ordinal)) { var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault(); var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault(); if (thisTable == null || otherTable == null) { continue; } var thisColumn = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).Single(); var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single(); var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name); if (key == null) { key = new ForeignKeySchema { KeyName = fk.Name, MemberName = ToValidName(fk.Name), ThisTable = thisTable, OtherTable = otherTable, ThisColumns = new List <ColumnSchema>(), OtherColumns = new List <ColumnSchema>(), CanBeNull = true, }; thisTable.ForeignKeys.Add(key); } key.ThisColumns.Add(thisColumn); key.OtherColumns.Add(otherColumn); } #endregion var pst = GetProviderSpecificTables(dataConnection); if (pst != null) { tables.AddRange(pst); } } else { tables = new List <TableSchema>(); } if (options.GetProcedures) { #region Procedures var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder(); var procs = GetProcedures(dataConnection); var procPparams = GetProcedureParameters(dataConnection); var n = 0; if (procs != null) { procedures = ( from sp in procs where (IncludedSchemas.Count == 0 || IncludedSchemas.Contains(sp.SchemaName)) && (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) && (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(sp.CatalogName)) && (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(sp.CatalogName)) join p in procPparams on sp.ProcedureID equals p.ProcedureID into gr select new ProcedureSchema { CatalogName = sp.CatalogName, SchemaName = sp.SchemaName, ProcedureName = sp.ProcedureName, MemberName = ToValidName(sp.ProcedureName), IsFunction = sp.IsFunction, IsTableFunction = sp.IsTableFunction, IsAggregateFunction = sp.IsAggregateFunction, IsDefaultSchema = sp.IsDefaultSchema, Parameters = ( from pr in gr join dt in DataTypes on pr.DataType equals dt.TypeName into g1 from dt in g1.DefaultIfEmpty() let systemType = GetSystemType(pr.DataType, null, dt, pr.Length, pr.Precision, pr.Scale) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(pr.DataType, dt, pr.Length, pr.Precision, pr.Scale), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName ?? "par" + ++n), ParameterType = ToTypeName(systemType, true), SystemType = systemType ?? typeof(object), DataType = GetDataType(pr.DataType, null, pr.Length, pr.Precision, pr.Scale), ProviderSpecificType = GetProviderSpecificType(pr.DataType), } ).ToList() } into ps where ps.Parameters.All(p => p.SchemaType != "table type") select ps ).ToList(); var current = 1; foreach (var procedure in procedures) { if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure)) { var commandText = sqlProvider.ConvertTableName(new StringBuilder(), procedure.CatalogName, procedure.SchemaName, procedure.ProcedureName).ToString(); LoadProcedureTableSchema(dataConnection, procedure, commandText, tables); } options.ProcedureLoadingProgress(procedures.Count, current++); } } else { procedures = new List <ProcedureSchema>(); } #endregion var psp = GetProviderSpecificProcedures(dataConnection); if (psp != null) { procedures.AddRange(psp); } } else { procedures = new List <ProcedureSchema>(); } return(ProcessSchema(new DatabaseSchema { DataSource = GetDataSourceName(dbConnection), Database = GetDatabaseName(dbConnection), ServerVersion = dbConnection.ServerVersion, Tables = tables, Procedures = procedures, ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(), DataTypesSchema = DataTypesSchema, }, options)); }
internal static void SetForeignKeyMemberName(GetSchemaOptions schemaOptions, TableSchema table, ForeignKeySchema key) { string?name = null; if (schemaOptions.GetAssociationMemberName != null) { name = schemaOptions.GetAssociationMemberName(key); if (name != null) { key.MemberName = ToValidName(name); } } if (name == null) { name = key.MemberName; if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id")) { name = key.ThisColumns[0].MemberName; name = name.Substring(0, name.Length - "id".Length).TrimEnd('_'); if (table.ForeignKeys.Select(_ => _.MemberName).Concat( table.Columns.Select(_ => _.MemberName)).Concat( new[] { table.TypeName }).Any(_ => _ == name)) { name = key.MemberName; } } if (name == key.MemberName) { if (name.StartsWith("FK_")) { name = name.Substring(3); } if (name.EndsWith("_BackReference")) { name = name.Substring(0, name.Length - "_BackReference".Length); } name = string.Concat(name .Split('_') .Where(_ => _.Length > 0 && _ != table.TableName && (table.SchemaName == null || table.IsDefaultSchema || _ != table.SchemaName))); var digitEnd = 0; for (var i = name.Length - 1; i >= 0; i--) { if (char.IsDigit(name[i])) { digitEnd++; } else { break; } } if (digitEnd > 0) { name = name.Substring(0, name.Length - digitEnd); } } if (string.IsNullOrEmpty(name)) { name = key.OtherTable != key.ThisTable ? key.OtherTable.TableName ! : key.KeyName; } if (table.ForeignKeys.Select(_ => _.MemberName).Concat( table.Columns.Select(_ => _.MemberName)).Concat( new[] { table.TypeName }).All(_ => _ != name)) { key.MemberName = ToValidName(name); } } }
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema, GetSchemaOptions schemaOptions) { foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys.ToList()) { if (!key.KeyName.EndsWith("_BackReference")) { key.OtherTable.ForeignKeys.Add( key.BackReference = new ForeignKeySchema { KeyName = key.KeyName + "_BackReference", MemberName = key.MemberName + "_BackReference", AssociationType = AssociationType.Auto, OtherTable = t, ThisColumns = key.OtherColumns, OtherColumns = key.ThisColumns, CanBeNull = true, }); } } } foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys) { if (key.BackReference != null && key.AssociationType == AssociationType.Auto) { if (key.ThisColumns.All(_ => _.IsPrimaryKey)) { if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count) { key.AssociationType = AssociationType.OneToOne; } else { key.AssociationType = AssociationType.ManyToOne; } } else { key.AssociationType = AssociationType.ManyToOne; } key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable); } } foreach (var key in t.ForeignKeys) { SetForeignKeyMemberName(schemaOptions, t, key); } } return(databaseSchema); }
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { if (options == null) options = new GetSchemaOptions(); IncludedSchemas = options.IncludedSchemas ?? new string[0]; ExcludedSchemas = options.ExcludedSchemas ?? new string[0]; GenerateChar1AsString = options.GenerateChar1AsString; var dbConnection = (DbConnection)dataConnection.Connection; InitProvider(dataConnection); DataTypes = GetDataTypes(dataConnection); DataTypesDic = new Dictionary<string, DataTypeInfo>(DataTypes.Count, StringComparer.OrdinalIgnoreCase); foreach (var dt in DataTypes) if (!DataTypesDic.ContainsKey(dt.TypeName)) DataTypesDic.Add(dt.TypeName, dt); List<TableSchema> tables; List<ProcedureSchema> procedures; if (options.GetTables) { tables = ( from t in GetTables(dataConnection) where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(t.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(t.SchemaName)) select new TableSchema { ID = t.TableID, CatalogName = t.CatalogName, SchemaName = t.SchemaName, TableName = t.TableName, Description = t.Description, IsDefaultSchema = t.IsDefaultSchema, IsView = t.IsView, TypeName = ToValidName(t.TableName), Columns = new List<ColumnSchema>(), ForeignKeys = new List<ForeignKeySchema>() } ).ToList(); var pks = GetPrimaryKeys(dataConnection); #region Columns var columns = from c in GetColumns(dataConnection) join pk in pks on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2 from pk in g2.DefaultIfEmpty() join t in tables on c.TableID equals t.ID orderby c.Ordinal select new { t, c, dt = GetDataType(c.DataType), pk }; foreach (var column in columns) { var dataType = column.c.DataType; var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); var isNullable = column.c.IsNullable; var columnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); column.t.Columns.Add(new ColumnSchema { Table = column.t, ColumnName = column.c.Name, ColumnType = columnType, IsNullable = isNullable, MemberName = ToValidName(column.c.Name), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale), ProviderSpecificType = GetProviderSpecificType(dataType), SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity, SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity, IsPrimaryKey = column.pk != null, PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1, IsIdentity = column.c.IsIdentity, Description = column.c.Description, Length = column.c.Length, Precision = column.c.Precision, Scale = column.c.Scale, }); } #endregion #region FK var fks = GetForeignKeys(dataConnection); foreach (var fk in fks.OrderBy(f => f.Ordinal)) { var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault(); var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault(); if (thisTable == null || otherTable == null) continue; var thisColumn = (from c in thisTable. Columns where c.ColumnName == fk.ThisColumn select c).Single(); var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single(); var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name); if (key == null) { key = new ForeignKeySchema { KeyName = fk.Name, MemberName = ToValidName(fk.Name), ThisTable = thisTable, OtherTable = otherTable, ThisColumns = new List<ColumnSchema>(), OtherColumns = new List<ColumnSchema>(), CanBeNull = true, }; thisTable.ForeignKeys.Add(key); } key.ThisColumns. Add(thisColumn); key.OtherColumns.Add(otherColumn); } #endregion var pst = GetProviderSpecificTables(dataConnection); if (pst != null) tables.AddRange(pst); } else tables = new List<TableSchema>(); if (options.GetProcedures) { #region Procedures var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder(); var procs = GetProcedures(dataConnection); var procPparams = GetProcedureParameters(dataConnection); var n = 0; if (procs != null) { procedures = ( from sp in procs where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(sp.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) join p in procPparams on sp.ProcedureID equals p.ProcedureID into gr select new ProcedureSchema { CatalogName = sp.CatalogName, SchemaName = sp.SchemaName, ProcedureName = sp.ProcedureName, MemberName = ToValidName(sp.ProcedureName), IsFunction = sp.IsFunction, IsTableFunction = sp.IsTableFunction, IsAggregateFunction = sp.IsAggregateFunction, IsDefaultSchema = sp.IsDefaultSchema, Parameters = ( from pr in gr join dt in DataTypes on pr.DataType equals dt.TypeName into g1 from dt in g1.DefaultIfEmpty() let systemType = GetSystemType(pr.DataType, null, dt, pr.Length, pr.Precision, pr.Scale) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(pr.DataType, dt, pr.Length, pr.Precision, pr.Scale), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName ?? "par" + ++n), ParameterType = ToTypeName(systemType, true), SystemType = systemType ?? typeof(object), DataType = GetDataType(pr.DataType, null, pr.Length, pr.Precision, pr.Scale), ProviderSpecificType = GetProviderSpecificType(pr.DataType), } ).ToList() } into ps where ps.Parameters.All(p => p.SchemaType != "table type") select ps ).ToList(); var current = 1; foreach (var procedure in procedures) { if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure)) { var commandText = sqlProvider.ConvertTableName(new StringBuilder(), procedure.CatalogName, procedure.SchemaName, procedure.ProcedureName).ToString(); LoadProcedureTableSchema(dataConnection, procedure, commandText, tables); } options.ProcedureLoadingProgress(procedures.Count, current++); } } else procedures = new List<ProcedureSchema>(); #endregion var psp = GetProviderSpecificProcedures(dataConnection); if (psp != null) procedures.AddRange(psp); } else procedures = new List<ProcedureSchema>(); return ProcessSchema(new DatabaseSchema { DataSource = GetDataSourceName(dbConnection), Database = GetDatabaseName (dbConnection), ServerVersion = dbConnection.ServerVersion, Tables = tables, Procedures = procedures, ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(), DataTypesSchema = DataTypesSchema, }, options); }
protected abstract IReadOnlyCollection <PrimaryKeyInfo> GetPrimaryKeys(DataConnection dataConnection, IEnumerable <TableSchema> tables, GetSchemaOptions options);
protected virtual void LoadProcedureTableSchema( DataConnection dataConnection, GetSchemaOptions options, ProcedureSchema procedure, string commandText, List <TableSchema> tables) { CommandType commandType; DataParameter[] parameters; if (procedure.IsTableFunction) { commandText = BuildTableFunctionLoadTableSchemaCommand(procedure, commandText); commandType = CommandType.Text; parameters = Array <DataParameter> .Empty; } else { commandType = CommandType.StoredProcedure; parameters = procedure.Parameters.Select(BuildProcedureParameter).ToArray(); } try { var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters, options); procedure.IsLoaded = true; if (st != null && st.Columns.Count > 0) { var columns = GetProcedureResultColumns(st, options); if (columns.Count > 0) { procedure.ResultTable = new TableSchema { IsProcedureResult = true, TypeName = ToValidName(procedure.ProcedureName + "Result"), ForeignKeys = new List <ForeignKeySchema>(), Columns = columns }; foreach (var column in procedure.ResultTable.Columns) { column.Table = procedure.ResultTable; } procedure.SimilarTables = ( from t in tables where t.Columns.Count == procedure.ResultTable.Columns.Count let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 }) where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType) select t ).ToList(); } } } catch (Exception ex) { procedure.ResultException = ex; } }
protected virtual List <TableSchema>?GetProviderSpecificTables(DataConnection dataConnection, GetSchemaOptions options) => null;
protected virtual List <ProcedureParameterInfo>?GetProcedureParameters(DataConnection dataConnection, IEnumerable <ProcedureInfo> procedures, GetSchemaOptions options) => null;
protected virtual List <ProcedureInfo>?GetProcedures(DataConnection dataConnection, GetSchemaOptions options) => null;
protected abstract List <ColumnInfo> GetColumns(DataConnection dataConnection, GetSchemaOptions options);
protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema, GetSchemaOptions schemaOptions) { foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys.ToList()) { if (!key.KeyName.EndsWith("_BackReference")) { key.OtherTable.ForeignKeys.Add( key.BackReference = new ForeignKeySchema { KeyName = key.KeyName + "_BackReference", MemberName = key.MemberName + "_BackReference", AssociationType = AssociationType.Auto, OtherTable = t, ThisColumns = key.OtherColumns, OtherColumns = key.ThisColumns, CanBeNull = true, }); } } } foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys) { if (key.BackReference != null && key.AssociationType == AssociationType.Auto) { if (key.ThisColumns.All(_ => _.IsPrimaryKey)) { if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count) key.AssociationType = AssociationType.OneToOne; else key.AssociationType = AssociationType.ManyToOne; } else key.AssociationType = AssociationType.ManyToOne; key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable); } } foreach (var key in t.ForeignKeys) { string name = null; if (schemaOptions.GetAssociationMemberName != null) { name = schemaOptions.GetAssociationMemberName(key); if (name != null) key.MemberName = name; } if (name == null) { name = key.MemberName; if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id")) { name = key.ThisColumns[0].MemberName; name = name.Substring(0, name.Length - "id".Length); if (t.ForeignKeys.Select(_ => _.MemberName). Concat( t.Columns. Select(_ => _.MemberName)).Concat( new[] { t.TypeName }).All(_ => _ != name)) { name = key.MemberName; } } if (name == key.MemberName) { if (name.StartsWith("FK_")) name = name.Substring(3); if (name.EndsWith("_BackReference")) name = name.Substring(0, name.Length - "_BackReference".Length); name = string.Join("", name .Split('_') .Where(_ => _.Length > 0 && _ != t.TableName && (t.SchemaName == null || t.IsDefaultSchema || _ != t.SchemaName)) .ToArray()); } if (name.Length != 0 && t.ForeignKeys.Select(_ => _.MemberName).Concat( t.Columns. Select(_ => _.MemberName)).Concat( new[] { t.TypeName }).All(_ => _ != name)) { key.MemberName = name; } } } } return databaseSchema; }
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions?options = null) { if (options == null) { options = new GetSchemaOptions(); } IncludedSchemas = GetHashSet(options.IncludedSchemas, options.StringComparer); ExcludedSchemas = GetHashSet(options.ExcludedSchemas, options.StringComparer); IncludedCatalogs = GetHashSet(options.IncludedCatalogs, options.StringComparer); ExcludedCatalogs = GetHashSet(options.ExcludedCatalogs, options.StringComparer); GenerateChar1AsString = options.GenerateChar1AsString; var dbConnection = dataConnection.Connection; InitProvider(dataConnection); DataTypesDic = new Dictionary <string, DataTypeInfo>(StringComparer.OrdinalIgnoreCase); ProviderSpecificDataTypesDic = new Dictionary <string, DataTypeInfo>(StringComparer.OrdinalIgnoreCase); DataTypesByProviderDbTypeDic = new Dictionary <int, DataTypeInfo>(); ProviderSpecificDataTypesByProviderDbTypeDic = new Dictionary <int, DataTypeInfo>(); foreach (var dt in GetDataTypes(dataConnection)) { if (dt.ProviderSpecific) { if (!ProviderSpecificDataTypesDic.ContainsKey(dt.TypeName)) { ProviderSpecificDataTypesDic.Add(dt.TypeName, dt); } if (!ProviderSpecificDataTypesByProviderDbTypeDic.ContainsKey(dt.ProviderDbType)) { ProviderSpecificDataTypesByProviderDbTypeDic.Add(dt.ProviderDbType, dt); } } else { if (!DataTypesDic.ContainsKey(dt.TypeName)) { DataTypesDic.Add(dt.TypeName, dt); } if (!DataTypesByProviderDbTypeDic.ContainsKey(dt.ProviderDbType)) { DataTypesByProviderDbTypeDic.Add(dt.ProviderDbType, dt); } } } List <TableSchema> tables; List <ProcedureSchema> procedures; if (options.GetTables) { tables = ( from t in GetTables(dataConnection, options) where (IncludedSchemas.Count == 0 || IncludedSchemas.Contains(t.SchemaName)) && (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(t.SchemaName)) && (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(t.CatalogName)) && (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(t.CatalogName)) && (options.LoadTable == null || options.LoadTable(new LoadTableData(t))) select new TableSchema { ID = t.TableID, CatalogName = t.CatalogName, SchemaName = t.SchemaName, TableName = t.TableName, Description = t.Description, IsDefaultSchema = t.IsDefaultSchema, IsView = t.IsView, TypeName = ToValidName(t.TableName), Columns = new List <ColumnSchema>(), ForeignKeys = new List <ForeignKeySchema>(), IsProviderSpecific = t.IsProviderSpecific } ).ToList(); var pks = GetPrimaryKeys(dataConnection, tables, options); #region Columns var columns = from c in GetColumns(dataConnection, options) join pk in pks on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2 from pk in g2.DefaultIfEmpty() join t in tables on c.TableID equals t.ID orderby c.Ordinal select new { t, c, dt = GetDataType(c.DataType, c.Type, options), pk }; foreach (var column in columns) { var dataType = column.c.DataType; var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale, options); var isNullable = column.c.IsNullable; var columnType = column.c.ColumnType ?? GetDbType(options, dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale, null, null, null); column.t.Columns.Add(new ColumnSchema { Table = column.t, ColumnName = column.c.Name, ColumnType = columnType, IsNullable = isNullable, MemberName = ToValidName(column.c.Name), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType, DataType = column.c.Type ?? GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale), ProviderSpecificType = GetProviderSpecificType(dataType), SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity, SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity, IsPrimaryKey = column.pk != null, PrimaryKeyOrder = column.pk?.Ordinal ?? -1, IsIdentity = column.c.IsIdentity, Description = column.c.Description, Length = column.c.Length, Precision = column.c.Precision, Scale = column.c.Scale, }); } #endregion #region FK var fks = options.GetForeignKeys ? GetForeignKeys(dataConnection, tables, options) : Array <ForeignKeyInfo> .Empty; foreach (var fk in fks.OrderBy(f => f.Ordinal)) { var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault(); var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault(); if (thisTable == null || otherTable == null) { continue; } var stringComparison = ForeignKeyColumnComparison(fk.OtherColumn); var thisColumn = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).SingleOrDefault(); var otherColumn = ( from c in otherTable.Columns where string.Equals(c.ColumnName, fk.OtherColumn, stringComparison) select c ).SingleOrDefault(); if (thisColumn == null || otherColumn == null) { continue; } var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name); if (key == null) { key = new ForeignKeySchema { KeyName = fk.Name, MemberName = ToValidName(fk.Name), ThisTable = thisTable, OtherTable = otherTable, ThisColumns = new List <ColumnSchema>(), OtherColumns = new List <ColumnSchema>(), CanBeNull = true, }; thisTable.ForeignKeys.Add(key); } key.ThisColumns.Add(thisColumn); key.OtherColumns.Add(otherColumn); } #endregion var pst = GetProviderSpecificTables(dataConnection, options); if (pst != null) { tables.AddRange(pst); } } else { tables = new List <TableSchema>(); } if (options.GetProcedures) { #region Procedures var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder(dataConnection.MappingSchema); var procs = GetProcedures(dataConnection, options); var n = 0; if (procs != null) { var procParams = (IEnumerable <ProcedureParameterInfo>?)GetProcedureParameters(dataConnection, procs, options) ?? Array <ProcedureParameterInfo> .Empty; procedures = ( from sp in procs where (IncludedSchemas.Count == 0 || IncludedSchemas.Contains(sp.SchemaName)) && (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) && (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(sp.CatalogName)) && (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(sp.CatalogName)) join p in procParams on sp.ProcedureID equals p.ProcedureID into gr select new ProcedureSchema { CatalogName = sp.CatalogName, SchemaName = sp.SchemaName, PackageName = sp.PackageName, ProcedureName = sp.ProcedureName, MemberName = ToValidName($"{sp.PackageName}{(sp.PackageName != null ? "_" : null)}{sp.ProcedureName}"), IsFunction = sp.IsFunction, IsTableFunction = sp.IsTableFunction, IsResultDynamic = sp.IsResultDynamic, IsAggregateFunction = sp.IsAggregateFunction, IsDefaultSchema = sp.IsDefaultSchema, Description = sp.Description, Parameters = ( from pr in gr let dt = GetDataType(pr.DataType, null, options) let systemType = GetSystemType(pr.DataType, pr.DataTypeExact, dt, pr.Length, pr.Precision, pr.Scale, options) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(options, pr.DataType, dt, pr.Length, pr.Precision, pr.Scale, pr.UDTCatalog, pr.UDTSchema, pr.UDTName), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName ?? "par" + ++n), ParameterType = ToTypeName(systemType, true), SystemType = systemType, DataType = GetDataType(pr.DataType, pr.DataTypeExact, pr.Length, pr.Precision, pr.Scale), ProviderSpecificType = GetProviderSpecificType(pr.DataType), IsNullable = pr.IsNullable, Description = pr.Description } ).ToList() } into ps select ps ).ToList(); var current = 1; var isActiveTransaction = dataConnection.Transaction != null; if (GetProcedureSchemaExecutesProcedure && isActiveTransaction) { throw new LinqToDBException("Cannot read schema with GetSchemaOptions.GetProcedures = true from transaction. Remove transaction or set GetSchemaOptions.GetProcedures to false"); } if (!isActiveTransaction) { dataConnection.BeginTransaction(); } try { foreach (var procedure in procedures) { if (!procedure.IsResultDynamic && (!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure)) { var commandText = sqlProvider.BuildObjectName( new (), new (procedure.ProcedureName, Database: procedure.CatalogName, Schema: procedure.SchemaName, Package: procedure.PackageName)).ToString(); LoadProcedureTableSchema(dataConnection, options, procedure, commandText, tables); } options.ProcedureLoadingProgress(procedures.Count, current++); } } finally { if (!isActiveTransaction) { dataConnection.RollbackTransaction(); } } } else { procedures = new List <ProcedureSchema>(); } var psp = GetProviderSpecificProcedures(dataConnection); if (psp != null) { procedures.AddRange(psp); } #endregion } else { procedures = new List <ProcedureSchema>(); } return(ProcessSchema(new DatabaseSchema { DataSource = GetDataSourceName(dataConnection), Database = GetDatabaseName(dataConnection), ServerVersion = dbConnection.ServerVersion, Tables = tables, Procedures = procedures, ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(), DataTypesSchema = DataTypesSchema, }, options)); }
protected abstract List <TableInfo> GetTables(DataConnection dataConnection, GetSchemaOptions options);
protected virtual DataTable?GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters, GetSchemaOptions options) { using var rd = dataConnection.ExecuteReader(commandText, commandType, CommandBehavior.SchemaOnly, parameters); return(rd.Reader !.GetSchemaTable()); }
protected virtual List <ColumnSchema> GetProcedureResultColumns(DataTable resultTable, GetSchemaOptions options) { return (( from r in resultTable.AsEnumerable() let columnType = r.Field <string>("DataTypeName") let columnName = r.Field <string>("ColumnName") let isNullable = r.Field <bool> ("AllowDBNull") let dt = GetDataType(columnType, options) let length = r.Field <int?> ("ColumnSize") let precision = Converter.ChangeTypeTo <int>(r["NumericPrecision"]) let scale = Converter.ChangeTypeTo <int>(r["NumericScale"]) let systemType = GetSystemType(columnType, null, dt, length, precision, scale, options) select new ColumnSchema { ColumnName = columnName, ColumnType = GetDbType(options, columnType, dt, length, precision, scale, null, null, null), IsNullable = isNullable, MemberName = ToValidName(columnName), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(columnType, null, length, precision, scale), ProviderSpecificType = GetProviderSpecificType(columnType), IsIdentity = r.Field <bool>("IsIdentity"), } ).ToList()); }
protected virtual Type?GetSystemType(string?dataType, string?columnType, DataTypeInfo?dataTypeInfo, long?length, int?precision, int?scale, GetSchemaOptions options) { var systemType = dataTypeInfo != null?Type.GetType(dataTypeInfo.DataType) : null; if (length == 1 && !GenerateChar1AsString && systemType == typeof(string)) { systemType = typeof(char); } return(systemType); }
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { if (options == null) options = new GetSchemaOptions(); IncludedSchemas = options.IncludedSchemas ?? new string[0]; ExcludedSchemas = options.ExcludedSchemas ?? new string[0]; GenerateChar1AsString = options.GenerateChar1AsString; var dbConnection = (DbConnection)dataConnection.Connection; DataTypes = GetDataTypes(dataConnection); DataTypesDic = new Dictionary<string, DataTypeInfo>(DataTypes.Count, StringComparer.OrdinalIgnoreCase); foreach (var dt in DataTypes) if (!DataTypesDic.ContainsKey(dt.TypeName)) DataTypesDic.Add(dt.TypeName, dt); List<TableSchema> tables; List<ProcedureSchema> procedures; if (options.GetTables) { tables = ( from t in GetTables(dataConnection) where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(t.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(t.SchemaName)) select new TableSchema { ID = t.TableID, CatalogName = t.CatalogName, SchemaName = t.SchemaName, TableName = t.TableName, Description = t.Description, IsDefaultSchema = t.IsDefaultSchema, IsView = t.IsView, TypeName = ToValidName(t.TableName), Columns = new List<ColumnSchema>(), ForeignKeys = new List<ForeignKeySchema>() } ).ToList(); var pks = GetPrimaryKeys(dataConnection); #region Columns var columns = from c in GetColumns(dataConnection) join pk in pks on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2 from pk in g2.DefaultIfEmpty() join t in tables on c.TableID equals t.ID orderby c.Ordinal select new { t, c, dt = GetDataType(c.DataType), pk }; foreach (var column in columns) { var dataType = column.c.DataType; var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); var isNullable = column.c.IsNullable; column.t.Columns.Add(new ColumnSchema { Table = column.t, ColumnName = column.c.Name, ColumnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale), IsNullable = isNullable, MemberName = ToValidName(column.c.Name), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(dataType, column.c.ColumnType), SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity, SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity, IsPrimaryKey = column.pk != null, PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1, IsIdentity = column.c.IsIdentity, Description = column.c.Description, }); } #endregion #region FK var fks = GetForeignKeys(dataConnection); foreach (var fk in fks.OrderBy(f => f.Ordinal)) { var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault(); var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault(); if (thisTable == null || otherTable == null) continue; var thisColumn = (from c in thisTable. Columns where c.ColumnName == fk.ThisColumn select c).Single(); var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single(); var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name); if (key == null) { key = new ForeignKeySchema { KeyName = fk.Name, MemberName = ToValidName(fk.Name), ThisTable = thisTable, OtherTable = otherTable, ThisColumns = new List<ColumnSchema>(), OtherColumns = new List<ColumnSchema>(), CanBeNull = true, }; thisTable.ForeignKeys.Add(key); } key.ThisColumns. Add(thisColumn); key.OtherColumns.Add(otherColumn); } #endregion } else tables = new List<TableSchema>(); if (options.GetProcedures) { #region Procedures var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder(); var procs = GetProcedures(dataConnection); var procPparams = GetProcedureParameters(dataConnection); if (procs != null) { procedures = ( from sp in procs where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(sp.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) join p in procPparams on sp.ProcedureID equals p.ProcedureID into gr select new ProcedureSchema { CatalogName = sp.CatalogName, SchemaName = sp.SchemaName, ProcedureName = sp.ProcedureName, MemberName = ToValidName(sp.ProcedureName), IsFunction = sp.IsFunction, IsTableFunction = sp.IsTableFunction, IsAggregateFunction = sp.IsAggregateFunction, IsDefaultSchema = sp.IsDefaultSchema, Parameters = ( from pr in gr join dt in DataTypes on pr.DataType equals dt.TypeName into g1 from dt in g1.DefaultIfEmpty() let systemType = GetSystemType(pr.DataType, null, dt, pr.Length ?? 0, pr.Precision, pr.Scale) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName), ParameterType = ToTypeName(systemType, true), SystemType = systemType ?? typeof(object), DataType = GetDataType(pr.DataType, null) } ).ToList() } into ps where ps.Parameters.All(p => p.SchemaType != "table type") select ps ).ToList(); var current = 1; foreach (var procedure in procedures) { if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure)) { var catalog = procedure.CatalogName == null ? null : sqlProvider.Convert(procedure.CatalogName, ConvertType.NameToDatabase). ToString(); var schema = procedure.SchemaName == null ? null : sqlProvider.Convert(procedure.SchemaName, ConvertType.NameToOwner). ToString(); var procName = procedure.ProcedureName == null ? null : sqlProvider.Convert(procedure.ProcedureName, ConvertType.NameToQueryTable).ToString(); var commandText = sqlProvider.BuildTableName(new StringBuilder(), catalog, schema, procName).ToString(); CommandType commandType; DataParameter[] parameters; if (procedure.IsTableFunction) { commandText = "SELECT * FROM " + commandText + "("; for (var i = 0; i < procedure.Parameters.Count; i++) { if (i != 0) commandText += ","; commandText += "NULL"; } commandText += ")"; commandType = CommandType.Text; parameters = new DataParameter[0]; } else { commandType = CommandType.StoredProcedure; parameters = procedure.Parameters.Select(p => new DataParameter { Name = p.ParameterName, Value = p.SystemType == typeof(string) ? "" : p.SystemType == typeof(DateTime) ? DateTime.Now : DefaultValue.GetValue(p.SystemType), DataType = p.DataType, Size = p.Size, Direction = p.IsIn ? p.IsOut ? ParameterDirection.InputOutput : ParameterDirection.Input : ParameterDirection.Output }).ToArray(); } { try { var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters); if (st != null) { procedure.ResultTable = new TableSchema { IsProcedureResult = true, TypeName = ToValidName(procedure.ProcedureName + "Result"), ForeignKeys = new List<ForeignKeySchema>(), Columns = GetProcedureResultColumns(st) }; foreach (var column in procedure.ResultTable.Columns) column.Table = procedure.ResultTable; procedure.SimilarTables = ( from t in tables where t.Columns.Count == procedure.ResultTable.Columns.Count let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 }) where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType) select t ).ToList(); } } catch (Exception ex) { procedure.ResultException = ex; } } } options.ProcedureLoadingProgress(procedures.Count, current++); } } else procedures = new List<ProcedureSchema>(); #endregion } else procedures = new List<ProcedureSchema>(); return ProcessSchema(new DatabaseSchema { DataSource = GetDataSourceName(dbConnection), Database = GetDatabaseName (dbConnection), ServerVersion = dbConnection.ServerVersion, Tables = tables, Procedures = procedures, }); }
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { if (options == null) { options = new GetSchemaOptions(); } IncludedSchemas = options.IncludedSchemas ?? new string[0]; ExcludedSchemas = options.ExcludedSchemas ?? new string[0]; GenerateChar1AsString = options.GenerateChar1AsString; var dbConnection = (DbConnection)dataConnection.Connection; DataTypes = GetDataTypes(dataConnection); List <TableSchema> tables; List <ProcedureSchema> procedures; if (options.GetTables) { tables = ( from t in GetTables(dataConnection) where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(t.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(t.SchemaName)) select new TableSchema { ID = t.TableID, CatalogName = t.CatalogName, SchemaName = t.SchemaName, TableName = t.TableName, Description = t.Description, IsDefaultSchema = t.IsDefaultSchema, IsView = t.IsView, TypeName = ToValidName(t.TableName), Columns = new List <ColumnSchema>(), ForeignKeys = new List <ForeignKeySchema>() } ).ToList(); var pks = GetPrimaryKeys(dataConnection); #region Columns var columns = from c in GetColumns(dataConnection) join dt in DataTypes on c.DataType equals dt.TypeName into g1 from dt in g1.DefaultIfEmpty() join pk in pks on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2 from pk in g2.DefaultIfEmpty() join t in tables on c.TableID equals t.ID orderby c.Ordinal select new { t, c, dt, pk }; foreach (var column in columns) { var columnType = column.c.DataType; var systemType = GetSystemType(columnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); var isNullable = column.c.IsNullable; column.t.Columns.Add(new ColumnSchema { Table = column.t, ColumnName = column.c.Name, ColumnType = column.c.ColumnType ?? GetDbType(columnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale), IsNullable = isNullable, MemberName = ToValidName(column.c.Name), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(columnType, column.c.ColumnType), SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity, SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity, IsPrimaryKey = column.pk != null, PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1, IsIdentity = column.c.IsIdentity, Description = column.c.Description, }); } #endregion #region FK var fks = GetForeignKeys(dataConnection); foreach (var fk in fks.OrderBy(f => f.Ordinal)) { var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault(); var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault(); if (thisTable == null || otherTable == null) { continue; } var thisColumn = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).Single(); var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single(); var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name); if (key == null) { key = new ForeignKeySchema { KeyName = fk.Name, MemberName = ToValidName(fk.Name), ThisTable = thisTable, OtherTable = otherTable, ThisColumns = new List <ColumnSchema>(), OtherColumns = new List <ColumnSchema>(), CanBeNull = true, }; thisTable.ForeignKeys.Add(key); } key.ThisColumns.Add(thisColumn); key.OtherColumns.Add(otherColumn); } #endregion } else { tables = new List <TableSchema>(); } if (options.GetProcedures) { #region Procedures var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder(); var procs = GetProcedures(dataConnection); var procPparams = GetProcedureParameters(dataConnection); if (procs != null) { procedures = ( from sp in procs where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(sp.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) join p in procPparams on sp.ProcedureID equals p.ProcedureID into gr select new ProcedureSchema { CatalogName = sp.CatalogName, SchemaName = sp.SchemaName, ProcedureName = sp.ProcedureName, MemberName = ToValidName(sp.ProcedureName), IsFunction = sp.IsFunction, IsTableFunction = sp.IsTableFunction, IsDefaultSchema = sp.IsDefaultSchema, Parameters = ( from pr in gr join dt in DataTypes on pr.DataType equals dt.TypeName into g1 from dt in g1.DefaultIfEmpty() let systemType = GetSystemType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName), ParameterType = ToTypeName(systemType, true), SystemType = systemType ?? typeof(object), DataType = GetDataType(pr.DataType, null) } ).ToList() } into ps where ps.Parameters.All(p => p.SchemaType != "table type") select ps ).ToList(); var current = 1; foreach (var procedure in procedures) { if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure)) { var catalog = procedure.CatalogName == null ? null : sqlProvider.Convert(procedure.CatalogName, ConvertType.NameToDatabase).ToString(); var schema = procedure.SchemaName == null ? null : sqlProvider.Convert(procedure.SchemaName, ConvertType.NameToOwner).ToString(); var procName = procedure.ProcedureName == null ? null : sqlProvider.Convert(procedure.ProcedureName, ConvertType.NameToQueryTable).ToString(); var commandText = sqlProvider.BuildTableName(new StringBuilder(), catalog, schema, procName).ToString(); CommandType commandType; DataParameter[] parameters; if (procedure.IsTableFunction) { commandText = "SELECT * FROM " + commandText + "("; for (var i = 0; i < procedure.Parameters.Count; i++) { if (i != 0) { commandText += ","; } commandText += "NULL"; } commandText += ")"; commandType = CommandType.Text; parameters = new DataParameter[0]; } else { commandType = CommandType.StoredProcedure; parameters = procedure.Parameters.Select(p => new DataParameter { Name = p.ParameterName, Value = p.SystemType == typeof(string) ? "" : p.SystemType == typeof(DateTime) ? DateTime.Now : DefaultValue.GetValue(p.SystemType), DataType = p.DataType, Size = p.Size, Direction = p.IsIn ? p.IsOut ? ParameterDirection.InputOutput : ParameterDirection.Input : ParameterDirection.Output }).ToArray(); } { try { var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters); if (st != null) { procedure.ResultTable = new TableSchema { IsProcedureResult = true, TypeName = ToValidName(procedure.ProcedureName + "Result"), ForeignKeys = new List <ForeignKeySchema>(), Columns = GetProcedureResultColumns(st) }; foreach (var column in procedure.ResultTable.Columns) { column.Table = procedure.ResultTable; } procedure.SimilarTables = ( from t in tables where t.Columns.Count == procedure.ResultTable.Columns.Count let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 }) where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType) select t ).ToList(); } } catch (Exception ex) { procedure.ResultException = ex; } } } options.ProcedureLoadingProgress(procedures.Count, current++); } } else { procedures = new List <ProcedureSchema>(); } #endregion } else { procedures = new List <ProcedureSchema>(); } return(ProcessSchema(new DatabaseSchema { DataSource = GetDataSourceName(dbConnection), Database = GetDatabaseName(dbConnection), ServerVersion = dbConnection.ServerVersion, Tables = tables, Procedures = procedures, })); }