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
961 views
List All SQL Server Instance & Perform Basic Operations

Hello Friends, On the Previous Post on this series, I have told you the code and procedure up-to Connect Button to List all SQL Server Instances in network.

Hope you have been through it and enjoyed that. If not, please read below post before moving ahead.

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

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 Server
List all SQL Server Instances & Perform basic operations

In 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.

Let’s Begin the Code. On Button Click and Successful Connection, we will load all database names in Database Combo. We have done it earlier.

Let’s 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 Let’s see the Code on SelectedIndexChange 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 Every time, 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. Let’s see a snapshot once again with Result.

List All SQL Server

Keep visiting for more articles on SQL & other programming topics. Subscribe to Feeds here.

Related Articles

Leave a Reply

Do NOT follow this link or you will be banned from the site!