Download Source Code
Introduction
This little project was more of an experiment than anything else. Could a class be designed to programmatically build SQL queries such that using the class was considerably easier than writing the query yourself?
In the end, I'm not sure it could. I tried to come up with some slick techniques that would make this really cool. But in order to handle most common queries, the class had to be a little more complex that I would have preferred. And, not surprisingly, there are still many types of queries that the class does not support.
I suppose this is why the SQL query has endured as it has. It's simple and flexible, and no one has really come up with a better interface for giving commands to a SQL database.
Still, it was an interesting project. And I can imagine cases where using the class I created might be a little more manageable than writing the query yourself, particularly when the query is very long and/or you're not entirely comfortable writing SQL queries by hand.
The QueryBuilder Class
By default, the QueryBuilder class performs a SELECT query. You can set the QueryType property to choose a SELECT, INSERT, UPDATE or DELETE query.
The class has a number of methods for adding data. For example, use the AddColumn() method to specify a column name. (If no columns are specified, "*" is used.)
There are also methods for adding tables, name/value pairs (for INSERT and UPDATE queries), conditions and sort columns. Many of these methods are overloaded with multiple versions. For example, the AddTable() method has one version that takes only the name of a table. You would use this version if you are only adding one table, or if you are adding the first of several tables. It has another version that includes join information such as the tables and columns on both sides of the join and also the join type. You would use the second version when adding subsequent tables.
The added values are stored in private collections within the QueryBuilder class. The ToString() method builds the actual query using the data that has been collected.
The class is not complex. The hardest thing about writing it was trying to come up with techniques that would merge common tasks. In the end, I used a little bit more of a brute-force approach as I really didn't find many tricks that could come close to matching the flexibility of SQL queries.
Due to space limitations, the source code for the class itself is not listed in this article, but is included in the attached project.
Using the QueryBuilder Class
Here are some examples for using my QueryBuilder class along with the results.
Listing 1: Building a Simple SELECT Query
private void SelectDemo()
{
QueryBuilder builder = new QueryBuilder();
builder.AddTable("Contacts");
txtQuery.Text = builder.ToString();
}
Results:
SELECT * FROM [Contacts]
Listing 2: Building a SELECT Query that Specifies Columns, Conditions, and Sorting
private void Select2Demo()
{
QueryBuilder builder = new QueryBuilder();
builder.AddColumn("Name");
builder.AddColumn("City");
builder.AddColumn("State");
builder.AddColumn("Zip");
builder.AddTable("Contacts");
builder.AddCondition("[State] = 'UT'");
builder.AddCondition("[State] = 'CA'", ConditionOperators.Or);
builder.AddSortColumn("Name");
txtQuery.Text = builder.ToString();
}
Results:
SELECT [Name], [City], [State], [Zip] FROM [Contacts] WHERE [State] = 'UT' OR [State] = 'CA' ORDER BY [Name]
Listing 3: Building a SELECT Query with Joined Tables
private void Select3Demo()
{
QueryBuilder builder = new QueryBuilder();
builder.QueryType = QueryTypes.Select;
builder.AddColumn("ID", "Contacts", "ContactID");
builder.AddColumn("City", "Contacts");
builder.AddColumn("State", "Contacts");
builder.AddColumn("Zip", "Contacts");
builder.AddColumn("Name", "Company", "CompanyName");
builder.AddTable("Contacts");
builder.AddTable("Contacts", "CompanyID", "Companies", "ID", JoinTypes.InnerJoin);
builder.AddSortColumn("Name", "Companies", SortOrder.Descending);
builder.AddSortColumn("Name", "Contact", SortOrder.Ascending);
txtQuery.Text = builder.ToString();
}
Results:
SELECT [Contacts].[ID] AS [ContactID], [Contacts].[City], [Contacts].[State], [Contacts].[Zip], [Company].[Name] AS [CompanyName] FROM [Contacts] INNER JOIN [Companies] ON [Contacts].[CompanyID] = [Companies].[ID] ORDER BY [Companies].[Name] DESC, [Contact].[Name]
Listing 4: Building an INSERT Query
private void InsertDemo()
{
QueryBuilder builder = new QueryBuilder();
builder.QueryType = QueryTypes.Insert;
builder.AddNameValuePair("Name", "'Bill'");
builder.AddNameValuePair("City", "'Salt Lake City'");
builder.AddNameValuePair("State", "'UT'");
builder.AddNameValuePair("Zip", "'84084'");
builder.AddTable("Contacts");
txtQuery.Text = builder.ToString();
}
Results:
INSERT INTO [Contacts] ([Name], [City], [State], [Zip]) VALUES ('Bill', 'Salt Lake City', 'UT', '84084')
Listing 5: Building an UPDATE Query
private void UpdateDemo()
{
QueryBuilder builder = new QueryBuilder();
builder.QueryType = QueryTypes.Update;
builder.AddNameValuePair("Name", "'Bill'");
builder.AddNameValuePair("City", "'Salt Lake City'");
builder.AddNameValuePair("State", "'UT'");
builder.AddNameValuePair("Zip", "'84084'");
builder.AddTable("Contacts");
builder.AddCondition("[ID] = 123");
txtQuery.Text = builder.ToString();
}
Results:
UPDATE [Contacts] SET [Name] = 'Bill', [City] = 'Salt Lake City', [State] = 'UT', [Zip] = '84084' WHERE [ID] = 123
Listing 6: Building a DELETE Query
private void DeleteDemo()
{
QueryBuilder builder = new QueryBuilder();
builder.QueryType = QueryTypes.Delete;
builder.AddTable("Contacts");
builder.AddCondition("[Name] = 'Bill'");
builder.AddCondition("[Name] = 'Bob'", ConditionOperators.Or);
txtQuery.Text = builder.ToString();
}
Results:
DELETE FROM [Contacts] WHERE [Name] = 'Bill' OR [Name] = 'Bob'
Conclusion
As previously mentioned, your mileage may vary as to how useful this class is. If you are an expert SQL developer, then you probably won't have much use for it. However, if your SQL skills are not quite developed and/or you have some very long queries that you'd like to build in a more manageable way, then you might just want to give this class a try.
End-User License
Use of this article and any related source code or other files is governed
by the terms and conditions of
.
Author Information
Jonathan Wood
I'm a software/website developer working out of the greater Salt Lake City area in Utah. I've developed many websites including Black Belt Coder, Insider Articles, and others.