Is there a dynamic Sql builder library for .Net? [closed]

Question

Something like this.

http://code.google.com/p/squiggle-sql/wiki/Tutorial.

This is required for cases where It is required to build complex sql from the user input from UI. Currently in the project I am working is using String Manipulation which looks ugly and is difficult to maintain.

Downspout

in 2018 you can use sqlkata.com – Glossa

Answer 1

Try DbExtensions, available as NuGet package.

Sisto

DbExtensions is easy to use. However, it has a few fundamental design problems. First, it doesn't have a much desire INSERT IGNORE INTO function. Secondly, it is very rigid. When one does INSERT_INTO, he cannot do INSERT_INTO({0}({1},{2}), tableName, col1, col2) because that will add single quotes around the parameters. Instead you have to use the overload and basically hardcode in the input string. Another example is doing the VALUES which you either have to do VALUES(some_string) or do VALUE({0} = {1}, col1, val1). With that said, it's not a bad library but it leaves much to be desired. – Havre

@SomeNewbie Post your comments here and I'll help you out. – Sisto

Answer 2

I was able to find a very good library for creating dynamic sql in .Net.

http://dynasql.codeplex.com/documentation

Downspout

Answer 3

Not that I am aware of (although that doesn't mean there definitely isn't).

What about Entity Framework? It allows the query to be built up and translates that to SQL against entities:

customers.OrderBy(c => c.Name).Skip(10).Take(20) 

Generates:

SELECT value c 
FROM NW.Customers AS c 
ORDER BY c.Name skip 10 limit 20; 

Coley

The application I am working on is in .Net 2.0. So EF is not an option for me. – Downspout

Can you use a full blown orm such as NHibernate? It will give you the ability to build I queries through it'd Criteria API. – Coley

We are using nHibernate 1.2 in our application but this particular functionality has bypassed nHibernate for performance reasons. – Downspout

Answer 4

Anything wrong with Linq-to-Sql?

var dc = new YourDataContext();
var query = dc.TableName.Where(x=>x.MatchesYourPredicate);
Console.WriteLine(dc.GetCommand(query).CommandText);

Bun

The project for which I needed that was .Net 2.0 project. – Downspout

Also, the filtering elements are not always know – Northumberland

For me the table name is only known at runtime – Bishop

Answer 5

I always build my own... it's quick and easy and you don't have to rely on 3rd-party libraries. Plus it helps you become that little bit more intimate with SQL.

Valentin

That's true if you're a programmer, but I can see that library being used to drive a GUI that allows non-programmers/data entry people to build generic queries against a database. They're never going to be able to do so efficiently but sometimes it's useful. – Neckline

Do you mind sharing it? – Protrude

Is it really that easy? How are you handling sub selects? Is there strong typing? Joining? Aliasing? Can you group items in a where clause so OR statements don't nullify the AND statements? I could go on. – Overtask

Yes, it's quite easy - and you retain complete control of what's going on, which can help you better understand the approach you're taking and why you've taken that approach. – Valentin