provided by: 
Originally published at Internet.comFrom Codeguru's .NET Nuts & Bolts column. The last Nuts and Bolts article focused on using custom attributes and reflection to help eliminate tedious database access programming. This month will continue to focus on database related topics and show you how to build a database access layer that is database independent.
Why Would You Need Database Independence?
Whether or not you need to worry about database independence depends upon the organization that you work for and the project work in which you are involved. Have you ever worked on a custom application development project that where just two days before the application is put into production someone higher up in the food chain decides that your Oracle database must now be a SQL Server database? Does the database vary from SQL Server, to Oracle, to DB2, etc from project to project? If you are one of the lucky souls that answered "no" to the above questions then you can feel superior to us less fortunate. For the rest of you please read on and we'll discuss how to create a database independent data access layer that you can use across most, if not all of your projects.
What Data Providers Can We Support?
We need to look at the different databases that we need to access with our data provider. Ideally we would like to have our object be able to support any and all of the databases that we need to access and use. By default the .NET framework offers an optimized data provider specifically for accessing Microsoft SQL Server and another for accessing OLE DB data sources such as Oracle. There are other data providers available for download from Microsoft such as ODBC and a Microsoft data provider for Oracle. In addition it is possible to write custom data providers. I will limit this article to just the SQL Server and OLE DB providers, but the concepts used apply across provider types.
What Functionality Do We Want To Expose?
The data providers in the .NET framework all provide similar functionality by design. They allow you to connect to a data source and retrieve or modify the data. Each data provider exposes four specific objects that are the core of the provider and specific to each provider. The objects are listed below: * Connection — Establishes a connection to the data source * Command — Executes a command * DataReader — A forward-only, read-only stream of data, also known as fire hose * DataAdapter — Populates a DataSet When using a .NET provider the following basic steps are used when accessing or updating data through the provider: * Establish a connection using the Connection object. * Create a Command object to execute a specific command and assign it to use the established connection. * Execute the desired Command or DataAdapter action, which will result in the return of a DataReader or DataSet or some alternate result. * Clean up the objects since they are typically valuable resources and we don't want to wait for the garbage collector. Since the goal of our data provider is to simplify accessing data from alternate data sources we don't want to have to deal with the Connection, Command, and DataAdapter objects directly. We simply want to call a method with some indicator of the command to execute and have it return data in the form of a DataReader or DataSet. Thus, the Command and DataAdapter objects expose the functionality that our data provider should make available. The functionality is listed below: * ExecuteNonQuery — Executes commands such as SQL INSERT, DELETE, AND UPDATE statements and returns the number of rows affected. * ExecuteScalar — Retrieves a single value (for example, an aggregate value) from a database. It retrieves the first value in the first row from the result. * ExecuteReader — Executes a command that returns rows. * ExecuteXmlReader — Executes SQL commands that return rows containing XML data (SQL Server data provider only) * Fill — Executes SQL commands that return rows and fill a DataSet with the results.
Creating the Database Independent Data Access Provider
Here is a class called DataProvider that performs to the design outlined above. using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace CodeGuru.DataAccess
{
///
Author: Mark Strawmyer
Read article at Internet.com site