Building a Proxy Data Provider with WCF

Over the years I’ve had quite a few times where I’ve wanted to access a database from a machine that didn’t have the necessary client libraries installed. In some cases I couldn’t install the libraries due to license constraints. In other cases, I couldn’t install them due to software conflicts (ex. ODP.NET on Vista x64). In some cases I simply didn’t want to install the DBs client libraries on that machine due to all the other garbage it installs along with them.

My standard solution to this has been to install the client libraries on a virtual machine. I would then launch the VM for the database I needed. This solution solves most problems. However, it leaves me with a new set of problems. To work with the database I now have to work on the VM. If I need to use any tools that don’t come with the DB, I have to install them in the VM. Now I have to keep the tools in synch.

I’ve put up with this for years until a few weeks ago. I was speaking with someone who had a similar issue. In his case he needed to be able to access a database through the internet. However, he couldn’t expose the DB straight to the internet due to firewall restrictions. Likewise, he couldn’t set up a VPN connection. He does have the ability to run a web service that has access to the database. Unfortunately, the client app could only use ADO.Net data providers (IDbConnection, IDbCommand, etc.).

This got me to thinking. Why not create a .Net data provider and web service to function as a proxy to the database? This would allow existing apps to work with databases that are exposed via the web service without changing any of the client code.

Some scenarios where this might be useful are:

• You have a website hosted someplace like GoDaddy that won’t provide direct external access to their hosted database yet they will allow you to host web services.
• You need to access a database through the internet which doesn’t encrypt it’s communications.
  • You can set up the Web Service to use SSL
• You need to remotely access a data source that only supports direct access connections.
  • For example a CSV file.
• You need to access a database but are unable to install the client libraries on the machine needing access.
  • You may have an 32-bit ODBC driver, but a 64-bit version of windows.

I felt this was a sufficiently interesting concept that I worked up a proof of concept. I was able to get a basic IDbConnection, IDbCommand & IDataReader classes which would talk to a custom web service. The web service would then instantiate a connection to the database and pass all the results back.

As I’m currently learning WCF, I thought it would be a good practice to convert this system from web services to WCF.

The following series of articles will walk through the code used to build it.

Source Code

Next: Architecture