Home ProgrammingSQL List All SQL Server Instances in Combo (Windows Form) – Part-2

List All SQL Server Instances in Combo (Windows Form) – Part-2

by John Bhatt
899 views
Hello Friends,
On the Previous Post on this series, I have told you the code and procedure upto Connect Button. Hope you have been through it and enjoyed that. If not, please read below post before moving ahead.
Today, I am going to share rest of the Code for Listing Database, Tables Inside Database and Columns and apply them with Operator and simply perform Select Operation from database and list the Result in DataGrid. Let’s look the Design you have previously saw when making connection.
List All SQL ServerIn above design,
In Database Combo, we will list all database from Connected Server. Then in Table Combo, we will list all Tables of Selected Database and in Column Combo all columns of Selected Table.

Lets Begin the Code. On Button Click and Successful Connection, we will load all database names in Database Combo. Actually we have did it earlier.
Lets have look at this Code:

comboBox2.Items.Clear();
                   if (checkBox1.Checked == true)
                   {
                       ConStr = "Data Source = " + comboBox1.Text.ToString() + ";Integrated Security = true;";
                   }
                   else
                   {
                       ConStr = "Data Source = " + comboBox1.Text.ToString() + ";UID=" + textBox1.Text + ";pwd=" + textBox2.Text + ";";
                   }
                   SqlConnection Conexion = new SqlConnection(ConStr);
                   Conexion.Open();
                   label9.Visible = false;
                   panel2.Visible = false;
                   button2.Visible = true;
                   panel1.Visible = true;
                   groupBox1.Visible = true;
                   DataTable tblDatabases = Conexion.GetSchema("Databases");
 
                   for (int i = 0; i <= tblDatabases.Rows.Count - 1; i++)
                   {
                       comboBox2.Items.Add(tblDatabases.Rows[i][0].ToString());
                   }
                   Conexion.Close();

This is code from earlier post.
Now Lets see the Code on SelectedIndex Change event of Database Combo (Combobox2).

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox3.Items.Clear();
            SqlConnection conx = new SqlConnection(ConStr + "Database ="+comboBox2.Text.ToString()+";");
            conx.Open();
            DataTable tblTables = conx.GetSchema("Tables");
            for (int i = 0; i <= tblTables.Rows.Count - 1; i++)
            {
                comboBox3.Items.Add(tblTables.Rows[i][2].ToString());
            }
            conx.Close();
        }

Here we are creating Connection String Everytime, because, It is changing every-time. Now this code will list all the Tables inside Database in Table Combo (combobox3): Code:

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboBox4.Items.Clear();
            SqlConnection conx = new SqlConnection(ConStr + "Database =" + comboBox2.Text.ToString() + ";");
            conx.Open();
            string tableName = comboBox3.SelectedItem.ToString();
            SqlDataAdapter adp = new SqlDataAdapter("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '"+comboBox3.SelectedItem.ToString()+"'",conx);
            DataTable tblColumns = new DataTable();
            adp.Fill(tblColumns);
            for (int i = 0; i <= tblColumns.Rows.Count - 1; i++)
            {
                comboBox4.Items.Add(tblColumns.Rows[i][3].ToString());
            }
            conx.Close();
        }

Now above code will make all Columns of Selected table from database.
Now Directly move to Keyword TextBox textchange event which will load data as you type.

private void textBox3_TextChanged(object sender, EventArgs e)
        {
            string QueryCon = ConStr;
            if (comboBox5.Text.ToString() == "like")
            {
                QueryStr = "SELECT * FROM " + comboBox3.Text.ToString() + " WHERE " + comboBox4.Text.ToString() + " " + comboBox5.Text.ToString() + " '%" + textBox3.Text + "%'";
            }
            else
            {
                QueryStr = "SELECT * FROM " + comboBox3.Text.ToString() + " WHERE " + comboBox4.Text.ToString() + " " + comboBox5.Text.ToString() + " '" + textBox3.Text + "'";
            }
            TxtQueryBox.Text = ConStr + "Database=" + comboBox2.Text.ToString() + ";"+"n" + QueryStr;
            SqlDataAdapter adp = new SqlDataAdapter(QueryStr, ConStr+"Database="+comboBox2.Text.ToString()+";");
            DataSet ds = new DataSet();
            adp.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }

This will load data in GridView automatically. Lets see a snapshot once again with Result.


All the Best, Enjoy….

2 comments

Sachin November 7, 2012 - 3:48 pm

sir as this apps shows the result of select query i wanna shows the result of all yhe sql queries like insert update deleate etc….pls tell me that what should i do for that..

Reply
John Bhatt February 1, 2013 - 12:57 pm

Dear Sachin, I havn’t really worked with that but it is Possible.
As in above codes , you can see that Command ( Query) is passed from Backend, you can directly write Query in Front End textbox and pass it to backend.
Ex.
Query = txtQuery.Text;

and txtQuery.Text in Front is >

CREATE TABLE [dbo].[MyTable] (
[col1] [int] NOT NULL PRIMARY KEY,
[col2] [varchar](20) NULL,
[col3] [datetime] NULL,
[UserID] [varchar] (20) NOT NULL
)
Etc.

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

List All SQL Server Instances in Combo (Windows Form) – Part-2

by John Bhatt time to read: 2 min
2