ADO.NET in a Nutshell

2017-07-07 02:10:07

The DataView object also gives you the opportunity to apply sorting and filtering logic that customizes how data will appear without modifying the underlying data itself.

12.2.1 Sorting with the DataView

To apply a sort to bound data, you simply set the DataView.Sort property with a string with the corresponding sort information. ADO.NET sorting uses the same syntax as the ORDER BY clause in a SQL query. For example, you might use the following SQL statement to order results by country:

SELECT * FROM Customers ORDER BY Country ASC

The equivalent ADO.NET code is shown here:

ds.Tables["Customers"].DefaultView.Sort = "Country ASC"; dataGrid1.DataSource = ds.Tables["Customers"];

The sort is according to the sort order of the data type of the column. For example, string columns are sorted alphanumerically without regard to case ( assuming the DataTable.CaseSensitive property is false ). Numeric columns are ordered using a numeric sort. Columns that contain binary data can't be sorted. Add ASC after a column name for an ascending sort (with smallest values first) or DESC for a descending sort.

Keep in mind that if you want to bind a control to the full DataSet , setting the DataView.Sort property will have no effect because the default DataView isn't used. Instead, you must modify the DataViewSetting.Sort property exposed through the DataViewManager :

ds.DefaultViewManager.DataViewSettings["Customers"].Sort = "Country ASC"; dataGrid1.DataSource = ds;

DataGrid binding is dynamic and updateable. If you change a value that affects the sort order, the affected row is automatically repositioned. Similarly, if you programmatically modify a DataView while it is in use (or the underlying data), the linked controls update themselves immediately.

You can also use nested sorts. To sort using multiple columns, just add a comma between each sort specification. For example, the following code sorts first by country and then orders all rows that have the same country by city:

ds.Tables["Customers"].DefaultView.Sort = "Country ASC, City ASC"; dataGrid1.DataSource = ds.Tables["Customers"];

Alternatively, instead of setting the DataView.Sort property, you can set the DataView.ApplyDefaultSort property to true . In this case, ADO.NET automatically creates a sort order in ascending order based on the primary key column of the DataTable . ApplyDefaultSort applies only when the Sort property is a null reference or an empty string, and when the table has a defined primary key.

12.2.2 Filtering by Column

To filter a DataView , you set a filter expression in the DataView.RowFilter property. Filtering by column works similarly to the SQL WHERE clause: it allows you to select rows that match the filter criteria. For example, consider the following SQL query, which filters rows based on two column values:

SELECT * FROM Customers WHERE Country='Argentina' AND City='Buenos Aires'

This translates into the ADO.NET code shown here:

ds.Tables["Customers"].DefaultView.RowFilter = "Country='Argentina' AND City='Buenos Aires'"; dataGrid1.DataSource = ds.Tables["Customers"];

If you use this code with the Northwind table, you receive three rows. The other rows are still present in the underlying DataTable , but they are hidden from view.

12.2.2.1 Filter operators

Like the WHERE clause, the RowFilter property allows a wide range of operators and functions for both numeric and string data types. Consider some of the following examples:

// Find all the rows that match one of the three specified countries. ds.Tables["Customers"].DefaultView.RowFilter = "Country IN ('Argentina', 'Canada', 'Japan')"; // Find all the rows where a Country isn't specified. ds.Tables["Customers"].DefaultView.RowFilter = "Country IS NULL"; // Use alphabetic comparison to find all the rows where the Country // starts with S or any letter after it in the alphabet // (including Switzerland, USA, UK, Venezuela, and so on). ds.Tables["Customers"].DefaultView.RowFilter = "Country > 'S'";

After you apply a sort, you can read the DataView.Count property to determine how many rows meet the criteria and will be displayed in data bound controls.

With numeric values, you can use ranges or mathematical operators to filter rows. For example, here are some filters for the Products table:

// Find all the rows where UnitPrice is greater than 10. ds.Tables["Products"].DefaultView.RowFilter = "UnitPrice > 10"; // Find all the rows where UnitPrice is above 10 but below 15. // This is an exclusive range. ds.Tables["Products"].DefaultView.RowFilter = "UnitPrice > 10 AND UnitPrice < 15"; // Find all the rows where UnitPrice is anywhere from 10 to 15. // This is an inclusive range. ds.Tables["Products"].DefaultView.RowFilter = "UnitPrice BETWEEN 10 AND 15"; // Find all prodcuts where the total stock value is at least 00. ds.Tables["Products"].DefaultView.RowFilter = "UnitPrice * UnitsInStock > 1000";

Table 12-1 lists the most common filter operators.

Table 12-1. Filter operators

Operator	Description
AND	Combines more than one clause. Records must match all criteria to be displayed.
OR	Combines more than one clause. Records must match at least one of the filter expressions to be displayed.
NOT	Reverses an expression. Can be used in conjunction with any other clause.
<, >, <=, and >=	Performs comparison of values. These comparisons can be numeric (with numeric data types) or alphabetic dictionary comparisons (with string data types).
BETWEEN	Specifies an inclusive range. For example, `Units BETWEEN 5 AND 15` selects rows that have a value in the Units column from 5 to 15.
<> and =	Performs equality testing.
IS NULL	Tests the column for a `null` value.
IN(a,b,c)	A short form for using an OR clause with the same field. Tests for equality between a column and the specified values (a, b, and c).
LIKE	Performs pattern matching with string data types.
+	Adds two numeric values, or concatenates a string.
-	Subtracts one numeric value from another.
*	Multiplies two numeric values.
/	Divides one numeric value by another.
%	Finds the modulus (the remainder after one number is divided by another).

12.2.2.2 Pattern-matching filters

The LIKE keyword performs pattern matching on strings. Pattern matching is akin to regular-expression syntax but is much less powerful. Unfortunately, the pattern matching provided by ADO.NET, while similar to that provided in SQL Server, lacks a few features. Notably, the _ character (which represents a single variable character) and the [ ] brackets (which specify a character from a range of allowed values) aren't supported. However, you can use the % character to specify zero or more characters .

Here are two examples of pattern matching with ADO.NET:

// Use pattern matching to find all the countries that start with // the letter "A" (includes Argentina, Austria, and so on.) ds.Tables["Customers"].DefaultView.RowFilter = "Country LIKE 'A%'; // Matches contacts that contain the word "Manager" // (includes Sales Manager, Marketing Manager, and so on). ds.Tables["Customers"].DefaultView.RowFilter = "ContactTitle LIKE '%Manager%'";

12.2.2.3 Filter-supported functions

Finally, you can also use a few built-in SQL functions to further refine a column sort. These features (detailed in Table 12-2) allow you to perform comparisons that include null values, parse a portion of a string, or even perform an aggregate query on related child rows.

// Display records where the country name is longer than eight characters // (includes Venezuela, Argentina, and Switzerland). ds.Tables["Customers"].DefaultView.RowFilter = "Len(Country) > 8"; // Display records where the second and third letter are "ra" // (includes Brazil and France). // Note that this expression uses 1-based counting. ds.Tables["Customers"].DefaultView.RowFilter = "Substring(Country, 2, 2) = 'ra'"; // Display all the columns that have a region code of SP, or a null value. ds.Tables["Customers"].DefaultView.RowFilter = "IsNull(Region, 'SP') = 'SP'";

Table 12-2. Filter-supported functions

Function	Description
Sum, Avg, Min, Max, and Count	These aggregate functions return a single calculated number by examining several. They are used in conjunction with child rows.
Convert(value, type)	Allows you to modify the data type of a column. This is useful if you need to perform a numeric operation with a string column or vice versa.
Len	Returns the number of characters in a string.
IsNull(exp, replacement)	Returns the replacement value if the column is `null` or the column value otherwise .
IIF(exp, trueval, falseval)	Returns one of two values, depending on whether the specified condition evaluates to `true` or `false` .
SubString(string, start, length)	Retrieves a portion of a string field. The start value uses 1-based counting (the first letter is designated as 1, not 0).

12.2.2.4 Aggregate functions and relations in filters

You can also use aggregate functions to create a filter that restricts related child rows. For example, you can look at all customers that have total orders greater than a certain dollar figure. You can also return all the region records that have at least 20 matching customers. In order to use this technique, however, you need to create a DataRelation between the related tables first.

The basic syntax is Child(RelationName).ColumnName or Parent(RelationName).ColumnName . Here are a few examples that use the Suppliers and Products tables, which are linked on the SuppliersID column using a relation named Suppliers_Products :

// Only display products for a specific supplier. ds.Tables["Products"].DefaultView.RowFilter = "Parent(Suppliers_Products).CompanyName='Tokyo Traders'"; dataGrid1.DataSource = ds.Tables["Products"]; // Display suppliers that have at least five related products. ds.Tables["Suppliers"].DefaultView.RowFilter = "Count(Child(Suppliers_Products).SupplierID) >= 5"; dataGrid1.DataSource = ds.Tables["Suppliers"]; // Display suppliers that have at least one product with more than 50 units // in stock. ds.Tables["Suppliers"].DefaultView.RowFilter = "Max(Child(Suppliers_Products).UnitsInStock) > 50"; dataGrid1.DataSource = ds.Tables["Suppliers"];

Example 12-2 presents the full code needed to create a relationship, add it to the DataSet , and then use it with a relational filter expression.

Example 12-2. Using a relational filter expression

private void RelationTest_Load(object sender, System.EventArgs e) { string connectionString = "Data Source=localhost;" + "Initial Catalog=Northwind;Integrated Security=SSPI"; string SQL = "SELECT * FROM Suppliers"; // Create ADO.NET objects. SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(com); DataSet ds = new DataSet("Northwind"); // Execute the command. try { con.Open(); adapter.Fill(ds, "Suppliers"); com.CommandText = "SELECT * FROM Products"; adapter.Fill(ds, "Products"); } catch (Exception err) { Console.WriteLine(err.ToString()); } finally { con.Close(); } // Create references to the parent and child columns. DataColumn parentCol = ds.Tables["Suppliers"].Columns["SupplierID"]; DataColumn childCol = ds.Tables["Products"].Columns["SupplierID"]; // Create the DataRelation object. DataRelation relation = new DataRelation("Suppliers_Products", parentCol, childCol); // Add the relation to the DataSet. ds.Relations.Add(relation); // Define the filter expression for the Suppliers table. ds.Tables["Suppliers"].DefaultView.RowFilter = "Count(Child(Suppliers_Products).SupplierID) > 3"; // Display the table. dataGrid1.DataSource = ds.Tables["Suppliers"]; }

When you try this code, you'll find that as a side effect, the DataGrid automatically adds navigation links that allow you to view the related child rows of a supplier. These navigational links (shown in Figure 12-3) use the name of the corresponding DataRelation .

Figure 12-3. Relational data in the DataGrid

12.2.3 Filtering by Row State

The DataView.RowStateFilter property allows you to hide or show rows based on their state. Table 12-3 shows the DataViewRowState enumeration values that set the RowStateFilter . You can use any one of these values or a bitwise combination of values.

// Show only deleted rows. ds.Tables["Products"].DefaultView.RowStateFilter = DataViewRowState.Deleted; // Show deleted and added rows. ds.Tables["Products"].DefaultView.RowStateFilter = DataViewRowState.Deleted DataViewRowState.Added;

By default, the RowStateFilter is set to CurrentRows and shows everything except rows that are scheduled for deletion.

Table 12-3. Values from the DataViewRowState enumeration

Value	Description
Added	A new row that will be inserted into the data source when the next update is performed.
CurrentRows	Current rows, including unchanged, new, and modified rows. This is the default.
Deleted	A deleted row that is removed from the data source when the next update is performed.
ModifiedCurrent	A row that exists in the `DataSet` but has been modified.
ModifiedOriginal	The original version (although it has since been modified and is available as `ModifiedCurrent` ).
None	No rows will be shown.
OriginalRows	Original rows including unchanged and deleted rows.
Unchanged	A row that exists in the `DataSet` and has not been modified.

12.2.4 Displaying Multiple Views

One of the most useful aspects of the DataView is the ability to create multiple DataView objects to provide different representations of the same data. This technique is quite straightforward and is shown in Example 12-3 with three separate DataGrid controls. Each DataView applies a different SQL filter expression using the RowFilter property.

Example 12-3. Binding the same data with different views

private void MultipleView_Load(object sender, System.EventArgs e) { string connectionString = "Data Source=localhost;" + "Initial Catalog=Northwind;Integrated Security=SSPI"; string SQL = "SELECT * FROM Customers"; // Create ADO.NET objects. SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(com); DataSet ds = new DataSet("Northwind"); // Execute the command. try { con.Open(); adapter.Fill(ds, "Customers"); } catch (Exception err) { Console.WriteLine(err.ToString()); } finally { con.Close(); } // Create views. DataView viewArgentina = new DataView(ds.Tables["Customers"]); DataView viewBrazil = new DataView(ds.Tables["Customers"]); // Filter views. viewArgentina.RowFilter = "Country = 'Argentina'"; viewBrazil.RowFilter = "Country = 'Brazil'"; // Perform data binding. gridArgentina.DataSource = viewArgentina; gridBrazil.DataSource = viewBrazil; gridAll.DataSource = ds.Tables["Customers"].DefaultView; }

Notice that if you modify a row in one view, the changes appear automatically in all other views. Remember, there is only one data sourcethe linked DataTable .

Figure 12-4 shows the three views, each of which contains only a subset of the full data in the DataTable .