List SQL stored procedures for specific database based on combobox selection

Pieter

Hi. Noob asking. I have a windows form that needs to enable you to select a database from a combobox, and then enable you to list and execute the stored procedures in the listbox separately. I have already established a connection to SQL and have loaded the database names to the combobox. I'm struggling to find a way to list the stored procedures to the listbox based on the database selected. I've tried using a query in SQL that lists all the sp's, but it only lists the sp's in the master db. So trying to call them with parameters to the listbox feels like a dead end to me. Any ideas will be greatly appreciated.

Here is what I have so far:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Data.SqlClient;

namespace StoredProcedureExecution_Pieter_Bekker

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

try

{

LoadCombo();

}

catch (Exception)

{

err.SetError(cboSelectDatabase,"Problem loading databases. Please contact the system administrator.");

}

private void LoadCombo()

{

String conxString = (@"Data Source = WOLF-PC\WOLF; Integrated Security=True;");

using (SqlConnection conn = new SqlConnection(conxString))

{

conn.Open();

DataTable tblDatabases = conn.GetSchema("Databases");

conn.Close();

foreach (DataRow row in tblDatabases.Rows)

{

cboSelectDatabase.Items.Add(row["database_name"]);

}

private void cboSelectDatabase_SelectedIndexChanged(object sender, EventArgs e)

{

btnLoadProcedures.Enabled = true;

}

private void btnLoadProcedures_Click(object sender, EventArgs e)

{

//This is where I need some help...

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pieter14y ago
Thanks a lot for the reply. I got it working earlier by using a weird approach and got lucky. Will try your code as well and see if I can get the same result. Here is the code I got it working with:

private void LoadStoredProcedures()
{
lbProcedures.Items.Clear();
String conxString = (@"Data Source = WOLF-PC\WOLF; Integrated Security=True;" + "Database = " + cboSelectDatabase.Text);
using (SqlConnection conn = new SqlConnection(conxString))
{
conn.Open();
DataTable tblDatabases = conn.GetSchema("procedures");
conn.Close();

foreach (DataRow row in tblDatabases.Rows)
{
lbProcedures.Items.Add(row["SPECIFIC_NAME"]);
}
}
}

Thanks again for the help! I'll post the source code for the project if anyone wants it.
Jignesh Trivedi14y ago
hi,

to get list of SP in selected database use must pass database name as a initial catalog in connection string.

replace bold character with database name which are selected by other combo.

Try following...
private void btnLoadProcedures_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=WOLF-PC\WOLF;Initial Catalog=" + databasename + ";User ID=useIdr;Password=password;MultipleActiveResultSets=True");
con.Open();
DataSet _dataset = new DataSet();
string commandText = "select name from sys.objects where type = 'P'";
SqlDataAdapter datAd = new SqlDataAdapter(commandText, con);
datAd.Fill(_dataset, "Test");
con.Close();
DataTable dt = _dataset.Tables["Test"];
comboBox1.DataSource = dt;
comboBox1.DisplayMember = "name";
}

hope this will help u.