Generic .NET SQL constructor.

Question

I am looking for a way to write an SQL statement in C # for different providers. A typical example of differentiating SQL expressions is LIMIT in PostgreSQL versus TOP in MSSQL .

Is the only way to solve SQL syntax, such as the two above, to write if statements depending on which provider selects or uses try catch expressions as flow control (LIMIT doesn't work, instead I try TOP)? I saw the LINQ Take method, but I wonder if it can be done without LINQ?

In other words, does C # have some common SQL provider class that I could not find that can be used?

Answer 1

Entity Framework can target different databases. This will allow you to write LINQ statements that will work with both databases. You will need to find the postgresql provider for the Entity Framework. There are several options.

Hope this helps.

Answer 2

There is DBLinq :

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

When creating a query using LINQ to SQL, you can view the generated SQL and save it.

It does not meet your LINQ-free requirement. If you have LINQ, why not use it?

Answer 3

I don’t think there is a “generic SQL provider”.

In our store, we need to support both DB2 and SQL Server, so we decided to implement a layer template that creates the Model, Data Access, and Business Logic classes. The data access layer processes the connection to various DBMSs and loads model classes, passing them back to business logic. Business logic and model classes have no idea where the level of access to data receives data.

SQL differences are handled because the data access layer invokes stored procedures in the database. We have stored procedures implemented with the appropriate syntax in both systems. If we need to switch to another database, all we need to do is implement the necessary procedures for the new DBMS, and everything should just work.

Answer 4

Joining Mark Tidd's Idea - If you do not want Linq, create separate DAL classes for each provider or use the stored procedures that will be implemented in each database.