SelectQueryBuilder: Building complex and flexible SQL queries/commands from C#

• Download demo application - 13.7 Kb
• Download demo + source - 24.5 Kb

Introduction

Admit it. We have all done it. And we all thought it was the best -and only- way of doing it. When we were building on-the-fly SQL statements to fire against our databases, we were all building up huge strings containing all the applicable WHERE clauses. Endlessly attaching more and more statements to our SQL strings, bugs and the risk of SQL Injection attacks were very likely to happen. And hell, it even made our code look ugly and unmanageable!

This had to stop. But how? Some people would say to use Stored Procedures. But that doesn't really fix the problem. You would still have to dynamically build up your SQL statement, and you would only be moving your problem to the database side, while even keeping the SQL injection hazard. Besides this 'solution', there are probably tons of other options you could consider, but they all leave you with the same basic challenge: doing it nice and safe.

While I was building C# templates for my online DAL (Data Access Layer) generation tool www.code-engine.com, I wanted to provide an easy way to custom-query the database. Unlike with previous templates I had developed, I didn't want to use "string queries" to query the DB anymore. I was more than fed-up with this messy way of getting my data. I wanted something clean. Something intuitive. Something flexible. Something I could simply tell to SELECT data from some table, JOIN with some others, use a number of WHERE clauses, GROUP BY some column, and return me only the TOP x records.

I started developing the SelectQueryBuilder class with exactly this functionality in mind. It exposes a number of properties and methods that you can call to easily put together a SELECT statement. And once the BuildQuery() or BuildCommand() method is called, it provides you with either a good-old "string query", or a full-blown DbCommand object using command parameters to query the data.

Using the code

Let me start off by showing you the old-school way of creating a SELECT statement. I will then give you a quick demonstration of how you can do the same with the SelectQueryBuilder class.

The old-school way of doing it

The code below illustrates the 'old-school' way of building up a SELECT statement, using some kind of variable to keep track of which concatenation operator should be used (WHERE, or AND), while exposing your database to possible SQL injection attacks.

string statement = "SELECT TOP " + maxRecords + " * FROM Customers ";
string whereConcatenator = "WHERE ";

if (companyNameTextBox.Text.Length > 0) 
{
    statement += whereConcatenator;
    statement += "CompanyName like '" + companyNameTextBox.Text + "%' ";
    whereConcatenator = "AND ";
}
if (cityTextBox.Text.Length > 0)
{
    statement += whereConcatenator;
    statement += "City like '" + cityTextBox.Text + "%' ";
    whereConcatenator = "AND ";
}
if (countryComboBox.SelectedItem != null)
{
    statement += whereConcatenator;
    statement += "Country = '" + countryComboBox.SelectedItem + "' ";
    whereConcatenator = "AND ";
}

I'm pretty sure the code above seems familiar to you! If it doesn't, you have either been living on Mars for the past 10 years, or you haven't ever coded a database-driven application with search functionality. Let me tell you this though: this way of querying your database is not acceptable anymore! It is ugly and unsafe.

The SelectQueryBuilder way of doing it

The same query can be built using the SelectQueryBuilder class.

SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Customers");
query.SelectAllColumns();
query.TopRecords = maxRecords;

if (companyNameTextBox.Text.Length > 0)
    query.AddWhere("CompanyName", Comparison.Like, 
                    companyNameTextBox.Text + "%");

if (cityTextBox.Text.Length > 0)
    query.AddWhere("City", Comparison.Like, 
                   cityTextBox.Text + "%");

if (countryComboBox.SelectedItem != null)
    query.AddWhere("Country", Comparison.Equals, 
                  countryComboBox.SelectedItem);

string statement = query.BuildQuery();

// or, have a DbCommand object built

// for even more safety against SQL Injection attacks:

query.SetDbProviderFactory(
      DbProviderFactories.GetFactory(
      "System.Data.SqlClient")); 
DbCommand command = query.BuildCommand();

As you can see, this approach is much more intuitive than bluntly concatenating strings together. Also, considering the SQL Injection hazard of the first example, the SELECT query generated by the SelectQueryBuilder will be completely safe, no matter what the contents of the used TextBoxes are. It's really that simple!

Using SQL functions

If you want to use SQL functions in your queries, you can use the SqlLiteral class to wrap the function calls in. The best way to explain what this class does exactly is to show it to you with a little code sample:

SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");
query.AddWhere("OrderDate", Comparison.LessOrEquals, 
                       new SqlLiteral("getDate()"));

If we wouldn't have wrapped the getDate() function call in the SqlLiteral class, the built query would have produced WHERE OrderDate <= 'getDate()'. Of course, we want this function to appear in the statement without the single quotes around it. This is where the SqlLiteral class comes in handy: it copies the given string directly to the output, without formatting it as a string. The output will now be WHERE OrderDate <= getDate() instead!

Using Joins in your queries

To create JOINs to other tables, you can use the AddJoin method. The following code shows you how to create an INNER JOIN from table Orders to Customers:

SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");

query.AddJoin(JoinType.InnerJoin, 
              "Customers", "CustomerID", 
              Comparison.Equals, 
              "Orders", "CustomerID");

query.AddWhere("Customers.City", 
      Comparison.Equals, "London");

This code selects all orders from customers that are situated in London. Once the BuildQuery method is called, it will produce the following SQL statement:

SELECT Orders.* 
FROM Orders 
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID  
WHERE (Customers.City = 'London')

Note that, by default, the query that is built only selects * from the selected table (Orders.*, in this example). If you also want to select the columns from any joined table, you have to select them explicitly. You can do this by calling query.SelectColumns("Orders.*", "Customers.*");.

Building Count queries

If you want to execute a Count query on your database, you can use the SelectCount method as shown below:

query.SelectCount();

In more complex Count queries, you might want to use a GROUP BY statement. Take a look at the sample below, it demonstrates how to use the GroupBy and AddHaving methods.

SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectColumns("count(*) AS Count", "ShipCity");
query.SelectFromTable("Orders");
query.GroupBy("ShipCity");
query.AddHaving("ShipCity", Comparison.NotEquals, "Amsterdam");
query.AddOrderBy("count(*)", Sorting.Descending);

The code above selects the number of orders per city, sorts by the number of orders, and leaves out orders shipped to Amsterdam. The output of the BuildQuery method will now be:

SELECT count(*) AS Count, ShipCity 
FROM Orders 
GROUP BY ShipCity  
HAVING  (ShipCity <> 'Amsterdam')    
ORDER BY count(*) DESC

Complex WHERE statements

Have you ever gazed at the built-in query builders of Microsoft Access or SQL Server and wondered if you could build queries in the same way, containing multiple levels of ANDs and ORs, without having to bother about the right location for the () symbols? From code? Yes? So have I!

Well, guess what. You can do it with the SelectQueryBuilder class! You can add multiple levels of WHERE statements to your query. By default, all calls to query.AddWhere are placed on the first level of the query. You can compare this first level to the first 'Criteria' column in the SQL Server query builder; levels 2,3,4 and so on are represented by the 'Or...' columns in the grid.

Take a look at the following screenshot of the SQL Server query builder, in which I have quickly put together a simple -bogus- SELECT statement:

As you can see, I created a query that selects all orders from the customer 'VINET', which were placed before 1-1-2005, and orders from customer 'TOMSP', which were placed before 30-6-2004, or after 1-1-2006. (Please don't ask why anyone would want a query like this specific one, but hey.. it's just an example.) This query can be built as follows:

SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");

// Add 'Criteria' column to level 1

query.AddWhere("CustomerID", Comparison.Equals, 
                                   "VINET", 1);
query.AddWhere("OrderDate", Comparison.LessThan, 
                     new DateTime(2005,1,1), 1);

// Add first 'Or...' column to level 2

query.AddWhere("CustomerID", Comparison.Equals, "TOMSP", 2);
query.AddWhere("OrderDate", Comparison.LessThan, 
                    new DateTime(2004,6,30), 2);

// Add second 'Or...' column to level 3

query.AddWhere("CustomerID", Comparison.Equals, 
                                   "TOMSP", 3);
query.AddWhere("OrderDate", Comparison.GreaterThan, 
                        new DateTime(2006,1,1), 3);

When calling BuildQuery, all defined levels will be ORed together, and you end up with (almost) the same query that SQL Server would have prepared for you.

And to make things even more complicated, if you look at the created statement closely, you would probably say: "I would have put the two latter statements together in one statement, using an OR between the two dates". And you would have been right. In the SQL Server query builder, this would look like this:

It is possible to do this with the SelectQueryBuilder also, by creating 'nested WHERE clauses'. This is how to do it:

SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectFromTable("Orders");

// Add 'Criteria' column to level 1

query.AddWhere("CustomerID", Comparison.Equals, "VINET", 1);
query.AddWhere("OrderDate", Comparison.LessThan, 
                     new DateTime(2005,1,1), 1);

// Add 'Or...' column to level 2

query.AddWhere("CustomerID", 
                Comparison.Equals, "TOMSP", 2);

// Add the date selection clause

WhereClause clause = 
    query.AddWhere("OrderDate", Comparison.LessThan, 
                        new DateTime(2004,6,30), 2);

// Add a nested clause to the captured clause

clause.AddClause(LogicOperator.Or, 
       Comparison.GreaterThan, new DateTime(2006,1,1));

Notice that I capture a WhereClause object, which is returned from the AddWhere call. I then call clause.AddClause to create the nested clause, and choose to OR it to the first clause by specifying LogicOperator.Or. The statement it produces is as follows:

SELECT Orders.* 
FROM Orders  
WHERE  
(
    (CustomerID = 'VINET') 
    AND (OrderDate < '2005/01/01 12:00:00')
)  
OR 
(
    (CustomerID = 'TOMSP') 
    AND (OrderDate < '2004/06/30 12:00:00' OR 
         OrderDate > '2006/01/01 12:00:00')
)

Please note that the dates in this example contain '12:00:00'. This is because I omitted the time in the DateTime constructor. But that's just laziness on my side. If I had used new DateTime(2006,1,1,0,0,0), the date strings would have contained '00:00:00' instead.

Conclusion

I realize that most of you out there won't be using all the functionality in this extensively. But it is all in there, so if you've been waiting for something like this to come along, this is your party! I was having fun writing this article, rediscovering what exactly I had built a couple of months ago. I hope you enjoy using the code just as much as I do!

Like I mentioned in my introduction, the SelectQueryBuilder is part of the CodeEngine Framework. This framework also contains a DeleteQueryBuilder, a UpdateQueryBuilder, and a InsertQueryBuilder. I use these builders in the code that is generated by my C# DAL generator. You can download a copy of the framework DLL at www.code-engine.com. I will publish the source code of the other query builders in time. Meanwhile, if you have any questions, comments, or suggestions, don't hesitate to contact me!