How do I get the table names in a database into a combobox?

Question

I have created a program in C# that would allow the user to execute his query on a table that exists in a given database. Further, I want the user to select a table from the combo box on which he wants to execute his query. However, I am unable to fetch the table names from the database into the combo box. This is the code that I am using:

    public partial class AddQuery : Form
    {
        public AddQuery()
        {
            InitializeComponent();
            fill_combo();
        }
        void fill_combo()
        {
            string cmdstr = "Use Dev_Server";
            SqlConnection con = new SqlConnection(@"Data Source=INPDDBA027\NGEP;Initial Catalog=Dev_Server;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(cmdstr,con);

            DataSet ds = new DataSet();
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                //comboBox1.Items.Add(dr);
                foreach (DataTable dt in ds.Tables)
                {
                    comboBox1.Items.Add(dt.TableName[0]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }



    }
}

Please Help.

Please add **for all DB related questions!** the RDBMS you are using. Name **and version**! This makes it much easier to answer... Use the edit option to set the right tags... — Shnugo, Jul 12 '16 at 10:43

score 2 · Answer 1 · answered Jul 12 '16 at 10:48

If this was SQL-Server this might help you:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

Just try to call this with SELECT * to see the other information you would get.

Anyway, with INFORMATION_SCHEMA.XYZ you can get a lot of meta-data out of your database. Just replace the XYZ with COLUMNS or ROUTINES or read about the details here

score 2 · Accepted Answer · answered Jul 12 '16 at 11:06

I think you done something misconceptual wrong in your code

please try this

public partial class AddQuery : Form
{
    public AddQuery()
    {
        InitializeComponent();
        fill_combo();
    }
    void fill_combo()
    {
        string cmdstr = "select * from sys.tables";
        string conStr = @"Data Source=INPDDBA027\NGEP;Initial Catalog=Dev_Server;Integrated Security=True";
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(cmdstr,conStr);
        try
        {
            sda.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                comboBox1.Items.Add(row["name"]);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }



}
}

score 1 · Answer 3 · answered Jul 12 '16 at 10:44

1

If this is MS SQL Server, then you can use below query:

select * from sys.tables where type = 'U'

answered Jul 12 '16 at 10:44

Abdullah Nehir

1,027
13
23

This is possible, but will make it harder to find side data like the table's `SCHEMA`. you'd have to join to `sys.objects` and to `sys.schemas`. I'd prefer `INFORMATION_SCHEMA`, which is nothing else under the hood, but many usefull data is fetched implicitly. – Shnugo Jul 12 '16 at 10:55

Pranav Singh · Answer 4 · 2016-07-12T11:18:54.073

0

Replace string cmdstr = "Use Dev_Server";

with string cmdstr ="select * from sys.tables where type = 'U'";

or Better as stated by @shungo

string cmdstr ="select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'";

As mentioned by @AbdullahNeir in his answer this is query to get all table in given database.

edited Jul 12 '16 at 11:18

answered Jul 12 '16 at 10:48

Pranav Singh

17,079
30
77
104

I'd prefer `INFORMATION_SCHEMA`, placed some explanations below Abdullah's answer... – Shnugo Jul 12 '16 at 10:57
@Shnugo edited answer. Thanks – Pranav Singh Jul 12 '16 at 11:19

How do I get the table names in a database into a combobox?

4 Answers4