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.
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.
Keep visiting for more articles on SQL & other programming topics. Subscribe to Feeds here.