My Query class. Your opinion?

Question

I just wanted to share this Query class and get your thoughts on it. The Query class helps execute queries against a dbconnection. I haven't included the implementation, it's a bit much to post. Here's an example call, you'll get the idea:

OrdersDataTable table = 
   new Query(connection)
   .Table("Orders")
   .Fields("OrderID,CustomerID,Description,Amount")
   .GreaterThan("OrderID", 1000)
   .OrderBy("OrderID")
   .Execute<OrdersDataTable>();

Here's the reasons I like the class:

• No need to write the ADO.NET Command code. Calling this class takes up less lines than the ADO.NET equivilent.
• Each member function returns this so multiple calls can be chained together. I'm not sure if there is a name for this pattern. Do you know?
• Handles MySql, OleDb and SqlServer dialects.
• Seems more readable to me than the ADO.NET equivilent.
• Order of calls is irrelevant. All parameters are buffered in internal collections and read when Execute is called.
• My scenario requires me to communicate with multiple database products so I would like to write the query once in a generic manner and just pass in the given connection. It would be alot trouble to leverage db-specific features like stored procs.

I have a DAL that uses this class internally. The UI would make a call into the DAL, passing a few query params and the DataTable is returned. The class has really helped reduce LOC the DAL implementation as well as made it more readable.

Here's a sample DAL call:

OrdersDataTable orders = Orders.GetByOrderId( 1 )

And the impl:

public static OrdersDataTable GetByOrderId( int id )
{
    return  
       new Query(connection)
       .Table("Orders")
       .Fields("OrderID,CustomerID,Description,Amount")
       .Equals("OrderID", id)
       .Execute<OrdersDataTable>();
}

Thanks!

Edit: Thanks everyone for the great feedback. Many of you suggested LinqToSql. I did not choose Linq because Microsoft currently only supports Sql Server. I need to query Jet, MySql and Sql Server tables.

Someone asked how AND and OR clauses are constructed. Here an example of each

//and example
    public static OrdersDataTable GetByOrderIdAndCustomerId( int orderId, int customerId )
    {
        return  
           new Query(connection)
           .Table("Orders")
           .Fields("OrderID,CustomerID,Description,Amount")
           .Equals("OrderID", orderId)
           .Equals("CustomerID", customerId)
           .Execute<OrdersDataTable>();
    }

//or example
    public static OrdersDataTable GetByOrderIdOrCustomerId( int orderId, int customerId )
    {
        return  
           new Query(connection)
           .Table("Orders")
           .Fields("OrderID,CustomerID,Description,Amount")
           .OrBegin
           .Equals("OrderID", orderId)
           .Equals("CustomerID", customerId)
           .OrEnd
           .Execute<OrdersDataTable>();
    }

Answer 1

It looks like you've written a very nice little ORM (object relational mapper) with an implementation of the Repository (or close to) pattern. You also took advantage of the design pattern called Fluent Interfaces which is what is allowing you to do .Table .Fields cascading from each other.

All 3 of these patterns are very good to be included in modern software system design as you already notice they greatly reduce the LOC, enhance maintainability and greatly improve test ability. I also like that you constructed your functionality to be able to return strongly typed objects using generics correctly.

Edit: The only possible area I could see for improvement would to be change the fact every call is a new Query and perhaps change your code to use a dependency injection framework that will inject the correct provider and when the object is called that it will initiated a database transaction for the query. If your code already pretty much does that internally in the Query class that could be called "Poor man's dependency injection" or atleast it's very similar (depending on your implementation) which if it's already working for you and you don't intend to really grow out your types of database support the Poor man's DI should be just fine. I'm just not a fan of the new keyword in any usage anymore since it usually causes high levels of code coupling that a DI framework alleviates (or it's for method hiding with is a bad bad design decision).

In response to Mark's answer, I'm usually never a fan of code generation it always seems to become a point of failure however he does have a very good point to reduce the amount of magic strings involved. I would instead choose to use the Lambda operator and expressions to handle this though, so you could achieve this:

public static OrdersDataTable GetByOrderId( int id )
{
    return  
       new Query(connection)
       .Table(x => Inflector.Pluralize(x.GetType())
       .Fields(x=> { x.OrderID, x.CustomerID, x.Description, x.Amount })
       .Equals(x=>x.OrderID, id)
       .Execute<OrdersDataTable>();
}

This will remove all of the magic string refactoring issues allowing you to leverage Microsoft's built in refactoring tools much easier or Resharper's (of course resharper can find magic strings during refactoring).

Inflector is a free library (can't remember if it's OSS or not) that includes functionality for dealing with text, the Pluralize method will take a word and make it... plural if you couldn't guess. Where this is useful is when you have like Story where you can't just go GetType() + "s" since Storys isn't correct and Inflector will correctly return you "Stories".

Answer 2

I usually try and stay away from these functional ways of doing things. It can be confusing sometimes to track down issues when they arise.

I would just use LINQ and accomplish the same task with more readable code.

Answer 3

Although I agree that Linq is designed to do this, there's no reason not to build your own archtiecture if its works for your requirements.

I would go a bit further and use code generation to build Enumerated Types or other Object based entitied to reduce the need for literal text. (and reduce typos)

Answer 4

It's a fun exercise and your code does appear to be quite readable and concise. In a production environment, I would probably stay away from reinventing the wheel unless there was a serious need to. My time, and yours, would be better spent creating value in the application, not adding functionality to the custom ORM which we can get for "free" in other, battle-tested, packages such as LINQ and NHibernate.

Answer 5

How do you handle more complex WHERE clauses involving ANDs and ORs?

What about subqueries?