Click here to Skip to main content
15,562,222 members
Articles / Programming Languages / SQL
Article
Posted 6 Aug 2020

Stats

32.3K views
17 bookmarked

Dynamic Query Builder for Dapper

Rate me:
Please Sign up or sign in to vote.
4.94/5 (19 votes)
7 Aug 2020CPOL5 min read
Dapper Query Builder using String Interpolation and Fluent API
This wrapper around Dapper helps building dynamic SQL queries and commands and is based on 2 fundamentals. It seems very simple, but you'll see how powerful it is.

Introduction

DapperQueryBuilder is a wrapper around Dapper mostly for helping building dynamic SQL queries and commands. It's based on two fundamentals:

  1. String Interpolation instead of manually using DynamicParameters
  2. Query and Parameters walk side-by-side

It seems so simple, but you'll see how powerful it is.

Background

We all love Dapper micro-ORM.

Many developers have realized that although Entity Framework offers some features that Dapper doesn't, Dapper is still more flexible (and much much faster) than EF. And many have realized that the right tool for the right job sometimes involving using both EF for some things and Dapper for others. And one of the usages where Dapper is the king is for building dynamic queries.

Dynamic SQL

Old-school developers (those who have been coding since the late 90s or early 2000s, probably in VB6, ASP3, or PHP) will probably remember code like this:

C#
string sql = "SELECT * FROM [Product] WHERE 1=1";

if (!string.IsNullOrEmpty(Request["ProductName"]))
   sql += " AND Name LIKE '" + Request["ProductName"].Replace("'", "''") + "'"; // this replace!
if (!string.IsNullOrEmpty(Request["SubCategoryId"]))
    sql += " AND ProductSubcategoryID = " + 
             Request["SubCategoryId"].Replace("'", "''");  // this replace!

// here is where you pray that you've correctly sanitized inputs against sql-injection
var products = cn.Query<Product>(sql);

Basically, we were all sending dynamic SQL statements directly to our databases. And since user-input can be malicious (see below), we had to manually sanitize user-input to avoid SQL-Injection attacks.

Hello Bobby Tables

If you weren't sanitizing your input correctly, you were at serious risk. SQL-injection is probably the most popular web-hacking technique. If you forgot to sanitize something like an "id" querystring, you could be opening a huge vulnerability and possibly exposing all your database, not only for reading but also for modifications.

Parametrized SQL

Even if you're old school (hey VB6 guy, I'm looking at you again), you probably know that Dynamically building SQL like that is not only error-prone (vulnerable to SQL-injection) but also does not benefit from SQL Server Cached Execution Plans. You probably used some kind of code-generation tool which helped you to build code like this (maybe to invoke a stored procedure, since 3-tier-architecture and "Windows DNA" were in the hype in the early 2000s):

VBScript
Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
    & " NEEDS_ID     = @NEEDS_ID, " & vbNewLine _
    & " OBJ_ID       = @OBJ_ID, " & vbNewLine _
    & " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
    & " TIME21_ID    = @TIME21_ID, " & vbNewLine _
    & " WHERE ID     = @WHEREID"

With cmd
    .ActiveConnection = Cn
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("@NEEDS_ID", adInteger, adParamInput, 2, 12)
    .Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
    .Parameters.Append .CreateParameter
                ("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
    .Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 18, 456)
    .Execute
End With 

I hope the millennial developers are not so surprised that parametrized SQL already existed in the past century.

Back to Modern Software

Time flew by, Java and .NET emerged (and maybe submerged a little?), Reflection, Bytecode emission, Full-ORMs, Micro-ORMs, 3-tier was deprecated in favor of dozens of modern architectures, and now database access is much easier, right?

Now we don't have to manually describe each one of our parameters to SQL. Our favorite ORMs will do that for us.

Dapper Example

C#
var dynamicParams = new DynamicParameters();

string sql = "SELECT * FROM [Product] WHERE 1=1";

if (productName != null)
{
    sql += " AND Name LIKE @productName"; 
    dynamicParams.Add("productName", productName);
}

if (subCategoryId != null)
{
    sql += " AND ProductSubcategoryID = @subCategoryId"; 
    dynamicParams.Add("subCategoryId", subCategoryId);
}

var products = cn.Query<Product>(sql, dynamicParams);

Don't Repeat Yourself

The example below makes me a little upset for two reasons:

  • I have to pass productName twice, both to the sql string and to the dynamicParams dictionary.
    Their names should match.
  • I have to keep those two variables (the SQL statement and the list of parameters) separated, even though they are closely related to each other.

String Interpolation Internals

String Interpolation was introduced in C# in 2016.

Instead of doing code like this:

C#
string name = "Rick";
int accesses = 10;
string output = string.Format("{0}, you've been here {1:n0} times.", name, accesses);

You could do like this:

C#
string name = "Rick";
int accesses = 10;
string output = $"{name}, you've been here {accesses:n0} times.";

Internally, when you write an interpolated string (starting with $), the compiler generates a FormattableString class, which contains both the template (as if it was "{0}, you've been here {1:n0} times."), and also the list of parameters (string name and int accesses).

If your method expects a regular string, the FormattableString will be implicitly converted into a regular string, and you get the same behavior as if you just passed a string.format to your method. However, if your method expects a FormattableString class, then you have access to format and arguments isolated from each other.

FormattableString can be useful for example if we wanted to build parameterized SQL statements while letting users build their strings as if it was just regular string concatenation:

C#
QueryBuilder ParseSQL(FormattableString query)
{
    QueryBuilder myWrapper = new QueryBuilder();

    string dapperSql = query.Format;
    // replace {0} by "@p0", 
    // replace {1} by "@p1", etc..
    myWrapper.SQL = dapperSql;

    var dapperArgs = new Dapper.DynamicParameters();
    var args = query.GetArguments();
    // dapperArgs.Add("p0", args[0]);
    // dapperArgs.Add("p1", args[1]); ...
    myWrapper.Arguments = dapperArgs;

    return myWrapper;
    // now anyone can use Dapper like  
    // var pocos = connection.Query<POCO>(myWrapper.SQL, myWrapper.Parameters);
}

DapperQueryBuilder

Based on the idea above, I've created DapperQueryBuilder - which is a simple wrapper around Dapper which allows us to pass SQL parameters using string interpolation.

You can code like this:

C#
var query = cn.QueryBuilder($"SELECT * FROM [Product] WHERE 1=1");

if (productName != null)
    query.Append($"AND Name LIKE {productName}"); 

if (subCategoryId != null)
    query.Append($"AND ProductSubcategoryID = {subCategoryId}"); 

var products = query.Query<Product>(); 

Although it looks like you're just building a dynamic SQL (with values as inline literals), actually what you get is parameterized SQL.

In case, query will have this underlying statement:

SQL
SELECT * FROM [Product] WHERE 1=1 AND Name LIKE @p0 AND ProductSubcategoryId = @p1

and will also hold the parameters:

SQL
@p0 = productName
@p1 = subCategoryId

To sum, instead of using Dapper's extension .Query<T> which extends IDbConnection and accepts a SQL string and a list of parameters, you use QueryBuilder() extension which creates a QueryBuilder where you can dynamically (and in a single statement) add new parameters and add the associated SQL clause.

Quickstart

If you liked it and want to start using it right now:

  1. Install the NuGet package Dapper-QueryBuilder
  2. Start using like this:
    C#
    using DapperQueryBuilder;
    // ...
    
    cn = new SqlConnection(connectionString);
    
    // If you have all your parameters at once and 
    // just want to benefit from string interpolation:
    var products = cn.QueryBuilder($@"
        SELECT ProductId, Name, ListPrice, Weight
        FROM [Product]
        WHERE [ListPrice] <= {maxPrice}
        AND [Weight] <= {maxWeight}
        AND [Name] LIKE {search}
        ORDER BY ProductId").Query<Product>();

    Or building dynamic conditions like this:

    C#
    using DapperQueryBuilder;
    // ...
    
    cn = new SqlConnection(connectionString);
    
    // If you want to dynamically add conditions
    var q = cn.QueryBuilder($@"
        SELECT ProductId, Name, ListPrice, Weight
        FROM [Product]
        WHERE 1=1 ");
    
    q.AppendLine("AND [ListPrice] <= {maxPrice}");
    q.AppendLine("AND [Weight] <= {maxWeight}");
    q.AppendLine("AND [Name] LIKE {search}");
    q.AppendLine("ORDER BY ProductId");
    
    var products = q.Query<Product>();

Filters List

Some people believe incorrectly that this "WHERE 1=1" causes a performance hit. It doesn't. So using this fake condition is a great trick so that you can just append additional conditions (if any) like shown in the example above.

Another option is that you can build your whole query but leave the filters to be dynamically defined (and rendered) later. DapperQueryBuilder has this special command Where() which is used to save the filters internally, and later they are replaced.

C#
int maxPrice = 1000;
int maxWeight = 15;
string search = "%Mountain%";

var cn = new SqlConnection(connectionString);

// You can build the query manually and 
// just use QueryBuilder to replace "where" filters (if any)
var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight
    FROM [Product]
    /**where**/
    ORDER BY ProductId
    ");
    
// You just pass the parameters as if it was an interpolated string, 
// and QueryBuilder will automatically convert them to Dapper parameters (injection-safe)
q.Where($"[ListPrice] <= {maxPrice}");
q.Where($"[Weight] <= {maxWeight}");
q.Where($"[Name] LIKE {search}");

// Query() will automatically build your query and replace 
// your /**where**/ (if any filter was added)
var products = q.Query<Product>();

You can also create OR filters like this:

C#
q.Where(new Filters(Filters.FiltersType.OR) 
{
    new Filter($"[Weight] <= {maxWeight}"), 
    new Filter($"[Name] LIKE {search}") 
});

Or you can mix and match OR/AND like this:

C#
q.Where(new Filters(Filters.FiltersType.OR)
{
    new Filters(Filters.FiltersType.AND)
    {
        $"[Weight] <= {maxWeight}",
        $"[Weight] >= {minWeight}",
    }
    new Filter($"[Name] LIKE {search}")
});

And even arrays can be used as interpolated strings:

C#
var categories = new string[] { "Components", "Clothing", "Acessories" };
q.Append($"WHERE c.[Name] IN {categories}");

Fluent API (Chained-methods)

For those who like method-chaining guidance (or for those who allow end-users to build their own queries), there's a Fluent API that allows you to build queries step-by-step mimicking dynamic SQL concatenation.

So, basically, instead of starting with a full query and just appending new filters (.Where()), the QueryBuilder will build the whole query for you:

C#
var q = cn.QueryBuilder()
    .Select($"ProductId")
    .Select($"Name")
    .Select($"ListPrice")
    .Select($"Weight")
    .From($"[Product]")
    .Where($"[ListPrice] <= {maxPrice}")
    .Where($"[Weight] <= {maxWeight}")
    .Where($"[Name] LIKE {search}")
    .OrderBy($"ProductId");
    
var products = q.Query<Product>();

You would get this query:

SQL
SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
WHERE [ListPrice] <= @p0 AND [Weight] <= @p1 AND [Name] LIKE @p2
ORDER BY ProductId

Hope you enjoyed this post as much as I did when I wrote this library!

You can find the full source code here. If you like it, please add a star and fork it in GitHub.

History

  • 6th August, 2020: First version

The original idea came from this other blog post.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
I'm passionate about building things since I was born and passionate about computers and programming since I was a kid. I taught myself programming when I was 12 by reading some BASIC programming books and never stopped since then, having played and worked with dozens of technologies.

I have more than 20 years of work experience, having played both technical and management roles, and having worked for companies in many different sectors, including Financial Institutions, Healthcare, and Startups.

I have a Bachelor's degree in Computer Science from University of São Paulo, and an MBA degree from Fundação Getúlio Vargas, both top-ranked universities in Latam. Born and raised in Brazil, lived in Chile for a while, now living in Florida (Fort Lauderdale Area).

If you like my articles please check and follow me on https://github.com/Drizin

Comments and Discussions

 
GeneralMy vote of 5 Pin
v# guy13-Jul-22 14:13
v# guy13-Jul-22 14:13 
QuestionSqlKata Pin
Jacquers11-Apr-22 0:00
Jacquers11-Apr-22 0:00 
Questioncn.QueryBuilder - .NET 5.0: SqlConnection does not contain a definition for QueryBuilder Pin
kasabb20-May-21 6:37
kasabb20-May-21 6:37 
AnswerRe: cn.QueryBuilder - .NET 5.0: SqlConnection does not contain a definition for QueryBuilder Pin
Rick Drizin19-Nov-21 13:07
Rick Drizin19-Nov-21 13:07 
GeneralMy vote of 5 Pin
Anurag Gandhi4-Sep-20 17:45
professionalAnurag Gandhi4-Sep-20 17:45 
SuggestionGood Work!! Pin
Anurag Gandhi4-Sep-20 17:44
professionalAnurag Gandhi4-Sep-20 17:44 
Good Work, thanks for the article.

Just a suggestion:
One of the purpose of ORM or Micro ORM is to avoid hard coded SQL query. The approach taken here is generic but can be disastrous in case Column/Column Type change is needed in database. Extensive test cases also may/may not be able to catch bugs early.
Alternate approach: I suggest creating the same / similar solution using LINQ dynamic query expressions/ predicates. That way, Type Safety could be ensured.

Still, have my 5 for your hard work. Thumbs Up | :thumbsup:
Life is a computer program and everyone is the programmer of his own life.

PraiseGreat Work! Pin
Marcelo Ricardo de Oliveira7-Aug-20 8:12
mvaMarcelo Ricardo de Oliveira7-Aug-20 8:12 
QuestionGreat work ! Pin
SaiRictus6-Aug-20 23:38
SaiRictus6-Aug-20 23:38 
AnswerRe: Great work ! Pin
Rick Drizin7-Aug-20 7:03
Rick Drizin7-Aug-20 7:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.