Database Abstraction - what are the options?

Question

How can I abstract my database from my application so that it is unaware of the database type? I have to design a .Net 3.5 WPF application that must support either SQL Server or Visual FoxPro as the data repository.

My design goals are to:

1. make the type of data repository invisible when writing code in the Data Access Layer
2. make it as quick and painless as possible to switch between SQL and FoxPro
3. allow use of both ANSI SQL and stored procedures from the DAL
4. learning curve must be minimal for other developers joining the project

This will be a WPF application, probably using CSLA.

I have thought about using some kind of ORM but have no experience with this type of technology.

Answer 1

Many ORMs are DataBase type agnostic, though the FoxPro requirement might restrict your choises or require you to write your own provider or adapter.

If you want your application to be able to work with multiple DataBase types, say through configuration, you must also be careful that your schemas in each database are compatable and that you don't depend on features that aren't implemented in one of the databases or in the ORM you choose.

The ORM I'm most familiar with LLBLGen Pro supports 5 or 6 back-end databases, but FoxPro is not officially supported. There was a customer contributed FoxPro adapter at one point. I don't know if it has been maintained.