As a C# developer, do you like to write your queries in SQL or in C# ? This article provides the solution...
Take a look at the following screen... The query (Access SQL) is completely built from C# code, and this is done with minimal effort.
In this article, I will show you how you can develop your own database query language in C#, without needing LINQ.
A while ago, I needed an application framework to develop an application for one of my customers. At that time, there was a big buzz about the SubSonic project[^], so I decided to check it out.
While it was actually a step in the right direction, it felt to me as if it just was not sufficient, so I got very -a little bit too much - ambitious and decided to write my own within one week [^].
As you can imagine, doing it in one week was not possible, but I got very close.
Using the Code
This article shows a single item of my own framework that uses an approach I have not seen before. Therefore I think it might be useful to show other people how you can build code like this. This code will allow you to use C# syntax with intellisense to build your queries.
An example of what is possible with this approach (
CST is the
customers table and
ORD is the
DB db = new DB();
string sql = Query<Customer>
.Where(db.CST.FirstName >= "C")
.And(db.CST.DateOfBirth <= DateTime.Today.AddYears(-20))
.And(db.CST.Id == db.ORD.Customer)
.And(db.ORD.OrderDate >= DateTime.Today)
What Do I Need to do to Get this Functionality in my Own Application?
Either do it yourself or get some kind of code generator to implement the following code:
DB class: implement all tables together with its columns inheriting from
QrySrc and instancing a
QryEl for each column. The datatype of the column is not relevant here.
public class Customers : QrySrc
public QryEl Id;
public QryEl FirstName;
public QryEl LastName;
public QryEl DateOfBirth;
public Customers(string t,string a):base(t,a)
Id = new QryEl(this.Alias, "ID");
FirstName = new QryEl(this.Alias, "FNAME");
LastName = new QryEl(this.Alias, "LNAME");
DateOfBirth = new QryEl(this.Alias, "BIRTHDATE");
public Customers CST = new Customers("Customers", "CST");
public class Orders : QrySrc
public QryEl OrderDate;
public QryEl Customer;
public Orders(string t, string c):base(t,c)
OrderDate = new QryEl(c, "ORDERDATE");
Customer = new QryEl(c, "CUSTOMERID");
public Orders ORD = new Orders("Orders", "ORD");
Model class: This would be the class containing all classes contained within the model. The instance of the model should be loaded from a datareader based on the SQL that the query generates. I personally prefer to use reflection to load the values. In the downloadable example, this stuff is not implemented.
public class Customer
public int ID;
public string FirstName;
public string LastName;
public DateTime DateOfBirth;
public class Order
After that, you can use the query functionality in all it's glory, without using LINQ...
So How Does It Work?
With a whole lot of Bow-Chika-Bow-Wow-Chika_Bow® :-)
The whole system is based on the following 3 classes:
The Query<T> Class
This is the class that offers the query functionality itself; it groups
where functionalities and should implement a datareader. This class is always initialized using a
T: A class type that will be instanced and used for mapping the results from the datareader. In this example code, the implementation of this part is left to the reader as an exercise.
columns: A series of
QryEl class instances (
tables: A series of
QrySrc class instances (
expression: One or more
QryEl instances together with an operator
DB.Table.column <<operator>> [ DB.Table.column | object ] )
The QrySrc Class
This is the equivalent of a table in a database. It should be initialized with the tablename (for example
"Customer") and table alias (for example
The QryEl Class
This can be any SQL expression you define, but most of the time this will be table columns and criteria. This is where all the magic happens: by overloading all default operators, and converting them to a
string instead of executing them, we automatically get our SQL.
That's all folks !!!
The source code for this article is by no means a complete framework; it is just a quick mockup to show you how one small part of the framework works.
Some information for the more curious people regarding my complete framework (warning: shameless plug).
My own framework is a work in progress. Some features:
- It is written in ASP.NET
- It uses a buildprovider to automatically derive the class model from the database.
The database needs to be modelled following a few easy rules, and automatically the built model also supports Parent/Child relationships and inheritance/late binding/updating only changed fields,logging, security, ...
- The framework also offers a grid- and detailsview that is built on the fly, using dropdownbuttons, calendar controls, etc...
- The model tracks updates, and posts only changed fields back to the database...
Maybe I might release the source of the full framework to the community in the future, but I will not promise anything...
Please give your vote for this article. If you vote, let me know what you like and dislike, so I can improve this in the future!!
- 2007-06-05: First version