Filtering and Sorting in ADO.NET

Filtering and Sorting with the DataView Manager

If the DataSet contains multiple tables, an alternative is to create a DataViewManager object, which works something like a global DataSet DataView. This new object 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 DataT able - 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.

Example

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

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.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates