How to design a provider independet DAL (.Net)

Question

I am currently developing a query builder application, basically a simple graphical interface that should allow users with no knowledge of SQL to define various queries on a database (joins, select, update, insert, delete). I will be using .Net 3.5. My application should support multiple databases, it should work with MS-SQL Server, MySQL and Oracle, so I would appreciate any hints or links to relevant lecture on how to design a provider independent DAL.

The user will select a database server, a database on the current server, provide the connection credentials, chose various tables, define queries (using a series of combo boxes) and finally execute the queries if they are valid. Of course, in the DAL I do want to have methods for each DB provider. I am thinking something on the lines of the factory pattern.

Note: This is a simple school project, so I am not interested in security or performance of the resulting queries.

UPDATE: After some more research and with the very valuable input that you have provided, I decided to use DbProviderFactory. ORM would be interesting but since I just want a query analyzer/builder I don't see the point of using one. So, I would appreciate if you would point me to a detailed tutorial on how to use DbProviderFactory and the associated classes.

Answer 1

I think ADO.NET Entity Framework (available since .NET 3.5 SP1) is a great choice as it pretty much abstracts away database-dependent SQL with its entity SQL language.

Answer 2

You might be surprised, but a very simple provider independent DAL can be achieved with:

plain old DataSet and DataTable.

Answer 3

I must say that editing a reasonably complex query visually is very cumbersome. And allowing users to insert/delete data using visual designer is a certain way to shoot yourself in the foot. A sized-down version of Management Studio, knowledge of basic SQL plus restricted server user will do a much better job.

If you're still inclined to design this app, you'll need NHibernate. More precisely, Criteria Queries will do the job, since they map pretty close to what you need.

Answer 4

Most any ORM (Object-Relational Mapper) will know how to talk to a variety of database types.

As for allowing users to build their own queries: you need to be very careful with this. It's not so much that users could create malicious queries (though that can be a problem) as it is accident. It's surprisingly easy to write a query that will use all available server resources and create an effective denial of service for your database.

Answer 5

I recommend using the System.Data.Common.DbProviderFactories class to generate generic ADO.NET classes.

As you find more .NET providers for databases you want to support, simply drop the provider DLL in the app's path and add a reference to the DbProviderFactory of the provider in the app.config file. You can have the user select the provider to use.

Here is an MSDN article on the topic: Obtaining a DbProviderFactory (ADO.NET)

I've used this approach before and been able to support MSSQL and SQLite in the same project with a minor configuration change.

Not sure if it'll work as well for a query builder app though...

Answer 6

I'm not sure if this assists with your quest, but one thing I learned rather recently and took to heart is to have your data model's unique identifier implementation not propagate directly outside of the data layer, but to be wrapped in an abstraction. For instance, here is an interface which wraps a model's identifier:

public interface IModelIdentifier<T> where T : class 
{
    /// <summary>
    /// A string representation of the domain the model originated from.
    /// </summary>
    string Origin { get; }

    /// <summary>
    /// The model instance identifier for the model object that this 
    /// <see cref="IModelIdentifier{T}"/> refers to.  Typically, this 
    /// is a database key, file name, or some other unique identifier.
    /// <typeparam name="KeyDataType">The expected data type of the 
    /// identifier.</typeparam>
    /// </summary>
    KeyDataType GetKey<KeyDataType>();

    /// <summary>
    /// Performs an equality check on the two model identifiers and 
    /// returns <c>true</c> if they are equal; otherwise <c>false</c> 
    /// is returned.  All implementations must also override the equal operator.
    /// </summary>
    /// <param name="obj">The identifier to compare against.</param>
    /// <returns><c>true</c> if the identifiers are equal; otherwise 
    /// <c>false</c> is returned.</returns>
    bool Equals(IModelIdentifier<T> obj);
}

Your business logic layer, which may have in the past passed around ints as unique identifiers (for instance, from a identity column in your database table), is now passed as such:

    public IPerson RetrievePerson(IModelIdentifier<IPerson> personId)
    {
        /// Retrieval logic here...
    }

Your data layer will then have a class which implements IModelIdentifier<Person> and populates its internal data type with the physical model's unique identifier. This insulates your business layer from any changes you may have at the data layer, such as replacing your int key identifiers with Guids.