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, INSERT, UPDATE, 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, choose 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 the 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.
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 called 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…