CODEWITHSUNDEEP
.netdatabaseenterprise-library
ProfK
ProfK

Reputation: 51063

MySQL and SQL Server for same application

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.

Upvotes: 2

Views: 1004

Answers (8)

3Dave
3Dave

Reputation: 29041

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.

Upvotes: 0

Thomas Hansen
Thomas Hansen

Reputation: 5513

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

Upvotes: 0

Patrick de Kleijn
Patrick de Kleijn

Reputation: 520

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;
            }
        }
    }

Upvotes: 0

dkretz
dkretz

Reputation: 37645

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

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391306

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.

Upvotes: 0

Don Pratt
Don Pratt

Reputation: 414

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.

Upvotes: 1

dkretz
dkretz

Reputation: 37645

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.

Upvotes: 0

Rob Prouse
Rob Prouse

Reputation: 22647

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.

Upvotes: 4

Related Questions