Introduction
SqlBinder is a tool that deals with the challenge of building your SQL queries but in a sort of a unique way that I haven't found anywhere else yet - it combines the plain old SQL with the possibilities you may find in XSLT. Since there's quite a number of tools and extensions thereof that deal with the similar topic maybe it's better to explain what SqlBinder is not.
It isn't an ORM or micro-ORM solution - instead, it is DBMS-independent, SQL-centric templating engine. All it does is it removes the hassle of writing code that generates SQLs and bind variables . It does not generate the entire SQL itself, it transforms an existing SQL template instead. Thefore it should assists (rather than replace) whatever ORM solution you may be using.
It isn't 'SQL builder' due to its high degree of composability, it is aimed at writing more complex queries, those with more than one line - with SqlBinder your dynamic SQL can be composed of a single template, you wouldn't have to build it, the idea is to be able to store it somewhere as it is. It isn't a swiss army knife either and it can be elegantly used alongside other popular SQL building assistants that come with tools such as Dapper, Dapper.Contrib, PetaPoco and others, it wasn't made to fit all scenarios.
Generating SQL queries via string concatenation is still a surprisingly common method despite many tools enabling you to build SQLs. While these tools offer some degree of help in making your queries they naturally have their short comings. As a side note, I am personally not a fan of tools that generate the entire SQL - for me, it's just another layer of additional programming to make something which already works now work with the tool. With SqlBinder though, you can fully express your database querying desires without being constantly concerned about whether your SQL will work with the X tool or how much time you will need to make it work.
Background
I originally wrote the first version of this library back in 2009 to make my life easier. The projects I had worked on relied on large and very complex Oracle databases with all the business logic in them so I used SQL to access anything I needed. I was in charge of developing the front-end which involved great many filters and buttons which helped the user customize the data to be visualized. Fetching thousands of records and then filtering them on client side was out of the question. Therefore, with some help of DBAs, PLSQL devs, etc., we were able to muster up some very performant, complex and crafty SQLs.
This however, resulted in some pretty awkward SQL-generating and variable-binding code that was hard to maintain, optimize and modify. Tools like NHibernate solved a lot of problems we didn't have but didn't entirely solve the one we had. I wasn't aware of Dapper back then but while it would lessen the problems it still couldn't solve them (otherwise I would just switch to Dapper as it's a really great library). This is where SqlBinder syntax came to the rescue, all that mess was converted into a string.Format-like code where I could write the whole script and then pass the variables (or don't pass them). It helped me greatly so to make it more accessible and reusable for multiple projects, I released it on GitHub. Now, I'm writing this article for everyone else too, for whatever it is worth.
Using the Library
The source of SqlBinder comes with a demo app, console examples and many unit tests. I will demonstrate here some basic usage for you to get started with. As you'll see later on, the essence and syntax of SqlBinder is actually very simple.
A Quick Demonstration
Consider the following method signature:
IEnumerable<CategorySale> GetCategorySales(
IDbConnection connection,
IEnumerable<int> categoryIds = null,
DateTime? fromShippingDate = null, DateTime? toShippingDate = null,
DateTime? fromOrderDate = null, DateTime? toOrderDate = null,
IEnumerable<string> shippingCountries = null);
Implementation of this method should return a summary of sales grouped by categories and filtered by any combination of the following criteria: categories, shipping dates, order dates and shipping countries.
Usually, you'd implement this method by building an SQL via some Fluent API (e.g. PetaPoco's Sql.Builder), Dapper.Contrib's nice SqlBuilder or just StringBuilder. Instead, I'm going to show you how you could implement this method via SqlBinder and regular Dapper. It would look like this:
IEnumerable<CategorySale> GetCategorySales(
IDbConnection connection,
IEnumerable<int> categoryIds = null,
DateTime? fromShippingDate = null, DateTime? toShippingDate = null,
DateTime? fromOrderDate = null, DateTime? toOrderDate = null,
IEnumerable<string> shippingCountries = null)
{
var query = new Query(GetEmbeddedResource("CategorySales.sql"));
query.SetCondition("categoryIds", categoryIds);
query.SetConditionRange("shippingDates", fromShippingDate, toShippingDate);
query.SetConditionRange("orderDates", fromOrderDate, toOrderDate);
query.SetCondition("shippingCountries", shippingCountries);
return connection.Query<CategorySale>(query.GetSql(), query.SqlParameters);
}
But where's the SQL, what's in this CategorySales.sql? Now here's the nice part, you can safely store the SQL somewhere else and it may have multiple WHERE clauses, multiple ORDER BY's and any number of sub-queries - all of this is natively supported by SqlBinder's templates, being so composable there's almost never a reason to store templates inside your method unless they're one-liners and very small.
There are multiple possible SQL scripts which will all work with the above method if we put them in CategorySales.sql.
For example this script with shortcut aliases and an optional sub-query:
SELECT
CAT.CategoryID,
CAT.CategoryName,
SUM(CCUR(OD.UnitPrice * OD.Quantity * (1 - OD.Discount) / 100) * 100) AS TotalSales
FROM ((Categories AS CAT
INNER JOIN Products AS PRD ON PRD.CategoryID = CAT.CategoryID)
INNER JOIN OrderDetails AS OD ON OD.ProductID = PRD.ProductID)
{WHERE
{OD.OrderID IN (SELECT OrderID FROM Orders AS ORD WHERE
{ORD.ShippedDate :shippingDates}
{ORD.OrderDate :orderDates}
{ORD.ShipCountry :shippingCountries})}
{CAT.CategoryID :categoryIds}}
GROUP BY
CAT.CategoryID, CAT.CategoryName
What's this optional sub-query? Well, since our OD.OrderID IN condition is enclosed within { } braces it means that it won't be used if it's not needed - in other words, if it's not needed then output SQL won't contain it along with its sub-query SELECT OrderID FROM Orders. Again, the whole part enclosed in { } would be removed if its conditions aren't used, specifically if none of the :shippingDates, :orderDates or :shippingCountries are used.
The :categoryIds condition is separate from this and belongs to the parent query, SqlBinder will connect it with the above condition automatically (if it's used) with an AND operand.
The next script uses different aliases and would work just the same:
SELECT
Categories.CategoryID,
Categories.CategoryName,
SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity *
(1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories
INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
{WHERE
{OrderDetails.OrderID IN (SELECT OrderID FROM Orders WHERE
{Orders.ShippedDate :shippingDates}
{Orders.OrderDate :orderDates}
{Orders.ShipCountry :shippingCountries})}
{Categories.CategoryID :categoryIds}}
GROUP BY
Categories.CategoryID, Categories.CategoryName
It's the same thing except it uses different aliases - please note that you don't need to modify your GetCategorySales method for this template to work, it'll work as long as the parameter names are the same.
Next template uses a completely different join and has no sub-queries, it may be a little less optimal but it'll work just the same:
SELECT
Categories.CategoryID,
Categories.CategoryName,
SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity *
(1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM (((Categories
INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
INNER JOIN Orders ON Orders.OrderID = OrderDetails.OrderID)
{WHERE
{Orders.ShippedDate :shippingDates}
{Orders.OrderDate :orderDates}
{Orders.ShipCountry :shippingCountries}
{Categories.CategoryID :categoryIds}}
GROUP BY
Categories.CategoryID, Categories.CategoryName
Or if you want something totally different, here's another template which has two WHERE clauses, is using a different syntax to join and has no GROUP BY - again, it works out of the box and would produce the same data:
SELECT
Categories.CategoryID,
Categories.CategoryName,
(SELECT SUM(CCUR(UnitPrice * Quantity * (1 - Discount) / 100) * 100)
FROM OrderDetails WHERE ProductID IN
(SELECT ProductID FROM Products WHERE Products.CategoryID = Categories.CategoryID)
{AND OrderID IN (SELECT OrderID FROM Orders WHERE
{Orders.ShippedDate :shippingDates}
{Orders.OrderDate :orderDates}
{Orders.ShipCountry :shippingCountries})}) AS TotalSales
FROM Categories {WHERE {Categories.CategoryID :categoryIds}}
Any one of aforementioned scripts may be put in the CategorySales.sql file and used without modifying the C# code. With SqlBinder your SQL scripts can be truly separate from everything else.
What SqlBinder does is it binds SqlBinder.Condition objects to its template scripts returning a valid SQL which you can then pass to your ORM.
By the way, if you're unfamiliar with Dapper, you may be interested in this excellent 'A Look at Dapper.NET' article. It is an ORM solution which also provides a relatively basic assistance in passing bind variables to the SQL.
Some Tutorials
Tutorials can be tested via ConsoleTutorial.sln solution available in the source. The demo database (Northwind Traders.mdb) is also there.
Now let's jump onto the tutorials so you can better understand what this thing is all about.
Tutorial 1: Querying Employees
Let's connect to Northwind demo database:
var connection = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind Traders.mdb");
And then write a simple OleDB SQL query which will retrieve the list of employees.
var query = new DbQuery(connection, @"SELECT * FROM Employees {WHERE EmployeeID :employeeId}");
As you can see, this is not typical SQL, there is some formatting syntax in it which is later processed by the SqlBinder. It's an SQL template which will be used to create the actual SQL.
We can in fact create a command out of this template right now:
IDbCommand cmd = query.CreateCommand();
Console.WriteLine(cmd.CommandText);
Output:
SELECT * FROM Employees
Notice how the initial SQL enclosed in the {...} tags is not present in the output SQL.
Now let's single out an employee by his ID:
query.SetCondition("employeeId", 1);
cmd = query.CreateCommand();
Console.WriteLine(cmd.CommandText);
This is the output:
SELECT * FROM Employees WHERE EmployeeID = :pemployeeId_1
We're using the same query to create two entirely different commands with different SQL. This time, the {WHERE EmployeeID :employeeId} part wasn't eliminated.
Let's go further and retrieve employees by IDs 1 and 2. Again, we use the same query but different parameters are supplied to the crucial SetCondition method.
query.SetCondition("employeeId", new[] { 1, 2 });
cmd = query.CreateCommand();
Console.WriteLine(cmd.CommandText);
Output:
SELECT * FROM Employees WHERE EmployeeID IN (:pemployeeId_1, :pemployeeId_2)
So what happened? Let's first go back to our SQL template:
SELECT * FROM Employees {WHERE EmployeeID :employeeId}
In the first test, the query object was not provided any conditions, so, it removed all the magical syntax that begins with { and ends with } as it served no purpose.
In the second test, we called SetCondition("employeeId", 1); so now the magical syntax comes into play.
So, this template:
... {WHERE EmployeeID :employeeId} ...
Plus this method:
SetCondition("employeeId", 1);
Produced this SQL:
... WHERE EmployeeID = :pemployeeId_1 ...
The :employeeId placeholder was simply replaced by = :pemployeeId_1. SqlBinder also automatically takes care of the command parameters (bind variables) that will be passed to IDbCommand.
In the third test, we called SetCondition("employeeId", new[] { 1, 2 }); which means we would like two employees this time.
This caused the SqlBinder query template:
... {WHERE EmployeeID :employeeId} ...
To be transformed into this SQL:
... WHERE EmployeeID IN (:pemployeeId_1, :pemployeeId_2) ...
There are great many things into which :employeeId can be transformed but for now, we'll just cover the basic concepts.
Tutorial 2: Query Yet Some More Employees
Let's do a different query this time:
SELECT * FROM Employees {WHERE {City :city} {HireDate :hireDate} {YEAR(HireDate) :hireDateYear}}
This time, we have nested scopes {...{...}...}. First and foremost, note that this syntax can be put anywhere in the SQL and that the WHERE clause means nothing to SqlBinder, it's just plain text that will be removed if its parent scope is removed.
Remember: The scope is removed only if all its child scopes are removed or its child placeholder (i.e., :param, @param or ?param) is removed which in turn is removed if no matching condition was found for it.
For example, if we don't pass any conditions at all, all the magical stuff is removed and you end up with:
SELECT * FROM Employees
But if we do pass some condition, for example, let’s try and get employees hired in 1993:
query.SetCondition("hireDateYear", 1993);
This will produce the following SQL:
SELECT * FROM Employees WHERE YEAR(HireDate) = :phireDateYear_1
By the way, don't worry about command parameter values, they are already passed to the command.
As you can see, the scopes {City :city} and {HireDate :hireDate} were eliminated as SqlBinder did not find any matching conditions for them.
Now let's try and get employees hired after July 1993
query.Conditions.Clear();
query.SetCondition("hireDate", from: new DateTime(1993, 6, 1));
This time, we're clearing the conditions collection as we don't want hireDateYear, we just want hireDate right now - if you take a look at the SQL template again, you'll see that they are different placeholders.
The resulting SQL will be:
SELECT * FROM Employees WHERE HireDate >= :phireDate_1
How about employees from London that were hired between 1993 and 1994?
query.Conditions.Clear();
query.SetCondition("hireDateYear", 1993, 1994);
query.SetCondition("city", "London");
Now we have two conditions that will be automatically connected with an AND operator in the output SQL. All consecutive (i.e., separated by white-space) scopes will automatically be connected with an operator (e.g. AND, OR).
The resulting SQL:
SELECT * FROM Employees WHERE City = :pcity_1 AND YEAR(HireDate) _
BETWEEN :phireDateYear_1 AND :phireDateYear_2
Neat!
Demo App
This library comes with a very nice, interactive Demo App developed in WPF which serves as a more complex example of the SqlBinder capabilities. It's still actually quite basic (it's just a MDB after all) but offers a deeper insight into the core features and serves as a real-world example.
The demo app serves as an example of a real world usage pattern. It stores its SqlBinder queries in .sql files which are in fact embedded resources compiled into the binary. Each screen in the app is backed by its own .sql script.
By looking at these files, even somebody who hasn't had contact with SqlBinder would grasp what kind of data it is supposed to be querying - it only requires understanding of SQL syntax. The queries are concise, readable, easy to extend and modify. Each of the complex search screens in the app are defined by a single corresponding SqlBinder template - there's no string concatenation or complex Linq/Lambda C# code generating the SQL in the background. Also note that SqlBinder is very fast, the template is only parsed once and then cached.
Lets go through the demo screens to see what's behind each of them.
Products Screen
As you can see, one can filter by one or more categories or suppliers, by product name or unit price (greater than, less than, equal or between), by discontinuation status (did I write that right?) or by whether the price is greater than average.
The user can filter by any or all these filters. Now, here's the single SqlBinder script behind this screen and all its options:
SELECT P.*,
(SELECT CategoryName FROM Categories WHERE CategoryID = P.CategoryID) AS CategoryName,
(SELECT CompanyName FROM Suppliers WHERE SupplierID = P.SupplierID) AS SupplierCompany
FROM Products P
{WHERE
{ProductID :productId}
{ProductName :productName}
{SupplierID :supplierIds}
{CategoryID :categoryIds}
{UnitPrice :unitPrice}
{UnitPrice :priceGreaterThanAvg}
{Discontinued :isDiscontinued}}
So all of the options above and just that one SQL? Yes.
And here's the C# method behind this screen:
public IEnumerable<Product> GetProducts(decimal? productId = null,
string productName = null,
int[] supplierIds = null,
int[] categoryIds = null,
decimal? unitPriceFrom = null,
decimal? unitPriceTo = null,
bool? isDiscontinued = null,
bool priceGreaterThanAvg = false)
{
var query = new DbQuery(_connection, GetSqlBinderScript("Products.sql"));
if (productId != null)
query.SetCondition("productId", productId);
else
{
query.SetCondition("productName", productName, StringOperator.Contains);
query.SetCondition("supplierIds", supplierIds);
query.SetCondition("categoryIds", categoryIds);
query.SetConditionRange("unitPrice", unitPriceFrom, unitPriceTo);
query.SetCondition("isDiscontinued", isDiscontinued, ignoreIfNull: true);
if (priceGreaterThanAvg)
query.DefineVariable("priceGreaterThanAvg", "> (SELECT AVG(UnitPrice) From Products)");
}
using (var r = query.CreateCommand().ExecuteReader())
while (r.Read())
yield return OledbOrm.CreateProduct(r);
}
I am manually feeding the POCOs here via very crude ORM (i.e OledbOrm.CreateProduct(r)) but you can use just about any ORM you want for that task, I just didn't want extra dependencies for readability. Notice how straightforward all this is? You're not building anything here, you're just using a template and applying conditions to it. SqlBinder takes care of the rest.
Download the source, fire up the demo and see what kind of SQL it generates as you fiddle with the filter options.
Orders Screen
This screen is even more complex. You can filter by customers, products, employees and shippers. Then, you have a variety of dates you can choose from, freight costs, shipping country and finally the shipping city.
Here's the SqlBinder query used for this screen:
SELECT O.*,
(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
(SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
{WHERE
{OrderID :orderId}
{CustomerID :customerIds}
{EmployeeID :employeeIds}
{ShipVia :shipperIds}
{OrderDate :orderDate}
{RequiredDate :reqDate}
{ShippedDate :shipDate}
{Freight :freight}
{ShipCity :shipCity}
{ShipCountry :shipCountry}
{OrderID IN (SELECT OrderID FROM OrderDetails WHERE {ProductID :productIds})}}
As you can see, it's only a little bit more complex than the previous screen but again, anyone with even the basic grasping of SQL can understand what this does. There's really no sharp learning curve here.
Let's take a look at some of the SQL queries above SqlBinder script can generate.
No Filter Applied
SELECT O.*,
(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
(SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
A Single Product Picked from the Products Filter Tab
SELECT O.*,
(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
(SELECT FirstName + ' ' + LastName FROM Employees _
WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID = :pproductIds_1)
Same as Above, Plus Two Shippers Picked from the Shippers Tab
SELECT O.*,
(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
(SELECT FirstName + ' ' + LastName FROM Employees _
WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
ShipVia IN (:pshipperIds_1, :pshipperIds_2)
AND OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID = :pproductIds_1)
Multiple Products Picked and a Shipping Date (Only 'to' Value Specified)
SELECT O.*,
(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
(SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = O.EmployeeID) _
AS EmployeeName,
(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
ShippedDate <= :pshipDate_1
AND OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID IN (:pproductIds_1, :pproductIds_2))
Just Shipping Date, Specified Both from and to Values
SELECT O.*,
(SELECT ContactName FROM Customers WHERE CustomerID = O.CustomerID) AS CustomerName,
(SELECT FirstName + ' ' + LastName FROM Employees _
WHERE EmployeeID = O.EmployeeID) AS EmployeeName,
(SELECT CompanyName FROM Shippers WHERE ShipperID = O.ShipVia) AS ShippedVia
FROM Orders O
WHERE
ShippedDate BETWEEN :pshipDate_1 AND :pshipDate_2
And So On...
We'll stop here - there's a wide variety of what SqlBinder may generate.
Here's the C# method used for this screen:
public IEnumerable<Order> GetOrders(int? orderId = null,
int[] productIds = null,
string[] customerIds = null,
int[] employeeIds = null,
int[] shipperIds = null,
DateTime? orderDateFrom = null, DateTime? orderDateTo = null,
DateTime? reqDateFrom = null, DateTime? reqDateTo = null,
DateTime? shipDateFrom = null, DateTime? shipDateTo = null,
decimal? freightFrom = null, decimal? freightTo = null,
string shipCity = null,
string shipCountry = null)
{
var query = new DbQuery(_connection, GetSqlBinderScript("Orders.sql"));
if (orderId.HasValue)
query.SetCondition("orderId", orderId);
else
{
query.SetCondition("productIds", productIds);
query.SetCondition("customerIds", customerIds);
query.SetCondition("employeeIds", employeeIds);
query.SetCondition("shipperIds", shipperIds);
query.SetConditionRange("freight", freightFrom, freightTo);
query.SetConditionRange("orderDate", orderDateFrom, orderDateTo);
query.SetConditionRange("reqDate", reqDateFrom, reqDateTo);
query.SetConditionRange("shipDate", shipDateFrom, shipDateTo);
query.SetCondition("shipCity", shipCity, ignoreIfNull: true);
query.SetCondition("shipCountry", shipCountry, ignoreIfNull: true);
}
using (var r = query.CreateCommand().ExecuteReader())
while (r.Read())
yield return OledbOrm.CreateOrder(r);
}
Notice how you may specify orderId straight away and if you do so, the method will only pass this to the SqlBinder's query. Usually, you'd write a separate method for this with separate SQL, but with SqlBinder you don't have to, you can use the same template and the existence of other options add no performance overhead.
Category Sales Screen
The Category Sales screen looks simple and it is but underneath something interesting is going on. Take a look at its SqlBinder script:
SELECT
Categories.CategoryID,
Categories.CategoryName,
SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories
INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
{WHERE
{OrderDetails.OrderID IN (SELECT OrderID FROM Orders WHERE
{Orders.ShippedDate :shippingDates}
{Orders.OrderDate :orderDates}
{Orders.ShipCountry :shipCountry})}
{Categories.CategoryID :categoryIds}}
GROUP BY
Categories.CategoryID, Categories.CategoryName
As you can see, by examining the structure of curly braces, it becomes apparent that if :shippingDates, :orderDates and :shipCountry are all omitted, SqlBinder will remove a whole subquery. This is another trick SqlBinder has - you may write complex subqueries that will impose a significant performance penalty to the server but you may instruct SqlBinder to remove these queries if they are redundant - just surround them with { ... }. Hence, we have two WHERE clauses here, each with their own conditions.
In this example, to filter by shipping dates, we have to query Orders table. We're not displaying these dates anywhere, we just need to filter by them so we're either going to perform some kind of JOIN operation or perform a subquery. Often, developers pick the first option - join this table to have extra column(s) to filter by. With SqlBinder, you don't have to join all three tables at once, if one wants to filter by one or more columns from another table, a subquery will remain, if not, it'll get removed entirely - this is possible due to SqlBinder's scopes defined by curly braces.
So, if you don't specify any of the filtering conditions from Orders table, here's the SQL:
SELECT
Categories.CategoryID,
Categories.CategoryName,
SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories
INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
GROUP BY
Categories.CategoryID, Categories.CategoryName
But if you do, SqlBinder will include the subquery you provided in its template script:
SELECT
Categories.CategoryID,
Categories.CategoryName,
SUM(CCUR(OrderDetails.UnitPrice * OrderDetails.Quantity * (1 - OrderDetails.Discount) / 100) * 100) AS TotalSales
FROM ((Categories
INNER JOIN Products ON Products.CategoryID = Categories.CategoryID)
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID)
WHERE
OrderDetails.OrderID IN (SELECT OrderID FROM Orders WHERE
Orders.ShippedDate BETWEEN :pshippingDates_1 AND :pshippingDates_2)
GROUP BY
Categories.CategoryID, Categories.CategoryName
Neat!
And here's the C# code behind this screen:
public IEnumerable<CategorySale> GetCategorySales(int[] categoryIds = null, DateTime? fromDate = null, DateTime? toDate = null)
{
var query = new DbQuery(_connection, GetSqlBinderScript("CategorySales.sql"));
query.SetCondition("categoryIds", categoryIds);
query.SetConditionRange("shippingDates", fromDate, toDate);
using (var r = query.CreateCommand().ExecuteReader())
while (r.Read())
yield return OledbOrm.CreateCategorySale(r);
}
The Performance
SqlBinder is very fast but I have nothing to compare it with. Instead, you can combine it with micro ORM solutions like Dapper and measure the potential overhead. I took Dapper for reference as it's the fastest micro-ORM that I currently know of.
Consider the following tables, one tested on LocalDB and another one on Access. On the left column of each table, you will see performance of Dapper alone and in the right column, you will see Dapper doing the exact same thing but with added overhead of SqlBinder doing its magic.
| LocalDB (SQL Sever Express) | OleDb (Access) |
Dapper +SqlBinder
---------------------
52.88 53.46
57.31 59.55
56.22 68.07
55.97 56.16
66.52 55.59
54.82 52.96
50.98 61.97
59.06 57.53
50.38 53.97
AVG 56 AVG 58
^ Dapper = Just Dapper.
^ +SqlBinder = Dapper with SqlBinder.
|
Dapper +SqlBinder
---------------------
335.42 336.38
317.99 318.89
342.56 324.85
317.20 320.84
327.91 324.56
320.29 326.86
334.42 338.73
344.43 326.33
315.32 322.48
AVG 328 AVG 327
^ Dapper = Just Dapper.
^ +SqlBinder = Dapper with SqlBinder.
|
As you can observe, on SqlServer, we've had an additional overhead of 2ms which is the time it took SqlBinder to formulate a query based on different criteria. On the OleDb Access test, this difference was so insignificant, it was lost entirely in deviations (most likely in interaction with the FS/DB).
Each row in the test results was a result of 500 executions of the following queries:
SELECT * FROM POSTS WHERE ID IN @id
And:
SELECT * FROM POSTS {WHERE {ID @id}}
Where the latter was used in Dapper+SqlBinder combination.
It is important to note that SqlBinder has the ability to re-use compiled templates as it completely separates the parsing and templating concerns. You may create a SqlBinder query template once and then build all the subsequent SQL queries from the same pre-parsed template. One of the key functionalities of SqlBinder is that it doesn't parse or generate the whole SQL every time. Also, it relies on hand coded parser which is well optimized.
Simple performance tests are available in the source code where you can benchmark SqlBinder on your own.
How It Works?
The process of turning an SqlBinder template into a ADO.NET command is essentially done by four publicly exposed classes as shown in the below diagram:
I'll try and explain the workflow in greater detail:
Parser receives the SqlBinder script, tokenizes it and returns a parse tree. This is important due to SQL literals of various kinds which may interfere with your {...} scopes and bind parameters. This parser fully supports Oracle Alternative Quoting, PostgreSQL $$literals$$, MySql literals and others.SqlBinderProcessor (let's call it Processor for short) takes parse tree as input and enumerates it, validating tokens and firing events for parameter placeholders requesting SQL to be put in their place - parameters that don't get any feedback are removed along with their parent scopes. Thus, scopes that don't contain any valid parameter placeholders in them or in any of their child scopes will be removed. As it does this, the Processor class builds an output SQL string.Query is the central class which combines the features of Parser and Processor into one functionality. It takes an SqlBinder script and a list of Condition objects as input, it gets the parse tree from the Parser, optionally caching its results and then sending it to the Processor class while subscribed to its events. Processor fires an event for each parameter placeholder in the parse tree, Query class subscribes to it and uses the previously provided list of Condition objects to generate individual SQL for these placeholders (if they are matched), e.g. ':employeeId' becomes '= 123'. Based on this feedback from the Query class, Processor class will return a complete SQL.DbQuery class simply overrides the Query class and aside from providing base class functionality (an SQL and a KeyValue pair of bind variables), it creates a valid IDbCommand instance based on the provided IDbConnection. You can override the Query class yourself for any other custom implementation - this one is just an out of the box ADO.NET implementation. All other classes are DB-agnostic in a sense that they don't have anything to do with System.Data.*.
Why's There A Parser?
At first, SqlBinder relied on very fast and reliable .NET's compiled recursive regex but various flavors of SQL literals, comments, escape codes and whatnot proved too much for regex and it started to look ugly (as it often does when you go overoptimistic with Regex). So, I wrote a parser for it and now it's twice as fast.
Note however that this isn't SQL parser, it's SqlBinder parser. The only and only aspects of SQL that it looks for are string literals and comments - when you inject some magical formatting syntax into someone else's syntax (SQL in this example which may be Oracle's, MySql's, PostgreSql's, etc.), you want to take special care to respect its rules when it comes to comments and literals as you really don't want to alter those.
If you take a look at the code, you'll notice that the parser isn't especially object oriented and this is intentional. Avoiding StringBuilder, string methods, object instantiation and destruction, etc. are all intentional. Special care was taken not to invoke GC which is why there's bits of unsafe code as well. Even still, I was able to separate each token into its own corresponding class so adding any new tokens was very easy. I am familiar with parser generators such as Gold or ANTLR but I determined that it would be an overkill to use them - they would make sense if I was parsing the entire SQL syntax. There was also not much point in separating lexer from parser as, again, I wasn't dealing with great many tokens here - just comments, literals and the extremely simple syntax of SqlBinder.
Additional Perks
As you experiment with SqlBinder, you will notice a large number of overloads that the SetCondition method has. Many of these overloads are just shortcuts to wrap around a number of out-of-the-box ConditionValue implementations such as: BoolValue, NumberValue, DateValue and StringValue. The abstract class ConditionValue provides you with means to inject any kind of SQL into SqlBinder parameter placeholders and optionally back it up with bind variables.
For example, the class NumberValue provides various functionalities, albeit sounding very simple. It can take a number as input, two numbers, a list of numbers or null. It also validates input, making sure no junk goes into the SQL. For instance:
BETWEEN 1 AND 1 can't happen, it would output = 1 instead.NOT BETWEEN 1 AND 1 is handled similarly.IN (1) or NOT IN (1) can't happen, it would output = 1 or <> 1.IN (1, 1, 1) likewise, can't happen.- Can automatically handle
nulls, i.e., passing a null to the condition would cause IS NULL or IS NOT NULL to be inserted instead of passing the variable. Likewise, IN (NULL) can't happen. - Provides means of enforcing policies such as choosing between
<> X and != X.
This all goes a long way in helping alleviate the headaches of a DBA who would end up tuning your software's generated SQL. On a higher level, you have the means to enforce certain rules, policies and implement automation to technically prevent yourself or your developers from creating potentially harmful SQLs especially when input comes from the end user.
Here's a code snippet taken from the NumberValue class so you can get a better picture of how it works:
protected override string OnGetSql(int sqlOperator)
{
switch (sqlOperator)
{
case (int)Operator.Is:
return _values.Length == 0 ? "IS NULL" : ValidateParams("= {0}", 1);
case (int)Operator.IsNot:
return _values.Length == 0 ? "IS NOT NULL" : ValidateParams("<> {0}", 1);
case (int)Operator.IsLessThan: return ValidateParams("< {0}", 1);
case (int)Operator.IsLessThanOrEqualTo: return ValidateParams("<= {0}", 1);
case (int)Operator.IsGreaterThan: return ValidateParams("> {0}", 1);
case (int)Operator.IsGreaterThanOrEqualTo: return ValidateParams(">= {0}", 1);
case (int)Operator.IsBetween:
switch (_values.Length)
{
case 2: return ValidateParams("BETWEEN {0} AND {1}", 2);
case 1: return ValidateParams("= {0}", 1);
default: throw new InvalidOperationException
(Exceptions.PlaceholdersAndActualParamsDontMatch);
}
case (int)Operator.IsNotBetween:
switch (_values.Length)
{
case 2: return ValidateParams("NOT BETWEEN {0} AND {1}", 2);
case 1: return ValidateParams("<> {0}", 1);
default: throw new InvalidOperationException
(Exceptions.PlaceholdersAndActualParamsDontMatch);
}
case (int)Operator.IsAnyOf:
if (!IsValueList())
return ValidateParams("= {0}", 1);
return ValidateParams("IN ({0})", 1, true);
case (int)Operator.IsNotAnyOf:
if (!IsValueList())
return ValidateParams("<> {0}", 1);
return ValidateParams("NOT IN ({0})", 1, true);
default: throw new InvalidConditionException
(this, (Operator)sqlOperator, Exceptions.IllegalComboOfValueAndOperator);
}
}
Syntax Specification
I wrote a 'spec' for the syntax used by SqlBinder and put it on its GitHub page - it offers more options than described in this article. My current plan is to keep all the samples here and the exact spec on its GitHub page but we'll see how that goes or if anyone's even interested in any of this.
Points of Interest
This library can help anyone using the SQL whether it is in another library, an app, a website or a service. It doesn't matter if you're using Dapper, PetaPoco or something else to help you with SQL/ORM, SqlBinder is quite small, very fast but brings a whole new level to your SQL composition.
I originally wrote it entirely for myself, to help me do things in a better, albeit unconventional fashion. I have to say I really love it and wanted to publish it for quite a long time but had no time.
I hope you like it too, and if you do, rate the article and feel free to suggest features, test, report or fix bugs!
Downloading / Installing
You may download the source and/or compiled binaries via links at the top of the article which are as up to date as the article is - this is convenient as you have the code that matches what you're reading. The source code contains all the examples.
You may install it from within your Visual Studio via NuGet, i.e.:
Install-Package SqlBinder -Version 0.2.0
Article History
- June 13th, 2018 - A better introduction and Quick Demonstration added. Tried to better highlight the notion that
SqlBinder can be used with micro ORMs. Code/Binaries updated to v0.2.0 and updated the article along with associated code to match new version. - June 5th, 2018 - Initial version
