Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 14 Mar 2006
Rate this:
Please Sign up or sign in to vote.
The SelectQueryBuilder class allows you to build complex SQL statements and commands from your code. It also prevents you from possible SQL injection attacks.


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

string statement = query.BuildQuery();

// or, have a DbCommand object built
// for even more safety against SQL Injection attacks:
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.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();

              "Customers", "CustomerID", 
              "Orders", "CustomerID");

      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:


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.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();

// 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();

// 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
                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
       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  
    (CustomerID = 'VINET') 
    AND (OrderDate < '2005/01/01 12:00:00')
    (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.


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 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!


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Ewout Stortenbeker
Web Developer
Netherlands Netherlands
27 years old. Alive and kicking!

Comments and Discussions

GeneralUse DLL in my project Pinmemberchigz_fj3-Aug-14 17:08 
QuestionHow to use LogicOperator.In to build the following query? PinmemberNeel Kamal24-Jul-14 1:23 
QuestionAdd LogicOperator.Or in single Statement in Loop [modified] PinmemberPankajMishra9-Jul-14 22:33 
AnswerRe: Add LogicOperator.Or in single Statement in Loop PinmemberNeel Kamal19-Jul-14 23:51 
GeneralRe: Add LogicOperator.Or in single Statement in Loop PinmemberPankajMishra20-Jul-14 10:11 
QuestionHandling ranges Pinmemberbuzy_f6-Feb-14 1:14 
QuestionHow come you're not using a StringBuilder? PinmemberMember 1032528716-Jan-14 23:10 
QuestionAlias and more complex joins PinmemberMember 1045713011-Dec-13 22:37 
QuestionI hate seeing methods that return "either an string or a DbCommand" [modified] Pinmemberliviu_11-May-13 22:40 
QuestionHow SelectQueryBuilder join itself? Pinmembermiroyuan7-May-13 21:19 
QuestionJoins on more than one column PinmemberMember 20248944-Apr-13 5:22 
I've noticed that the version I have seems to only support joins on one column.
It seemed to be a straightforward change to allow multiple columns to be used.
I moved
Public FromColumn As String
Public ToColumn As String
Public ComparisonOperator As Comparison
into a class JoinColumn, then added a list(of JoinColumn) to JoinClause. I changed the current JoinClause constructor to support the list and added :
Public Sub New(join As JoinType, toTableName As String, fromTableName As String)
And then the change below in the BuildQuery method. Seems to work.

Dim first As Boolean = True
For Each joinColumn As JoinColumn In clause.ColumnList
    If first Then
        joinString += " " + clause.ToTable & " ON "
        joinString += " AND "
    End If
    first = False
    joinString += WhereStatement.CreateComparisonClause(clause.FromTable + "."c + joinColumn.FromColumn, joinColumn.ComparisonOperator, New SqlLiteral(clause.ToTable + "."c + joinColumn.ToColumn))

Bugunneeded quotes PinmemberK. Chikuse6-Mar-13 3:49 
GeneralRe: unneeded quotes PinmemberMember 93630727-Mar-13 10:26 
GeneralVery nice Pinmemberkevininstructor25-Feb-13 4:13 
QuestionTable Aliases in JoinClause's PinmemberFrancisco Freitas24-Jan-13 5:46 
GeneralMy vote of 5 PinmvpKanasz Robert26-Sep-12 4:59 
QuestionUnion or Union All Pinmemberluke10121-Aug-12 13:43 
Questionwanted a VB source for the project. PinmemberMember 83963920-Jun-12 4:36 
AnswerRe: wanted a VB source for the project. PinmemberMember 20248944-Apr-13 6:31 
QuestionJoins PinmemberKunalce4u6-Jun-12 19:31 
QuestionAdding sub query PinmemberMember 419196810-Apr-12 7:17 
GeneralMy vote of 5 Pinmemberimmeraufdemhund28-Mar-12 8:28 
QuestionHow do I use the dll in a VB.NET project? Pinmemberchris82521-Dec-11 2:34 
AnswerRe: How do I use the dll in a VB.NET project? Pinmemberkevininstructor25-Feb-13 4:18 
GeneralRe: How do I use the dll in a VB.NET project? Pinmembermarcogregori17-Mar-13 1:08 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 14 Mar 2006
Article Copyright 2006 by Ewout Stortenbeker
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid