MySQL and SQL Server for same application.

Question

What are good ways of achieving this DB agnosticism without actually coding two DAL's? I've heard that the Data Access Application Block is suitable for this.

Answer 1

You could also look at the various ORM libraries like NHibernate. When you are targeting multiple databases, it is best to look for a tool that can create your database from configuration files or from code. That will prevent you from having to create identical databases in MySQL and MSSql.

Answer 2

Use the ADODB abstraction library for MySQL. Native MySQL library calls are pretty ugly and primitive, so you'll probably want something higher-level in any case.

Answer 3

One option is to code using the IDbConnection, IDbCommand, etc. classes from the System.Data namespace, rather than the SQL Server specific classes (SqlConnection, SqlCommand) from System.Data.SqlClient or the corresponding MySQL specific classes. You still need to create an instance of the connection class for the specific database you're using, but from there on you can just use the generic interfaces.

http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/1175d7ea-f5b4-442f-9155-08bf8b2ba06c/ and http://www.15seconds.com/issue/040127.htm have some sample code.

Answer 4

Well, as others have pointed out, try to abstract yourself away from the actual classes involved. This can either involve using the interfaces, as AHd54 points out, or something similar.

That will only get you half the way though.

The SQL you need to execute will have differences as well, things like the following:

• Parameter name syntax
• Positional parameters vs. named parameters
• Function names
• Some syntax differences, like how to specify only give me the first N rows

If you need to cover all these things, and can't or won't use an ORM (they rarely get you 100% there though), then one way would be to create your own classes that abstract away not only the actual classes involved, but also does some type of SQL rewriting to handle the code differences.

I have gone down the rewriting path, so if you need pointers on that, add a comment to this answer with questions and I'll update the answer accordingly.

Answer 5

Use an ERD diagramming tool the construct a purely logical design, and then have it generate the DDL for each of your options.

You might check out DBDesigner ...

http://www.fabforce.net/downloads.php

Answer 6

ORM frameworks will help you achieve an RDBMS-agnostic infrastructure, but you'll still have to keep seperate DAL's if you're working with UDF's and stored procedures. As a consequence you will probably not use triggers, T-SQL, updating views and CLR hosted C# code. You would just map entities and objects.

ADO.NET will do fine in most projects. If you need to switch between MSSQL or MySQL, you can achieve provider-agnostic access really easy like this:

    private static DbProviderFactory _provider = DbProviderFactories.GetFactory(WebConfigurationManager.ConnectionStrings["database"].ProviderName);
    private static string _connectionString = WebConfigurationManager.ConnectionStrings["database"].ConnectionString;


    public static DbParameter CreateParameter(string name, object value)
    {
        DbParameter parameter = _provider.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        return parameter;
    }

    public static DataTable SelectAsTable(string query, DbParameter[] parameters)
    {
        using (DbConnection connection = _provider.CreateConnection())
        {
            connection.ConnectionString = _connectionString;
            using (DbDataAdapter adapter = _provider.CreateDataAdapter())
            {
                adapter.SelectCommand = connection.CreateCommand();
                adapter.SelectCommand.Parameters.AddRange(parameters);
                adapter.SelectCommand.CommandText = query;

                DataTable table = new DataTable();
                adapter.Fill(table);
                connection.Close();
                return table;
            }
        }
    }

Answer 7

The best way to do this (and mostly any database access layer in fact in general) is to use an O/RM tool. I prefer ActiveRecord which is a wrapper around nHibernate...

Here is some sample source code for something using AR; code to operator object in Stacked

Answer 8

The .net Provider model is designed specifically to tackle this problem. By providing a "provider" class which handles all of your db interaction and descends from a well-designed base class, you can swap out back-end providers (for sql, mySql (blech), xml or whatever) with minimal effort.

Also, in my experience ORM-generated code cannot compete with that written by a competent developer, and often creates more maintenance problems that it solves. ORMs are yet another abstraction layer contributing to slower, more resource-intensive code that are only necessary for those unfamiliar with the ins and outs of SQL.