Create a Vendor-Independent Data Access Class
Vendor independent or provider agnostic data access is a relatively easy thing to implement, thanks to the structure of ADO.NET. You’ll find the classes you’d need in the System.Data.Common namespace.
To access data without knowing the provider type, you’d start in your web.config, by adding these entries to it:
<add name=”theString” connectionString=”your string here” providerName=”System.Data.SqlClient”/>
<add key=”select” value=”SELECT * FROM Customers”/>
Then in your data access class, you should define a DBProviderFactory, create an instance of it by calling the GetFactory static method passing the provider type as parameter (defined in your web.config). Then you should create a DBConnection class and a DBCommand class, or anything else you’d need, and use it as if you’d use for example the SQL classes. Example:
DBProviderFactory fact = DBProviderFactory.GetFactory(WebConfigurationManager.ConnectionStrings[“theString”].ProviderName);
DBConnection theConnection = fact.CreateConnection();
theConnection.ConnectionString = WebConfigurationManager.ConnectionStrings[“theString”].ConnectionString;
DBCommand theCommand = fact.CreateCommand();
theCommand.CommandText = WebConfigurationManager.AppSettings[“select”];
theCommand.Connection = theConnection;
GridView1.DataSource = theCommand.ExecuteReader();
As you can see, creating a provider-agnostic data access class is an easy and straightforward task. Use it regularly to build more flexible applications.