public MetaProcedure(string procOwner, string procName, string procType, bool hasProcs) {
this.owner = procOwner;
this.name = procName;
this.procedureType = procType;
this.hasProcs = hasProcs;
if (hasProcs)
this.procs = new MetaProcedureCollection ();
}
public MetaProcedure(string procOwner, string procName, string procType, bool hasProcs)
{
this.owner = procOwner;
this.name = procName;
this.procedureType = procType;
this.hasProcs = hasProcs;
if (hasProcs)
{
this.procs = new MetaProcedureCollection();
}
}
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();
// 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);
}
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 ();
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;
}
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;
}
