DataRelation Object Reference

Using the DataTable Object's Searching and Filtering Features

The DataTable object exposes two methods that you can use to locate data based on search criteria. One method, Find, lets you locate a row based on its primary key values. The other, Select, acts as more of a filter, returning multiple rows of data based on more flexible search criteria.

Locating a Row by Its Primary Key Values

When you're querying your database for information, you'll often want to retrieve a specific row of data based on the values of its primary key columns, using a query such as this one:

SELECT CustomerID, CompanyName, ContactName, Phone        FROM Customers WHERE CustomerID = 'ALFKI'

You can also locate a DataRow in a DataTable based on the row's primary key values. You might have noticed that the final code snippet in Chapter 7 looped through the results of a query and had to determine whether a row of data already existed in the DataTable. This code used the Find method to perform a search of the contents of the DataTable based on the primary key value.

Although the Find method is designed for DataTable objects, it's actually exposed by the DataRowCollection class. The Find method accepts an object that contains the primary key value for the row you want to locate. Because primary key values are unique, the Find method can return at most one DataRow. The following code snippet attempts to locate a customer row by its primary key value and then determines whether the search located a row.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable() da.Fill(tbl) tbl.PrimaryKey = New DataColumn() {tbl.Columns("CustomerID")} Dim row As DataRow = tbl.Rows.Find("ALFKI") If row Is Nothing Then     Console.WriteLine("Row not found!") Else     Console.WriteLine(row("CompanyName")) End If

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable(); da.Fill(tbl); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["CustomerID"]}; DataRow row = tbl.Rows.Find("ALFKI"); if (row == null)     Console.WriteLine("Row not found!"); else     Console.WriteLine(row["CompanyName"]);

	Technically, your DataTable can contain multiple rows that have the same primary key values. If you set the EnforceConstraints property of the DataSet to False, the DataTable will not throw an exception if you violate the primary key constraint. In such a scenario, the Find method will return the row it finds with the desired primary key values.

The Find method is overloaded for scenarios in which the primary key for your DataTable consists of multiple DataColumn objects. For example, the primary key for the Order Details table is based on the OrderID and ProductID columns. So, to locate a row in a DataTable with a similar schema, you can use code like the following:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _          "FROM [Order Details]" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable() da.Fill(tbl) tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), _                                    tbl.Columns("ProductID")} Dim objCriteria As New Object() {10643, 28} Dim row As DataRow = tbl.Rows.Find(objCriteria) If row Is Nothing Then     Console.WriteLine("Row not found!") Else     Console.WriteLine(row("Quantity") & " - " & row("UnitPrice")) End If

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +          "FROM [Order Details]"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable(); da.Fill(tbl); tbl.PrimaryKey = new DataColumn[] {tbl.Columns["OrderID"],                                     tbl.Columns["ProductID"]}; object[] objCriteria = new object[] {10643, 28}; DataRow row = tbl.Rows.Find(objCriteria); if (row == null)     Console.WriteLine("Row not found!"); else     Console.WriteLine(row["Quantity"] + " - " + row["UnitPrice"]);

Conducting More Dynamic Searches

Locating a row based on its primary key values is efficient, but not all searches are that straightforward. What if you want to locate all customers in the United States who are not in the city of Seattle? You can add such criteria to a database query using a WHERE clause, as shown here:

SELECT CustomerID, CompanyName, ContactName, Phone, City, Country     FROM Customers WHERE Country = 'USA' AND City <> 'Seattle'

You can use the DataTable object's Select method to locate rows based on similar criteria. Let's say you retrieve the entire contents of the Customers table into a DataTable. You can use the same search criteria as in the WHERE clause in the previous example to locate just the customers from the United States who are not in Seattle:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, " & _          "Phone, City, Country FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable() da.Fill(tbl) Dim aRows As DataRow() Dim row As DataRow aRows = tbl.Select("Country = 'USA' AND City <> 'Seattle'") For Each row In aRows     Console.WriteLine(row("CompanyName") & " - " & row("City") & _                       " - " & row("Country")) Next row

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, " +          "Phone, City, Country FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable(); da.Fill(tbl); DataRow[] aRows = tbl.Select("Country = 'USA' AND City <> 'Seattle'"); foreach (DataRow row in aRows)     Console.WriteLine(row["CompanyName"] + " - " + row["City"] +                       " - " + row["Country"]);

Conducting Wildcard Searches

ADO.NET allows you to search using wildcards. The following SQL query returns all customers that have a value for the CustomerID column that begins with the letter A:

SELECT CustomerID, CompanyName, ContactName, Phone     FROM Customers WHERE CustomerID LIKE 'A%'

You can use either % or * as a wildcard at the beginning or the end of the search string. For example, the following filter returns customers in New Hampshire, New Jersey, New Mexico, and New York:

strFilter = "State LIKE 'New %'"

The following filter returns customers from North and South Dakota:

strFilter = "State LIKE '% Dakota'"

ADO.NET does not let you use a single-character wildcard such as ? or _.

Working with Delimiters

You might have noticed that in both the database query example and the DataTable.Select example, we specified criteria for string-based columns. In each case, we surrounded the value with single quotes. This process appears simple in an example but can pose a bit of a problem for developers.

You can't simply surround a string with single quotes in your search criteria. Well, actually, you can, but you shouldn't. Say your application allows the user to locate an employee based on the employee's last name. When the application prompts the user for the last name, the user enters O'Malley. If you simply surround the literal value with single quotes in your search criteria, your string will look like this:

LastName = 'O'Malley'

If the delimiter appears in the literal string, however, you have to double it. In this situation, the search criteria should look like this:

LastName = 'O''Malley'

If you build search criteria dynamically, you must be sure to search your strings for delimiters. Use the Replace method of the String class to handle such situations. The following code snippet builds a string for the Select method and uses the Replace method of the String class to replace single quotes with two single quotes in the string:

Visual Basic .NET

strCriteria = "LastName = '" & strLastName.Replace("'", "''") & "'"

Visual C# .NET

strCriteria = "LastName = '" + strLastName.Replace("'", "''") + "'";

How do you delimit dates in search criteria? You surround the date with pound symbols, as shown here. (Thankfully, you don't have to worry about delimiters appearing in your dates.)

strCriteria = "OrderDate >= #01/01/2002# AND OrderDate < #02/01/2002#"

In some scenarios, you'll need to delimit your column names in your search criteria—perhaps because you have a space or another nonalphanumeric character in your column name or because your column name is a reserved word such as LIKE or SUM. ADO.NET uses square brackets as column delimiters. So, if your column name is Space In Name and you're looking for all rows that have a value of 3 in that column, you can use this string to locate those rows:

strCriteria = "[Space In Name] = 3"

What if you have a column delimiter in your column name? You can use an escape character (\) before the closing delimiter (]) in your criteria string. For example, if your column name is Bad]Column[Name and you're looking for all rows that have a value of 5 in that column, you can construct the following criteria string:

Visual Basic .NET

strCriteria = "[Bad\]Column[Name] = 5"

Visual C# .NET

strCriteria = "[Bad\\]Column[Name] = 5";

	Remember that in C#, the \ character is an escape character. In the previous code snippet, the actual string assigned to the strCriteriaFilter variable is "[Bad\]Column[Name] = 5"

Before (finally) moving on from delimiters, let's take a look at a code snippet that handles a really horrible scenario fairly elegantly. The DataTable in the code snippet contains a hideously named column that accepts strings. The code performs a search against that string, successfully delimiting the column name as well as the value to locate.

Visual Basic .NET

Dim tbl As New DataTable() tbl.Columns.Add("ID", GetType(Integer)) tbl.Columns.Add("Why]would[you ever\use.this#column/name?", _                 GetType(String)) tbl.LoadDataRow(New Object() {1, "Thompson"}, True) tbl.LoadDataRow(New Object() {2, "O'Malley"}, True) Dim strFilter, strFieldName, strValue As String Dim row As DataRow strFieldName = "Why]would[you ever\use.this#column/name?" strValue = "O'Malley" strFilter = "[" & strFieldName.Replace("]", "\]") & _             "] = '" & strValue.Replace("'", "''") & "'" For Each row In tbl.Select(strFilter)     Console.WriteLine(row(strFieldName)) Next row

Visual C# .NET

DataTable tbl = new DataTable(); tbl.Columns.Add("ID", typeof(int)); tbl.Columns.Add("Why]would[you ever\\use.this#column/name?",                  typeof(string)); tbl.LoadDataRow(new object[] {1, "Thompson"}, true); tbl.LoadDataRow(new object[] {2, "O'Malley"}, true); string strFilter, strFieldName, strValue; strFieldName = "Why]would[you ever\\use.this#column/name?"; strValue = "O'Malley"; strFilter = "[" + strFieldName.Replace("]", "\\]") +              "] = '" + strValue.Replace("'", "''") + "'"; foreach (DataRow row in tbl.Select(strFilter)) Console.WriteLine(row[strFieldName]);

	After reading the previous few paragraphs, I hope you've figured out the simple and elegant way to avoid problems with delimiters and reserved words in column names: Don't use them!!!

Using the Additional Select Methods

Like many methods in the ADO.NET object model, the Select method is overloaded. You can supply just a search string, but you can also include a sort order as well as a parameter to control the state of the rows you want to search (only added rows, for example, or only modified rows). Let's look at these overloaded methods briefly.

Including a Sort Order

In our initial Select method code snippet, we searched a DataTable that contained customer information to locate the customers from the United States that are not located in Seattle. We can control the order of the DataRow objects that the Select method returns by using one of the overloaded method signatures.

In a SQL query, you control the sort order of the data returned by the query using the ORDER BY clause. For example, the following query returns customers sorted by city:

SELECT CustomerID, CompanyName, ContactName, Phone, City     FROM Customers ORDER BY City

To sort by city in descending order, you simply change ORDER BY City to ORDER BY City DESC.

The overloaded Select method accepts a sort order, just as the SQL ORDER BY clause does. Here, we've modified our initial code snippet so that the DataRow objects returned by the Select method are sorted by the City column in descending order:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, " & _          "Phone, City, Country FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable() da.Fill(tbl) Dim strCriteria As String = "Country = 'USA' AND City <> 'Seattle'" Dim strSortOrder As String = "City DESC" Dim aRows As DataRow() = tbl.Select(strCriteria, strSortOrder) Dim row As DataRow For Each row In aRows     Console.WriteLine(row("CompanyName") & " - " & row("City") & _                       " - " & row("Country")) Next row

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, " +          "Phone, City, Country FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable(); da.Fill(tbl); string strCriteria = "Country = 'USA' AND City <> 'Seattle'"; string strSortOrder = "City DESC"; DataRow[] aRows = tbl.Select(strCriteria, strSortOrder); foreach (DataRow row in aRows)     Console.WriteLine(row["CompanyName"] + " - " + row["City"] +                       " - " + row["Country"]);

Specifying the RowState of the Rows to Search

As you learned in Chapter 6, the DataSet supports caching changes. What if you want to perform a search against just the modified rows in a DataTable?

You can use an overloaded Select method to specify a value from the DataViewRowState enumeration. Think of this value as an added filter to your search criteria. Say you want to examine just the modified and deleted rows in a DataTable. You can use the ModifiedOriginal and Deleted values from the DataViewRowState enumeration and use empty strings for the filter and sort parameters. The following code snippet does just that.

Visual Basic .NET

Dim dvrs As DataViewRowState dvrs = DataViewRowState.ModifiedOriginal Or DataViewRowState.Deleted Dim aRows As DataRow() = tbl.Select("", "", dvrs) Dim row As DataRow For Each row In aRows     Console.WriteLine(row("CompanyName", DataRowVersion.Original)) Next row

Visual C#

DataViewRowState dvrs; dvrs = DataViewRowState.ModifiedOriginal Or DataViewRowState.Deleted; DataRow[] aRows = tbl.Select("", "", dvrs); foreach (DataRow row in aRows)     Console.WriteLine(row["CompanyName", DataRowVersion.Original]);

	Remember that with deleted rows, you can examine only the original version of the row.