Who Needs LINQ Anyway ? Build Queries with Intellisense Support in C#

• Download example query - 15.39 KB

Introduction

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.

Background

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 orders table):

DB db = new DB(); 
string sql = Query<Customer>
.Select(db.CST.Id, db.CST.LastName,db.CST.FirstName) 
  .From(db.CST,db.ORD) 
 .Where(db.CST.FirstName >= "C") 
   // customer older than 20 years 
   .And(db.CST.DateOfBirth <= DateTime.Today.AddYears(-20)) 
      .And(db.CST.Id == db.ORD.Customer) // join orders and customers 
   .And(db.ORD.OrderDate >= DateTime.Today) // orders in the future ? 
.SQL; 
Console.WriteLine(sql); 
Console.ReadLine(); 

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:

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

// this class is autogenerated from the database in my own framework
class DB
{
    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");
}

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

// this class is also autogenerated from the database in my own framework
    class Model
    {
        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 select/from/where functionalities and should implement a datareader. This class is always initialized using a Query<T>.Select(columns).From(tables).Where(expression).And/Or(Expression)...

• 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 (DB.Table.Column)
• tables: A series of QrySrc class instances (DB.Table)
• 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 "CST").

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

Epilogue

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

Voting

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

History

• 2007-06-05: First version