Don't get discouraged. You are still on the right path.
First, let me say that if you can come up with a design that avoid the CommandBuilder, I would recommend it. Here is a quote from Microsoft themselves:
Initializing DataAdapters for Update
In ADO.NET, you must add your own code for submitting database updates to the DataAdapter object. There are three ways of doing this:
- You can supply your own updating logic.
- You can use the Data Adapter Configuration Wizard to generate the updating logic.
- You can use the CommandBuilder object to generate the updating logic.
It is recommended that you supply your own updating logic. To save time, you can use the Data Adapter Configuration Wizard, but if you do, try not to generate the logic at run time. Do not rely on CommandBuilder objects unless you have to because your performance will suffer and you cannot control the updating logic the objects generate. In addition, a CommandBuilder will not help you submit updates using stored procedures.
You can use CommandBuilder with applications that dynamically generate data access logic, such as reporting or data extraction tools. Using the CommandBuilder eliminates the need for these tools to write their own code-generating modules.
That said, let's ignore that and move on. Creating a provider independent layer for data access is a Good Thing, so much so that MS provides a Data Access Application Block to do the same
Designing for Database-Agnostic Applications
Their application block is very bloated and unwieldy. If you were to write you own, as you are doing, you need to have a good look at the classes in both
System.Data AND
System.Data.Common namespaces. Some of what you want to accomplish is provided by Interfaces, but other classes are provided via abstract classes.
In particular, look at the
DbProviderFactory class. The concept is that each provider (SQLClient, Oracle, OLE DB, ODBC) will provide a provider-specific implementation of the classes (such as
DBCommand, DBConnection, etc). Those classes follow the interface contract (
IDbCommand for example) but you would actually work with the abstract classes, not the interfaces.
If you follow this so far, or spend some time understanding it, then you will see that even with a provider agostic data layer, you will eventually request a provider specific to Access, such as the
OleDBFactory. Each concrete implementation of
DbProviderFactory can create an instance of itself via a configuration string. That would be the only "provider specific" line of code. You can then work with the abstract class layer in a "provider agnostic" manner.
Okay. Still with me? The
DbProviderFactory (abstract) has a "
CreateCommandBuilder() method. So when you create your
DbProviderFactory (which under the covers in your case will be a
OleDbFactory implementation) you can call that method and get a
DbCommandBuilder (which under the covers is an
OleDBCommandBuilder instance for Access.