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