NEWBIE: How to design for different types of databases in C#?
Posted on 2006-04-25
I'm migrating my existing app from Java to C# (which I’m just learning). I’m trying to understand how to approximate one feature which will (hopefully) allow me to switch it easily from using an MS SQL db to any other db. (There is always a single db, which “belongs” to the product.)
Specifically, in Java, I encapsulated a “Connection” into a class called (cleverly) GetConnection.
All my other classes asked GetConnection for a Connection. If, at a later point, I wanted a different driver or even wanted to point to an Oracle database, in principle I just needed to change that one string inside GetConnection.
Now, in C#, I’m being introduced to “managed providers” and DataAdapters. I understand one is optimized for MS SQL, one is OLE, etc. We’ll definitely want the MS SQL for now. But I want to be able to allow future customers to easily use the OLE or Oracle versions in the future.
I don’t think I can do that simply by encapsulating the Connection, because there is a different DataAdapter for each managed provider.
I’m guessing what I want is a class that returns a “generic” DataSet. Inside that class would be whatever Connection, and whatever managed provider, is appropriate for that customer’s db type.
Is that right? How do C# people handle this?