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 GetDatabases(Common.Entities.MetaDataSchema.Project project)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(project.ExtractorManager.ConnectionString);
conn.Open();
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(connection);
project.Databases.Clear();
foreach (Microsoft.SqlServer.Management.Smo.Database database in server.Databases)
{
Entities.MetaDataSchema.Database db = new Common.Entities.MetaDataSchema.Database();
db.Name = database.Name;
db.ParentProject = project;
project.Databases.Add(db);
project.AddDatabaseNode(project.SchemaDataset.Schema, db.GuidId, "", db.Name, db.Name);
}
}
public override void GetDatabases(Common.Entities.MetaDataSchema.Project project)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(project.ExtractorManager.ConnectionString);
conn.Open();
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection(conn);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(connection);
project.Databases.Clear();
foreach (Microsoft.SqlServer.Management.Smo.Database database in server.Databases)
{
Entities.MetaDataSchema.Database db = new Common.Entities.MetaDataSchema.Database();
db.Name = database.Name;
db.ParentProject = project;
project.Databases.Add(db);
project.AddDatabaseNode(project.SchemaDataset.Schema, db.GuidId, "", db.Name, db.Name);
}
}
public override void GetFunctions(Common.Entities.MetaDataSchema.Database database)
{
System.Data.DataTable functions = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
functions.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE = 'FUNCTION'"), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".sys.all_objects Where [Type] = 'FN'"), LoadOption.OverwriteChanges);
database.Functions.Clear();
foreach (DataRow row in functions.Rows)
{
Entities.MetaDataSchema.Function function = new Common.Entities.MetaDataSchema.Function();
function.ParentDatabase = database;
function.Schema = row["Routine_Schema"].ToString();
function.Name = row["Routine_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + function.Name + "'");
if (sqlRows.Length > 0)
{
function.FunctionId = sqlRows[0]["object_id"].ToString();
}
//GetColumns(view);
database.Functions.Add(function);
}
}
public override void GetViews(Common.Entities.MetaDataSchema.Database database)
{
System.Data.DataTable views = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
views.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".INFORMATION_SCHEMA.VIEWS Order By Table_Name"), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".sys.all_objects Where Type_Desc = 'VIEW'"), LoadOption.OverwriteChanges);
database.Views.Clear();
foreach (DataRow row in views.Rows)
{
Entities.MetaDataSchema.View view = new Common.Entities.MetaDataSchema.View();
view.ParentDatabase = database;
view.Schema = row["Table_Schema"].ToString();
view.Name = row["Table_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + view.Name + "'");
if (sqlRows.Length > 0)
{
view.ViewID = sqlRows[0]["object_id"].ToString();
}
//GetColumns(view);
database.Views.Add(view);
}
}
public override void GetColumns(Common.Entities.MetaDataSchema.Database database)
{
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(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".INFORMATION_SCHEMA.COLUMNS"), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".sys.all_columns"), LoadOption.OverwriteChanges);
string query = "Select c.Constraint_Type,t.Column_Name,c.Table_Name,c.Table_Schema From " + database.Name + ".INFORMATION_SCHEMA.TABLE_CONSTRAINTS as c Inner join " + database.Name + ".INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as t on c.Constraint_Name = t.Constraint_Name";
//sqlObjectsTable.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement("Select * From " + table.ParentDatabase.Name + ".sys.objects"), LoadOption.OverwriteChanges);
keyConstraints.Load(database.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
foreach (Entities.MetaDataSchema.Table table in database.Tables)
{
OnStartLoading(new Common.Events.LoadingEventArgs(table.Name, "Columns", "Table"));
DataRow[] standRows = tables.Select("Table_Schema = '" + table.Schema + "' AND Table_Name = '" + table.Name + "'");
DataRow[] sqlRows = null;// sqlServerTable.Select("object_id = " + table.TableID);
DataRow[] keyRows = null;
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]["object_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;
}
}
}
table.Columns.Add(col);
}
OnEndLoading(new Common.Events.LoadingEventArgs(table.Name, "Columns", "Table"));
}
GC.Collect();
}
