Finding, Filtering, and Sorting Rows in a DataTable
Each row in a DataTable is stored in a DataRow object, and in this section you'll learn
how to find, filter, and sort the DataRow objects in a DataTable.
Finding a DataRow in a DataTable
To find a DataRow in a DataTable, you follow these steps:
1. Retrieve the rows from the database into your DataTable.
2. Set the PrimaryKey property of your DataTable.
3. Call the Find() method of your DataTable, passing the primary key column value
of the DataRow you want.
For example, the following code performs steps 1 and 2 in this list, retrieving the top 10
rows from the Products table and setting the PrimaryKey property to the ProductID
DataColumn:
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 10 ProductID, ProductName " +
"FROM Products " +
"ORDER BY ProductID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet, "Products");
mySqlConnection.Close();
DataTable productsDataTable = myDataSet.Tables["Products"];
productsDataTable.PrimaryKey =
new DataColumn[]
{
productsDataTable.Columns["ProductID"]
};
Next, the following example performs step 3, calling the Find() method to retrieve the

sortExpression specifies how the selected rows are to be ordered.
•
myDataViewRowState specifies the state of the rows to select. You set
myDataViewRowState to one of the constants defined in the
System.Data.DataViewRowState enumeration. Table 11.8
shows these constants.
Table 11.8: DataViewRowState ENUMERATION MEMBERS
CONSTANT DESCRIPTION
Added A new row.
CurrentRows The current rows, which include Unchanged, Added, and
ModifiedCurrent rows.
Table 11.8: DataViewRowState ENUMERATION MEMBERS
CONSTANT DESCRIPTION
Deleted A deleted row.
ModifiedCurrent A current row that has been modified.
ModifiedOriginal The original row before it was modified.
None Doesn't match any of the rows in the DataTable.
OriginalRows The original rows, which include Unchanged and Deleted rows.
Unchanged A row that hasn't been changed.
Let's take a look at some examples that use the Select() method.
The following example calls the Select() method with no parameters, which returns all
rows in the DataTable without any filtering or sorting:
DataRow[] productDataRows = productsDataTable.Select();
The next example supplies a filter expression to Sort(), which returns only the DataRow
objects with ProductID DataColumn values that are less than or equal to 5:
DataRow[] productDataRows = productsDataTable.Select("ProductID <= 5");
The following example supplies both a filter expression and a sort expression that orders
the DataRow objects by descending ProductID values:
DataRow[] productDataRows = productsDataTable.Select("ProductID <= 5", "ProductID
DESC");

/*
FindFilterAndSortDataRows.cs illustrates how to find, filter,
and sort DataRow objects
*/

using System;
using System.Data;
using System.Data.SqlClient;

class FindFilterAndSortDataRows
{
public static void Main()
{
SqlConnection mySqlConnection =
new SqlConnection(
"server=localhost;database=Northwind;uid=sa;pwd=sa"
);

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 10 ProductID, ProductName " +
"FROM Products " +
"ORDER BY ProductID;" +
"SELECT TOP 10 OrderID, ProductID, UnitPrice, Quantity " +
"FROM [Order Details] " +
"ORDER BY OrderID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();

"with a ProductID of 3");
DataRow productDataRow = productsDataTable.Rows.Find("3");
foreach (DataColumn myDataColumn in productsDataTable.Columns)
{
Console.WriteLine(myDataColumn + "= " + productDataRow[myDataColumn]);
}