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. http://www.prithviraj.com.np/2012/10/list-all-sql-server-instances-in-combo_9.html
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.
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:
This is code from earlier post.
Now Lets see the Code on SelectedIndex Change event of Database Combo (Combobox2).
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.
This will load data in GridView automatically. Lets see a snapshot once again with Result.
All the Best, Enjoy....
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. http://www.prithviraj.com.np/2012/10/list-all-sql-server-instances-in-combo_9.html
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.
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....
Check this at your machine. Best of Luck! 