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.

“God could create the world in six days because he didn't have to make it compatible with the previous version.”