Listing Tables From SQL Server 2008

[Behseini]

Hi Guys,
I am trying to list the name of all tables which belong to a selected database from the comboBox1(which is populated from SQL Server 2008).I used the following code but it seems it is just adding all System Tables! to listBox1 instead of choosing tables from selected database.
Could you please let me know how I can correct the code in order to list all tables from the selected item(Database), listed in comboBox1?

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            OleDbConnection cn = new OleDbConnection(@"provider = sqloledb; data source = .\WorkShop; integrated security = sspi;");
            cn.Open();
            DataTable MyTable;
            MyTable = cn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables.ToString());
            for (int i = 0; i < MyTable.Rows.Count; i++)
            {
                listBox1.Items.Add(MyTable.Rows[i].ItemArray[2].ToString());
            }
        }

Thanks

[Momerath]

First, why are you using an OleDBConnection to connect to Sql Server? Use SqlConnection for better performance and features.

Second, your call to GetSchema is requesting something odd, you just need to say SqlConnection.GetSchema()

[Behseini]

Hi Momerath
Thanks for your comment I tried to modify the cod as you commented:
But I am getting provider issues!what provider can I use for SqlConnection

[Momerath]

Connection Strings

[Behseini]

OK
I used the SqlConnection and you are right this seems to be faster.BUT I am still not getting the tables name based on what database has been selected from the combobox!.
Every time that I click on a database (listed in combobox) I just get these strings listed:
master
master
master
master
master
master
master
master
master
master
master
master
Does not matter which of databases I clicked!

SqlConnection connection = new SqlConnection();
            connection.ConnectionString = "data source = .\\WorkShop; integrated security = sspi;";
            connection.Open();
            {

                DataTable table = connection.GetSchema("Tables");
                foreach (DataRow tab in table.Rows)
                    listBox1.Items.Add(tab[0]);
                connection.Close();
            }

[Mitja Bonca]

You would like to get what?

[Behseini]

Hi Mitja,
Thanks for reply
Let's say I have 4 Databases listed on a combobox as [Movie,Books,Entertainment, and Games]. what I want to do is whenever I click on each of this databases, the tables which belong to SELECTED database get list on a list box.
For Example if user select the Movie database, the tables[Title, Director, Company,..]
get listed in the list box and so on.

[Momerath]

You aren't setting which database to use in your connection string so it's using the system database. You need an "Initial Catalog" section to specify the database.

So you need to select which database and build the connection string including the "Initial Catalog".

[Behseini]

Ok but How can I do that?
I mean I do not want to make a hard copy as it is suppose to be selected by user.so how I can use "Initial Catalog" based on selected item?

[Momerath]

Use the SelectedIndex property of your control to get the value then add it to the connection string:

connection.ConnectionString = String.Format("data source = .\\WorkShop; integrated security = sspi;Initial Catalog={0}", databaseName);

[Mitja Bonca]

Hi Mitja,
Thanks for reply
Let's say I have 4 Databases listed on a combobox as [Movie,Books,Entertainment, and Games].

Wait...
Are these DataBases,... or
are these names of the tables in one dataBase?

[Behseini]

Thank you my Friend,
I can select the database now

private void listBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            string databaseName = listBox3.SelectedItem.ToString();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = String.Format("data source = .\\WorkShop; integrated security = sspi;Initial Catalog={0}", databaseName);
            connection.Open();
            {
                DataTable table = connection.GetSchema("Tables");
                foreach (DataRow tab in table.Rows)
                    listBox1.Items.Add(tab[0]);
            }
            connection.Close();
        }

BUT there is another issue. it seems the code just list the address of table names not the real name.For Example, if I select the Movie Database (I knew that there are 4 TABLES in there) the list display like
Movie
Movie
Movie
Movie

this is happening on all databases and the list box got populated by name of database and the number of tables which existing on that database.
I tried to modify my code and add ToString() method to the

listBox1.Items.Add(tab[0]).ToString();

but nothing change

[Behseini]

Mitja,
NO, these are all user defined Databases. What I am trying to do is retrieving TABLES existing in each of those databases.