Sometimes a developer must show all the rows or just some rows on the same event and for that, generally developers use:
- A Stored Procedure and write a if else condition in it.
- Write the if else condition at the code level and use a different query in both conditions.
Like
- //if( Filter)
- //Fetch Filtered data via Query1
- //else means All
- // Fetch All data via Query2
But I will tell you how you can easily do it without using a stored procedure and conditions at the code level.
The following are the details of the preceding procedure.
Step 1
- Create a table named "Department" with 2 columns, "Department_ID" and "Department_Name".
Add some information for some Departments into it.
- insert into Department values(1,'IT')
- insert into Department values(2,'Finance')
And finally select the "Department" Table.
- Now create one more table named "Employee" with the 3 columns "Emp_ID", "Emp_Name" and "Department_ID".
Add some information for some Employees into it.
- insert into Employee values(101,'Sumit',1)
- insert into Employee values(102,'Amit',1)
- insert into Employee values(103,'Udit',1)
- insert into Employee values(104,'Ram',2)
- insert into Employee values(105,'Shyam',2)
And finally select the "Employee" Table.
Note: Here 3 employees belong to the "IT" Department and 2 employees belong to the "Finance" Department.
Step 2
- Create a new Empty Website named "Website1".
- And add the web form named "Deafult.aspx" to it.
- Add a "DropdownList", a button with the Text as "Submit" and a click event and a "Gridview" into the "Deafult.aspx" page.
Step 3
Write the code on Page load event in the "!IsPostBack" section due to remove duplicate bind.
- Bind the data in to the "DataTable" from the database.
- string Constr = ConfigurationManager.ConnectionStrings["Con"].ToString();
- SqlConnection con = new SqlConnection(Constr);
- string sql = "select * from Department";
- con.Open();
- SqlCommand cmd = new SqlCommand(sql, con);
- cmd.CommandType = CommandType.Text;
- DataTable dt = new DataTable();
- SqlDataAdapter ad = new SqlDataAdapter();
- ad.SelectCommand = cmd;
- ad.Fill(dt);
- con.Close();
- Add the "DataTable" values into the "DropDownList" Control.
- DropDownList1.DataSource = dt;
- DropDownList1.DataTextField = "Department_Name";
- DropDownList1.DataValueField = "Department_ID";
- DropDownList1.DataBind();
- Add an item into the "DropDownList" Control for "All" rows selection with it's key value.
- DropDownList1.Items.Add("Select All");
- DropDownList1.Items[DropDownList1.Items.Count - 1].Value = "00";
- By this I will get the output like this.
Note: Here 1 and 2 are Department Ids that come from the "Department" Table and "00" is the value of the "Select All" Text.
Step 4
Write the code on button click event.
- Write the inline query as in the following:
- string sql = @"select * from employee where Department_ID='" + DropDownList1.SelectedItem.Value + "' or (Department_ID!='" + DropDownList1.SelectedItem.Value + "' and '" + DropDownList1.SelectedItem.Value + "'='00')";
Note: Here "00" should be the key value of the "Select All" item of "DropDownlist1".
- Bind the data to the "DataTable" from the database on the basis of the selected value of "DropDownList1".
- string Constr = ConfigurationManager.ConnectionStrings["Con"].ToString();
- SqlConnection con = new SqlConnection(Constr);
- con.Open();
- SqlCommand cmd = new SqlCommand(sql, con);
- cmd.CommandType = CommandType.Text;
- DataTable dt = new DataTable();
- SqlDataAdapter ad = new SqlDataAdapter();
- ad.SelectCommand = cmd;
- ad.Fill(dt);
- con.Close();
- Add the "DataTable" values into the "GridView" Control.
- GridView1.DataSource = dt;
- GridView1.DataBind();
Step 5
If I run the page and select all 3 values of "DropDownList" respectively and press the Submit button.
- The "IT" Department shows 3 rows.
- The "Finance" Department shows 2 rows.
- The "Select All" Department shows all 5 rows.