New book: Microsoft ADO.NET 4 Step by Step

  • Article

638884.inddWe’re pleased to announce that Tim Patrick’s new book Microsoft ADO.NET 4 Step by Step (ISBN 9780735638884; 440 pages), is now available for purchase!

ADO.NET is Microsoft's interface between your applications and your data. Using various providers and a little forethought and planning, you can access data from SQL Server, Oracle, or other databases in a generic way, such that changing providers requires either no or only minor changes to your application code. It's true: ADO.NET makes data access easier than ever before. Still, ADO.NET is big, and learning to use it effectively and efficiently can take some time. Tim Patrick has broken this complex topic into manageable chunks. Using the tried and true Step by Step approach, which gets you started by walking you through important procedures, he provides numerous tutorials that you can follow to get up to speed as fast as possible. After learning the basics of ADO.NET--connecting to databases, accessing data in tables, making dynamic queries and running stored procedures, you'll explore how to deal with that data through various readers, in-memory DataSets, and discover considerations for displaying, inserting, and modifying data. You'll learn about standards and security, data integrity, debugging, and then move into more advanced topics such as mapping data to classes using the Entity Framework, querying data with LINQ, both LINQ to SQL and LINQ to Entities, and finally, get an introduction to using ADO.NET for Windows Communication Foundation services, data binding to user interface elements, and in restful services. With the solid grounding that Microsoft® ADO.NET 4 Step by Step provides, you'll be building complex database applications in no time.

Here’s an excerpt from Chapter 6, “Turning Data into Information.”

 

Chapter 6

Turning Data into Information

After completing this chapter, you will be able to:

  • Return a value that aggregates data from a table column
  • Add a column that aggregates data from a table, or from its parent or child table
  • Build an index-based view of a table
  • Generate a new table based on a projected view of the original table

After you have joined DataTable instances together in a DataSet, ADO.NET enables a few
more features that let you use those table relationships to analyze and select data. These
features build upon some of the single-table functions covered in earlier chapters.

This chapter introduces the data-aggregation features included in the ADO.NET Framework,
expressions that summarize data across multiple table rows. Although not as powerful as
the aggregation features found in relational database systems, the DataTable variations still
provide quick access to multirow data summaries. The chapter ends with an introduction to
the DataView class, which lets you establish row selection, filtering, and sorting standards for
a DataTable.

Note-- The exercises in this chapter all use the same sample project, a tool that demonstrates
aggregate and data view features. Although you will be able to run the application after each
exercise, the expected results for the full application might not appear until you complete all
exercises in the chapter.

Aggregating Data

An aggregation function returns a single calculated value from a set of related values.
Averages are one type of data aggregation; they calculate a single averaged value from an
input of multiple source values. ADO.NET includes seven aggregation functions for use in
expression columns and other DataTable features.

  • Sum Calculates the total of a set of column values. The column being summed must
    be numeric, either integral or decimal.
  • Avg Returns the average for a set of numbers in a column. This function also requires
    a numeric column.
  • Min Indicates the minimum value found within a set of column values. Numbers,
    strings, dates, and other types of data that can be placed in order are all valid for the
    target column.
  • Max Like Min, but returns the largest value from the available column values. As with
    the Min function, most column types will work.
  • Count Simply counts the number of rows included in the aggregation. You can pass
    any type of column to this function. As long as a row includes a non-NULL value in that
    column, it will be counted as 1.
  • StDev Determines the statistical standard deviation for a set of values, a common
    measure of variability within such a set. The indicated column must be numeric.
  • Var Calculates the statistical variance for a set of numbers, another measurement related
    to the standard deviation. Only numeric columns are supported.

These seven data aggregation features appear as functions within ADO.NET expressions.
Expressions were introduced in the “Using Expression Columns” section of Chapter 4,
“Accessing the Right Data Values.” String expressions form the basis of custom expression columns
and are also used in selecting subsets of DataTable rows. To aggregate data, use one of
the following function formats as the expression string:

  • Sum(column-name)
  • Avg(column-name)
  • Min(column-name)
  • Max(column-name)
  • Count(column-name)
  • StDev(column-name)
  • Var(column-name)

In ADO.NET, aggregates always summarize a single DataTable column. Each aggregate function
considers only non-NULL column values. Rows that contain NULL values in the specified
column are excluded from the aggregation. For example, if you take the average of a table
column with 10 rows, but 3 of those rows contain NULL values in the column being averaged,
the function will average only the 7 non-NULL values. This is especially useful with the Count
function; it counts only the number of rows that have a non-NULL value for the passed column
name. If all the column values are NULL, or if there are no rows to apply to the aggregation
function, the result is NULL (System.DBNull).