In this example, we create a nested GridView. In this Griview we will expand the grid to show the complete data regarding a Student with "jQuery" and Filter the GridView using a "Stored Procedure" like this:
Student Table

Expanding

Expending Student Table
Filter
Filter Student Table

Here we filter the Name and Total Marks depending on the Conditions (greater than 90 and less than 30).

Step 1: First we will create a table in the database (here we will use the example of a Student) as in the following:

  1. create table Student
  2. (
  3. id int identity(1,1),
  4. Name varchar(20),
  5. Class varchar(20),
  6. Age int,
  7. s_Address varchar(100),
  8. Phone varchar(20),
  9. Total varchar(20)
  10. )

Step 2: Now we create the nested GridView as in the following:

  1. <asp:GridView ID="gvStudents" runat="server" AutoGenerateColumns="false" DataKeyNames="id"
  2. OnRowDataBound="OnRowDataBound">
  3. <Columns>
  4. <asp:TemplateField>
  5. <ItemTemplate>
  6. <img alt="" style="height: 10px;" src="images/Up.jpg" />
  7. <asp:Panel ID="pnlDetails" runat="server" Style="display: none">
  8. <asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false">
  9. <Columns>
  10. <asp:BoundField ItemStyle-Width="150px" DataField="id" HeaderText="id" />
  11. <asp:BoundField ItemStyle-Width="150px" DataField="age" HeaderText="age" />
  12. <asp:BoundField ItemStyle-Width="150px" DataField="Class" HeaderText="Class" />
  13. <asp:BoundField ItemStyle-Width="150px" DataField="s_Address" HeaderText="Address" />
  14. <asp:BoundField ItemStyle-Width="150px" DataField="Phone" HeaderText="Phone" />
  15. </Columns>
  16. </asp:GridView>
  17. </asp:Panel>
  18. </ItemTemplate>
  19. </asp:TemplateField>
  20. <asp:TemplateField>
  21. <HeaderTemplate>
  22. Name:
  23. <asp:DropDownList ID="ddlName" runat="server" OnSelectedIndexChanged="NameChanged"
  24. AutoPostBack="true" AppendDataBoundItems="true">
  25. <asp:ListItem Text="Select" Value="Select"></asp:ListItem>
  26. <asp:ListItem Text="All" Value="All"></asp:ListItem>
  27. </asp:DropDownList>
  28. </HeaderTemplate>
  29. <ItemTemplate>
  30. <%# Eval("Name") %>
  31. </ItemTemplate>
  32. </asp:TemplateField>
  33. <asp:TemplateField>
  34. <HeaderTemplate>
  35. Total
  36. <asp:DropDownList ID="ddlTotal" runat="server" OnSelectedIndexChanged="TotalChanged"
  37. AutoPostBack="true" AppendDataBoundItems="true">
  38. <asp:ListItem Text="Select" Value="Select"></asp:ListItem>
  39. <asp:ListItem Text="All" Value="All"></asp:ListItem>
  40. <asp:ListItem Text="Greater Than 90" Value="90"></asp:ListItem>
  41. <asp:ListItem Text="Less Than 30" Value="30"></asp:ListItem>
  42. </asp:DropDownList>
  43. </HeaderTemplate>
  44. <ItemTemplate>
  45. <%# Eval("Total") %>
  46. </ItemTemplate>
  47. </asp:TemplateField>
  48. </Columns>
  49. </asp:GridView>

Here we will create a GridView(gvStudents), Inside this GridView, Here we take an Image(for Exapand and Collapse) and a Panel(pnlDetails) in which we will take an another GridView(gvDetails) as Nested GridView.

  1. <asp:GridView ID="gvStudents" runat="server" AutoGenerateColumns="false" DataKeyNames="id"
  2. OnRowDataBound="OnRowDataBound">
  3. <Columns>
  4. <asp:TemplateField>
  5. <ItemTemplate>
  6. <img alt="" style="height: 10px;" src="images/Up.jpg" />
  7. <asp:Panel ID="pnlDetails" runat="server" Style="display: none">
  8. <asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false">
  9. <Columns>
  10. <asp:BoundField ItemStyle-Width="150px" DataField="id" HeaderText="id" />
  11. <asp:BoundField ItemStyle-Width="150px" DataField="age" HeaderText="age" />
  12. <asp:BoundField ItemStyle-Width="150px" DataField="Class" HeaderText="Class" />
  13. <asp:BoundField ItemStyle-Width="150px" DataField="s_Address" HeaderText="Address" />
  14. <asp:BoundField ItemStyle-Width="150px" DataField="Phone" HeaderText="Phone" />
  15. </Columns>
  16. </asp:GridView>
  17. </asp:Panel>
  18. </ItemTemplate>
  19. </asp:TemplateField>
  20. </Columns>
  21. </asp:GridView>

Step 3: Now we will create the HeaderTemplate in our GridView for Filtering the data. Here we will use a DropDownList to filter the data like this:

  1. <headertemplate>
  2. Name:
  3. <asp:DropDownList ID="ddlName" runat="server"
  4. OnSelectedIndexChanged = "NameChanged" AutoPostBack = "true"
  5. AppendDataBoundItems = "true">
  6. <asp:ListItem Text = "Select" Value = "Select"></asp:ListItem>
  7. <asp:ListItem Text = "All" Value = "All"></asp:ListItem>
  8. </asp:DropDownList>
  9. </headertemplate>
  10. <itemtemplate>
  11. <%# Eval("Name") %>
  12. </itemtemplate>
  13. </asp:TemplateField>
  14. <asp:TemplateField>
  15. <headertemplate>
  16. Total:
  17. <asp:DropDownList ID="ddlTotal" runat="server"
  18. OnSelectedIndexChanged = "TotalChanged" AutoPostBack = "true"
  19. AppendDataBoundItems = "true">
  20. <asp:ListItem Text = "Select" Value = "Select"></asp:ListItem>
  21. <asp:ListItem Text = "All" Value = "All"></asp:ListItem>
  22. <asp:ListItem Text = "Greater Than 90" Value = "90"></asp:ListItem>
  23. <asp:ListItem Text = "Less Than 30" Value = "30"></asp:ListItem>
  24. </asp:DropDownList>
  25. </headertemplate>
  26. <itemtemplate>
  27. <%# Eval("Total") %>
  28. </itemtemplate>
  29. </asp:TemplateField>

Step 4: Now we will create the Stored Procedure for Name and Total like this:

  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. CREATE PROCEDURE GetName
  6. @Search VARCHAR(50)
  7. AS
  8. BEGIN
  9. SET NOCOUNT ON;
  10. IF @Search = 'Select'
  11. SELECT * FROM Student
  12. ELSE If @Search = 'All'
  13. SELECT * FROM Student
  14. ELSE IF @Search= '2'
  15. SELECT TOP 2 id, Name, Total FROM Student
  16. ELSE
  17. SELECT id, Name, Total FROM Student WHERE Name=@Search
  18. END
  19. GO
  20. SET ANSI_NULLS ON
  21. GO
  22. SET QUOTED_IDENTIFIER ON
  23. GO
  24. CREATE PROCEDURE GetTotal
  25. @Search VARCHAR(50)
  26. AS
  27. BEGIN
  28. SET NOCOUNT ON;
  29. IF @Search = 'All'
  30. SELECT * FROM Student
  31. ELSE IF @Search= '90'
  32. SELECT * from Student where Total>@Search
  33. ELSE IF @Search= '30'
  34. SELECT * from Student where Total<@Search
  35. ELSE
  36. SELECT id, Name, Total FROM Student WHERE Total=@Search
  37. END
  38. GO

Here we set the value of the search depending on the value of the DropDownList, by which we can filter the data like this:

  1. <asp:ListItem Text = "Select" Value = "Select"></asp:ListItem>
  2. <asp:ListItem Text = "All" Value = "All"></asp:ListItem>
  3. <asp:ListItem Text = "Greater Than 90" Value = "90"></asp:ListItem>
  4. <asp:ListItem Text = "Less Than 30" Value = "30"></asp:ListItem>

Here we will set the ListItem value "All" so the SP calls the following query:

  1. IF @Search = 'All'
  2. SELECT * FROM Student

Step 5: Now we will write our jQuery function for expanding and collapasing the data:

  1. <script type="text/javascript">
  2. $("[src*=Up]").live("click", function () {
  3. $(this).closest("tr").after("<tr><td></td><td colspan = '1000'>" + $(this).next().html() + "</td></tr>")
  4. $(this).attr("src", "images/Down.jpg");
  5. });
  6. $("[src*=Down]").live("click", function () {
  7. $(this).attr("src", "images/Up.jpg");
  8. $(this).closest("tr").next().remove();
  9. });
  10. </script>

Here we will use the two images (Up.jpg and Down.jpg), so when we click on the image (Up.jpg) a new row will be added. This is called Expanding and when we click on the Down.jpg the row is removed, this is called collapsing.

Step 6: Now we will write the code in the .cs page as in the following:

  1. if (!IsPostBack)
  2. {
  3. ViewState["Search"] = "All";
  4. ViewState["Search1"] = "All";
  5. BindName();
  6. BindTotal();
  7. gvStudents.DataSource = GetData("select * from Student");
  8. gvStudents.DataBind();
  9. }

Step 7: Now we will write the code to filter the data by name as in the following:

  1. protected void NameChanged(object sender, EventArgs e)
  2. {
  3. DropDownList ddlName = (DropDownList)sender;
  4. ViewState["Search"] = ddlName.SelectedValue;
  5. this.BindName();
  6. }

Here we will assign the DropDownlist Value in the ViewState, so it will be helpful in the filtering of data.

  1. private void BindName()
  2. {
  3. SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True");
  4. DataTable dt = new DataTable();
  5. SqlDataAdapter da = new SqlDataAdapter();
  6. SqlCommand cmd = new SqlCommand("GetName");
  7. cmd.CommandType = CommandType.StoredProcedure;
  8. cmd.Parameters.AddWithValue("@Search", ViewState["Search"].ToString());
  9. cmd.Connection = con;
  10. da.SelectCommand = cmd;
  11. da.Fill(dt);
  12. gvStudents.DataSource = dt;
  13. gvStudents.DataBind();
  14. DropDownList ddlName =
  15. (DropDownList)gvStudents.HeaderRow.FindControl("ddlName");
  16. this.BindNameList(ddlName);
  17. }

Here we will first create the ConnectionString and call the Stored Procedure (GetName) and set the value of the search depending on the value of ViewState["Search"]. And call another funtion (BindNameList) as in the following:

  1. private void BindNameList(DropDownList ddlName)
  2. {
  3. SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True");
  4. SqlDataAdapter sda = new SqlDataAdapter();
  5. SqlCommand cmd = new SqlCommand("select * from Student");
  6. cmd.Connection = con;
  7. con.Open();
  8. ddlName.DataSource = cmd.ExecuteReader();
  9. ddlName.DataTextField = "Name";
  10. ddlName.DataValueField = "Name";
  11. ddlName.DataBind();
  12. con.Close();
  13. ddlName.Items.FindByValue(ViewState["Search"].ToString())
  14. .Selected = true;
  15. }

Here we will add the data in the DropDownList(ddlName).

  1. protected void NameChanged(object sender, EventArgs e)
  2. {
  3. DropDownList ddlName = (DropDownList)sender;
  4. ViewState["Search"] = ddlName.SelectedValue;
  5. this.BindName();
  6. }

Like this we will write the code for Total:

  1. private void BindTotal()
  2. {
  3. SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True");
  4. DataTable dt1 = new DataTable();
  5. SqlDataAdapter da1 = new SqlDataAdapter();
  6. SqlCommand cmd1 = new SqlCommand("GetTotal");
  7. cmd1.CommandType = CommandType.StoredProcedure;
  8. cmd1.Parameters.AddWithValue("@Search", ViewState["Search1"].ToString());
  9. cmd1.Connection = con;
  10. da1.SelectCommand = cmd1;
  11. da1.Fill(dt1);
  12. gvStudents.DataSource = dt1;
  13. gvStudents.DataBind();
  14. DropDownList ddlTotal =
  15. (DropDownList)gvStudents.HeaderRow.FindControl("ddlTotal");
  16. this.BindTotalList(ddlTotal);
  17. }
  18. private void BindTotalList(DropDownList ddlTotal)
  19. {
  20. SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True");
  21. SqlDataAdapter sda = new SqlDataAdapter();
  22. SqlCommand cmd = new SqlCommand("select * from Student");
  23. cmd.Connection = con;
  24. con.Open();
  25. ddlTotal.DataSource = cmd.ExecuteReader();
  26. ddlTotal.DataTextField = "Total";
  27. ddlTotal.DataValueField = "Total";
  28. ddlTotal.DataBind();
  29. con.Close();
  30. ddlTotal.Items.FindByValue(ViewState["Search1"].ToString())
  31. .Selected = true;
  32. }
  33. protected void TotalChanged(object sender, EventArgs e)
  34. {
  35. DropDownList ddlTotal = (DropDownList)sender;
  36. ViewState["Search1"] = ddlTotal.SelectedValue;
  37. this.BindTotal();
  38. }

Now we will write the code for creating the DataTable like this:

  1. private static DataTable GetData(string query)
  2. {
  3. SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True");
  4. using (SqlCommand cmd = new SqlCommand())
  5. {
  6. cmd.CommandText = query;
  7. using (SqlDataAdapter sda = new SqlDataAdapter())
  8. {
  9. cmd.Connection = con;
  10. sda.SelectCommand = cmd;
  11. using (DataSet ds = new DataSet())
  12. {
  13. DataTable dt = new DataTable();
  14. sda.Fill(dt);
  15. return dt;
  16. }
  17. }
  18. }
  19. }

Step 8: Now we will write the code to fill the data in the GridView (gvDetails) as in the following:

  1. protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
  2. {
  3. if (e.Row.RowType == DataControlRowType.DataRow)
  4. {
  5. string StudentId = gvStudents.DataKeys[e.Row.RowIndex].Value.ToString();
  6. GridView gvDetails = e.Row.FindControl("gvDetails") as GridView;
  7. gvDetails.DataSource = GetData(string.Format("select * from Student where id='" + StudentId + "'"));
  8. gvDetails.DataBind();
  9. }
  10. }

Here we will fill in the data in the GridView depending on the KeyName, that we already specified in our GridView like this:

  1. <asp:GridView ID="gvStudents" runat="server" AutoGenerateColumns="false"
  2. DataKeyNames="id" OnRowDataBound="OnRowDataBound">