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); }
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "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).ToLower().Contains("system"); IPooledDbConnection conn2 = connectionPool.Request(); IDbCommand command2 = conn2.CreateCommand("SHOW CREATE PROCEDURE `" + procedure.Name + "`;"); using (IDataReader r2 = command2.ExecuteReader()) { r2.Read(); procedure.Definition = r2.GetString(2); } conn2.Release(); procedures.Add(procedure); } r.Close(); } } //else: do nothing, since procedures are only supported since mysql 5.x } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(procedures); }
public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); 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 (command) { 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"; StringBuilder sb = new StringBuilder(); sb.AppendFormat("-- Procedure: {0}\n", procedure.Name); sb.AppendFormat(" {0}\n);", GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]")); procedure.Definition = sb.ToString(); // FIXME : get sysproc or not procedures.Add(procedure); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } 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 ProcedureSchemaCollection(ProcedureSchemaCollection collection) : base(collection, true) { }