Get All or Specific Data From the Database Using Single Inline Query

Step 3

Write the code on Page load event in the "!IsPostBack" section due to remove duplicate bind.

1. Bind the data in to the "DataTable" from the database.
   
   1. string Constr = ConfigurationManager.ConnectionStrings["Con"].ToString();
   2. SqlConnection con = new SqlConnection(Constr);
   3. string sql = "select * from Department";
   4. con.Open();
   5. SqlCommand cmd = new SqlCommand(sql, con);
   6. cmd.CommandType = CommandType.Text;
   7. DataTable dt = new DataTable();
   8. SqlDataAdapter ad = new SqlDataAdapter();
   9. ad.SelectCommand = cmd;
   10. ad.Fill(dt);
   11. con.Close();
       
2. Add the "DataTable" values into the "DropDownList" Control.
   
   1. DropDownList1.DataSource = dt;
   2. DropDownList1.DataTextField = "Department_Name";
   3. DropDownList1.DataValueField = "Department_ID";
   4. DropDownList1.DataBind();
      
3. Add an item into the "DropDownList" Control for "All" rows selection with it's key value.
   
   1. DropDownList1.Items.Add("Select All");
   2. DropDownList1.Items[DropDownList1.Items.Count - 1].Value = "00";
      
   
   
   
4. 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.

1. Write the inline query as in the following:
   
   1. 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".
   
   
2. Bind the data to the "DataTable" from the database on the basis of the selected value of "DropDownList1".
   
   1. string Constr = ConfigurationManager.ConnectionStrings["Con"].ToString();
   2. SqlConnection con = new SqlConnection(Constr);
   3. con.Open();
   4. SqlCommand cmd = new SqlCommand(sql, con);
   5. cmd.CommandType = CommandType.Text;
   6. DataTable dt = new DataTable();
   7. SqlDataAdapter ad = new SqlDataAdapter();
   8. ad.SelectCommand = cmd;
   9. ad.Fill(dt);
   10. con.Close();
       
3. Add the "DataTable" values into the "GridView" Control.
   
   1. GridView1.DataSource = dt;
   2. GridView1.DataBind();
      
   

Step 5

If I run the page and select all 3 values of "DropDownList" respectively and press the Submit button.

1. The "IT" Department shows 3 rows.
   
   
   
   
2. The "Finance" Department shows 2 rows.
   
   
   
   
3. The "Select All" Department shows all 5 rows.