Generic SQL builder .NET

Question

I'm looking for a way to write an SQL statement in C# targeting different providers. A typical example of SQL statements differentiating is the LIMIT in PostgreSQL vs. TOP in MSSQL.

Is the only way to solve SQL-syntax like the two above to write if-statements depending on which provider the user selects or using try catch statements as flow control (LIMIT didn't work, I'll try TOP instead)? I've seen the LINQ Take method, but I'm wondering if one can do this without LINQ?

In other words, does C# have some generic SQL Provider class that I have failed to find that can be used?

Answer 1

There is DBLinq:

LINQ provider for Oracle, PostgreSQL, MySQL, Ingres, SQLite, Firebird and ... SQL Server (C# 3.0)

When you generate a query using LINQ to SQL it is possible to view the generated SQL and save it.

It doesn't meet your requirement "without using LINQ" though. If you have LINQ available, why not use it?

Answer 2

The Entity Framework is able to target different databases. This would allow you to write LINQ statements that would work with both databases. You would need to find a postgresql provider for the Entity Framework. There are several to choose from.

Hope this helps.

Answer 3

I don't think there is a "generic sql provider".

In our shop we need to support both DB2 and SQL Server so we chose to implement a layer pattern creating Model, Data Access and Business Logic classes. The data access layer handles the connection to the different DBMSs and loads the model classes passing them back to the business logic. The business logic and the model classes have no idea where the data access layer gets the data.

The differences in SQL are handled because the data access layer calls stored procedures in the database. We have the stored procedures implemented with the appropriate syntax in both systems. If we need to go to another database all we need to do is implement the necessary procedures on the new DBMSs and everything should just work.

Answer 4

Joining Marc Tidd's idea - If you don't want Linq, create separate DAL classes for each provider, or use stored procedures which will be implemented on each DB.