Implementing a Generic Data Access Layer in ADO.NET Part 1

A Data Access Layer (DAL) is an integral part in the design of any application. There are plenty of articles that discuss how we an implement a DAL using ADO.NET. Most of these have constraints in the sense that they are not generic in nature. In other words, they are not provider independent. This series of articles will discuss the implementation of a generic, i.e., a provider independent Data Access Layer in ADO.NET. The basic prerequisite to learning this article is a proper understanding of ADO.NET and good coding skills in C#. I will present the code examples in this article in C#. However with little effort, you can twist it over to VB.NET as well.

The Strategies InvolvedLet us first understand what the necessities are for building such a layer. I would rather start by discussing how an application designed using ADO.NET actually connects to the database and performs the CRUD (Create, Read, Update and Delete) operations.

First, you need to open the connection using a database provider. Fine, but what is a provider anyway? A provider is responsible for connecting to a specific database. Why specific? The reason is that a provider for an Oracle database cannot be used to connect to a SQL Server database and vice-versa. Next, you need a command object that can be used to execute the database commands of your choice. This is followed by the usage of a DataReader or a DataSet or a DataTable instance to retrieve data (if you are performing a Read operation) from the database table. When you use a DataSet, you need a DataAdapter as a bridge between the actual database and the DataSet instance.


Implementing the DAL FrameworkWith this in mind, let us design a provider independent Data Access Layer. Let us first understand the ADO.NET Library. The major classes that constitute the ADO.NET library are:

• Connection
• Command
• Data Reader
• Data Adapter

The corresponding interfaces that the above classes implement are stated below.

• IDBConnection
• IDataReader
• IDBCommand
• IDBDataAdapter

The Data Providers that make up the library are specific to a particular database that they would connect to. These are the Data Providers that are available in ADO.NET.

• SQL Server Data Provider
• Oracle Data Provider
• ODBC Data Provider
• OleDB Data Provider

Now we are all set to implement our DAL. The major components that constitute our DAL block are:

• ProviderType (Enum)
• DatabaseConnectionState (Enum)
• StoredProcedureParameterDirection (Enum)
• DBManager (Class)
• DBHelper (Class)

Slowly Changing Dimensions in SQL Server 2005

IntroductionSlowly changing dimensions (SCD) are very useful in data warehouses. Even though they are useful, it is very difficult to implement slowly changing dimensions with most of ETL tools. In case of SQL Server 2000 it was difficult to implement SCD's as there was no direct way of implimenting them. As you are aware there were numerous changes in SQL Server 2005 including the introduction of a SCD data flow task. This article explores the options available in the SQL Server 2005 for SCD's.RequirementsYou need to have installed SQL Server 2005 with Business Intelligence Development Studio (BIDS). It will also be beneficial if you know how to create simple SQL Server Integration Services (SSIS) packages. However, as usual I will elaborate on how to develop them when and where ever possible. What is SCD?The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. Let us take this hypothetical example. Let us say that we have a fact table that contains customersk.