What is the best way to support multiple databases for a .Net product?

Question

Hi,

We are designing a product which could support multiple databases. We are doing something like this currently so that our code supports MSSQL as well as MySQL.

namespace Handlers
{
    public class BaseHandler
    {
        protected string connectionString;
        protected string providerName;

        protected BaseHandler()
        {
            connectionString = ApplicationConstants.DatabaseVariables.GetConnectionString();
            providerName = ApplicationConstants.DatabaseVariables.GetProviderName();
        }
    }
}

========================================================
namespace Constants
{
    internal class ApplicationConstants
    {
        public class DatabaseVariables
        {
            public static readonly string SqlServerProvider = "System.Data.SqlClient";
            public static readonly string MySqlProvider = "MySql.Data.MySqlClient";

            public static string GetConnectionString()
            {
                return ConfigurationManager.ConnectionStrings["CONNECTION_STRING"].ConnectionString; 
            }

            public static string GetProviderName()
            {
                return ConfigurationManager.ConnectionStrings["CONNECTION_STRING"].ProviderName;
            }
        }
    }
}

=========================================================

namespace Handlers
{
    internal class InfoHandler : BaseHandler
    {
        public InfoHandler() : base()
        {
        }

        public void Insert(InfoModel infoModel)
        {
            CommonUtilities commonUtilities = new CommonUtilities();
            string cmdInsert = InfoQueryHelper.InsertQuery(providerName);
            DbCommand cmd = null;
            try
            {
                DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);
                DbConnection con = LicDbConnectionScope.Current.GetOpenConnection(provider, connectionString);
                cmd = commonUtilities.GetCommand(provider, con, cmdInsert);
                commonUtilities.PrepareCommand(cmd, infoModel.AccessKey, "paramAccessKey", DbType.String, false, provider, providerName);
                commonUtilities.PrepareCommand(cmd, infoModel.AccessValue, "paramAccessValue", DbType.String, false, provider, providerName);
                cmd.ExecuteNonQuery();
            }
            catch (SqlException dbException)
            {
                //-2146232060 for MS SQL Server
                //-2147467259 for MY SQL Server
                /*Check if Sql server instance is running or not*/
                if (dbException.ErrorCode == -2146232060 || dbException.ErrorCode == -2147467259)
                {
                    throw new BusinessException("ER0008");
                }
                else
                {
                    throw new BusinessException("GENERIC_EXCEPTION_ERROR");
                }
            }
            catch (Exception generalException)
            {
                throw generalException;
            }
            finally
            {
                cmd.Dispose();
            }
        }
    }
}

============================================================
namespace QueryHelpers
{
    internal class InfoQueryHelper
    {
        public static string InsertQuery(string providerName)
        {
            if (providerName == ApplicationConstants.DatabaseVariables.SqlServerProvider)
            {
                return @"INSERT INTO table1
           (ACCESS_KEY
           ,ACCESS_VALUE)
     VALUES
           (@paramAccessKey
           ,@paramAccessValue) ";
            }
            else if (providerName == ApplicationConstants.DatabaseVariables.MySqlProvider)
            {
                return @"INSERT INTO table1
           (ACCESS_KEY
           ,ACCESS_VALUE)
     VALUES
           (?paramAccessKey
           ,?paramAccessValue) ";
            }
            else
            {
                return string.Empty;
            }
        }
    }
}

Can you please suggest if there is any better way of doing it? Also what are the pros and cons of the approach?

regards...

Kalpak L

Answer 1

There are object-relational mapping layers out there that will support multiple database technologies, like Entity Spaces.

Answer 2

I'd use NHibernate.

Here's nice beginner tutorial

Answer 3

Whatever you do, don't write your own mapping code. Its already been done before, and its probably been done a million times better than whatever you could write by hand.

Without a doubt, you should use NHibernate. Its an object-relational mapper which makes database access transparent: you define a set of DAL classes which represent each table in your database, and you use the NHibernate providers to perform queries against your database. NHibernate will dynamically generate the SQL required to query the database and populate your DAL objects.

The nice thing about NHibernate is that it generates SQL based on whatever you've specified in the config file. Out of the box, it supports SQL Server, Oracle, MySQL, Firebird, PostGres and a few other databases.

Answer 4

One approach to this problem is to design your application to work entirely with disconnected DataSets, and write a data access component that handles fetching data from the different database brands you'll be supporting, as well as persisting changes made to the DataSets by your application back to the original databases.

Pros: DataSets in .Net are well-written, easy-to-use and powerful, and do an excellent job of providing methods and tools for working with table-based data.

Cons: This method can be problematic if your application needs to work with extremely large sets of data on the client side.

Answer 5

What's always good in such cases is to create a layered architecture, where all the DB related stuff is JUST in the data access layer. Then you could have different implementations of your DAO layer, one for Oracle, SQL Server etc...

You should separate the business layer from the DAO layer with interfaces, such that your Business layer just uses them to access the DAO layer. So you could perfectly exchange the underlaying implementation of the DAO layer to run on an Oracle DB or whatever system you like.

Another good suggestion is to take a look at object-relational mappers like Scott already suggested. I'd take a look at NHibernate or Entity framework.

Answer 6

Right now, Microsoft's Entity Framework has a few shortcommings, some of them that can be deal breakers, depending on the application's intended architecture.

From what I've seen and read about V2, which will be shipped with .Net 4, I think it will certainly deserve to be looked at.

Answer 7

Many people have suggested an O/R mapping framework such as NHibernate. This is quite a reasonable approach unless you don't want to use an O/R mapper for some reason. Something like NHibernate will probably get you 95%+ of the way but you may need to write some custom SQL. Don't panic if this is the case; you can still do an ad-hoc solution for the rest.

In this case, take the bits that do need the custom SQL for and separate them out into a platform specific plugin module. Write Oracle, MySQL, SQL Server (etc.) plugins as necessary for the individual database platforms you want to support.

ADO.Net makes it fairly easy to wrap sprocs, so you might be able to move the platform dependent layer down into some stored procedures, presenting a more-or-less consitent API to the middle tier. There are still some platform dependencies (such as the '@' prefix on SQL Server variable names), so you would need to make a generic sproc wrapper mechanism (which is not all that hard).

With any luck, the specific operations you need to break out in this manner will be fairly small in number so the amount of work to maintain the plugins will be limited.

Answer 8

If you have to code it yourself and not use a product which provides unified access, remember that objects like SqlDataAdapter and OracleDataAdapter inherit from the common DbDataAdapter (at least in later versions of the runtime). If you cast down to DbDataAdapter, you can write code that will work with both databases in the places where you would do the same for both databases. Some of your code will look a little like this:

DbDataAdapter adapter = GetOracleDataAdapter() as DbDataAdapter;

Once you've casted down, it doesn't matter if it is a SqlDataAdapter or a OracleDataAdapter. You can call it the same way.

However, remember that coding for two databases means using features that exist only within both while having to work around the shortcomings of both. It's not really a good idea.

Answer 9

If you need a mapping from database entries to Objects I suggest you go with the solution other already suggested: NHibernate. If this seems like overkill for your application and you want to go with the Ado.net approach and do not need a O/RM-soultion, you should have a look on what the Spring.net guys did and learn about the Ado.Net Provider Abstraction.

Answer 10

Hi,

Thanks a lot for all your answers. I think I would give NHibernate a well deserved try.

I appreciate all your views on the same.

Answer 11

For your current need, I agree with NHibernate...

Just want to point out something with your class hierarchy...

You will better to use Interface

Like (Just check the doc or Internet for exact syntax)

Interface IDBParser
Function1
Function2

class MSSQLParser : IDBParser
Function1
Function2

class MySQLParser : IDBParser
Function1
Function2

Then in your code you can use the interface

Main()
IDBParser dbParser;
if(...)
dbParser = new MSSQLParser();
else
dbParser = new MySQLParser();

SomeFunction( dbParser );

// the parser can be sent by parameter, global setting, central module, ...
SomeFunction( IDBParser dbParser)
dbParser.Function1();

That way it will be easier to manage and your code won't be full of the same if/else condition. It will be also a lot easier to add others DB. Another advantage is that it could help you with unit testing by sending mock object.