Building reusable SQL queries with SQL query builder

This article describes one of the approaches to easily create and manage your SQL queries in ASP.NET Core applications. Examples given here were created in C#, but you can use any other programming language compatible with ASP.NET Core.

The full source code is located at the GitHub repository and there is also an ASP.NET Core NuGet package.

What Is SQL Query Builder?

The SQL Query Builder (SQB) is a component of the Data Tools Platform (DTP) SQL Development Tools project.

How to Design Easy-To-Use SQL Queries?

Each time we need to create a data layer for an ASP.NET Core application, which is backed up by an SQL database, we're faced with the question how to design our SQL queries, to make them easy to use, maintain and extend. Of course, it's fairly easy to just implement SQL queries in ASP.NET. But, once implemented, the process of extending and maintaining those queries can quickly become difficult, especially if we implement those queries as simple string constants, which opens the door to SQL injections.

What we would like to implement, ideally, is something that is:

1. easy to maintain (which means we can easily add/delete/update/rename tables/columns)
2. easy to extend (which means we can quickly create new SQL queries on top of existing ones)
3. immune to security threats (like SQL injections)

Many of us usually decide to use an object-relational mapping (ORM) solution, like EntityFramework, NPoco, LINQ to SQL or similar, to deal with the data layer as a whole. This approach is pretty much a standard these days and this article does not offer an alternative to those solutions, but rather tries to make things even easier.

Note that SQL query builder, described in this article, is a standalone solution, without any dependency, and it is NOT a requirement to have an ORM solution in order to use it.

Creating maintainable and reusable SQL queries

Working on a web API project recently, I had an idea to create a small library, which could help us create SQL queries that are maintainable, reusable and easy to use. Part of the inspiration for this library was the .NET's Language Integrated Query facility (LINQ) and the elegance with which LINQ queries were extended.

The idea was to create reusable SQL queries and build any new queries on top of the existing ones. Ideally, we should be able to create a basic query, like SELECT * FROM [Table] and build any new queries on top of that basic query, just by extending it, just like in a LINQ query, where we start from an enumeration and keep extending the query until we shape it into what we need. For example:

IEnumerable allUsers = GetAll(); var activeGroups = allUsers
.Where(user => user.IsActive)
.Select(user => user.Id)
.Distinct()
.ToArray();

We start with an enumeration of all users, using GetAll(), which we can reuse as many times as we need. Then we extend our query by adding a filter (Where()), mapping the result to the list of user ids (Select()), reducing the result further to the distinct enumeration of user ids. After we crafted our query, we materialize it with ToArray().

The SQL query builder implements the similar behavior, helping us to create our queries in a similar fashion as LINQ queries, materializing them in the end as simple SqlQuery objects which consist of a string (the actual SQL command) and an object array (the SQL parameters):

public class SqlQuery{ public string Command { get; } public object[] Parameters { get; }}

That approach helps us create parameterized SQL queries to avoid being a victim of an SQL injection attack.

Usage of SqlQueryBuilder

Let's take a look at some examples, which would explain it better, hopefully. First, we create an SqlQueryBuilder:

var builder = new SqlQueryBuilder();

which we'll use in all the following examples. For example, let's select everything from a table User:

var query = builder .From() .SelectAll(); var sql = query.ToSqlQuery();

In our sql variable, we'll have an SqlQuery object, with the Command property set to:

SELECT * FROM [User]

And if we extend that query, by adding a filter to our result set, using a WHERE clause:

var name = "John"; var newQuery = query .Where(user => $"{user.Name} LIKE '%' + @0 + '%'", name); var newSql = newQuery.ToSqlQuery();

that will create an SqlQuery, with the Command property:

SELECT * FROM [User] WHERE ([User].[Name] LIKE '%' + @0 + '%')

and its Parameters property:

@0 = "John"

Note that we made use of the String.Format() method in order to leverage the help of IntelliSense, to help us write queries more conveniently. We also used the "string interpolation" feature of the C# language, to make things even easier.

The string $"{user.Name} LIKE '%' + @0 + '%'" is the same as string.Format("{0} LIKE '%' + @0 + '%'", user.Name).

The SqlQueryBuilder will parse this construct and enumerate all the classes and properties used and will map them to the appropriate tables/columns of the underlying SQL database. The default convention is to use the same naming for the C# classes and SQL tables, as well as the same naming for the C# properties on those classes and SQL columns of those tables. We can, of course, customize this mapping by providing our own mapper implementations (see section "Custom table/column names mapping").

Once we have our query built, we can use it, for example, directly using System.Data.SqlClient.SqlConnection, like this:

using (var connection = new SqlConnection(ConnectionString))
{ IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = query.Command;
var param = cmd.CreateParameter();
param.ParameterName = "@0";
param.Value = query.Parameters[0]; connection.Open(); ...}

Of course, this is just a basic example how can we use the query that SqlQueryBuilder has generated for us. But we can also use our favorite ORM solution instead.

How to Reuse Queries?

If we want to create a simple SQL query and reuse it to construct more complex new queries, we could do it easily, writing something like this:

var name = "John"; var userGroupIds = new[] { 1, 2, 3 }; var allUsersQuery = builder .From() .SelectAll(); var usersByNameQuery = allUsersQuery .Where(user => $"{user.Name} LIKE '%' + @0 + '%'", name); var usersByNameExtendedQuery = usersByNameQuery .InnerJoin