Dynamic dataaccess with Webmatrix Webpages

by dotnetnerd 11. October 2011 22:06

Today I went to a talk by Hadi Hariri about dynamics which was arranged for the ANUG user group in cooperation with the goto conference. The talk happened to fit very well with the first topic I had planned for my series on Webmatrix Webpages, which is dataaccess.

The talk was about not fearing dynamics, and some of the scenarios where it can provide some benefits over static types. Some of the scenarios Hadi talked about were DTO’s, ViewObjects and for DataAccess, which is exactly what WebMatrix WebPages utilizes.

The flood of Micro-ORMs

Over the last year or so a lot of so called Micro-ORMs have seen the light or day with some of the more popular ones being Simple Data, Dapper, PetaPoco and Massive. The reason for their popularity is that they provide a sense of simplicity in the vietnam of computer science as Ted Neward put it.

Each of these ORMs have their own focus, strengths and weaknesses – and some are more “Micro” than others. Compared to NHibernate or Entity Framework they are all very simple to get started with. For quite a few of them part of the strength is the return of good old SQL in stead of LINQ or some other abstraction.

WebMatrix.Data

When a new Webmatrix Webpages project is created it comes with its own Micro ORM out of the box. The ORM allows queries and commands to be executed, which are expressed as SQL statements. For queries data is returned as dynamic objects. So a regular query could be done like this.

var db = Database.Open("myDatabase");
dynamic user = db.QuerySingle("SELECT * FROM Users WHERE User_Id = @0", 123);

The big advantage of this approach is that you can select any fields, calculate fields, join with other tabels to your hearts content and you won’t have to write a class to represent each shape of the data returned. This also means that we can use the power of SQL and that we avoid overcomplicating things.  Because the distance from database, to query and then to the view is so short working with dynamic objects is not a problem. So if your domain is not too vast and complex life is good.

Doing inserts and updates is equally easy, but it is one of the areas where I find the ORM lacking. Most annoying is that it does not handle converting null to DBNull. Also while you do get extention methods to convert strings as int, DateTime etc, there is no option to get null instead of the default value of the datatype. So the code tends to get cluttered with parsing and conversions – if you don’t write the extentions yourself.

Clean Ajax

A nice surprise for me was how easy it is to expose data as JSon to enable Ajax when working with WebPages. All you have to do is create a WebPage that retrieves data, pass it to JsonEncode and write it to the response like this example shows.

var json = Json.Encode(user);
Response.Write(json);

Life does not get more simple than this, and it leaves you with this smooth feeling when moving data between server and client.

Conclusion

The dataaccess bits for WebMatrix have been fun to work with, and it has given me a great sense of freedom to get things done, without having to do viewobjects, mappers and a bunch of configuration.

The fluidity of working with dynamic data, and doing ajax certainly has opened my eyes with reguards to the value that this kind of framework can provide. “The return of SQL” has also reminded me, that LINQ is not all rainbows and smiles. The power of micromanaging a JOIN statement and doing UPDATES and deletes should not be overlooked.

Using the right tool for the right job is still the key phrase though. It has been a good match for this project I am working on, but I would not want to use it for an enterprise application. Testability is clearly not a goal of the framework, refactoring is error prone and when complexity increases the code tends to get messy.  So if anything I will argue that it proves that the place for WebMatrix is hobbyists, simple projects, startups and prototyping.

Who am I?

My name is Christian Holm Nielsen, and I work as an independent consultant, in Denmark, where I write code, give advice on architecture and help with training. On the side I get to do a bit of speaking and help with miscellaneous community events.

Some of my primary focus areas are code quality, programming languages and using new technologies to provide value.

Month List