Filtering and Sorting in ADO.NET

More Information on installing the .Net Framework click here.
Download full Visual Studio C# .NET Examples from this Article.

Contents

Filtering and Sorting with the DataTable Select Method
Filtering and Sorting with the DataView Object
Filtering and Sorting with the DataViewManager

Overview

ADO.NET supports many ways to manage memory-resident data returned in a DataSet. In this article you will see that ADO.NET exposes addidional functionality to the ways you can sort and filter data.

Because of the very nature of the disconnected DataSet architecture used in ADO.NET, it's often impractical or impossible to requery a remote data source to reorder or further filter data. Of course, this assumes that you are not implementing a traditional client/server application, which can execute further server-side sorting and filtering.

ADO.NET supports two fundamental approaches for performing these operations:

The DataTable Select Method - This method is overloaded to accept arguments to filter and sort data rows returning an array of DataRow objects.

The DataView object sort, filter and find methods - This object uses the same filter arguments supported by the Select method, but the DataView extrudes structures that can be bound to data-aware controls.

Filtering and Sorting with the DataTable Select Method

The DataTable Select returns a DataRow array from a table per a particular filter and/or sort order. The content reflects changes to the underlying table, but the membership and ordering remain static.

The DataTable Select method accepts a filter and sort argument to return an arry of DataRow objects that conform to the criteria in a FilterExpression.

public DataRow[] Select();
Gets an array of all DataRow objects.

public DataRow[] Select(string);
Gets an array of all DataRow objects that match the filter criteria

public DataRow[] Select(
string, string);
Gets an array of all DataRow objects that match the filter criteria, in the specified sort order

public DataRow[] Select(
string, string, DataViewRowState);
Gets an array of all DataRow objects that match the filter in the order of the sort, that match the specified state

For example, a Filter Expression might look like this:

"OrderDate >= '01.03.1998' AND OrderDate <= '31.03.1998'"

A typical Sort Expression is imply the name of the column to sort following by an optional ASC or DESC.

"OrderDate DESC"

The fundamental problem with the Select method is that it does not return a flitered table object as expected - it returns an arry of DataRow objects. This means you can't directly bind this array to a DataGrid or other data bound controls. To accomplish this, use a DataView as shown later in this article.

The following example illustrates how to filter and sort using the DataTable Select method. It begins by setting up a Command object to retrieve the ORDERS table from the Northwind Database.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace WithDataTableSelect
{
public class FilterAndSort : System.Windows.Forms.Form
{
    private System.Windows.Forms.DataGrid dataGrid;
    private System.ComponentModel.Container components = null;
    private System.Windows.Forms.TextBox textBox;
    private System.Windows.Forms.Label label1;
    private System.Windows.Forms.Button BtnFilterAndSort;
    private DataSet ds;

    public FilterAndSort()
    {
      InitializeComponent();

      // Setup DB-Connection, create and fill Dataset, show
      // Resultset in DataGrid
      SqlConnection cn = new SqlConnection(
        "data source=xeon;uid=sa;password=manager;database=northwind");
      ds = new DataSet("Orders");
      SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",cn);
      da.TableMappings.Add("Table","Orders");
      da.Fill(ds);
      dataGrid.DataSource = ds.Tables["Orders"];
    }

    ....
    ....

    [STAThread]
    static void Main()
    {
      Application.Run(new FilterAndSort());
    }

    // Filter and Sort with the DataTable Select Method
    private void BtnFilterAndSort_Click(object sender, System.EventArgs e)
    {
      string strText;
      string strExpr;
      string strSort;
      DataRow[] foundRows;
      DataTable myTable;
      myTable = ds.Tables["Orders"];

      // Setup Filter and Sort Criteria
      strExpr = "OrderDate >= '01.03.1998' AND OrderDate <= '31.03.1998'";
      strSort = "OrderDate DESC";

      // Use the Select method to find all rows matching the filter.
      foundRows = myTable.Select(strExpr, strSort);

      // Apply all Columns to the TextBox, this
      // must be done Row-By-Row.
      strText = null;
      for (int i = 0 ; i <= foundRows.GetUpperBound(0); i++)
      {
        for (int j = 0; j <= foundRows[i].ItemArray.GetUpperBound(0); j++)
        {
          strText = strText + foundRows[i][j].ToString() + "\t";
        }
        strText = strText + "\r\n";
        textBox.Text = strText;
      }
    }
}
}

Filtering and Sorting with the DataView Object

A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.

A DataView provides a dynamic view of data whose content, ordering, and membership reflect changes to the underlying DataTable as they occur. This is different from the Select method of the DataTable, which returns a DataRow array from a table per a particular filter and/or sort order and whose content reflects changes to the underlying table, but whose membership and ordering remain static. The dynamic capabilities of the DataView make it ideal for data-binding applications.

A DataView provides you with a dynamic view of a single set of data to which you can apply different sorting and filtering criteria, similar to the view provided by a database. However, a DataView differs significantly from a database view in that the DataView cannot be treated as a table and cannot provide a view of joined tables. You also cannot exclude columns that exist in the source table, nor can you append columns, such as computational columns, that do not exist in the source table.

The DataTable.DefaultView Property is the DataView associated with a DataTable, it can be used to sort, filter, and search a DataTable.

The DataView.RowFilter Property gets or sets the expression used to filter which rows are viewed in the DataView. To form a RowFilter value, specify the name of a column followed by an operator and a value to filter on. The value must be in quotes. For example:

"LastName = 'Smith'"

To return only those columns with null values, use the following expression:

"Isnull(Col1,'Null Column') = 'Null Column'"

After you set the RowFilter Property, ADO.NET hides (but does not eliminate) all rows in the associated DataTable object's Rows collection that don't match the filter expression. The DataView.Count property returns the number of rows remaining unhidden in the view.

To sort a DataView, construct a sort expression string, note that the Sort property can accept any number of columns on which to sort the Rows collection. For example use the the following sort expression string:

"Price DESC, Title ASC"

The following example illustrates how to filter and sort using the DataTable's DataView object.

using System;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace Akadia.DataView
{
public class FilterOrder : System.Windows.Forms.Form
{
    ....
    private SqlConnection cn;
    private SqlCommand cmd;
    private SqlDataAdapter da;
    private DataSet ds;

    public FilterOrder()
    {
      try
      {
        InitializeComponent();

        // Initializing
        cn = new SqlConnection("
        server=xeon;database=northwind;uid=sa;pwd=manager");
        cmd = new SqlCommand("SELECT * FROM orders",cn);
        da = new SqlDataAdapter(cmd);
        ds = new DataSet();

        // Load initial Data
        RetrieveData();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }

    // Retrieve Orders from the DB into the DataGrid
    private void RetrieveData()
    {
      try
      {
        da.Fill(ds,"Orders");
        DataGrid.DataSource = ds.Tables[0];

       // Fill Combobx with Column Names
        FillSortCriteria();
      }
      catch (Exception ex)
      {
        Debug.WriteLine(ex.ToString());
        MessageBox.Show(ex.ToString());
      }
    }

    // Fill Combobx with Column Names
    private void FillSortCriteria()
    {
      try
      {
        // IF Combobox are already filled return ...
        if (cmbSortArg.Items.Count > 0)
        {
          return;
        }
        // ... else fill Comobox with Column Names
        foreach (DataColumn dc in ds.Tables[0].Columns)
        {
          cmbSortArg.Items.Add(dc.Caption); // Sort Combobox
          cmbFields.Items.Add(dc.Caption);   // Filter on Column Combobox
        }
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }

    // Setup the expression used to filter the rows which
    // are viewed by the DefaultView
    private void SetFilter(string strFilterExpression)
    {
      try
      {
        // Apply Filter Expression
        ds.Tables[0].DefaultView.RowFilter = strFilterExpression;

        // Gets the number of records in the DataView after
        // RowFilter and RowStateFilter have been applied.
        if (ds.Tables[0].DefaultView.Count > 0)
        {
          DataGrid.DataSource = ds.Tables[0].DefaultView;
        }
        else
        {
          MessageBox.Show("Filter criteria does not meet criteria");
        }
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }

    // Remove any existing Filter and ResultSet from a previous query
    private void btnQuery_Click(object sender, System.EventArgs e)
    {
      try
      {
        // Clear DataSet
        ds.Clear();

        // Clear Filter
        ds.Tables[0].DefaultView.RowFilter = "";

        // Re-Retrieve Data
        RetrieveData();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }

    // Sets the sort column or columns, and sort order for the DataView
    private void btnSort_Click(object sender, System.EventArgs e)
    {
      try
      {
        string strSort;

        // IF Radiobox "Ascending" is checked, then
        // sort ascending ...
        if (rbAsc.Checked)
        {
          strSort = cmbSortArg.Text + " ASC";    // Note space after "
        }
        // ... else descending
        else
        {
          strSort = cmbSortArg.Text + " DESC";   // Note space after "
        }

        // Apply Sort Criteria to the DataView
        ds.Tables[0].DefaultView.Sort = strSort;
        DataGrid.DataSource = ds.Tables[0].DefaultView;
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }

    }

    // Setup specific Filter: "CutomerID LIKE 'xxx'"
    private void btnFilterTitle_Click(object sender, System.EventArgs e)
    {
      try
      {
        SetFilter("CustomerID like '" + txtFilter.Text + "'");
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }

    // Setup the general Filter entered by the User
    private void btnGeneralFilter_Click(object sender, System.EventArgs e)
    {
      try
      {
        SetFilter(txtGeneralFilter.Text);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }

    }

    // Setup the Column Filter entered by the User
    private void btnFilteronColumn_Click(object sender, System.EventArgs e)
    {
      try
      {
        SetFilter(cmbFields.Text + " " + txtFilterColumn.Text);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
        Console.WriteLine();
      }
    }

    ...

    [STAThread]
    static void Main()
    {
      Application.Run(new FilterOrder());
    }
}
}

Filtering and Sorting with the DataViewManager

If the DataSet contains multiple tables, an alternative is to create a DataViewManager object, which works something like a DataSet global DataView. This new objects provides a single object that manages a collection of DataView objects, each of which is associated with a specific DataTable in the selected DataSet object. You can define individual DataView objects that sort or filter the data in each DataTable; as a matter of fact, ADO.NET creates one for you as it creates each DataTable - all of which can be managed by the DataViewManager.

You can use a DataViewManager to manage view settings for all the tables in a DataSet. If you have a control that you want to bind to multiple tables, such as a grid that navigates relationships, a DataViewManager is ideal.

The DataViewManager contains a collection of DataViewSetting objects that are used to set the view setting of the tables in the DataSet. The DataViewSettingCollection contains one DataViewSetting object for each table in a DataSet. You can set the default ApplyDefaultSort, Sort, RowFilter, and RowStateFilter properties of the referenced table using its DataViewSetting. You can reference the DataViewSetting for a particular table by name or ordinal reference, or by passing a reference to that specific table object. You can access the collection of DataViewSetting objects in a DataViewManager using the DataViewSettings property.

The following code example fills a DataSet with the Northwind database Customers, Orders, and Order Details tables, creates the relationships between the tables, uses a DataViewManager to set default DataView settings, and binds a DataGrid to the DataViewManager. The example sets the default DataView settings for all tables in the DataSet to sort by the primary key of the table (ApplyDefaultSort = true), and then modifies the sort order of the Customers table to sort by CompanyName.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace WithDataViewManager
{
public class MyDataViewManager : System.Windows.Forms.Form
{
    private System.Windows.Forms.DataGrid dataGrid;
    private System.ComponentModel.Container components = null;

    public MyDataViewManager()
    {
      InitializeComponent();

      // Create a Connection, DataAdapters, and a DataSet.
      SqlConnection nwindConn = new SqlConnection(
        "server=xeon;database=northwind;uid=sa;pwd=manager");

      SqlDataAdapter custDA = new SqlDataAdapter(
        "SELECT * FROM Customers", nwindConn);
      SqlDataAdapter orderDA = new SqlDataAdapter(
        "SELECT * FROM Orders", nwindConn);
      SqlDataAdapter ordDetDA = new SqlDataAdapter(
        "SELECT * FROM [Order Details]", nwindConn);

      DataSet custDS = new DataSet();

      // Fill the DataSet with schema information and data.
      custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      orderDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      ordDetDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;

      custDA.Fill(custDS, "Customers");
      orderDA.Fill(custDS, "Orders");
      ordDetDA.Fill(custDS, "OrderDetails");

      // Create Relationships.
      custDS.Relations.Add("CustomerOrders",
        custDS.Tables["Customers"].Columns["CustomerID"],
        custDS.Tables["Orders"].Columns["CustomerID"]);

      custDS.Relations.Add("OrderDetails",
        custDS.Tables["Orders"].Columns["OrderID"],
        custDS.Tables["OrderDetails"].Columns["OrderID"]);

      // Create DataView settings for each Table
      // using the DataViewManager
      DataViewManager myDVM = new DataViewManager(custDS);

      // Loop through the DataViewSettings and set Sort
      // or Rowfilter for each Table individually
      String myTable;
      foreach (DataViewSetting myDVS in myDVM.DataViewSettings)
      {
        // Set Default Sort Order = Primary Key for all Tables
        myDVS.ApplyDefaultSort = true;

        // Set individual Sort and Rowfilter ...
        myTable = myDVS.Table.ToString();
        if (myTable == "Customers")
        {
          myDVS.Sort = "CompanyName DESC";
        }
        if (myTable == "Orders")
        {
          myDVS.RowFilter = "OrderDate >= '01.03.1998'
          AND OrderDate <= '31.03.1998'";
        }
        if (myTable == "OrderDetails")
        {
          myDVS.RowFilter = "ProductID = 35";
        }
      }

      // ... or directly (the same as above)
      myDVM.DataViewSettings["Customers"].Sort =
        "CompanyName DESC";
      myDVM.DataViewSettings["Orders"].RowFilter =
        "OrderDate >= '01.03.1998' AND OrderDate <= '31.03.1998'";
      myDVM.DataViewSettings["OrderDetails"].RowFilter =
       "ProductID = 35";

      // Bind the DataViewManager to a DataGrid
      dataGrid.SetDataBinding(myDVM, "Customers");
    }

    ....
    ....

    [STAThread]
    static void Main()
    {
      Application.Run(new MyDataViewManager());
    }
}
}