Searching and Analyzing Data

Introduction

This chapter focuses on searching for records in views and tables, calculating values based on values in the same or other tables, and navigating data relations between tables.

The DataView is a data-bindable view of a DataTable that presents data with different sort orders and filters. You can create multiple views for each table; every table has a default data view. The DataViewManager class helps to manage the default data views for tables in a DataSet . Recipe 3.1 demonstrates how to use the DataView and DataViewManager class to filter and sort data in a DataSet . Recipe 3.12 shows how to filter a data view for rows that have null values.

The DataRelation class creates a parent/child relation between two tables in a DataSet . The DataRelation maintains referential integrity and you can use it to cascade updates and deletes. It can also be used to navigate between the tables. Recipe 3.4 shows how to use the data relation to get the parent row and the child rows for any row.

The DataTable and DataView classes both provide several ways to locate records from specified criteria. Searching an existing table or view saves a roundtrip to the database server. Although the DataView is created from a DataTable , searching is done differently in each. Recipe 3.8 shows how to find rows in a DataTable while Recipe 3.9 shows how to find rows in a DataView . You can create a DataView from a typed DataSet to search or filter. Recipe 3.11 shows how to convert untyped rows from the DataView to typed DataRow objects.

ADO.NET does not provide a way to compare two DataSet objects with identical schemas to determine the data differences between the two. Recipe 3.3 creates a method GetDataSetDifference( ) that returns the differences as a DiffGram , which is an XML format that identifies original and current versions of data and is used by .NET to serialize and persist the DataSet .

In addition to recipes about the DataRelation , DataSet , DataTable , and DataView classes, this chapter covers:

Expression columns

Expression columns are calculated from column values in the same row or from an aggregation of values from rows in the table or in a related table. The expression column is not stored but calculated whenever its value is requested and can be used like any other columns. For example, you can sort and filter tables and views on an expression column. Recipe 3.2 shows how to add an expression column to a table to calculate a value that is calculated from other values in the same row. Recipe 3.7 shows how to create an expression column to get a value from a parent table and an expression column that will aggregate values from a child table.

Globalization and localization

Globalizing is creating applications that support multiple cultures. Localizing is customizing an application to support a specific culture primarily by translating the user interface. The System.Globalization namespace contains classes that control the display of culture-specific values such as currencies and dates. Recipe 3.5 shows how to display data formatted based on a user's culture settings.

Advanced queries

ADO.NET does not provide a way to get the TOP N rows from a DataTable based on the value of a column in the table. Recipe 3.10 shows how to build a filter on the DataView at runtime to return the TOP N rows.

Queries sometimes need to be based on data from more than one data source. Recipe 3.6 shows how to return a result set from a query based on more than one table using ad-hoc connector names that allow data from heterogeneous data sources to be accessed by providing the connection information in the SQL statement.

The COMPUTE BY clause returns both summary and detail data in a single result set from a single SELECT statement. Recipe 3.13 shows how to execute a COMPUTE BY statement and how to navigate the result set.

The Shape language uses Data Shaping Services for OLE DB as an alternative to JOIN and GROUP BY syntax to generate hierarchical result sets. Recipe 3.14 shows how to use the Shape language to get a hierarchical result set from SQL Server using the OLE DB .NET data provider, and how to navigate the result set.