Data the easy way – WebMatrix.Data in Umbraco

I was quite inspired by the Hanseminutes Podcast 249 – On WebMatrix with Rob Conery where Conery (a.k.a. the SubSonic creator) is expressing his likings about the WebMatrix.Data approach for lightweight dynamic direct way of accesing a database.

And yes, who hasnt spent all too many hours creating complicated db layers just for simple webpage-CRUD-things against this and that database?

So – here is a library that uses dynamic objects to Linq-access any database table without the need of explicit creation of Data models.

A quick Umbraco context sample (get the top 100 rows from the umbraco log) would look something like this:

@using System.Linq
@using WebMatrix.Data
@{
var connstr = System.Configuration.ConfigurationManager.AppSettings["umbracoDbDSN"];
using (var db = Database.OpenConnectionString(connstr,"System.Data.SqlClient"))
{
  var logs = db.Query("select top 100 * from umbracoLog order by id DESC");
  <table>
  @foreach(var log in logs) {
  <tr>
    <td>@log.userId</td>
    <td>@log.NodeId</td>
    <td>@log.logHeader</td>
    <td>@log.Datestamp.ToShortDateString()</td>
    <td>@log.logComment</td>
  </tr>
  }
</table>
}

Does it work? Yes it does – just drop a WebMatrix.Data.dll into your /bin folder. The dll comes with the WebMatrix installation.

Can we do inserts, deletes and updates aswell? Sure – have a look at the samples on asp.net – but here’s a simple insert sample:

var insertQuery = "INSERT INTO Product (Name, Description, Price) VALUES (@0, @1, @2)"; 
db.Execute(insertQuery, Name, Description, Price); 

Yes, WebMatrix.Data requires basic T-SQL syntax skills from the developer. But isn’t that something we all have already? And – for 100% control of your queries, this might very well be what we like anyway?

However — for those of you that want to go a step further – Mark Rendle are already there for you with his project SimpleData – have a look at SimpleData GitHub.

A strongly-typed wrapper around WebMatrix.Data: https://gist.github.com/792450 by Jeremy Skinner.

Please read Thoughts on WebMatrix by Rob Conery, both for the article itself but also for the comments.

Advertisements

5 thoughts on “Data the easy way – WebMatrix.Data in Umbraco

  1. the code sample you’ve presented reminds me of the bad old days of ASP/VBScript/ADO. I don’t want to sound disrespectful or anything, should we strive to move away from such practises (mixing UI, db and business logics)?

    • Three years ago I would have agreed about the “bad old days” but now that we have dynamics it’s a new ball game. If the repository pattern is followed, where you have some formal organization of your database calls and not in your code behind, I think using Web Matrix can be a powerful addition to a developers toolkit.

      I usually implement the repository pattern by creating a new project, for smaller projects, it may be just a folder.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s