In this article, I am going to explain how to build a set of data access components built on ADO.NET that can use different types of databases without many problems. When you build a database application, you will probably want to start off with a simple Microsoft Access database, but upgrade later to SQL Server or Oracle. In this case, you would have to completely rewrite your code before you can use the other types of databases. However, this isn't strictly necessary. You can always build the components so that they accept different types of databases. There are different approaches to solve this problem; I am going to explain just one way to build the data access components.
In this article, I am going to explain how to build data access components through the use of interfaces and a helper class. An interface provides us with a way to make calls to classes we don’t really know, but as long as they are built according to the interface, we can use them.
Multiple types of databases, same data
To make it possible to use different types of databases for the same application, you must first be sure that the databases deliver the same type of data to your application. After that, you can simply build the components.
The interface is, in this case, simple. I assume that there is only one table we want to link at the moment. So, we start off creating the interface like this:
public interface IDataAccess
DataSet UpdateArticles(DataSet ds);
The interface simply implements 2 methods, one method for getting the data from the database, and another method for synchronizing the data with the database.
To connect this to OLEDB, we will need a Data Access Class for OLEDB. For easy use, we will create a new component and name that
OleDbDataAccess. If you have Visual Studio .NET or SharpDevelop, you can then design the component by dragging an
OleDbConnection and an
OleDbDataAdapter to the designer. Configure them to use the database you made with Microsoft Access. After we designed the component itself, we go to the code and implement the interface. This could look something like this:
public class OleDbDataAccess: Component, IDataAccess
public DataSet GetArticles()
DataSet ds = new DataSet();
public DataSet UpdateArticles(DataSet ds)
If(ds == null)
The helper class
The helper class is nothing more than just a class that can spawn the right data access components for a given setting. The class implements a method
GetDataAccessComponent which returns an instance of
IDataAccess. The class looks like this:
public class DataAccessHelper
string drivertype = ConfigurationSettings.AppSettings[“DriverType”];
IDataAccess dal = null;
dal = new OleDbDataAccess();
throw new NotSupportedException(“This drivertype is not supported”);
The method looks in the App.Config file of the application for an entry with the key
DriverType. If the value of this entry is
OleDb or any other valid driver type, a new instance of that data access component will be created and returned. If no valid driver type is found, the method will throw an exception.
You can add more Data Access components to your application that implement the interface
IDataAccess. And change the
switch statement in the
GetDataAccessComponent method. I normally use OLEDB and SQL as my database types, but you can also use Oracle or ODBC if you like. As long as the data fits in a
DataSet, you can use the interface.
Other implementations may be possible; this is the way I chose to use multiple databases with my applications. I am curious how other people have solved this problem, feel free to post a message with your idea :)
WillemM is a 25 year old software developer working for Info Support. He loves new technology and spends most of his free time finding new ways to do things with his computer.
When not working on computers you can find him outside with his camera taking pictures.