// get arguments for stand-alone stored procedures/functions
private void GetProcedureArguments(MetaProcedure proc)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
// GET ARGUMENTS FOR STAND-ALONE PROCEDURES/FUNCTIONS
string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
" POSITION, SEQUENCE, "+
" IN_OUT AS DIRECTION, "+
" DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE "+
"FROM SYS.ALL_ARGUMENTS " +
"WHERE OWNER = '" + proc.Owner + "' " +
"AND OBJECT_NAME = '" + proc.Name + "' " +
"AND PACKAGE_NAME IS NULL " +
"AND DATA_LEVEL = 0 AND DATA_TYPE IS NOT NULL " +
"ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
string procType = "Procedures";
while (reader.Read())
{
string procName = reader.GetString(0);
string argName = reader.GetString(2);
if (argName.Equals("(RETURN)"))
{
procType = "Functions";
}
else
{
procType = "Procedures";
}
string direction = reader.GetString(5);
if (argName.Equals("(RETURN)"))
{
direction = String.Empty;
}
string dataType = reader.GetString(6);
MetaProcedureArgument arg = new MetaProcedureArgument(proc.Owner, procName, procType,
argName, direction, dataType);
proc.Arguments.Add(arg);
}
reader.Close();
reader = null;
}
// get the MetaProcedure given its name. if they are overloaded, it gets
// the first MetaProcedure for the name
public MetaProcedure this[string name] {
get {
MetaProcedure p = null;
foreach (object o in list)
{
p = (MetaProcedure)o;
if (p.Name.Equals(name))
{
return(p);
}
}
throw new Exception("MetaProcedure not found");
}
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaProcedureCollection procs = new MetaProcedureCollection();
string sql =
"SELECT OWNER, OBJECT_NAME, " +
" DECODE(OBJECT_TYPE,'PROCEDURE','Procedures','FUNCTION','Functions','PACKAGE','Packages') AS OBJ_TYPE " +
"FROM ALL_OBJECTS " +
"WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') " +
"ORDER BY 3, 1, 2";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string procOwner = reader.GetValue(0).ToString();
string procName = reader.GetValue(1).ToString();
string procType = reader.GetValue(2).ToString();
MetaProcedure proc = null;
if (procType.Equals("Packages"))
{
proc = new MetaProcedure(procOwner, procName, procType, true);
GetPackageProcedures(proc);
}
else
{
proc = new MetaProcedure(procOwner, procName, procType);
GetProcedureArguments(proc);
}
// add to stored object to collection
procs.Add(proc);
}
reader.Close();
reader = null;
return(procs);
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaProcedureCollection procs = new MetaProcedureCollection();
string sql =
"SELECT routine_schema, routine_name, routine_type " +
"FROM information_schema.ROUTINES " +
"ORDER BY 3,1,2";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string procSchema = reader.GetString(0);
string procName = reader.GetString(1);
string procType = reader.GetString(2);
if (procType.Equals("FUNCTION"))
{
procType = "Functions";
}
else if (procType.Equals("PROCEDURE"))
{
procType = "Procedures";
}
MetaProcedure proc = new MetaProcedure(procSchema, procName, procType);
procs.Add(proc);
}
reader.Close();
reader = null;
return(procs);
}
public MetaProcedure[] GetProcedures(string name)
{
ArrayList list = new ArrayList();
foreach (object o in list)
{
MetaProcedure p = (MetaProcedure)o;
if (p.Name.Equals(name))
{
list.Add(o);
}
}
if (list.Count == 0)
{
throw new Exception("MetaProcedure not found");
}
return((MetaProcedure[])list.ToArray(typeof(MetaProcedure)));
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaProcedureCollection procs = new MetaProcedureCollection();
// starting with MySQL 5.0, it supports stored procedures
// prior version of MySQL did not have the INFORMATION_SCHEMA either
if (!infoSchemaExists)
{
return(procs);
}
string sql =
"SELECT routine_schema, routine_name " +
" FROM information_schema.ROUTINES";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string procSchema = reader.GetString(0);
string procName = reader.GetString(1);
string procType = "Procedures";
MetaProcedure proc = new MetaProcedure(procSchema, procName, procType);
procs.Add(proc);
}
reader.Close();
reader = null;
return(procs);
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaProcedureCollection procs = new MetaProcedureCollection();
string sql =
"SELECT su.name AS proc_owner, so.name as proc_name, " +
" CASE so.xtype " +
" WHEN 'P' THEN 'Stored Procedures' " +
" WHEN 'X' THEN 'Extended Procedures' " +
" WHEN 'FN' THEN 'Functions' " +
" END AS proc_type_desc " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE xtype in ('P', 'X', 'FN') " +
"AND su.uid = so.uid " +
"ORDER BY 3, 1, 2";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MetaProcedure proc = new MetaProcedure(reader.GetString(0),
reader.GetString(1), reader.GetString(2));
procs.Add(proc);
}
reader.Close();
reader = null;
return(procs);
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
MetaProcedureCollection procs = new MetaProcedureCollection();
DataTable table2 = null;
DataRow row2 = null;
table2 = GetSchema("Procedures", new string[] { null, null, null });
for (int r = 0; r < table2.Rows.Count; r++)
{
row2 = table2.Rows[r];
string procName = row2["PROCEDURE_NAME"].ToString();
MetaProcedure proc = new MetaProcedure("", procName, "Procedures");
procs.Add(proc);
row2 = null;
}
table2 = null;
table2 = GetSchema("Functions", new string[] { null, null, null, null });
for (int r = 0; r < table2.Rows.Count; r++)
{
row2 = table2.Rows[r];
string funcName = row2["FUNCTION_NAME"].ToString();
MetaProcedure proc = new MetaProcedure("", funcName, "Functions");
procs.Add(proc);
row2 = null;
}
table2 = null;
return(procs);
}
// get procedures/functions for a given stored package and get the
// arguments for each procedure/function
private void GetPackageProcedures(MetaProcedure pkg)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
// GET ARGUMENTS FOR PROCEDURES/FUNCTIONS FOR PACKAGES
string sql =
"SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
" POSITION, SEQUENCE, "+
" IN_OUT AS DIRECTION, "+
" DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE "+
"FROM SYS.ALL_ARGUMENTS " +
"WHERE OWNER = '" + pkg.Owner + "' " +
"AND PACKAGE_NAME = '" + pkg.Name + "' " +
"AND DATA_LEVEL = 0 " +
"ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
// Notes:
// 1. an Oracle stored package can overloaded functions or procedures
// 2. stand-alone functions or procedures can not be overloaded
// 3. a procedure with no arguments will still have one row - data_type will be null
string previousProcName = "~";
string previousOverload = "~";
MetaProcedure proc = null;
string procType = "Procedures";
while (reader.Read())
{
string procName = reader.GetString(0);
string argName = reader.GetString(2);
string overload = String.Empty;
if (!reader.IsDBNull(1))
{
overload = reader.GetString(1);
}
string direction = String.Empty;
if (!reader.IsDBNull(5))
{
direction = reader.GetString(5);
}
string dataType = String.Empty;
if (!reader.IsDBNull(6))
{
dataType = reader.GetString(6);
}
if (!procName.Equals(previousProcName) || !previousOverload.Equals(overload))
{
if (argName.Equals("(RETURN)") && (!dataType.Equals(String.Empty)))
{
procType = "Functions";
direction = String.Empty;
}
else
{
procType = "Procedures";
}
proc = new MetaProcedure(String.Empty, procName, procType);
pkg.Procedures.Add(proc);
previousProcName = procName;
previousOverload = overload;
}
if (!dataType.Equals(String.Empty))
{
MetaProcedureArgument arg = new MetaProcedureArgument(pkg.Owner, procName, procType,
argName, direction, dataType);
proc.Arguments.Add(arg);
}
}
reader.Close();
reader = null;
}
public MetaProcedureCollection GetProcedures(string owner)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaProcedureCollection procs = new MetaProcedureCollection ();
// starting with MySQL 5.0, it supports stored procedures
// prior version of MySQL did not have the INFORMATION_SCHEMA either
if (!infoSchemaExists)
return procs;
string sql =
"SELECT routine_schema, routine_name " +
" FROM information_schema.ROUTINES";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while(reader.Read()) {
string procSchema = reader.GetString (0);
string procName = reader.GetString (1);
string procType = "Procedures";
MetaProcedure proc = new MetaProcedure (procSchema, procName, procType);
procs.Add (proc);
}
reader.Close ();
reader = null;
return procs;
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
con.Open();
MetaProcedureCollection procs = new MetaProcedureCollection ();
string sql =
"SELECT su.name AS proc_owner, so.name as proc_name, " +
" CASE so.xtype " +
" WHEN 'P' THEN 'Stored Procedures' " +
" WHEN 'X' THEN 'Extended Procedures' " +
" WHEN 'FN' THEN 'Functions' " +
" END AS proc_type_desc " +
"FROM dbo.sysobjects so, dbo.sysusers su " +
"WHERE xtype in ('P', 'X', 'FN') " +
"AND su.uid = so.uid " +
"ORDER BY 3, 1, 2";
IDbCommand cmd = con.CreateCommand ();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader ();
while (reader.Read ()) {
MetaProcedure proc = new MetaProcedure (reader.GetString (0),
reader.GetString (1), reader.GetString (2));
procs.Add (proc);
}
reader.Close ();
reader = null;
return procs;
}
// get procedures/functions for a given stored package and get the
// arguments for each procedure/function
private void GetPackageProcedures (MetaProcedure pkg)
{
if (con.State != ConnectionState.Open)
con.Open();
// GET ARGUMENTS FOR PROCEDURES/FUNCTIONS FOR PACKAGES
string sql =
"SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
" POSITION, SEQUENCE, " +
" IN_OUT AS DIRECTION, " +
" DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE " +
"FROM SYS.ALL_ARGUMENTS " +
"WHERE OWNER = '" + pkg.Owner + "' " +
"AND PACKAGE_NAME = '" + pkg.Name + "' " +
"AND DATA_LEVEL = 0 " +
"ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";
IDbCommand cmd = con.CreateCommand ();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader ();
// Notes:
// 1. an Oracle stored package can overloaded functions or procedures
// 2. stand-alone functions or procedures can not be overloaded
// 3. a procedure with no arguments will still have one row - data_type will be null
string previousProcName = "~";
string previousOverload = "~";
MetaProcedure proc = null;
string procType = "Procedures";
while (reader.Read ()) {
string procName = reader.GetString (0);
string argName = reader.GetString (2);
string overload = String.Empty;
if (!reader.IsDBNull (1))
overload = reader.GetString (1);
string direction = String.Empty;
if (!reader.IsDBNull (5))
direction = reader.GetString (5);
string dataType = String.Empty;
if (!reader.IsDBNull (6))
dataType = reader.GetString (6);
if (!procName.Equals (previousProcName) || !previousOverload.Equals (overload)) {
if (argName.Equals ("(RETURN)") && (!dataType.Equals (String.Empty))) {
procType = "Functions";
direction = String.Empty;
}
else
procType = "Procedures";
proc = new MetaProcedure (String.Empty, procName, procType);
pkg.Procedures.Add (proc);
previousProcName = procName;
previousOverload = overload;
}
if (!dataType.Equals (String.Empty)) {
MetaProcedureArgument arg = new MetaProcedureArgument (pkg.Owner, procName, procType,
argName, direction, dataType);
proc.Arguments.Add (arg);
}
}
reader.Close ();
reader = null;
}
// get arguments for stand-alone stored procedures/functions
private void GetProcedureArguments (MetaProcedure proc)
{
if (con.State != ConnectionState.Open)
con.Open();
// GET ARGUMENTS FOR STAND-ALONE PROCEDURES/FUNCTIONS
string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " +
" POSITION, SEQUENCE, " +
" IN_OUT AS DIRECTION, " +
" DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE " +
"FROM SYS.ALL_ARGUMENTS " +
"WHERE OWNER = '" + proc.Owner + "' " +
"AND OBJECT_NAME = '" + proc.Name + "' " +
"AND PACKAGE_NAME IS NULL " +
"AND DATA_LEVEL = 0 AND DATA_TYPE IS NOT NULL " +
"ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL";
IDbCommand cmd = con.CreateCommand ();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader ();
string procType = "Procedures";
while (reader.Read ()) {
string procName = reader.GetString (0);
string argName = reader.GetString (2);
if (argName.Equals ("(RETURN)"))
procType = "Functions";
else
procType = "Procedures";
string direction = reader.GetString (5);
if (argName.Equals ("(RETURN)"))
direction = String.Empty;
string dataType = reader.GetString (6);
MetaProcedureArgument arg = new MetaProcedureArgument(proc.Owner, procName, procType,
argName, direction, dataType);
proc.Arguments.Add (arg);
}
reader.Close ();
reader = null;
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
con.Open();
MetaProcedureCollection procs = new MetaProcedureCollection ();
string sql =
"SELECT OWNER, OBJECT_NAME, " +
" DECODE(OBJECT_TYPE,'PROCEDURE','Procedures','FUNCTION','Functions','PACKAGE','Packages') AS OBJ_TYPE " +
"FROM ALL_OBJECTS " +
"WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') " +
"ORDER BY 3, 1, 2";
IDbCommand cmd = con.CreateCommand ();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader ();
while (reader.Read ()) {
string procOwner = reader.GetValue(0).ToString ();
string procName = reader.GetValue(1).ToString ();
string procType = reader.GetValue(2).ToString ();
MetaProcedure proc = null;
if (procType.Equals("Packages")) {
proc = new MetaProcedure (procOwner, procName, procType, true);
GetPackageProcedures (proc);
}
else {
proc = new MetaProcedure (procOwner, procName, procType);
GetProcedureArguments (proc);
}
// add to stored object to collection
procs.Add (proc);
}
reader.Close ();
reader = null;
return procs;
}
public MetaProcedureCollection GetProcedures(string owner)
{
if(con.State != ConnectionState.Open)
con.Open();
MetaProcedureCollection procs = new MetaProcedureCollection ();
string sql =
"SELECT routine_schema, routine_name, routine_type " +
"FROM information_schema.ROUTINES " +
"ORDER BY 3,1,2";
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
IDataReader reader = cmd.ExecuteReader();
while(reader.Read()) {
string procSchema = reader.GetString (0);
string procName = reader.GetString (1);
string procType = reader.GetString (2);
if (procType.Equals("FUNCTION"))
procType = "Functions";
else if (procType.Equals("PROCEDURE"))
procType = "Procedures";
MetaProcedure proc = new MetaProcedure (procSchema, procName, procType);
procs.Add (proc);
}
reader.Close ();
reader = null;
return procs;
}
public MetaProcedureCollection GetProcedures(string owner)
{
if (con.State != ConnectionState.Open)
con.Open();
MetaProcedureCollection procs = new MetaProcedureCollection ();
DataTable table2 = null;
DataRow row2 = null;
table2 = GetSchema ("Procedures", new string[] {null, null, null});
for (int r = 0; r < table2.Rows.Count; r++) {
row2 = table2.Rows[r];
string procName = row2["PROCEDURE_NAME"].ToString();
MetaProcedure proc = new MetaProcedure ("", procName, "Procedures");
procs.Add (proc);
row2 = null;
}
table2 = null;
table2 = GetSchema ("Functions", new string[] {null, null, null, null});
for (int r = 0; r < table2.Rows.Count; r++) {
row2 = table2.Rows[r];
string funcName = row2["FUNCTION_NAME"].ToString();
MetaProcedure proc = new MetaProcedure ("", funcName, "Functions");
procs.Add (proc);
row2 = null;
}
table2 = null;
return procs;
}
void PopulateProcedureArguments (TreeIter parentIter, MetaProcedure proc)
{
TreeIter argIter = tree.Store.AppendValues (parentIter, "Parameters");
foreach (MetaProcedureArgument arg in proc.Arguments)
tree.Store.AppendValues (argIter, arg.ToString (), "");
}
void PopulatePackageProcedures (TreeIter parentIter, MetaProcedure parentProc)
{
TreeIter procsIter = TreeIter.Zero;
TreeIter argsIter = TreeIter.Zero;
MetaProcedureCollection procs = parentProc.Procedures;
string procType = "Procedures";
bool first = true;
foreach (MetaProcedure proc in procs) {
if (proc.ProcedureType.Equals (procType)) {
if (first) {
procsIter = tree.Store.AppendValues (parentIter, procType);
first = false;
}
argsIter = tree.Store.AppendValues (procsIter, proc.ToString(), "");
//PopulateProcedureArguments (argsIter, proc);
}
}
procType = "Functions";
first = true;
foreach (MetaProcedure proc2 in procs) {
if (proc2.ProcedureType.Equals (procType)) {
if (first) {
procsIter = tree.Store.AppendValues (parentIter, procType);
first = false;
}
argsIter = tree.Store.AppendValues (procsIter, proc2.ToString(), "");
//PopulateProcedureArguments (argsIter, proc2);
}
}
}
