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.
|
|
Gets an array of all DataRow objects. |
|
|
Gets an array of all DataRow objects that match the filter criteria |
|
|
Gets an array of all DataRow objects that match the filter criteria, in the specified sort order |
|
|
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;
}
}
}
}
Comments