7

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?

Patrick
  • 17,669
  • 6
  • 70
  • 85
  • 1
    Why don't you want to use LINQ? – Mark Byers May 30 '10 at 10:51
  • There are many ways to solve these differences, are you sure using SQL is the way to do it, that is, find some system that allows you to write one SQL statement that will work across different providers? – Lasse V. Karlsen May 30 '10 at 13:47
  • @Mark Byers: Well, I find myself often targeting .NET 2 and as far as I know, LINQ doesn't work that well there. Historically this was because I wanted to be able to port to Mono without any fuzz, and then I just got "stuck". Since Mono works well with LINQ nowadays, I can't really give any other good answer why I'm still living in the past.. – Patrick May 30 '10 at 14:55
  • @Lasse V. Karlsen: How would I get data from a SQL database in C# *without* using a SQL statement? Do you have an example of a system in which I can solve the differences? – Patrick May 30 '10 at 16:34
  • 1
    No, that's not what I meant. You could set up a system where you would effectively have to write two SQL statements, each with their own optimizations for the different database engines, and then just layer that away so that the code that uses it doesn't need to know which one is currently being used. An IoC container or similar could be used here. However, if you want/need to go the way of one sql targetting different providers, then I have gone down that route and might have some code for you. – Lasse V. Karlsen May 30 '10 at 16:55

5 Answers5

7

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.

Joel Cunningham
  • 13,620
  • 8
  • 43
  • 49
3

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?

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
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.

Marc Tidd
  • 1,066
  • 9
  • 11
  • Ouch, my first down vote. An explanation would be appreciated. Thanks. – Marc Tidd May 30 '10 at 14:38
  • So basically, implement your own? :-) Stored procedures are not a "standard" among databases as far as I know, although a good point since it reduces errors that can occur when working with business logic. I was looking for a way to for instance list tables in a db and then get the columns from a particular table in "any database", in which case S.P. are not that good of a match. – Patrick May 30 '10 at 15:31
  • I see, your question did not detail the requirement of "discovering" database objects, this goes well beyond the differences in SQL implementations. Thanks for the explanation and I understand that this is not what you are looking for and thus the down vote. – Marc Tidd May 30 '10 at 15:52
  • I didn't downvote. I appreciate the answer, and thought it was a good answer actually given the requirements I specified in my question. – Patrick May 30 '10 at 16:27
  • @Patrick, I assumed you were responding to my request for clarification. Thanks for the feedback anyway. – Marc Tidd May 30 '10 at 16:31
1

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.

rkellerm
  • 5,362
  • 8
  • 58
  • 95
  • Yes, the main idea to solve the SQL issue is the use of stored procedures, you don't have to go through the full layer pattern if it doesn't fit your solution. – Marc Tidd May 30 '10 at 13:53
0

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

Serg046
  • 1,043
  • 1
  • 13
  • 42