private void OnProcedureAdd(ITreeBuilder builder, ProcedureSchema schema)
{
if (((bool)builder.Options["ShowSystemObjects"]) == true || schema.IsSystemProcedure == false)
builder.AddChild (schema);
builder.Expanded = true;
}
public override ProcedureSchema[] GetProcedures()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
NpgsqlCommand command = new NpgsqlCommand ();
command.Connection = connection;
command.CommandText =
"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;";
NpgsqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ProcedureSchema procedure = new ProcedureSchema ();
procedure.Provider = this;
procedure.Name = r.GetString (0);
procedure.Definition = r.GetString (3);
procedure.LanguageName = r.GetString (2);
try {
if (r.GetInt32 (1) <= LastSystemOID)
procedure.IsSystemProcedure = true;
} catch {}
collection.Add (procedure);
}
r.Close ();
return (ProcedureSchema[]) collection.ToArray (typeof (ProcedureSchema));
}
public override ColumnSchema[] GetProcedureColumns(ProcedureSchema schema)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
// TODO:
return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
}
public override ProcedureSchema[] GetProcedures()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (SqlCommand cmd = connection.CreateCommand ()) {
cmd.CommandText = "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";
using (SqlDataReader reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
ProcedureSchema proc = new ProcedureSchema ();
proc.Provider = this;
proc.Name = (string) reader ["proc_name"];
proc.OwnerName = (string) reader ["owner"];
proc.LanguageName = "TSQL";
// FIXME : get sysproc or not
collection.Add (proc);
}
}
// get procedure text
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_helptext";
SqlParameter param = cmd.Parameters.Add ("@objname", SqlDbType.VarChar);
foreach (ProcedureSchema proc in collection) {
param.Value = proc.Name;
using (SqlDataReader reader = cmd.ExecuteReader ()) {
if (reader.Read ())
proc.Definition = (string) reader [0];
}
}
}
return (ProcedureSchema []) collection.ToArray (typeof (ProcedureSchema));
}
public override ColumnSchema[] GetProcedureColumns(ProcedureSchema schema)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
// FIXME: Won't work properly with overload functions.
// Maybe check the number of columns in the parameters for
// proper match.
NpgsqlCommand command = new NpgsqlCommand ();
command.Connection = connection;
command.CommandText = String.Format (
"SELECT format_type (prorettype, NULL) "
+ "FROM pg_proc pc, pg_language pl "
+ "WHERE pc.prolang = pl.oid "
+ "AND pc.proname = '{0}';", schema.Name);
NpgsqlDataReader r = command.ExecuteReader ();
while (r.Read ()) {
ColumnSchema column = new ColumnSchema ();
column.Provider = this;
column.DataTypeName = r.GetString (0);
column.Name = r.GetString (0);
collection.Add (column);
}
r.Close ();
return (ColumnSchema[]) collection.ToArray (typeof (ColumnSchema));
}
public override ColumnSchema[] GetProcedureColumns(ProcedureSchema schema)
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
using (SqlCommand cmd = connection.CreateCommand ()) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_sproc_columns";
SqlParameter owner = cmd.Parameters.Add ("@procedure_owner", SqlDbType.VarChar);
SqlParameter name = cmd.Parameters.Add ("@procedure_name", SqlDbType.VarChar);
owner.Value = schema.OwnerName;
name.Value = schema.Name;
using (SqlDataReader reader = cmd.ExecuteReader ()) {
while (reader.Read ()) {
ColumnSchema column = new ColumnSchema ();
column.Provider = this;
column.Name = (string) reader ["COLUMN_NAME"];
column.DataTypeName = (string) reader ["TYPE_NAME"];
collection.Add (column);
}
}
}
return (ColumnSchema []) collection.ToArray (typeof (ColumnSchema));
}
public virtual ColumnSchema[] GetProcedureColumns(ProcedureSchema schema)
{
throw new NotImplementedException ();
}
public virtual void GetProcedureColumns(SQLCallback Callback, ProcedureSchema schema)
{
Thread eThread = null;
lock (ThreadSync) {
ThreadedSQLCallback = Callback;
ThreadedProcedureSchema = schema;
eThread = new Thread (new ThreadStart (
GetProcedureColumnsThreadStart));
eThread.Start ();
}
}
public static void BuildChildNodes(ITreeBuilder builder, ProcedureSchema node)
{
builder.AddChild (new ColumnsNode (node.Provider, node));
builder.AddChild (new ParametersNode (node.Provider));
}
public override ProcedureSchema[] GetProcedures()
{
if (IsOpen == false && Open () == false)
throw new InvalidOperationException ("Invalid connection");
ArrayList collection = new ArrayList ();
DataTable table2 = null;
DataRow row2 = null;
table2 = connection.GetSchema ("Procedures", new string[] {null, null, null});
for (int r = 0; r < table2.Rows.Count; r++) {
row2 = table2.Rows[r];
ProcedureSchema procedure = new ProcedureSchema ();
procedure.Provider = this;
procedure.Name = row2["PROCEDURE_NAME"].ToString();
procedure.Definition = "";
procedure.LanguageName = "";
procedure.IsSystemProcedure = false;
collection.Add (procedure);
row2 = null;
}
table2 = null;
table2 = connection.GetSchema ("Functions", new string[] {null, null, null, null});
for (int r = 0; r < table2.Rows.Count; r++) {
row2 = table2.Rows[r];
ProcedureSchema procedure = new ProcedureSchema ();
procedure.Provider = this;
procedure.Name = row2["FUNCTION_NAME"].ToString();
procedure.Definition = "";
procedure.LanguageName = "";
procedure.IsSystemProcedure = false;
collection.Add (procedure);
row2 = null;
}
table2 = null;
return (ProcedureSchema[]) collection.ToArray (typeof (ProcedureSchema));
}