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

BrianMc1958Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
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.
0
BrianMc1958Author Commented:
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
0
Carl TawnSystems and Integration DeveloperCommented:
Luckily, DataSets are generic, so you only need to worry about Adapters, Commands and Connections.

If you wanted you could simply extend the DbObjectFactory to also return the appropriate Adatper/Command objects:

public class DbObjectFactory
{
    bool isSqlServer = true;       // Replace this with code to access flags from config file or elsewhere

    public static System.Data.Common.DbConnection GetConnection()
    {
        if (isSqlServer)
            return new System.Data.SqlClient.SqlConnection();
        else
            return new System.Data.OleDb.OleDbConnection();
    }

    public static System.Data.Common.DbDataAdapter GetAdapter()
    {
        if (isSqlServer)
            return new System.Data.SqlClient.SqlDataAdapter();
        else
            return new System.Data.OleDb.OleDbDataAdapter();
    }

    public static System.Data.Common.DbCommand GetCommand()
    {
        if (isSqlServer)
            return new System.Data.SqlClient.SqlCommand();
        else
            return new System.Data.OleDb.OleDbCommand();
    }
}


This way all you need is a single flag in your config to make each of these methods return the correct type.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Carl TawnSystems and Integration DeveloperCommented:
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.
0
PoeticAudioCommented:
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.
0
Carl TawnSystems and Integration DeveloperCommented:
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.
0
BrianMc1958Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.