This week, I am working with a client on a project that includes web services. And my current job is building a data access layer. We are building a .NET application and interfacing to a SQL Server 2000 box. We've all had to do this kind of work before, and it's not real glamorous. ADO.NET is so simple, but repeatedly writing a function that is different by a stored procedure name gets monotonous. However, it is critical work and the guy building the layer above yours would appreciate if the data layer works correctly, performs well, and allows for getting data easy (i.e. less lines of code). What are options for getting out of this repetitious writing of data access code?
Microsoft's Data Access Application Block
If you've been around the Microsoft world in the past year, then you have heard about the Data Access Application Block. I have heard many positive comments about it and now a second version is out (requires .NET v1.1). Never having used it before, I had to check it out. There is an overview at http://msdn.microsoft.com/architecture/default.aspx?pull=/library/en-us/dnbda/html/daab-rm.asp. This version includes download links for both versions, system requirements, a class overview, samples, and a list of enhancements. The basic idea is that I can write less of the repetitive code (look up connection string, create connection and command objects, etc).
New features in v2: support for Typed Datasets, dataset updates, additional DataRow methods, and bug fixes.
v1 and v2 are mostly built into a shared (or static) class called SqlHelper.
Atomic DAL
My first experience with a Data Access Layer helper class was while working with Dan Fox. We were developing the Atomic.NET class (it's an ASP.NET class) and Dan added a couple of interesting modules, "Abstracting ADO.NET Providers" and "Building a Data Access Layer". This was a well thought out architecture and the Data Access Layer (DAL) bar was set in mind. (See some of Dan's insight in Implement a Data Access Layer for Your App with ADO.NET.)
So I'm looking at two optioins: either use the Atomic DAL or write a class that uses the Data Access Application Block. Let me explain my hesitancy in using the Data Access Application Block after using Dan's. I'll do this by looking at the differences.
Differences with Atomic DAL
- Returning interfaces instead of ADO.NET objects. The Atomic DAL returns interface references so that the underlying data provider can be anything. The MS Data Access Application Block is today specific to SQL Server.
- Caching of Objects. The Atomic DAL caches Command and Parameter objects (any provider) and clones these for the next matching request. The MS Data Access Application Block caches only SqlParameter objects.
- NotInheritable. The Atomic DAL is a an abstract class, where the Data Access Application Block is sealed or NotInheritable. At first, I thought that this was not favorable because I wanted to add logic for finding a connection string and other changes.
- Passing of Connection string or object. This is related to the previous point. All of the methods of the SqlHelper class expect a connection string or a connection object. I didn't want the business layer (my caller) to have to know about a connection string.
- XML Mapping Layer. The Atomic DAL depends on a XML file that includes a logical-to-physical mapping. The Data Access Application Block does not have this additional abstraction layer.
Using the Data Access Application Block
So I'm trying it out. I'm creating class methods that are specific to the entity, like GetCustomerReader. The caller passes an ID and I return a SqlDataReader after calling the appropriate SqlHelper shared (or static) method. I'm looking up the connection properties and accessing the cache for SqlParameters. See below.
Public Shared Function GetEmployeeReader(ByVal EmployeeId As String, ByVal PropertyId As String) As SqlDataReader
Const SP_NAME As String = "spEmployeeSelect"
Dim params As SqlParameter() = SqlHelperParameterCache.GetSpParameterSet(GetDbConnStr(), SP_NAME)
params(0).Value = EmployeeId
params(1).Value = PropertyId
Return SqlHelper.ExecuteReader(GetDbConnStr(), CommandType.StoredProcedure, SP_NAME)
End Function
We have discussed going further by returning a entity class (like Employee in the previous example) in order to remove any dependency on ADO.NET or its data providers. What would you do?
Dan Fox info
Dan's List of Writings,
Teach Yourself ADO.NET in 21 Days
1:20:00 AM 
|
