Implementing a Provider Independent Data Access Layer in .NET

For applications built using Microsoft DNA, ADO provided one of the easiest ways of data access. ADO gave developers, COM based API for data access and using ADO components, a multitude of data sources could be accessed. The main reason for this was that ADO used the OLEDB provider internally to connect to data sources and more importantly there existed an OLEDB provider for ODBC. This meant that developers could use ADO to connect to ODBC sources using DSNs in their connection strings as well.

Data access in ADO.NET is not quite the same. Unlike ADO which just used OLEDB, ADO.NET provides many independent providers targeted to specific data sources. For example, the SQLClient provider deals specifically with SQL Server 7 and SQL Server 2000 as the data sources. The ODBC.NET provider deals with access to data sources using ODBC and DSNs. Likewise the OLEDB.NET provider is used to connect to OLEDB data sources. Also, there exists a separate .NET provider for Oracle databases.

Having independent providers for data access has the advantage that each provider is tuned for a specific data source and provides the most efficient access for that data source. For example, SQLClient is by far, the most efficient way of accessing SQL Server 7 or 2000 data sources. But the disadvantage here is that developers may have to decide on a particular provider and the generic way of access is not as easy as in ADO.

Now, what if the decision of selecting a provider is taken as late as application runtime? How should the data access layer (let�s call it DAL) be implemented so that applications that use the DAL be unaware of the provider being used and still work seamlessly with the DAL? This article deals with the design and implementation of the DAL which answers the above questions.

Read this article Read this article Read this article Read this article