What database technology to use for legacy application?

Question

I am looking for advice. I know stackoverflow likes "answerable" questions, I hope this is.

I have a large legacy (Windows) application, using a proprietary (ISAM) database. I wish to investigate moving it over to using a SQL database: primarily MS SQL Server, I wouldn't mind Oracle flexibility but that is only a "would be nice", and probably shouldn't influence the decision much.

The application is written in C/C++/MFC, not managed. At this stage I am looking very much to "prototype" an initial solution attempt. I would like this not to be too complex, it can be refined if the whole process looks like it's going somewhere.

The candidates I see are:

• ODBC
• OLEDB
• ADO.NET

of which I think I have learnt that nowadays I should prefer OLEDB over ODBC. I know ADO.NET but not the others. However, ADO.NET is going to require me to use .NET/CLR/managed code from the application, which it doesn't do at present.

Any advice for which way to go? Prototyping speed/simplicity would be appreciated, though obviously if I know the final solution will require a different access method it might be silly to start with another one. (I would appreciate not being advised to redesign/rewrite the whole application.)

Answer 1

I wouldn't use ADO.NET unless you intend to switch everything to managed code (which sounds quite unlikely).

That leaves ODBC and OLEDB.

ODBC is an open standard, but OLEDB is a more modern design. Considering your program isn't likely going anywhere off Windows I would suggest OLEDB.

Note: The actual database you use shouldn't matter much in this decision. All of the above have great support for SQL Server and Oracle (and to a more limited extent Sybase, DB2, UDB, etc).

Answer 2

I would recommend ODBC as it has a fairly simple API and object model. OLEDB is more complicated and requires a steep learning curve.

If you were doing a complete re-write from scratch, I'd recommend ADO.NET, but for a legacy migration, you'll want to keep it simple.