Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

Before I wade into ADO.NET, we need to pause briefly and talk about an alternativethe Data Access Application Block (DAAB). For some reason, Microsoft felt that developers needed another way to create data access applications without ADO.NET. I expect they wanted to further distance themselves from a "low-level" data access paradigm that could be used with any upcoming data access interface. To this end, they created the DAAB sample data layer to make the process of working with data "easier". As I see it, the DAAB won't help much, and it could just slow you down. For one thing, it requires that you learn an entirely new set of classes, methods, and properties, as well as a new set of behaviors, constraints, and limitations. New people coming into an organization who are required to use the DAAB will have to learn your team's coding regimen and the DAABeven when they know how to code ADO.NET with acuity. Upon closer inspection, I (and many others) have found the DAAB makes some of the same mistakes made by other applications using ADO.NET directlybut without the benefit of being able to easily trace through "straight" ADO.NET code. I've heard reports of leaking connections and other issues that can be eliminated through "best practices" coding techniques. As the 2.0 Framework ships, there is no 2.0 version of the DAAB, but I expect it to catch up eventually. Whether or not you should use it is another question.

Developers have to make some fundamental decisions before writing their first line of data access codeincluding whether or not to use the DAAB. Several of these decisions have to do with the balance between client-side ADO.NET code and server-side T-SQL (and CLR) code. Should ADO.NET be tasked with fetching and updating database data, or just fetching and executing custom update logic that posts changes as parameters back to the database to be posted to the database via stored procedures? For example, in order to use the SqlDataAdapter Update method to post changes to the server, developers have to plan a data access strategy that returns rowsets from a single table and manage the DataTable objects individually. This usually means you'll have to create and configure "N" SqlDataAdapter objectsone for each database table you intend to update.

The 2.0 Framework and Visual Studio have attempted to make this process easier (in a way) by introducing the strongly typed TableAdapter class, which can make this table-centric approach easier. While the jury is still out on the TableAdapter, many developers have abandoned the DataAdapter Update approach (by whatever means) in favor of calling SQL Server stored procedures to post changes to the database. A big reason behind this decision is that their protected SQL Server databases are procedure-driven. That is, the DBAs have already locked out access to base tables, and all updates must be done via stored procedures which write to one or many tables using server-side transaction management. I suggest you consult with your DBA before launching out on any far-reaching data access strategyit will save you time and hair follicles in the long run.

At least one of my reviewers chides me on my steadfast endorsement of designs built on stored procedures, and some at Microsoft and others in the industry agree. They would have you build applications using Object Relational Mapping tools and designs that (IMHO) put you even further behind the learning curve. They would have you manage transactions at the client instead of in a common procedure that all applications can leverage. I expect that the well-worn and well-understood stored procedure designs will be around for quite some time, despite those that say we're just being hard-headed about change. Perhaps we are, but perhaps we like to stick with architectures that simply work.