[RESOLVED] Coding for two types of back-end database

[InvisibleDuncan]

I'm writing a VB.Net 2008 application that needs to read data from three very old Sybase databases. Because of their age, I'm using an OdbcDataReader for this. However, in about six months one of these databases will be replaced with a SQL Server version as part of an upgrade process. To access this new database, I would tend to use a SqlDataReader.

In order to avoid having to recode when the back-end changes, is there a way that I can use a higher level object with a cheeky bit of polymorphism so that I can choose what type of reader to use at run time (this would be based on a config setting)? Both of these readers inherit from System.Data.Common.DbDataReader, but that's marked as MustInherit.

Any advice would be appreciated!

[jmcilhinney]

It can be done but it's a bit of trouble. To learn how, follow the Blog link in my signature and check out my post on a Database-independent DAL.

That said, it be better to simply use ODBC for both to begin with and then change your Odbc types to SqlClient types when you next make some other change to the application. It would pretty much just be a Find & Replace.

[InvisibleDuncan]

Excellent - thanks. I'll have a rummage through your blog.

As for your second point, I may well end up using ODBC for both if the other method is too tricky. The problem is that it's only one databse that will be changing in the short to mid term, so I wouldn't be able to do a find and replace. My code will need to do exactly the same work on each of the three databases, so I didn't want to have to code them separately, but equally, I wanted the better performance of the SQLClient stuff over ODBC where possible.

[InvisibleDuncan]

I've had a dig through the code on your blog and it's absolutely perfect for my needs. It's also very simple to follow, so I've got it up and running and looking at various data sources within an hour of checking your blog out.

Full marks to that man! Thanks...