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

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

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

1. Create a new Empty Website named "Website1".
   
   
   
   
2. And add the web form named "Deafult.aspx" to it.
   
   
   
   
3. 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.

1. 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();

2. Add the "DataTable" values into the "DropDownList" Control.
   

   DropDownList1.DataSource = dt;
   DropDownList1.DataTextField = "Department_Name";
   DropDownList1.DataValueField = "Department_ID";
   DropDownList1.DataBind();

3. 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";

   
   
   
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:
   

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

   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();

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

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