DB Agnostic ASP.Net?

we are making an ASP.Net application. We would like to have our application to be at least sorta DB agnostic, most notable to be compatible with both SQL Server and PostgreSQL. What is the best way of doing this? What are some of the common pitfalls? Also is there a class or something that already abstracts away the difference between things like SqlConnection and whatever PostgreSQL uses for connections?

(We are wanting to be DB agnostic so we can use PostgreSQL here(in development and later in our own hosting) due to price an开发者_C百科d let our self-hosting clients use Sql Server if they so wish)

All ADO.Net providers extend the basic interfaces:

• IDbConnection
• IDbTransaction
• IDbCommand
• IDataReader
• IDbDataParameter

So in theory you can write your whole DAL (Data Access Layer) against the abstract interfaces and leverage any provider, including 3rd party ones like MySQLs. In practice, no mortal ever managed to pull this trick. The interfaces are very hard to program against on one hand, and any application beyond a demo-ware will quickly run into the wall of SQL dialects incompatibility.

A more feasable approach is to settle for a number of target destinations and develope specific DALs. Entity Framework and repository pattern and nHibernate and all such help, but none solves the fundamental issues.

You could use Entity Framework. That way you would have a single programming model to work against.

http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx

http://www.devart.com/dotconnect/postgresql/

If you just want to use ADO.NET directly, you can use the DbProviderFactory to abstract away the provider (SqlClient, OleDb etc) that you are using. Usually you use this in conjunction with the <connectionStrings> configuration element, with code something like:

ConnectionStringSettings c = ConfigurationManager.ConnectionStrings[name];
DbProviderFactory factory = DbProviderFactories.GetFactory(c.ProviderName)
...
IDbConnection connection = _factory.CreateConnection();
connection.ConnectionString = c.ConnectionString;
...

There's lots of info in MSDN.

Of course this doesn't help you with differences in SQL syntax between different providers - you need a lowest common denominator approach to work with multiple providers. This affects things like the syntax for parameters (e.g. @ prefix for SQL Server, positional parameters only for OleDb, ...).

I just create an interface for my data source and then implement it for each data source type I need to use. Normally they look something like this:

public interface IMyProjectDataSource
{
    IEnumerable<string> GetUserNames();
    void AddUser(string userName);
}

public class SqlServerMyProjectDataSource : IMyProjectDataSource
{
    public SqlServerMyProjectDataSource(string connectionString)
    {
        //...
    }

    public IEnumerable<string> GetUserNames()
    {
        //...
    }

    public void AddUser(string userName)
    {
        //...
    }
}

public class PostgreSqlMyProjectDataSource : IMyProjectDataSource
{

    public IEnumerable<string> GetUserNames()
    {
        //...
    }

    public void AddUser(string userName)
    {
        //...
    }
}

public class HttpCacheSqlMyProjectDataSource : IMyProjectDataSource
{
    public HttpCacheSqlMyProjectDataSource(IMyProjectDataSource parentMyProjectDataSource)
    {

    }

    public IEnumerable<string> GetUserNames()
    {
        //...
    }

    public void AddUser(string userName)
    {
        //...
    }
}

I also like this because it allows you to "chain" them together. For instance you can do all of your data source caching like this:

public class HttpCacheSqlMyProjectDataSource : IMyProjectDataSource
{
    public HttpCacheSqlMyProjectDataSource(IMyProjectDataSource parentMyProjectDataSource)
    {
            //...
    }

    public IEnumerable<string> GetUserNames()
    {
        //...
    }

    public void AddUser(string userName)
    {
        //...
    }
}

The hardest part is deciding how to create it, especially if you're planning to chain more than one together. For that I generally store it in my Global.asax.cs as a singleton and create it from a type name in the .config file. You can use Type.GetType() and then Activator.CreateInstance() to do that and most of the chaining I have will be done no matter what the data source in the .config is so I don't have to worry about creating some sort of "constructor" type or a complicated .config.

Hopefully that makes sense. This may not be the best for all situations but I have had a lot of luck with it.

Entity Framework is an option as mentioned by Shiraz. You might also consider other ORM solutions such as NHibernate.

I would recommend to go with some sort of Repository Pattern.

It's not a silver bullet that will save you from hacking for each DBMS flavour, but that is the way to go if you want it to be maintainable from the beginning, something that ORMs just do not provide.

http://blog.wekeroad.com/2008/04/07/asp-net-mvc-mvc-storefront-part-2

At a very high level you would want to use something that abstracts your database more than just the SqlConnection, SqlCommand, and Sql* classes. So when people say to use ORMs, they're pretty much right. The ORMs provide that level of abstraction for you.

But for abstracting away SqlCommand and SqlConnection there are interfaces in the System.Data namespace. IDbConnection, IDbCommand, etc. are already inside the framework and you can code against those. The issue then is finding kind of way to create the concrete classes and you can tackle that using an IoC container or a provider pattern.

The best solution for database-agnostic applications is usually an ORM (this tag). There are plenty of them for .NET - this question will give you some guidance on which one to choose.

The three most popular ones are:

• NHibernate
• Linq-to-SQL (now superceded by Entity Framework) - used by Stackoverflow.com. SQL Server only
• Subsonic