public override void GetTables(Common.Entities.MetaDataSchema.Project project)
{
foreach (Entities.MetaDataSchema.Database dbase in project.Databases)
{
System.Data.DataTable tables = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
tables.Load(project.ExtractorManager.SelectStatement("Select * From " + dbase.Name + ".INFORMATION_SCHEMA.TABLES Where Table_Type = '" + Resources.DataStructure.TableType + "'"), LoadOption.OverwriteChanges);
sqlServerTable.Load(project.ExtractorManager.SelectStatement("Select * From " + dbase.Name + ".sys.all_objects Where Type_Desc = 'user_table'"), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(dbase.Name,"Tables","Database"));
dbase.Tables.Clear();
foreach (DataRow row in tables.Rows)
{
Entities.MetaDataSchema.Table tbl = new Common.Entities.MetaDataSchema.Table();
tbl.ParentDatabase = dbase;
tbl.Schema = row["Table_Schema"].ToString();
tbl.Name = row["Table_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + tbl.Name + "'");
if (sqlRows.Length > 0)
{
tbl.TableID = sqlRows[0]["object_id"].ToString();
}
if (project.CheckHasData)
{
tbl.DataCount = CountRecordsInTable(tbl);
tbl.HasData = tbl.DataCount > 0;
}
dbase.Tables.Add(tbl);
}
OnEndLoading(new Common.Events.LoadingEventArgs(dbase.Name, "Tables","Database"));
tables.Dispose();
}
GC.Collect();
}
public override void GetTables(Common.Entities.MetaDataSchema.Project project)
{
foreach (Entities.MetaDataSchema.Database dbase in project.Databases)
{
System.Data.DataTable tables = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
tables.Load(project.ExtractorManager.SelectStatement("Select * From " + dbase.Name + ".INFORMATION_SCHEMA.TABLES Where Table_Type = '" + Resources.DataStructure.TableType + "'"), LoadOption.OverwriteChanges);
sqlServerTable.Load(project.ExtractorManager.SelectStatement("Select * From " + dbase.Name + ".sys.all_objects Where Type_Desc = 'user_table'"), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(dbase.Name, "Tables", "Database"));
dbase.Tables.Clear();
foreach (DataRow row in tables.Rows)
{
Entities.MetaDataSchema.Table tbl = new Common.Entities.MetaDataSchema.Table();
tbl.ParentDatabase = dbase;
tbl.Schema = row["Table_Schema"].ToString();
tbl.Name = row["Table_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + tbl.Name + "'");
if (sqlRows.Length > 0)
{
tbl.TableID = sqlRows[0]["object_id"].ToString();
}
if (project.CheckHasData)
{
tbl.DataCount = CountRecordsInTable(tbl);
tbl.HasData = tbl.DataCount > 0;
}
dbase.Tables.Add(tbl);
}
OnEndLoading(new Common.Events.LoadingEventArgs(dbase.Name, "Tables", "Database"));
tables.Dispose();
}
GC.Collect();
}
public override void GetTables(Common.Entities.MetaDataSchema.Database database)
{
System.Data.DataTable tables = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
tables.Load(database.ParentProject.ExtractorManager.SelectStatement(String.Format("Select * From {0}.INFORMATION_SCHEMA.TABLES Where Table_Type = '{1}'", database.Name, Resources.DataStructure.TableType)), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement(String.Format("Select * From {0}.sys.all_objects Where Type_Desc = 'user_table'", database.Name)), LoadOption.OverwriteChanges);
database.Tables.Clear();
foreach (DataRow row in tables.Rows)
{
Entities.MetaDataSchema.Table tbl = new Common.Entities.MetaDataSchema.Table();
tbl.ParentDatabase = database;
tbl.Schema = row["Table_Schema"].ToString();
tbl.Name = row["Table_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + tbl.Name + "'");
if (sqlRows.Length > 0)
{
tbl.TableID = sqlRows[0]["object_id"].ToString();
}
if(database.ParentProject.CheckHasData)
{
tbl.DataCount = CountRecordsInTable(tbl);
tbl.HasData = tbl.DataCount > 0;
}
database.Tables.Add(tbl);
}
}
public override void GetTables(Common.Entities.MetaDataSchema.Database database)
{
System.Data.DataTable tables = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
tables.Load(database.ParentProject.ExtractorManager.SelectStatement(String.Format("Select * From {0}.INFORMATION_SCHEMA.TABLES Where Table_Type = '{1}'", database.Name, Resources.DataStructure.TableType)), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement(String.Format("Select * From {0}.sys.all_objects Where Type_Desc = 'user_table'", database.Name)), LoadOption.OverwriteChanges);
database.Tables.Clear();
foreach (DataRow row in tables.Rows)
{
Entities.MetaDataSchema.Table tbl = new Common.Entities.MetaDataSchema.Table();
tbl.ParentDatabase = database;
tbl.Schema = row["Table_Schema"].ToString();
tbl.Name = row["Table_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + tbl.Name + "'");
if (sqlRows.Length > 0)
{
tbl.TableID = sqlRows[0]["object_id"].ToString();
}
if (database.ParentProject.CheckHasData)
{
tbl.DataCount = CountRecordsInTable(tbl);
tbl.HasData = tbl.DataCount > 0;
}
database.Tables.Add(tbl);
}
}
public override void GetChildRelations(Common.Entities.MetaDataSchema.Table table)
{
DataTable relations = new DataTable();
string query = string.Format(@"select * From
(select sv.*,cu.COLUMN_NAME as ParentColumn,cu.TABLE_CATALOG as ParentCatalog,cu.TABLE_NAME as ParentTable,cu.TABLE_SCHEMA as ParentSchema
From
(select r.CONSTRAINT_NAME,r.UNIQUE_CONSTRAINT_CATALOG,r.UNIQUE_CONSTRAINT_NAME,r.UNIQUE_CONSTRAINT_SCHEMA,r.UPDATE_RULE,t.CONSTRAINT_CATALOG,t.CONSTRAINT_SCHEMA,t.CONSTRAINT_TYPE,t.TABLE_CATALOG,t.TABLE_NAME,t.TABLE_SCHEMA,c.COLUMN_NAME,c.ORDINAL_POSITION
From {0}.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as r
INNER JOIN {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as t on r.CONSTRAINT_NAME = t.CONSTRAINT_NAME
INNER JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c on
t.CONSTRAINT_NAME = c.CONSTRAINT_NAME) as sv Inner Join
{0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as cu on sv.UNIQUE_CONSTRAINT_NAME = cu.CONSTRAINT_NAME
) as mview
where ParentTable = '{2}' AND ParentSchema = '{1}'
", table.ParentDatabase.Name, table.Schema, table.Name);
List <Common.Entities.MetaDataSchema.RelationShip> _ChildRelationShips = new List <Common.Entities.MetaDataSchema.RelationShip>();
relations.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
foreach (DataRow row in relations.Rows)
{
Common.Entities.MetaDataSchema.RelationShip relationShip = new Entities.MetaDataSchema.RelationShip();
relationShip.Name = row["CONSTRAINT_NAME"].ToString();
relationShip.ChildTable = new Entities.MetaDataSchema.Table(row["TABLE_NAME"].ToString(), row["TABLE_SCHEMA"].ToString(), table.ParentDatabase);
relationShip.ChildColumn = new Entities.MetaDataSchema.Column(row["COLUMN_NAME"].ToString());
relationShip.MasterTable = new Entities.MetaDataSchema.Table(row["ParentTable"].ToString(), row["ParentSchema"].ToString(), table.ParentDatabase);
relationShip.ParentColumn = new Entities.MetaDataSchema.Column(row["ParentColumn"].ToString());
_ChildRelationShips.Add(relationShip);
}
table.ChildRelationShips = _ChildRelationShips;
}
public int CountRecordsInTable(Common.Entities.MetaDataSchema.Table table)
{
IDataReader reader = table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(string.Format("select isnull(count(*),0) from [{0}].[{1}].[{2}]", table.ParentDatabase.Name, table.Schema, table.Name));
int result = 0;
if (reader.Read())
{
result = reader.GetInt32(0);
}
return(result);
}
public override void GetTableRelations(Common.Entities.MetaDataSchema.Table table)
{
GetParentRelations(table);
GetChildRelations(table);
}
public override void GetColumns(Common.Entities.MetaDataSchema.Table table)
{
System.Data.DataTable tables = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
//System.Data.DataTable sqlObjectsTable = new DataTable();
System.Data.DataTable keyConstraints = new DataTable();
tables.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(String.Format("Select * From {0}.INFORMATION_SCHEMA.COLUMNS", table.ParentDatabase.Name)), LoadOption.OverwriteChanges);
//sqlObjectsTable.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement("Select * From " + table.ParentDatabase.Name + ".sys.objects"), LoadOption.OverwriteChanges);
sqlServerTable.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(String.Format("Select * From {0}.sys.all_columns", table.ParentDatabase.Name)), LoadOption.OverwriteChanges);
string query = String.Format("Select c.Constraint_Type,t.Column_Name,c.Table_Name,c.Table_Schema From {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as c Inner join {0}.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as t on c.Constraint_Name = t.Constraint_Name", table.ParentDatabase.Name);
//Select * From INFORMATION_SCHEMA.KEY_COLUMN_USAGE
//"Select * From " + table.ParentDatabase.Name + ".INFORMATION_SCHEMA.KEY_COLUMN_USAGE"
keyConstraints.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
DataRow[] standRows = tables.Select(String.Format("Table_Schema = '{0}' AND Table_Name = '{1}'", table.Schema, table.Name));
DataRow[] sqlRows = null;// sqlServerTable.Select("object_id = " + table.TableID);
DataRow[] keyRows = null;
OnStartLoading(new Common.Events.LoadingEventArgs(table.Name, "Columns", "Table"));
table.Columns.Clear();
foreach (DataRow row in standRows)
{
Entities.MetaDataSchema.Column col = new Common.Entities.MetaDataSchema.Column();
col.ParentTable = table;
col.Name = row["Column_Name"].ToString();
col.OrdinalPosition = Convert.ToInt32(row["Ordinal_Position"]);
if (row["Is_Nullable"].ToString().ToLower() == "yes")
{
col.AllowNull = true;
}
else
{
col.AllowNull = false;
}
col.ColumnDataType.SQLType = row["Data_Type"].ToString();
if (row["Character_Maximum_Length"] != DBNull.Value)
{
col.Length = Convert.ToInt32(row["Character_Maximum_Length"]);
}
sqlRows = sqlServerTable.Select("object_id = " + table.TableID + " AND [name] = '" + col.Name + "'");
if (sqlRows.Length > 0)
{
col.IsIdentity = Convert.ToBoolean(sqlRows[0]["is_identity"]);
col.IsComputed = Convert.ToBoolean(sqlRows[0]["is_computed"]);
col.Precision = Convert.ToInt32(sqlRows[0]["precision"]);
col.Scale = Convert.ToInt32(sqlRows[0]["scale"]);
col.ColumnId = sqlRows[0]["column_id"].ToString();
keyRows = keyConstraints.Select("Table_Schema = '" + table.Schema + "' AND Table_Name = '" + table.Name + "' AND Column_Name = '" + col.Name + "'");
foreach (DataRow key in keyRows)
{
if (key["Constraint_Type"].ToString() == "PRIMARY KEY")
{
col.IsPrimary = true;
}
else if (key["Constraint_Type"].ToString() == "FOREIGN KEY")
{
col.IsForeign = true;
}
}
//keyRows = keyConstraints.Select("TABLE_Name = '" + table.Name + "' AND Column_Name = '" + col.Name + "'");
//foreach(DataRow key in keyRows)
//{
// string ordinalKey = key["Column_Name"].ToString();
// DataRow[] objectRelationRow = sqlObjectsTable.Select("name = '" + key["Constraint_Name"] + "'");
// if (objectRelationRow.Length > 0)
// {
// if (objectRelationRow[0]["type"].ToString().Trim() == "PK")
// col.IsPrimary = true;
// else if ((objectRelationRow[0]["type"].ToString().Trim() == "FK" || objectRelationRow[0]["type"].ToString().Trim() == "F"))
// col.IsForeign = true;
// }
//}
}
table.Columns.Add(col);
}
OnEndLoading(new Common.Events.LoadingEventArgs(table.Name, "Columns", "Table"));
GC.Collect();
}
