public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(@"SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, so.crdate as created_date, so.xtype as proc_type FROM dbo.sysobjects so, dbo.sysusers su WHERE xtype = 'P' AND su.uid = so.uid ORDER BY 1, 2" )) try { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ProcedureSchema procedure = new ProcedureSchema(this); procedure.Name = r.GetString(1); procedure.OwnerName = r.GetString(0); procedure.LanguageName = "TSQL"; procedure.Definition = GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]"); if (Array.Exists(system_procs, delegate(string s) { return(s == procedure.Name); })) { procedure.IsSystemProcedure = true; } else { procedure.IsSystemProcedure = false; } procedures.Add(procedure); } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } return(procedures); }
private void BuildChildNodesThreaded(object state) { BaseNode node = state as BaseNode; ITreeBuilder builder = Context.GetTreeBuilder(state); bool showSystemObjects = (bool)builder.Options["ShowSystemObjects"]; ProcedureSchemaCollection procedures = node.ConnectionContext.SchemaProvider.GetProcedures(); DispatchService.GuiDispatch(delegate { foreach (ProcedureSchema procedure in procedures) { if (procedure.IsSystemProcedure && !showSystemObjects) { continue; } builder.AddChild(new ProcedureNode(node.ConnectionContext, procedure)); } builder.Expanded = true; }); }
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html public override ProcedureSchemaCollection GetProcedures () { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection (); using (IPooledDbConnection conn = connectionPool.Request ()) { IPooledDbConnection conn2 = connectionPool.Request (); IDbCommand command = conn.CreateCommand (string.Concat ( @"SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA ='", ConnectionPool.ConnectionContext.ConnectionSettings.Database, "' ORDER BY ROUTINE_NAME")); try { using (command) { if (GetMainVersion (command) >= 5) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ProcedureSchema procedure = new ProcedureSchema (this); procedure.Name = r.GetString (0); procedure.OwnerName = r.GetString (1); procedure.IsSystemProcedure = (r.GetString (2).IndexOf ("system", StringComparison.OrdinalIgnoreCase) > -1); procedure.IsFunction = (r.GetString (2).IndexOf ("function", StringComparison.OrdinalIgnoreCase) > -1); IDbCommand command2; if (!procedure.IsFunction) command2 = conn2.CreateCommand (string.Concat ("SHOW CREATE PROCEDURE `", procedure.Name, "`;")); else command2 = conn2.CreateCommand (string.Concat("SHOW CREATE FUNCTION `", procedure.Name , "`;")); using (IDataReader r2 = command2.ExecuteReader()) if (r2.Read ()) procedure.Definition = r2.GetString (2); procedures.Add (procedure); } r.Close (); } } //else: do nothing, since procedures are only supported since mysql 5.x } } catch (Exception e) { QueryService.RaiseException (e); } finally { conn.Release (); if (conn2 != null) conn2.Release (); } } return procedures; }
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { IPooledDbConnection conn2 = connectionPool.Request(); IDbCommand command = conn.CreateCommand(string.Concat( @"SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA ='" , ConnectionPool.ConnectionContext.ConnectionSettings.Database, "' ORDER BY ROUTINE_NAME")); try { using (command) { if (GetMainVersion(command) >= 5) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ProcedureSchema procedure = new ProcedureSchema(this); procedure.Name = r.GetString(0); procedure.OwnerName = r.GetString(1); procedure.IsSystemProcedure = (r.GetString(2).IndexOf("system", StringComparison.OrdinalIgnoreCase) > -1); procedure.IsFunction = (r.GetString(2).IndexOf("function", StringComparison.OrdinalIgnoreCase) > -1); IDbCommand command2; if (!procedure.IsFunction) { command2 = conn2.CreateCommand(string.Concat("SHOW CREATE PROCEDURE `", procedure.Name, "`;")); } else { command2 = conn2.CreateCommand(string.Concat("SHOW CREATE FUNCTION `", procedure.Name, "`;")); } using (IDataReader r2 = command2.ExecuteReader()) if (r2.Read()) { procedure.Definition = r2.GetString(2); } procedures.Add(procedure); } r.Close(); } } //else: do nothing, since procedures are only supported since mysql 5.x } } catch (Exception e) { QueryService.RaiseException(e); } finally { conn.Release(); if (conn2 != null) { conn2.Release(); } } } return(procedures); }
public override ProcedureSchemaCollection GetProcedures () { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection (); using (IPooledDbConnection conn = connectionPool.Request ()) { // Exclude: Language Handler (2280) - Triggers (2279) - Void (2278) using (IDbCommand command = conn.CreateCommand ( @"SELECT pc.proname, pc.oid::integer, pl.lanname, pc.prosrc, pc.procost, pc.provolatile, pt.typname as rettypename, pc.proargnames as argnames, pc.proargmodes as argmodes, pc.proallargtypes as argtypes FROM pg_proc pc, pg_user pu, pg_type pt, pg_language pl WHERE pc.proowner = pu.usesysid AND pc.prorettype = pt.oid AND pc.prolang = pl.oid AND pc.prorettype not in (2278, 2279, 2280) UNION SELECT pc.proname, pt.oid::integer, pl.lanname, pc.prosrc, pc.procost, pc.provolatile, pt.typname as rettypename, pc.proargnames as argnames, pc.proargmodes as argmodes, pc.proallargtypes as argtypes FROM pg_proc pc, pg_user pu, pg_type pt, pg_language pl WHERE pc.proowner = pu.usesysid AND pc.prorettype not in (2278, 2279, 2280) AND pc.prorettype = 0 AND pc.prolang = pl.oid;")) { try { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ProcedureSchema procedure = new ProcedureSchema (this); procedure.Name = r.GetString (0); procedure.LanguageName = r.GetString (2); if (!r.IsDBNull (1) && r.GetInt32 (1) < LastSystemOID) procedure.IsSystemProcedure = true; procedure.IsFunction = true; // Procedure Definition StringBuilder proc = new StringBuilder (); if (procedure.IsFunction) { string lang = r.GetString (2); string proctype = string.Empty; string retType = r.GetString (6); switch (r.GetString (5)) { case "s": proctype = "STABLE"; break; case "i": proctype = "INMUTABLE"; break; default: proctype = "VOLATILE"; break; } float cost = r.GetFloat (4); proc.AppendFormat ("CREATE OR REPLACE FUNCTION {0} (", r.GetString (0)); // Get parameters collection ParameterSchemaCollection pars = GetParams (r.GetValue (7).ToString (), r.GetValue (8).ToString (), r.GetValue (9).ToString (), conn); bool first = true; // Set Parameters list foreach (ParameterSchema p in pars) { if (!first) { proc.Append (", "); } first = false; proc.Append (p.ParameterType.ToString ()); if (p.Name != "") { proc.AppendFormat (" {0}", p.Name); } proc.AppendFormat (" {0}", p.DataTypeName); } proc.Append (")"); if (lang == "edbspl") proc.AppendFormat ("\nRETURN {0} AS \n", retType); else proc.AppendFormat ("\nRETURNS {0} AS \n", retType); proc.AppendFormat ("$BODY$ {0} $BODY$\n", r.GetString (3)); proc.AppendFormat ("LANGUAGE '{0}' {1}\n", lang, proctype); proc.AppendFormat ("COST {0};\n", cost.ToString ()); } procedure.Definition = proc.ToString (); procedures.Add (procedure); } r.Close (); } } catch (Exception e) { QueryService.RaiseException (e); } finally { conn.Release (); } } } return procedures;
public override ProcedureSchemaCollection GetProcedures () { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection (); using (IPooledDbConnection conn = connectionPool.Request ()) { using (IDbCommand command = conn.CreateCommand (@"SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, so.crdate as created_date, so.xtype as proc_type FROM dbo.sysobjects so, dbo.sysusers su WHERE xtype = 'P' AND su.uid = so.uid ORDER BY 1, 2")) try { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ProcedureSchema procedure = new ProcedureSchema (this); procedure.Name = r.GetString (1); procedure.OwnerName = r.GetString (0); procedure.LanguageName = "TSQL"; procedure.Definition = GetSource ("[" + procedure.OwnerName + "].[" + procedure.Name + "]"); if (Array.Exists (system_procs, delegate (string s) {return s == procedure.Name; })) procedure.IsSystemProcedure = true; else procedure.IsSystemProcedure = false; procedures.Add (procedure); } r.Close (); } } catch (Exception e) { QueryService.RaiseException (e); } finally { conn.Release (); } } return procedures;
public virtual ProcedureSchemaCollection GetProcedures () { ProcedureSchemaCollection collection = new ProcedureSchemaCollection (); IPooledDbConnection conn = connectionPool.Request (); try { //restrictions: database, schema, name, type DataTable dt = conn.GetSchema (proceduresCollectionString, null, connectionPool.ConnectionContext.ConnectionSettings.Database); for (int r = 0; r < dt.Rows.Count; r++) { DataRow row = dt.Rows[r]; collection.Add (GetProcedure (row)); } } catch (Exception e) { QueryService.RaiseException (e); } conn.Release (); return collection; }
public override ProcedureSchemaCollection GetProcedures () { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection (); IPooledDbConnection conn = connectionPool.Request (); IDbCommand command = conn.CreateCommand ( "SELECT pc.proname, pc.oid::integer, pl.lanname, pc.prosrc " + "FROM " + " pg_proc pc, " + " pg_user pu, " + " pg_type pt, " + " pg_language pl " + "WHERE pc.proowner = pu.usesysid " + "AND pc.prorettype = pt.oid " + "AND pc.prolang = pl.oid " + "UNION " + "SELECT pc.proname, pt.oid::integer, pl.lanname, pc.prosrc " + "FROM " + " pg_proc pc, " + " pg_user pu, " + " pg_type pt, " + " pg_language pl " + "WHERE pc.proowner = pu.usesysid " + "AND pc.prorettype = 0 " + "AND pc.prolang = pl.oid;" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read ()) { ProcedureSchema procedure = new ProcedureSchema (this); procedure.Name = r.GetString (0); procedure.Definition = r.GetString (3); procedure.LanguageName = r.GetString (2); if (!r.IsDBNull (1) && r.GetInt32 (1) <= LastSystemOID) procedure.IsSystemProcedure = true; procedures.Add (procedure); } r.Close (); } } } catch (Exception e) { QueryService.RaiseException (e); } conn.Release (); return procedures;