p&p Data Access Application Block - Review

Almost all the sites will be portraying the salient benefits of this application block (AB), so I decided my review to hint on the issues {my perception} and enhancements needed. Year back I did a feature analysis with Ver 1.0 of the AB. After a year I decided to look back into this AB to see if there are any major improvements or feature additions. For this I used both the Ver 2.0 release from Microsoft and Ver 3.1 release from GotDotNet (GDN) workspace. There were few enhancements in GDN version, but it is anyone’s guess why Microsoft hasn’t take clue from GDN version.

I list out what i feel some of the missing features:
Critical
Resource clean-up
        This one is very severe, there are many places where and object is created and not cleaned up properly, for example a SQLConnection object is created and some command is executed, but there is no clean-up of connection when there is an exception. A SQLCommand object created is not cleaned up anywhere. I still see lot of open issues in GDN bug tracker.
Command time-out
        Though looks like a simple feature, this actually helps the system scalability. Nobody would like to end up figuring out long running queries and to fix scalability issue at a fag end of a project. Rather this should be configurable from application and also a way to set configurable max limit for each connection.
Logging execution information
        It is critical for any data access layer to log the excution information like executing query, parameters and exception. This should be configurable for a connection both from application and externally, also it should be having multiple levels like TraceSwitch.
Support and Extensibility to any data store
        Microsoft version is written very specific to SQLServer and designed with very less flexibility to extensions to other data store. GDN version has taken the first step in this by using an abstract ADOHelper.cs, but there is a long way to go.
Abstraction of ADO.Net implementation
        I would still like to see more abstraction from ADO.net workings. Still developers need to work with connection and parameter objects in many situations. I would like to see helper methods for parameter creation that hides ADO.net implementations.

Non-Critical
Single store for connection string information
        I learnt this from a client I worked with, the concept of having a logical data source and maintaining connection information for that in an external store {registry, xml config} is advantageous in many facets. First an application will not be able to arbitrarily connect to any data store. You can enforce constraints for a logical data source, like max command time out, you will be happy to see that you are not allowing a command to run for hours against a real-time db. Without any code/config changes, we can point an application to appropriate environments like dev/qa/prod.
Design flexibility & extensibility
        The static nature of the design might suit very well for some situations.But I often end up in situation where I needed to store information specific to a connection like a transaction object, thus I feel an object would be more convenient for a data access block. But looking at all other application blocks, it seems obvious that Microsoft wants to go all out static design. Also the extension for other data store is not easy with the existing design.
Application configuration file issues in COM+ environment
        GDN version is now using app.config for storing provider information. Having app.config for server-activated serviced component assembly means managing the app.config is going to be complex. With SOA wave all around us, this issue is now needs more attention. COM+ 1.5 provides an option of having an individual app.config for each server application package, better than a single app.config for all server-activated serviced components. But still managing these app.config files is still going to be tough; it’s not anymore a just XCOPY.

        Existing feature’s of runtime discovery of stored procedure parameters, I would not recommend unless there is a very strong reasons to use this. Idea of parameter cache looks good, but practically I haven’t had a chance to use this feature, that too with help of OpenXML that use looks remote to me.

        Everyone should agree that p&p application blocks are designed to be extensible and hence all of the missing features mentioned can be included by customizing the block. But the question is how much customization you need. I neither offend nor defend the usage of this application block; it depends on the architectural requirement. More than anything this component is an integral part of your enterprise architecture.