Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-04-25
7
Medium Priority
?
459 Views
Last Modified: 2013-12-03
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

0
Comment
Question by:BrianMc1958
  • 4
  • 2
7 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16533795
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
 

Author Comment

by:BrianMc1958
ID: 16533898
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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 1000 total points
ID: 16533957
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16533980
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
 
LVL 6

Expert Comment

by:PoeticAudio
ID: 16534024
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16534047
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
 

Author Comment

by:BrianMc1958
ID: 16534157
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question