evelopers frequently must work with data at different levels, with different tools, and in different forms. This article is the first in a two-part series on some of the more common data challenges that developers face. In Part 1 of this article, I'll cover some capabilities in ADO.NET 2.0, ASP.NET 2.0, and T-SQL 2005. Part 2 will feature some additional T-SQL 2005 coverage, as well as ways to use .NET 2.0 generics.
Beginning with the End in Mind: An Entire Potpourri of Data Handling Tips
Recently I presented some CodeCamp sessions on different topics dealing with data. Some covered the new T-SQL enhancements of interest to developers, some covered new techniques in ADO.NET 2.0, and some even got into handling data in Web applications. One attendee approached me afterwards and told me he was pleasantly surprised that someone who's listed as an MVP for C# would talk so much about data and what could be done today. I smiled and told him that my next article for CoDe Magazine
was an article on data-handling techniques, and that the experiences of the day (including some questions I received from this gentleman) helped to further shape this article.
Regardless of whether you work with C# or Visual Basic .NET or any other language, what you're really working with throughout the day is DATAwhether you're retrieving it, summarizing it, presenting it, etc. As an application developer your chief task is handling data. Don't ever let anyone tell you differently.
Many developers don't just handle data in one tool. You may work with the database, with a report writer, with third-party utilities, with the presentation layer, or other areas altogether. You have many tools and many choices! Someone could write a book on different ways to handle data in the .NET world.
So in this article I'll present the first-ever Baker's Dozen doubleheader. Game one will cover the following:
- Five different areas of ADO.NET (typed datasets, data math, data relations, filtering data, and summing data).
- Four different tips for handling data in an ASP.NET 2.0 application.
- Four different tips for retrieving data using the new capabilities in T-SQL 2005.
The nightcap of this twin-bill will cover practical uses of .NET generics for handling data along with coverage of the new ObjectDataSource class, and some additional techniques for retrieving data in T-SQL 2005. So
let's play ball!
Tip 1: Typed Datasets 101
A typed dataset is a strongly-typed container for collections of business entity data. It inherits from the System.Data.DataSet class. However, unlike the standard dataset that features late-binding and weak typing, typed datasets provide strongly-typed access to data. Typed datasets expose table, column, and relation objects as named properties, rather than generic collection elements.
Typed datasets give you the ability to use IntelliSense for statement completion, and type checking at compile time. In the context of reporting, typed datasets aid in data-driven report design and provide an excellent form of self-documentation of stored procedure result sets. Coding against typed datasets (as I'll show you in different examples) adds a level of efficiency and structure.
You can create a typed dataset in one of two ways. If you have an existing schema (or can build one easily), you can use an approach similar to the following:
|While T-SQL doesn't lend itself to the level of program modularity that C# or Visual Basic developers enjoy, the APPLY operator improves the integration of reusable table-valued UDFs with the different stored procedures that call them.|
DataSet DsReturn = this.RunStoredProc("GetAgingReceivables");
// Define a DataSet name and table names
// (we could use Table Mappings as well)
DsReturn.DataSetName = "dsAgingReport";
DsReturn.Tables.TableName = "dtAgingDetails";
DsReturn.Tables.TableName = "dtAgingSummary";
DsReturn.Tables.TableName = "dtAgingBrackets";
DsReturn.Tables.TableName = "dtClients";
// Write out the schema
After running this test code (which you could make into a schema generator utility), you can take the final schema and add it to Visual Studio 2005 as a typed dataset. In Solution Explorer, right-click, choose "Add," then "Existing Item
," and then navigate to the folder that contains the XSD file. Once Visual Studio builds the typed dataset, you have different options for viewing/editing it. If you want to use the standard XML schema editor, just right-click on the dataset in Solution Explorer and select "Open With…".
If you're building a typed dataset definition from scratch, where no schema yet exists, you can create the typed dataset manually using the DataSet Designer. However, if you find that you're doing this more often than building a typed dataset from an existing schema, you may want to re-think your approach.
OK, so you've created a typed dataset. So what? Well, let's take a look at what a typed dataset gives you.
First, a typed dataset gives you strong typing and statement completion in IntelliSense (see Figure 1
). Compare that to an untyped example, where you either must refer to columns by column number, or type it in as an expression. In addition, the example in Figure 1
does not need to cast any column to the corresponding data type. In my opinion, eliminating the need to cast a column object to the necessary type is one of the most attractive features of typed datasets.
|Figure 1: Typed dataset statement completion.|
Second, you can easily define default column values in the Dataset Designer by setting the defaultvalue
Third, if you've defined a primary key for any strongly-typed DataTable in the Dataset Designer, the class exposes a Find
method associated with the column name. As a result, you can perform a FIND
against a table with a primary key of OrderID
by using FindByOrderID
. If the primary key is a concatenation of two columns (such as CustomerID
), the developer would use the pre-generated method FindByCustomerIDOrderID
. I find this much more efficient than any of the alternatives.
Fourth, if a typed dataset contains table relations, the XML schema definition tool adds methods to the typed dataset class for traversing hierarchical data. For example, a developer can write parent/child code for the tables DtOrderHeader
by using GetDtOrderDetailRows
that the .NET schema tool automatically generates. (I'll cover this in Tip 3).
Fifth, you can more easily work with null values using typed datasets. Each strongly-typed DataRow contains a method to check if a column value is null, and to set a column value to null.
// Set value to NULL
// Check for null value
Sixth, some critics of typed datasets in Visual Studio .NET 2003 will acknowledge that the new partial class capability in Visual Studio 2005 makes it easier to extend typed datasets. While partial classes are a great new feature, one could subclass a typed dataset even in VS2003 to add validation code or other capabilities. In the January/February 2006 issue of CoDe Magazine
, I presented some code that subclassed a typed dataset to add XML import functionality.
Typed datasets aren't perfect. They carry overhead. While certain specific aspects of typed datasets are faster than untyped datasets, instantiating a complex typed dataset can be costly. An application that frequently creates an instance of the same typed dataset may spend a measurable percentage of time on the creation process. Uri N. has developed a proxy class for typed datasets so that the actual creation only needs to occur once. You can find his .NET class on CodeProject
Some developers complain about the default naming conventions in the typed dataset class for items like the DataTable/DataRow objects and methods/events. The generated names may not be consistent with the preferred naming conventions on your development projects. Fortunately, developers can use typed dataset annotations to solve some of the common naming issues, which allow developers to modify the names of elements in the typed dataset without modifying the actual schema.
Annotations also allow you to specify a nullValue
annotation to instruct the typed dataset class how to react when a column is DbNull. You can optionally tell the typed dataset to return an empty string or a default replacement value. Shawn Wildermuth demonstrates typed dataset annotations in this excellent online article
(if you are not familiar with Shawn, he has written numerous materials on ADO.NET and is an outstanding writer).