OneQuery - A Fluent Interface to Database Queries
Tuesday, December 11, 2007 at 6:21AM Aside from my school work and my work on the DotNetNuke Reports Module (very very close to release now), I've been tinkering with another project over the past few months. It's called OneQuery and its yet another Data Access Layer tool. OneQuery is designed to provide a "fluent interface" to database queries.
What is a fluent interface? Wikipedia defines it as "an object oriented construct that defines a behaviour capable of relaying the instruction context of a subsequent call". Essentially, it means that instead of writing code like this:
List<int> ints = new List<int>();
ints.Add(1);
ints.Add(2);
ints.Add(3);
You can write code like this
List<int> ints = new List<int>().Add(1).Add(2).Add(3);
That's quite a savings and, in my opinion, it's much more readable.
So, what's the point?
Well, recently I've been thinking about the issue of database agnostic...ness (database agnostivity? database agnosticism?). I really want to be able to write data access code that will run on multiple database engines without having to manually translate it. DotNetNuke provides a, theoretically, database agnostic interface through Data Providers. However, to support another database engine, the core DataProvider must be translated, Stored Procedures must be converted and all sorts of "Porting" issues arise. Wouldn't it be nice if you could WORA (Write Once, Run Anywhere... a Java term) your SQL Scripts?
Wait, hasn't this been done?
Of course it has, and many many times. First, there's the elephant in the room, LINQ. However, aside from requiring .Net 3.5, if you want to write database agnostic code, you need to use something like the ADO.Net Entity Framework. OneQuery is designed to work in .Net 2.0, and I'm planning to write a LINQ layer on top of it so that you can write LINQ queries and run them on multiple database engines (not just SQL Server). I think OneQuery could serve as a "migration path" where you can write OneQuery syntax and then move to LINQ syntax when you're ready to move to .Net 3.5 while still keeping the database-agnostic OneQuery core.
There are a lot more database libraries out there as well. SubSonic provides an excellent Object/Relational Mapper (ORM) that allows you to work directly with objects, but sometimes you want a little more control. There are, of course, many others such as NHibernate, IBATIS.Net, and EntitySpaces. However, I found that while working with these tools I would find little things that would annoy me. For example, SubSonic does provide a way for you to directly query the database and get an IDataReader back, but it has an interface I found a little awkward (though it is a nice fluent interface). There are also smaller annoying features such as a lack of support for certain SQL statements (like "INSERT") and a lack of JOIN support. On the other side, tools like NHibernate are really designed to allow you to ignore the database completely, which is a nice feature, but sometimes I need that direct database control.
So, what is OneQuery?
[Wow, I started two sub-headings off with 'so', perhaps that summer at Microsoft is affecting my patterns of speech. They say Microsofties tend to start there sentences with "so" and say "drinking from a firehose" and "super" a lot]
OneQuery is designed to let you focus on writing one query, and allows you to run the query on multiple database systems. Instead of using strings, which the compiler ignores, I wanted to provide a fluent interface and use operator overloading to make the queries as readable as possible.
Enough already, get to the code!
Ok, so here's a sample query using OneQuery's fluent syntax:
(I posted it as a screenshot to make sure the spacing worked out, I'll post the text at the end if you want to copy it).
There are a few things I want to do to let you clear that up even further. For example, it would be much easier if you could write ".OrderBy(Product.ListPrice).Desc()", or ".Where(Product.ListPrice.Between(10).And(100))"
Behind the scenes, the only other code you need to "write" is a helper class for the products table. I used quotes there because I'm planning on creating a code generator to do that for you.
CAN HAZ CODE?
Hopefully I've tantalized you a little bit and you'd like to check it out. You can download a preview release from our CodePlex Project Site, and discuss future features there as well. I'm planning a whole bunch of new features including (but definitely NOT limited to):
- Support for ALL SQL DML Statements (INSERT, UPDATE, DELETE)
- Full support for SELECT (including JOIN, GROUP BY, HAVING, and sub-queries)
- A Code Generator to create the helper classes
- and more!
Please let me know what you think! I want to make sure this system solves problems for as many people as possible, and the best way to do that is to get contributions from the community as early as possible. The CodePlex site has plenty of places to contribute, so please check it out!
Sample Code in Text Form
IDataReader rdr = Product.Query()
.Where(Product.ListPrice > 10 & Product.ListPrice < 100)
.OrderBy(Product.ListPrice, SortDirection.Descending)
.ExecuteReader();
