Filtering and Sorting in ADO.NET

Filtering and Sorting with the DataTable Select Me

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"

Problem with the DataTable Select

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.

Example

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;
      }
    }
  }
}

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Walking on water and developing software from a specification are easy if both are frozen.” - Edward V Berard