SqlBinder Library – DEVELOPPARADISE
05/06/2018

SqlBinder Library

Introduction

First and foremost, SqlBinder is not yet another ORM solution that would alleviate your ORM-ing headaches. Instead, it deals with the challenge of building an actual SQL query but in a sort of a unique way that I haven’t found anywhere else yet.

Generating the SQL query via string concatenation is still a surprisingly common method despite all the tools that generate it via lambda expressions, Linq or via some other means. What’s common among all these methods is that they must generate the entire SQL. I was personally never a fan of that – for me, it’s just another layer of additional programming to make something which already works now work with the tool. With SqlBinder though, I was able to fully express my database querying desires without being constantly concerned about whether my SQL will work with the X tool or how much time I will need to make it work.

I will go ahead and describe SqlBinder as a SQL-centric templating engine that doesn’t depend on any specific DBMS flavor and thus doesn’t hinder your DB’s full potential.

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 such as NHibernate solved a lot of problems we didn’t have but didn’t entirely solve the one we had. This is where my 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.

All the below examples 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 examples so you can understand what this thing is all about.

Example 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 the passed SQL

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); // Output the passed SQL

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 the passed SQL

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.

Example 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(); // Remove any previous conditions 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.

SqlBinder Library

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.

I will go through the demo screens to show you what’s behind.

Products

SqlBinder Library

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:

-- Products.sql  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  {   if (!string.IsNullOrEmpty(productName))    query.SetCondition("productName", $"%{productName}%", StringOperator.IsLike);    if (supplierIds?.Any() ?? false)    query.SetCondition("supplierIds", supplierIds);    if (categoryIds?.Any() ?? false)    query.SetCondition("categoryIds", categoryIds);    if (unitPriceFrom.HasValue || unitPriceTo.HasValue)    query.SetCondition("unitPrice", unitPriceFrom, unitPriceTo);    if (isDiscontinued.HasValue)    query.SetCondition("isDiscontinued", isDiscontinued.Value);    if (priceGreaterThanAvg)    query.DefineVariable("priceGreaterThanAvg", "> _                         (SELECT AVG(UnitPrice) From Products)");  }   using (var r = query.CreateCommand().ExecuteReader())  {   while (r.Read())   {    yield return new Product    {     ProductId = (int)r["ProductID"],     ProductName = (string)r["ProductName"],     CategoryName = (string)r["CategoryName"],     SupplierCompany = (string)r["SupplierCompany"],     SupplierId = (int)r["SupplierID"],     CategoryId = (int)r["CategoryID"],     QuantityPerUnit = (string)r["QuantityPerUnit"],     UnitPrice = (decimal)r["UnitPrice"],     UnitsInStock = Convert.ToInt32(r["UnitsInStock"] as Int16?),     UnitsOnOrder = Convert.ToInt32(r["UnitsOnOrder"] as Int16?),     Discontinued = (bool)r["Discontinued"],    };   }  } }</product>

I am manually feeding the POCOs here but you can use just about any ORM you want for that task. 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

SqlBinder Library

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:

-- Orders.sql  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     {         if (productIds?.Any() ?? false)             query.SetCondition("productIds", productIds);          if (customerIds?.Any() ?? false)             query.SetCondition("customerIds", customerIds);          if (employeeIds?.Any() ?? false)             query.SetCondition("employeeIds", employeeIds);          if (shipperIds?.Any() ?? false)             query.SetCondition("shipperIds", shipperIds);          if (freightFrom.HasValue || freightTo.HasValue)             query.SetCondition("freight", freightFrom, freightTo);          if (orderDateFrom.HasValue || orderDateTo.HasValue)             query.SetCondition("orderDate", orderDateFrom, orderDateTo);          if (reqDateFrom.HasValue || reqDateTo.HasValue)             query.SetCondition("reqDate", reqDateFrom, reqDateTo);          if (shipDateFrom.HasValue || shipDateTo.HasValue)             query.SetCondition("shipDate", shipDateFrom, shipDateTo);          if (shipCity != null)             query.SetCondition("shipCity", shipCity);          if (shipCountry!= null)             query.SetCondition("shipCountry", shipCountry);     }      using (var r = query.CreateCommand().ExecuteReader())     {         while (r.Read())         {             yield return new Order             {                 OrderId = (int)r["OrderId"],                 CustomerId = (string)r["CustomerId"],                 EmployeeId = (int)r["EmployeeId"],                 OrderDate = r["OrderDate"] as DateTime?,                 RequiredDate = r["RequiredDate"] as DateTime?,                 ShippedDate = r["ShippedDate"] as DateTime?,                 ShipperId = (int)r["ShipVia"],                 Freight = (decimal)r["Freight"],                 CustomerName = (string)r["CustomerName"],                 EmployeeName = (string)r["EmployeeName"],                 ShippedVia = r["ShippedVia"] as string,                 ShipName = r["ShipName"] as string,                 ShipAddress = r["ShipAddress"] as string,                 ShipCity = r["ShipCity"] as string,                 ShipRegion = r["ShipRegion"] as string,                 ShipCountry = r["ShipCountry"] as string,                 ShipPostalCode = r["ShipPostalCode"] as string,             };         }     } }

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

SqlBinder Library

The Category Sales screen looks simple and it is but underneath something interesting is going on. Take a look at its SqlBinder script:

-- CategorySales.sql  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})}) AS TotalSales FROM Categories {WHERE {Categories.CategoryID :categoryIds}}

As you can see, by examining the structure of curly braces, it becomes apparent that if :shippingDates isn’t specified, 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 simply surround them with { ... }.

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, if one wants to filter by one or more columns from another table, a subquery will remain, if not, it’ll get removed entirely.

So, if you don’t specify shipping dates, here’s the SQL:

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)           ) AS TotalSales FROM Categories

But if you do, SqlBinder will include the subquery you provided in its template script:

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 _                           BETWEEN :pshippingDates_1 AND :pshippingDates_2)) AS TotalSales FROM Categories

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"));      if (categoryIds?.Any() ?? false)         query.SetCondition("categoryIds", categoryIds);      if (fromDate.HasValue || toDate.HasValue)         query.SetCondition("shippingDates", fromDate, toDate);      using (var r = query.CreateCommand().ExecuteReader())     {         while (r.Read())         {             yield return new CategorySale             {                 CategoryId = (int)r["CategoryID"],                 CategoryName = (string)r["CategoryName"],                 TotalSales = r["TotalSales"] as decimal? ?? 0             };         }     } }

The Performance

While SqlBinder is very fast, it’s pointless to compare it with other tools as they do different things – I was unable to find anything similar enough. However, you may combine it with micro ORM solutions like Dapper – it wouldn’t make sense to compare the performance differences of SqlBinder and Dapper side by side but one can measure the overhead added by utilizing both at the same time. I took Dapper for reference as it’s the fastest micro-ORM that I currently know of.

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 pretty basic assistance in passing bind variables to the SQL.

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 providing the SQL and command parameter values based on a given template.

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:

SqlBinder Library

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 a library, an app, a website or a service.

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.1.0

Article History

  • June 5th, 2018 – Initial version