Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataView – Part 11

Posted by Ravi Varma Thumati on November 19, 2009

Data binding, a technique for displaying data without writing any code, has suffered from a poor reputation. In the past, the only applications that could use it successfully were simple report-generating tools or thin database wrappers that were typically written in a high-level language such as Visual Basic or Microsoft Access Basic. These applications were easy to program but notoriously limited, inflexible, and performed poorly.

Almost every data-binding solution suffered from the same well-known problems:

  • Continuous connection requirements. With traditional data binding, connections are required for long periods of time, usually as long as a window is being displayed. This might be acceptable for a small client-server application, but it won’t work in a distributed environment or with a web-based application.
  • Little customizability. The developer can’t control any part of the data binding process, has little ability to apply validation logic, and can’t customize the display of most of the controls that support data binding.
  • Tight coupling. Data binding breaks down the separation needed for good three-tier design. Not only is it hard to reuse and likely to stop working if the structure of the data source changes, data binding code also can’t be ported from one programming language or environment to another.

ADO.NET addresses these issues with a customizable and reusable data-binding framework that centers on two classes: the DataView and DataViewManager. This chapter examines these classes and explores the two data-binding models used in .NET: Windows Forms and ASP.NET.

The DataView and DataViewManager

Data binding depends on two classes in the System.Data namespace: DataView and DataViewManager. These classes provide an important layer of indirection between your data and its display format, allowing you to apply sorts and filter rows without modifying the underlying information—that is, to have different views on the same data. ADO.NET binding is always provided through one of these objects.

The DataView class acts as a view onto a single DataTable. When creating a DataView object, you specify the underlying DataTable in the constructor:

// Create a new DataView for the Customers table.

DataView view = new DataView(ds.Tables[“Customers”]);

Every DataTable also provides a default DataView through the DataTable.DefaultView property:

// Obtain a reference to the default DataView for the Customers table.

DataView view = ds.Tables[“Customers”].DefaultView;

The DataViewManager represents a view of an entire DataSet. As with the DataView, you can create a DataViewManager manually, passing in a reference to a DataSet as a constructor argument, or you can use the default DataViewManager provided through the DataSet.DefaultViewManager property.

The DataView and DataViewManager provide three key features:

  • Sorting based on any column criteria
  • Filtering based on any combination of column values
  • Filtering based on the row state (such as deleted, inserted, and unchanged)

Binding to a DataView

To make all this a little clearer, it helps to consider a simple example with the Windows DataGrid control. In Example 12-1, three tables are queried and added to a DataSet. By setting its DataSource property, the Customers table is then bound to the DataGrid in a single highlighted line.

Example 12-1. Binding a single table from a DataSet

private void DataTest_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”);

com.CommandText = “SELECT * FROM Products”;

adapter.Fill(ds, “Products”);

com.CommandText = “SELECT * FROM Suppliers”;

adapter.Fill(ds, “Suppliers”);

}

catch (Exception err)

{

MessageBox.Show(err.ToString());

}

finally

{

con.Close();

}

// Show the customers table in the grid.

dataGrid1.DataSource = ds.Tables[“Customers”];

}

On the surface, it looks as though this code is binding the grid directly to a DataTable object. However, behind the scenes, .NET retrieves the corresponding DataTable.DefaultDataView and uses that. You can replace the highlighted line with the following equivalent syntax:

dataGrid1.DataSource = ds.Tables[“Customers”].DefaultView;

Similarly, you can create a new DataView object and use it for the binding:

DataView view = new DataView(ds.Tables[“Customers”]);

dataGrid1.DataSource = view;

This technique is particularly useful if you want to display different views of the same data in multiple controls. Figure 12-1 shows the result of binding the view. The DataGrid automatically creates a column for each field in the table and displays all the data in the order it was retrieved from the data source. By default, every column is the same width, and the columns are arranged according to the order of fields in the SELECT statement; you’ll learn how to customize the view later in this chapter.

There is one reason why you should bind directly to the view rather than use the table name. If you specify an invalid table name when binding directly to a table, you don’t receive an error; the DataGrid just appears empty. However, if you make the same mistake when binding to a view, you receive a more informative NullReferenceException.

Figure 12-1. Binding a DataView to a DataGrid

Binding to a DataViewManager

The DataGrid is the only Windows Forms control that supports binding to an entire DataSet as well as a single table, although many other third-party controls follow suit. When binding a DataSet, .NET automatically uses the corresponding DataViewManager provided through the DataSet.DefaultViewManager property:

// Bind to the DefaultViewManager explicitly.

dataGrid1.DataSource = ds.DefaultViewManager;

// Bind to the DefaultViewManager implicitly. This code is equivalent.

dataGrid1.DataSource = ds;

// Bind to an identical DataViewManager you create manually.

// This code has the same effect, but isn’t exactly the same

// (because it creates a new object).

dataGrid1.DataSource = new DataViewManager(ds);

Figure 12-2 shows the initial appearance of a DataGrid when bound to a DataSet. A separate navigational link is provided for every table in the DataSet. When the user clicks on one of these links, the corresponding table is shown, as in Figure 12-2.

Figure 12-2. Binding a DataViewManager to a DataGrid

There is one important difference between the DataViewManager and the DataView approach, however. When you use the navigational links to display a table, .NET doesn’t use the DefaultView to configure the appearance of the that table. Instead, every DataViewManager provides a collection of DataViewSetting objects. When the user navigates to a table through a DataViewManager, a new DataView is created according to the settings in the corresponding DataViewSetting object.

Sorting and Filtering

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.

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;

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.

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.

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'”;

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 $1000.

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).

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%'”;

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).

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

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.

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 source—the linked DataTable.

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

Figure 12-4. Multiple views of the same data

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: