Click here to Skip to main content
15,439,117 members
Articles / Programming Languages / SQL
Posted 14 Mar 2006


211 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.74/5 (78 votes)
14 Mar 20067 min read
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:

Image 1

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:

Image 2

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

Written By
Web Developer
Netherlands Netherlands
27 years old. Alive and kicking!

Comments and Discussions

QuestionUpdate , insert query builder Pin
Member 127077521-May-20 23:10
MemberMember 127077521-May-20 23:10 
QuestionLooking for the VB.NET Version Pin
Member 1449202510-Jun-19 3:17
MemberMember 1449202510-Jun-19 3:17 
QuestionAlternative - Sqlkata Pin
Vaso Elias23-Oct-18 4:19
MemberVaso Elias23-Oct-18 4:19 
I found also this in case somebody would be interested in similar solution GitHub - sqlkata/querybuilder: SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql and Firebird[^]
QuestionGet only where statment ? Pin
Wrangly27-Mar-18 22:12
MemberWrangly27-Mar-18 22:12 
QuestionWhy not use Entity Frameworks instead? Pin
Member 1179891211-Oct-17 15:48
MemberMember 1179891211-Oct-17 15:48 
QuestionStudy code for testing Pin
Alastair Fletcher Greener23-Jul-17 5:01
MemberAlastair Fletcher Greener23-Jul-17 5:01 
QuestionSelect Customed column ? Pin
ARIA 58-Nov-16 21:45
MemberARIA 58-Nov-16 21:45 
AnswerRe: Select Customed column ? Pin
ARIA 58-Nov-16 22:54
MemberARIA 58-Nov-16 22:54 
QuestionStartWith,EndWith and contains not Like Pin
ARIA 58-Nov-16 21:19
MemberARIA 58-Nov-16 21:19 
QuestionComplex and WildCharClause support Pin
ARIA 58-Nov-16 20:08
MemberARIA 58-Nov-16 20:08 
PraiseThank you so very much my yute Pin
Member 122158922-Oct-16 14:35
MemberMember 122158922-Oct-16 14:35 
QuestionThank you so very much my yute Pin
Member 122158922-Oct-16 14:35
MemberMember 122158922-Oct-16 14:35 
QuestionBlob Pin
Cool Smith25-Jul-16 7:22
MemberCool Smith25-Jul-16 7:22 
QuestionLicence Pin
User 1249497126-Jun-16 2:36
MemberUser 1249497126-Jun-16 2:36 
AnswerRe: Licence Pin
Vitaliy Fedorchenko18-Jul-16 6:44
MemberVitaliy Fedorchenko18-Jul-16 6:44 
QuestionExcellent! Pin
K Howell1-Jun-16 10:51
MemberK Howell1-Jun-16 10:51 
QuestionHow would I create a nested sub-clause Pin
Member 146314919-May-16 6:17
MemberMember 146314919-May-16 6:17 
GeneralMy vote of 5 Pin
Sam Leatherdale2-Mar-16 18:43
MemberSam Leatherdale2-Mar-16 18:43 
QuestionSQL Query Builder Pin
Member 1209456128-Oct-15 4:01
MemberMember 1209456128-Oct-15 4:01 
Questionhelp, SQL Query Builder Pin
Member 1209456128-Oct-15 3:59
MemberMember 1209456128-Oct-15 3:59 
QuestionSelectQueryBuilder Pin
Member 102173628-Sep-15 21:01
MemberMember 102173628-Sep-15 21:01 
QuestionHow to query the column IS NULL Pin
don75042128-Aug-15 1:12
Memberdon75042128-Aug-15 1:12 
AnswerRe: How to query the column IS NULL Pin
Sam Leatherdale2-Mar-16 18:37
MemberSam Leatherdale2-Mar-16 18:37 
QuestionOutstanding tool - Great work! Pin
DavidInvenioDavid18-Aug-15 8:18
MemberDavidInvenioDavid18-Aug-15 8:18 
GeneralExcellent Pin
asazello20-Mar-15 3:11
Memberasazello20-Mar-15 3:11 

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

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