Generic SQL builder .NET

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-开发者_JS百科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?

---

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.

---

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?

---

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.

---

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.

---

For some reason I do not like linq as query interface and started to create a sql generating library some time ago. Take a look on LambdaSql. For now it contains basic scenarios for select clause and where filters. Setting fields, where, group by, having, order by, joins, nested queries are already supported. Insert, Update and Delete are going to be supported later. It also contains some points to extend existing behavior. For example Limit is implemented using that way.

Example:

var qry = new SqlSelect
(
    new SqlSelect<Person>()
        .AddFields(p => p.Id, p => p.Name)
        .Where(SqlFilter<Person>.From(p => p.Name).EqualTo("Sergey"))
    , new SqlAlias("inner")
).AddFields<Person>(p => p.Name);

Console.WriteLine(qry.ParametricSql);
Console.WriteLine("---");
Console.WriteLine(string.Join("; ", qry.Parameters
    .Select(p => $"Name = {p.ParameterName}, Value = {p.Value}")));

Output:

SELECT
    inner.Name
FROM
(
    SELECT
        pe.Id, pe.Name
    FROM
        Person pe
    WHERE
        pe.Name = @w0
) AS inner
---
Name = @w0, Value = Sergey

See more here https://github.com/Serg046/LambdaSql