How to use SqlBuilder

Question

This SqlBuilder:

var builder = new SqlBuilder(); 
var sql = builder.AddTemplate( /*...

Intensely dumb question but, how do I use this? I know it's in Dapper.Contrib, but that using statement isn't enough. What references or other using statements do I need to add?

An alternative way is to use a fluent Query Builder – amd May 08 '18 at 11:53 — amd, May 08 '18 at 11:53

Carlos Cuevas · Answer 1 · 2019-04-05T01:15:48.840

This question appears in the dapper tutorial page, so I'm updating the answer.

In version 1.6, SqlBuilder is in the namespace Dapper. And it is included in the nuget package Dapper.SqlBuilder.

This is an example of how it works:

var builder = new SqlBuilder();
builder.Select("id_something");
builder.Select("MyCol");
builder.Select("OtherCol");
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@MyParam", 3, DbType.Int32, ParameterDirection.Input);
builder.Where("id_something < @MyParam", parameters);
// builder.Where("id_something < @MyParam", new { MyParam =3}); //this is other option for params.
builder.InnerJoin("OtherTable on OtherTable.id=MyTable.id");
//The /**something**/ are placeholders,
var builderTemplate = builder.AddTemplate("Select /**select**/ from MyTable /**innerjoin**/ /**where**/ ");
var result = connection.Query<MyClass>(builderTemplate.RawSql, builderTemplate.Parameters);

This is the Sql generated:

Select id_something , MyCol , OtherCol
 from MyTable 
INNER JOIN OtherTable on OtherTable.id=MyTable.id
 WHERE id_something < @MyParam

Which Dapper tutorial page are you referring to? I would like to read it. — Terry, Jan 31 '21 at 14:47
Dapper had a tutorial page that linked to questions on stackoverflow. Not a real tutorial. I can´t find it again, probably changed it and now has this link https://dapper-tutorial.net/knowledge-base/tag/sqlbuilder — Carlos Cuevas, Feb 18 '21 at 17:25
@CarlosCuevas that's a not Dapper's Tutorial page. That's someone's attempt to hijack the library name and has absolutely no relation to Dapper itself. In fact, *bad* examples in that site cause headaches to the Dapper maintainers all the time. — Panagiotis Kanavos, Sep 15 '21 at 09:22

Fabricio · Answer 2 · 2020-07-03T00:44:46.840

1

To this date, SqlBuilder has not made yet to any official package on Nuget. I got that installed by using the following package: Dapper.SqlBuilder. I really hope it becomes part of the official library.

Anyways, to your question, once the package is installed, you don't need to add any new "using" clause, as the SqlBuilder is inside the same Dapper namespace.

You can check the source code details in here: https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper.SqlBuilder/SqlBuilder.cs

EDIT: The package can be found at: https://www.nuget.org/packages/Dapper.SqlBuilder/

edited Jul 03 '20 at 00:44

answered Dec 10 '15 at 21:04

Fabricio

532
1
6
21

1

Comment is obsolete – Andrew Dennison Jun 11 '20 at 14:43

drizin · Answer 3 · 2020-08-29T19:37:54.773

There's a library called DapperQueryBuilder which is an alternative to Dapper SqlBuilder, and the similar code from question above would be like this:

// using DapperQueryBuilder; ...

int myParam = 3;
var builder = cn.QueryBuilder();

builder.Select($"id_something");
builder.Select($"MyCol");
builder.Select($"OtherCol");
builder.From($"MyTable");
builder.From($"inner join OtherTable on OtherTable.id=MyTable.id");
builder.Where($"id_something < {myParam}");

var result = builder.Query<MyClass>();

Or even shorter if the only dynamic part is the filters:


var builder = cn.QueryBuilder($@""
    SELECT id_something, MyCol, OtherCol
    FROM MyTable
    inner join OtherTable on OtherTable.id=MyTable.id
    /**where**/
");

int myParam = 3;
builder.Where($"id_something < {myParam}");

var result = builder.Query<MyClass>();

Even though it looks like we're using unsafe interpolated strings, that's not true - the output is fully parametrized SQL (WHERE id_something < @p0) - and you don't have to manually manage the dictionary of parameters as you would do if using SqlBuilder.

Disclaimer: I'm the author of this library

score -2 · Accepted Answer · answered Sep 25 '13 at 16:31

-2

Don't know if no one knew or if it was just too silly to answer, but this is the correct statement:

using StackExchange.Profiling.Helpers.Dapper;

answered Sep 25 '13 at 16:31

idlackage

2,715
8
31
52

1

what nuget project you used to have that namespace? I am using Dapper & dapper.contrib. That namespace is not available. – Zo Has Jul 03 '14 at 05:27
Answer is obsolete – Andrew Dennison Jun 11 '20 at 14:44
This answer is not correct. I fixed my original answer, as it has been 5 years since I posted it. – Fabricio Jul 03 '20 at 00:45

score -4 · Answer 5 · answered Nov 09 '13 at 20:35

-4

I did figure it out by making a new class file named SqlBuilder.cs and copied content from THIS link

answered Nov 09 '13 at 20:35

Vitap Ramdevputra

160
10

Answer is obsolete – Andrew Dennison Jun 11 '20 at 14:44

How to use SqlBuilder

5 Answers5

Linked