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