public override void GetFunctionParameters(Common.Entities.MetaDataSchema.Function function)
{
System.Data.DataTable functions = new DataTable();
string query = "select " + function.ParentDatabase.Name + ".sys.all_parameters.*," + function.ParentDatabase.Name + ".sys.types.name as typename From " + function.ParentDatabase.Name + ".sys.all_parameters inner join " + function.ParentDatabase.Name + ".sys.types on " + function.ParentDatabase.Name + ".sys.all_parameters.system_type_id = " + function.ParentDatabase.Name + ".sys.types.system_type_id and " + function.ParentDatabase.Name + ".sys.all_parameters.user_type_id = " + function.ParentDatabase.Name + ".sys.types.user_type_id where object_id = " + function.FunctionId;
functions.Load(function.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(function.Name, "Parameters", "Function"));
function.Parameters.Clear();
foreach (DataRow row in functions.Rows)
{
Entities.MetaDataSchema.Column col = new Common.Entities.MetaDataSchema.Column();
col.ParentFunction = function;
col.Name = row["name"].ToString();
col.OrdinalPosition = Convert.ToInt32(row["parameter_id"]);
col.Precision = Convert.ToInt32(row["precision"]);
col.Scale = Convert.ToInt32(row["scale"]);
col.AllowNull = false;
col.ColumnDataType.SQLType = row["typename"].ToString();
if (row["max_length"] != DBNull.Value)
{
col.Length = Convert.ToInt32(row["max_length"]) / 2;
}
col.IsIdentity = false;
col.IsComputed = false;
col.ColumnId = row["parameter_id"].ToString();
if (row["is_output"].ToString() == "1" || row["is_output"].ToString() == "True")
{
if (col.Name == "")
{
function.ResultType.SQLType = col.ColumnDataType.SQLType;
}
col.ColumnDirection = ColumnDirection.Output;
function.Parameters.Add(col);
}
else
{
col.ColumnDirection = ColumnDirection.Input;
function.Parameters.Add(col);
}
}
OnEndLoading(new Common.Events.LoadingEventArgs(function.Name, "Parameters", "Function"));
GC.Collect();
}
public override void GetColumns(Common.Entities.MetaDataSchema.View view)
{
System.Data.DataTable views = new DataTable();
string query = String.Format("select {0}.sys.all_columns.*,{0}.sys.types.name as typename From {0}.sys.all_columns inner join {0}.sys.types on {0}.sys.all_columns.system_type_id = {0}.sys.types.system_type_id where object_id = {1}", view.ParentDatabase.Name, view.ViewID);
views.Load(view.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(view.Name, "Columns", "View"));
view.Columns.Clear();
foreach (DataRow row in views.Rows)
{
Entities.MetaDataSchema.Column col = new Common.Entities.MetaDataSchema.Column();
col.ParentView = view;
col.Name = row["name"].ToString();
col.OrdinalPosition = Convert.ToInt32(row["column_id"]);
col.Precision = Convert.ToInt32(row["precision"]);
col.Scale = Convert.ToInt32(row["scale"]);
if (row["is_nullable"].ToString().ToLower() == "yes" || row["is_nullable"].ToString().ToLower() == "1" || row["is_nullable"].ToString().ToLower() == "true")
{
col.AllowNull = true;
}
else
{
col.AllowNull = false;
}
col.ColumnDataType.SQLType = row["typename"].ToString();
if (row["max_length"] != DBNull.Value)
{
col.Length = Convert.ToInt32(row["max_length"]) / 2;
}
col.IsIdentity = Convert.ToBoolean(row["is_identity"]);
col.IsComputed = Convert.ToBoolean(row["is_computed"]);
col.ColumnId = row["column_id"].ToString();
view.Columns.Add(col);
}
OnEndLoading(new Common.Events.LoadingEventArgs(view.Name, "Columns", "View"));
GC.Collect();
}
public override void GetFunctionParameters(Common.Entities.MetaDataSchema.Function function)
{
System.Data.DataTable functions = new DataTable();
string query = "select " + function.ParentDatabase.Name + ".sys.all_parameters.*," + function.ParentDatabase.Name + ".sys.types.name as typename From " + function.ParentDatabase.Name + ".sys.all_parameters inner join " + function.ParentDatabase.Name + ".sys.types on " + function.ParentDatabase.Name + ".sys.all_parameters.system_type_id = " + function.ParentDatabase.Name + ".sys.types.system_type_id and " + function.ParentDatabase.Name + ".sys.all_parameters.user_type_id = " + function.ParentDatabase.Name + ".sys.types.user_type_id where object_id = " + function.FunctionId;
functions.Load(function.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(function.Name, "Parameters","Function"));
function.Parameters.Clear();
foreach (DataRow row in functions.Rows)
{
Entities.MetaDataSchema.Column col = new Common.Entities.MetaDataSchema.Column();
col.ParentFunction = function;
col.Name = row["name"].ToString();
col.OrdinalPosition = Convert.ToInt32(row["parameter_id"]);
col.Precision = Convert.ToInt32(row["precision"]);
col.Scale = Convert.ToInt32(row["scale"]);
col.AllowNull = false;
col.ColumnDataType.SQLType = row["typename"].ToString();
if (row["max_length"] != DBNull.Value)
{
col.Length = Convert.ToInt32(row["max_length"]) / 2;
}
col.IsIdentity = false;
col.IsComputed = false;
col.ColumnId = row["parameter_id"].ToString();
if (row["is_output"].ToString() == "1" || row["is_output"].ToString() == "True")
{
if (col.Name == "")
function.ResultType.SQLType = col.ColumnDataType.SQLType;
col.ColumnDirection = ColumnDirection.Output;
function.Parameters.Add(col);
}
else
{
col.ColumnDirection = ColumnDirection.Input;
function.Parameters.Add(col);
}
}
OnEndLoading(new Common.Events.LoadingEventArgs(function.Name, "Parameters","Function"));
GC.Collect();
}
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();
}
public override void GetColumns(Common.Entities.MetaDataSchema.View view)
{
System.Data.DataTable views = new DataTable();
string query = String.Format("select {0}.sys.all_columns.*,{0}.sys.types.name as typename From {0}.sys.all_columns inner join {0}.sys.types on {0}.sys.all_columns.system_type_id = {0}.sys.types.system_type_id where object_id = {1}", view.ParentDatabase.Name, view.ViewID);
views.Load(view.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(view.Name, "Columns","View"));
view.Columns.Clear();
foreach (DataRow row in views.Rows)
{
Entities.MetaDataSchema.Column col = new Common.Entities.MetaDataSchema.Column();
col.ParentView = view;
col.Name = row["name"].ToString();
col.OrdinalPosition = Convert.ToInt32(row["column_id"]);
col.Precision = Convert.ToInt32(row["precision"]);
col.Scale = Convert.ToInt32(row["scale"]);
if (row["is_nullable"].ToString().ToLower() == "yes" || row["is_nullable"].ToString().ToLower() == "1" || row["is_nullable"].ToString().ToLower() == "true")
col.AllowNull = true;
else
col.AllowNull = false;
col.ColumnDataType.SQLType = row["typename"].ToString();
if (row["max_length"] != DBNull.Value)
{
col.Length = Convert.ToInt32(row["max_length"])/2;
}
col.IsIdentity = Convert.ToBoolean(row["is_identity"]);
col.IsComputed = Convert.ToBoolean(row["is_computed"]);
col.ColumnId = row["column_id"].ToString();
view.Columns.Add(col);
}
OnEndLoading(new Common.Events.LoadingEventArgs(view.Name, "Columns","View"));
GC.Collect();
}
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();
}