public override void GetStoredProcedures(Common.Entities.MetaDataSchema.Project project)
{
foreach (Entities.MetaDataSchema.Database database in project.Databases)
{
System.Data.DataTable procedures = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
procedures.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE = 'PROCEDURE'"), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".sys.all_objects Where [Type] = 'P'"), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(database.Name, "Stored Procedures", "Database"));
database.StoredProcedures.Clear();
foreach (DataRow row in procedures.Rows)
{
Entities.MetaDataSchema.StoredProcedure storedProcedure = new Common.Entities.MetaDataSchema.StoredProcedure();
storedProcedure.ParentDatabase = database;
storedProcedure.Schema = row["Routine_Schema"].ToString();
storedProcedure.Name = row["Routine_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + storedProcedure.Name + "'");
if (sqlRows.Length > 0)
{
storedProcedure.StoredProcedureId = sqlRows[0]["object_id"].ToString();
}
//GetColumns(view);
database.StoredProcedures.Add(storedProcedure);
}
OnEndLoading(new Common.Events.LoadingEventArgs(database.Name, "Stored Procedures", "Database"));
procedures.Dispose();
}
GC.Collect();
}
public override void GetStoredProcedureParameters(Common.Entities.MetaDataSchema.StoredProcedure procedure)
{
System.Data.DataTable functions = new DataTable();
string query = "select " + procedure.ParentDatabase.Name + ".sys.all_parameters.*," + procedure.ParentDatabase.Name + ".sys.types.name as typename From " + procedure.ParentDatabase.Name + ".sys.all_parameters inner join " + procedure.ParentDatabase.Name + ".sys.types on " + procedure.ParentDatabase.Name + ".sys.all_parameters.system_type_id = " + procedure.ParentDatabase.Name + ".sys.types.system_type_id and " + procedure.ParentDatabase.Name + ".sys.all_parameters.user_type_id = " + procedure.ParentDatabase.Name + ".sys.types.user_type_id where object_id = " + procedure.StoredProcedureId;
functions.Load(procedure.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(procedure.Name, "Parameters", "Stored Procedures"));
procedure.Parameters.Clear();
foreach (DataRow row in functions.Rows)
{
Entities.MetaDataSchema.Column col = new Common.Entities.MetaDataSchema.Column();
col.ParentStoredProcedure = procedure;
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")
{
col.ColumnDirection = ColumnDirection.Output;
procedure.Parameters.Add(col);
}
else
{
col.ColumnDirection = ColumnDirection.Input;
procedure.Parameters.Add(col);
}
}
OnEndLoading(new Common.Events.LoadingEventArgs(procedure.Name, "Parameters", "Stored Procedures"));
GC.Collect();
}
public override void GetStoredProcedures(Common.Entities.MetaDataSchema.Project project)
{
foreach (Entities.MetaDataSchema.Database database in project.Databases)
{
System.Data.DataTable procedures = new DataTable();
System.Data.DataTable sqlServerTable = new DataTable();
procedures.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE = 'PROCEDURE'"), LoadOption.OverwriteChanges);
sqlServerTable.Load(database.ParentProject.ExtractorManager.SelectStatement("Select * From " + database.Name + ".sys.all_objects Where [Type] = 'P'"), LoadOption.OverwriteChanges);
OnStartLoading(new Common.Events.LoadingEventArgs(database.Name, "Stored Procedures", "Database"));
database.StoredProcedures.Clear();
foreach (DataRow row in procedures.Rows)
{
Entities.MetaDataSchema.StoredProcedure storedProcedure = new Common.Entities.MetaDataSchema.StoredProcedure();
storedProcedure.ParentDatabase = database;
storedProcedure.Schema = row["Routine_Schema"].ToString();
storedProcedure.Name = row["Routine_Name"].ToString();
DataRow[] sqlRows = sqlServerTable.Select("[name] = '" + storedProcedure.Name + "'");
if (sqlRows.Length > 0)
{
storedProcedure.StoredProcedureId = sqlRows[0]["object_id"].ToString();
}
//GetColumns(view);
database.StoredProcedures.Add(storedProcedure);
}
OnEndLoading(new Common.Events.LoadingEventArgs(database.Name, "Stored Procedures", "Database"));
procedures.Dispose();
}
GC.Collect();
}
