public override IDatabaseSchema Load(DbConfiguration cfg)
{
var databaseSchema = new DatabaseSchema();
ColumnInfo[] allColumns = null;
ForeignKeyInfo[] allFks = null;
using (var ctx = cfg.CreateDbContext())
{
InitConnection(ctx.Connection);
using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql))
allColumns = reader.ToList <ColumnInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql))
allFks = reader.ToList <ForeignKeyInfo>().ToArray();
}
Dictionary <string, TableSchema> tables = new Dictionary <string, TableSchema>();
foreach (var c in allColumns)
{
TableSchema table = null;
if (!tables.TryGetValue(c.TableName, out table))
{
table = new TableSchema {
TableName = c.TableName, IsView = c.IsView
};
tables[c.TableName] = table;
}
var key = allFks.FirstOrDefault(p => p.Type == "P" &&
p.ThisTableName == c.TableName &&
p.ThisKey == c.ColumnName);
c.IsPrimaryKey = key != null;
var column = ToColumn(c);
table.AddColumn(column);
}
foreach (var item in allFks.Where(p => p.OtherTableName.HasValue()))
{
TableSchema thisTable = tables[item.OtherTableName];
TableSchema otherTable = tables[item.ThisTableName];
IColumnSchema thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.OtherKey);
IColumnSchema otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey);
thisTable.AddFK(new ForeignKeySchema
{
ThisTable = thisTable,
Name = item.Name,
ThisKey = thisKey,
OtherTable = otherTable,
OtherKey = otherKey
});
}
databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray();
databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray();
return(databaseSchema);
}
public override IDatabaseSchema Load(DbConfiguration cfg)
{
var databaseSchema = new DatabaseSchema();
DataTable allColumns = null;
DataTable pkTable = null;
DataTable fkTable = null;
DataTable viewTable = null;
using (var conn = new OleDbConnection(cfg.ConnectionString))
{
conn.Open();
allColumns = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
pkTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
fkTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
viewTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Views, null);
}
Dictionary <string, TableSchema> tables = new Dictionary <string, TableSchema>();
foreach (var item in allColumns.Rows.Cast <DataRow>())
{
var tableName = item["TABLE_NAME"] as string;
var tableSchema = item["TABLE_SCHEMA"] as string;
var tableCatalog = item["TABLE_CATALOG"] as string;
var c = new ColumnSchema();
c.ColumnName = item["COLUMN_NAME"] as string;
c.Order = Convert.ToInt32(item["ORDINAL_POSITION"]);
if (string.IsNullOrEmpty(item["NUMERIC_PRECISION"] as string))
{
c.Precision = 0;
}
else
{
c.Precision = (int)item["NUMERIC_PRECISION"];
}
if (string.IsNullOrEmpty(item["NUMERIC_SCALE"] as string))
{
c.Scale = 0;
}
else
{
c.Scale = (int)(item["NUMERIC_SCALE"]);
}
if (string.IsNullOrEmpty(item["CHARACTER_MAXIMUM_LENGTH"] as string))
{
c.Length = 0;
}
else
{
c.Length = (int)item["CHARACTER_MAXIMUM_LENGTH"];
}
c.DefaultValue = item["COLUMN_DEFAULT"] as string;
c.Comment = item["DESCRIPTION"] as string;
c.DbType = ParseDbType((int)item["DATA_TYPE"]);
c.Type = ParseType(c.DbType);
foreach (var p in pkTable.Rows.Cast <DataRow>())
{
if (tableName == p["TABLE_NAME"] as string && c.ColumnName == p["COLUMN_NAME"] as string)
{
c.IsPrimaryKey = true;
break;
}
}
if (!item.IsNull("IS_NULLABLE"))
{
c.IsNullable = (bool)item["IS_NULLABLE"];
}
TableSchema table = null;
string key = string.Concat(tableCatalog, tableSchema, tableName);
if (!tables.TryGetValue(key, out table))
{
table = new TableSchema {
TableName = tableName, Schema = tableSchema
};
tables[tableName] = table;
}
table.AddColumn(c);
}
foreach (var item in fkTable.Rows.Cast <DataRow>())
{
var tableSchema = item["PK_TABLE_SCHEMA"] as string;
var tableCatalog = item["PK_TABLE_CATALOG"] as string;
TableSchema thisTable = null;
TableSchema otherTable = null;
IColumnSchema thisKey = null;
IColumnSchema otherKey = null;
var key = string.Concat(tableCatalog, tableSchema, item["FK_TABLE_NAME"] as string);
if (tables.TryGetValue(key, out thisTable))
{
thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item["FK_COLUMN_NAME"] as string);
}
key = string.Concat(tableCatalog, tableSchema, item["PK_TABLE_NAME"] as string);
if (tables.TryGetValue(key, out otherTable))
{
otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item["PK_COLUMN_NAME"] as string);
}
thisTable.AddFK(new ForeignKeySchema
{
ThisTable = thisTable,
Name = item["FK_NAME"] as string,
ThisKey = thisKey,
OtherTable = otherTable,
OtherKey = otherKey
});
}
foreach (var item in viewTable.Rows.Cast <DataRow>())
{
var tableName = item["TABLE_NAME"] as string;
var tableSchema = item["TABLE_SCHEMA"] as string;
var tableCatalog = item["TABLE_CATALOG"] as string;
TableSchema table = null;
var key = string.Concat(tableCatalog, tableSchema, tableName);
if (tables.TryGetValue(key, out table))
{
table.IsView = true;
}
}
databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray();
databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray();
//allColumns.WriteXmlSchema(Console.Out);
//pkTable.WriteXmlSchema(Console.Out);
//fkTable.WriteXmlSchema(Console.Out);
//viewTable.WriteXmlSchema(Console.Out);
return(databaseSchema);
}
public virtual IDatabaseSchema Load(DbConfiguration cfg)
{
var databaseSchema = new DatabaseSchema();
ColumnInfo[] allColumns = null;
ConstraintInfo[] allConstraints = null;
ForeignKeyInfo[] allFks = null;
using (var ctx = cfg.CreateDbContext())
{
InitConnection(ctx.Connection);
using (var reader = ctx.DbHelper.ExecuteReader(AllColumnsSql))
allColumns = reader.ToList <ColumnInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllConstraintsSql))
allConstraints = reader.ToList <ConstraintInfo>().ToArray();
using (var reader = ctx.DbHelper.ExecuteReader(AllFKsSql))
allFks = reader.ToList <ForeignKeyInfo>().ToArray();
}
PopulateConstraints(allColumns, allConstraints);
Dictionary <string, TableSchema> tables = new Dictionary <string, TableSchema>();
PopulateTables(allColumns, tables);
foreach (var item in allFks)
{
TableSchema thisTable = null;
TableSchema otherTable = null;
IColumnSchema thisKey = null;
IColumnSchema otherKey = null;
var key = string.Format("{0}.{1}", item.ThisSchema, item.ThisTableName);
if (tables.TryGetValue(key, out thisTable))
{
thisKey = thisTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.ThisKey);
}
key = string.Format("{0}.{1}", item.OtherSchema, item.OtherTableName);
if (tables.TryGetValue(key, out otherTable))
{
otherKey = otherTable.AllColumns.FirstOrDefault(p => p.ColumnName == item.OtherKey);
}
thisTable.AddFK(new ForeignKeySchema
{
ThisTable = thisTable
,
Name = item.Name
,
ThisKey = thisKey
,
OtherTable = otherTable
,
OtherKey = otherKey
});
}
databaseSchema.Tables = tables.Values.Where(p => !p.IsView).ToArray();
databaseSchema.Views = tables.Values.Where(p => p.IsView).ToArray();
return(databaseSchema);
}
