DataSet Advance Operations – Search , Sort , Filter

Introduction:

My last article covered the Dataset basic operations Add and Remove. Now let’s talk about advance operations with DataSet. This article will cover the following topics

Search in DataSet
Sort DataSet
Filter DataSet

To demonstrate the above functionality we will use the same subject data in XML format.

XML Data:

Listing 1: XML Data

<?xml version="1.0" standalone="yes"?>
<Subjects>
  <Subject>
    <ID>1</ID>
    <Name>Math's</Name>
    <Chapters>10</Chapters>
    <Exams>5</Exams>
  </Subject>
  <Subject>
    <ID>3</ID>
    <Name>Physics</Name>
    <Chapters>8</Chapters>
    <Exams>4</Exams>
  </Subject>
  <Subject>
    <ID>4</ID>
    <Name>Chemistry</Name>
    <Chapters>15</Chapters>
    <Exams>10</Exams>
  </Subject>
</Subjects>

Figure 1: Application With the XML Data

ASP.NET Code:

To demonstrate the functionally, we are using the ASP.NET page. The page is holding the following controls.

One textbox for filter criteria
Three buttons for Search, sort and Filter operations.
One DataGrid to display the data

Listing 2: ASP.NET Script

<head runat="server">
    <title>MrBool.com ASP.NET and C# Tutorials - Advance Dataset Operation</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Search/Filter:
                </td>
                <td>
                    <asp:TextBox ID="txtCriteria" runat="server"></asp:TextBox>
                </td>
            </tr>           
            <tr>
                <td colspan="2">
                    <asp:Label runat="server" ID="lblError" Font-Bold="true" ForeColor="Green"></asp:Label>
                </td>
            </tr>
        </table>
        <br />
        <asp:Button ID="btnSearch" runat="server" Text="Search" 
            onclick="btnSearch_Click" />
         <asp:Button ID="btnSort" runat="server" Text="Sort" 
            onclick="btnSort_Click" />
         <asp:Button ID="btnFilter" runat="server" Text="Filter" 
            onclick="btnFilter_Click" />
             <asp:Button ID="btnRepopulate" runat="server" Text="RePopulate" onclick="btnRepopulate_Click" 
             />
        <br />
        <br />
        <br />
        <asp:DataGrid ID="dgSubjects" runat="server" AutoGenerateColumns="true">
            <HeaderStyle Font-Bold="true" />
        </asp:DataGrid>
        <br />
    </div>
    </form>
</body>

C# Code:

The C# code has some methods to support the basic read write and display operations. We are not covering these functions in this article. To get the idea of these functions, please refer to previous articles.

Perform Search on DataSet:

Search is one the most useful operation. If you have a large data in dataset and want to find a particular record, then must use the search operation. The Dataset does not provide any search function, while you can use the select function to find particular record.

Figure 2: Search Operation - Physics

To perform search first get the dataset and apply the select function with the criteria.

Listing 3: Search Criteria Construction

string strQuery = "Name = '" + txtCriteria.Text + "'";
DataRow[] drFilterRows = oDs.Tables[0].Select(strQuery);

The select function will return a set of datarows, just import these datarows in another dataset: oDsResults.Tables[0].ImportRow(dr);

Now just display this result dataset in grid.

Listing 4: Search Operation

protected void btnSearch_Click(object sender, EventArgs e)
    {        
        //Check for user input
        if (txtCriteria.Text.Trim() != "")
        {
            DataSet oDs = ReadSubjectDataSet();
            DataSet oDsResults = oDs.Clone();
            //Check if table exist
            if (oDs != null && oDs.Tables.Count > 0)
            {
                string strQuery = "Name = '" + txtCriteria.Text + "'";
                DataRow[] drFilterRows = oDs.Tables[0].Select(strQuery);

                foreach(DataRow dr in drFilterRows)
                    oDsResults.Tables[0].ImportRow(dr);

                if (drFilterRows.Length>0)
                {
                    oDsResults.AcceptChanges();
                    PopulateGrid(oDsResults);

                    lblError.Visible = true;
                    lblError.Text = "One Record Found.";
                }
                else
                {
                    PopulateGrid((DataTable)null);
                    lblError.Visible = true;
                    lblError.Text = "No Record Found.";
                }
            }
        }
        else
        {
            lblError.Visible = true;
            lblError.Text = "Please fill criteria before search";
        }
    }

Sort the Dataset:

Figure 3: Sort Operation

To sort the records of dataset use the sort method, while sorting you have to provide the name of column on which you are applying the sorting: oDs.Tables[0].DefaultView.Sort = "Name";

Listing 5: Sort Operation

protected void btnSort_Click(object sender, EventArgs e)
    {
        DataSet oDs = ReadSubjectDataSet();
        DataSet oDsResults = oDs.Clone();
        //Check if table exist
        if (oDs != null && oDs.Tables.Count > 0)
        {
            oDs.Tables[0].DefaultView.Sort = "Name";
            PopulateGrid(oDs.Tables[0]);
            lblError.Visible = true;
            lblError.Text = "Record Sorted.";

        }
    }

Filter the DataSet:

Filtering the dataset is similar to search, only difference is that here you will play with like operator.

Listing 6: Filter Criteria

string strQuery = "Name like '%" + txtCriteria.Text + "%'";
DataRow[] drFilterRows = oDs.Tables[0].Select(strQuery);

So create a sql kind criteria on a column and use the select method of table.

Figure 4: Filter Operation - i

Listing 7: Filter Operation

protected void btnFilter_Click(object sender, EventArgs e)
    {        
        //Check for use input
        if (txtCriteria.Text.Trim() != "")
        {
            DataSet oDs = ReadSubjectDataSet();
            DataSet oDsResults = oDs.Clone();
            //Check if table exist
            if (oDs != null && oDs.Tables.Count > 0)
            {
                string strQuery = "Name like '%" + txtCriteria.Text + "%'";
                DataRow[] drFilterRows = oDs.Tables[0].Select(strQuery);

                foreach (DataRow dr in drFilterRows)
                    oDsResults.Tables[0].ImportRow(dr);

                if (drFilterRows.Length > 0)
                {
                    oDsResults.AcceptChanges();
                    PopulateGrid(oDsResults);

                    lblError.Visible = true;
                    lblError.Text = drFilterRows.Length + " Record Found.";
                }
                else
                {
                    PopulateGrid((DataTable)null);
                    lblError.Visible = true;
                    lblError.Text = "No Record Found.";
                }
            }
        }
        else
        {
            lblError.Visible = true;
            lblError.Text = "Please fill criteria before search";
        }
    }

Give your note to this post:	1 2 3 4 5 6 7 8 9 10
Is this post helpful?	Yes No

DataSet Advance Operations – Search , Sort , Filter - .NET