Robbie Datkov Robbie Datkov - 6 months ago 27
SQL Question

Creating DropDown menu C# with SQL DB Tables

I need your help with something I am working on.

It's very simple, but it has been bugging me.

I am creating a ComboBox[DropDown Menu] in a WPF application and I want to fill it with all the current Tables I have in my DB.

This is what I'm struggling to do:
When I click on the ComboBox it will show all the available tables in the DataBase. Then when I click on one of them it will show the information that is contained within the selected table in a DataGrid I've placed below the menu.

My Application

And here Is the code I am using when the ComboBox opens:

private void tableComboBox_DropDownOpened(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';", db.connection);

SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);

foreach(DataRow row in dataSet.Tables)
{
tableComboBox.Items.Add(row);
}
}


I've already looked and tried some different approaches but non of them work.
And I've tried to show the Content of a Table in the DataGrid but I got stuck again.

Please fellow coders. Help this newbie over here! :)

Answer

So this is what I quickly came up with.

  1. remove the tableComboBox_DropDownOpened event.
  2. add the event comboBox_SelectionChange
  3. Change your db connection string, names of the combobox and dataGrid to match yours.

Here is the code below, I moved the loadCombo() below your initialize to make it simple.

    public partial class MainWindow : Window
{
SqlConnection db = new SqlConnection("Your Connection String Here");

    public MainWindow()
    {
        InitializeComponent();

        loadCombo();
    }

    private void loadCombo()
    {
        SqlCommand cmd = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';", db);

        SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);

        foreach (DataRow row in dataSet.Tables[0].Rows)
        {
            comboBox.Items.Add(row[0]);
        }
    }

    private DataTable loadDataGrid(String inTableName )
    {
        SqlCommand cmd = new SqlCommand("SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"+ inTableName + "';", db);

        SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);

        return dataSet.Tables[0];
    }
    private void comboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        string text = e.AddedItems[0].ToString(); ;
        dataGrid.ItemsSource = loadDataGrid(text).DefaultView;
    }
}

Hope this helps

Comments