asked on

NEWBIE: How to design for different types of databases in C#?

Dear Experts,

Hi.

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?

Thanks,
BrianMc1958

Carl Tawn

One option would be to use a "Factory" pattern to return the correct type of connection/command object. Something like:

public class DbObjectFactory
{
public static System.Data.Common.DbConnection GetConnection()
{
bool isSqlServer = true; // Replace this with code to access flags from config file or elsewhere

if (isSqlServer)
return new System.Data.SqlClient.SqlConnection();
else
return new System.Data.OleDb.OleDbConnection();
}
}

Then you simply need to assign the result to a DbConnection type:

System.Data.Common.DbConnection conn = DbObjectFactory.GetConnection();

Hope this helps.

BrianMc1958

ASKER

That's half the answer. I tried to write my question clearly and carefully and may have accidently given the impression that I know what I'm talking about.

I'm trying to understand how the managed providers fit in. As I see it, encapsulating the Connection only solves the Connection problem. In Java, that's all I need to do. But in C#, I still need to decide between SqlDataAdappter and OleDbDataAdapter, for instance. Can I encapsulate those at the same time? (Again, I'm only vaguely aware of what they do at this point...) Can I have all my "normal" code just say "give me a DataSet--you figure out from where and what type"?

Yours in Slowly Decreasing Ignorance,
BrianMc1958

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carl Tawn

Of course, doing it this way will only give you access to the Properties and Methods defined in the base Db* object. You would still need to cast to the correct type if you wanted to use any provider-specific properties/methods.

PoeticAudio

Just wanted to drop in and say that I highly advocate the factory pattern in situations like these, as carl has been saying. We recently did an application that needs to be able to connect to Oracle, Access AND SQL Server (yay...=/ ) We used the factory pattern and it's been nice.

One of the biggest benefits of this pattern is that all your creation logic is in one class, instead of strung throughout your code.

Carl Tawn

Hi PoeticAudio, when you used this method did you return the base type (i.e. DbConnection) from your methods ? I've never actually used a factory in this situation, so am just curious as to how you went about it in practice.

BrianMc1958

ASKER

To carl tawn: Uh, yep. That looks like what I needed to know. I asked a million questions over in the Java section of EE, and when I really needed help, I would offer--in addition to points--to come over an mow people's lawns if they gave me the right answer. You don't have a lawn, do you?

To PoeticAudio: I'll leave this question open a little while if you'd like to respond to carl...

Thanks everybody!
--BrianMc1958