Creating a Data Access Layer using Dynamics in WebMatrix

Introduction

So we’ve all seen the WebMatrix demo where we put some inline SQL in the page and pull back some records right? And I’m guessing that most of you just looked at that and thought, “YUK! What happens if the schema changes or I want to access the same set of data in different places? I want all my code in one place.”

Well, we could spend a couple of hours writing a traditional old-school data access layer, using classes to represent our domain model and methods to query and persist to the database, etc., etc…. but that is the way your Dad did it! WebMatrix is supposed to be all about cool, dynamic, quick scripting groovyness – so lets do it the dynamic way… 

The Setup

First off, lets create a SQL Compact database in our WebMatrix project called “Catalog” and give it a table called “Products” (original huh?):

dalTable

And we’ll add a few records:

dalData

And that’s it, we’re all set.

 

The “5 Minute Demo” way

Following the form of the usual demo code we do something like this to display a list of product names:

@{
    var db = Database.Open("Catalog");
    var sql = "SELECT * FROM Products";
    var qry = db.Query(sql);
}

<ul>
    @foreach (var product in qry) {
        <li>@product.Name</li>
    }
</ul>

So what’s the problem? Well, if this was a one page site then it wouldn’t really be a issue at all. The problem comes when you want to get data in different places across multiple pages – it becomes a maintenance nightmare. Kittens may die. 

Imagine that you have data access code in 20 different places in your site (which is a realistic number for a smallish site) and you change the database or rename the products table? What happens when you have products queries on several different pages and the spec changes to include an ‘inStock’ column and you need to amend all your product lists to only show items that have this field marked as true? I realise that I am probably “preaching to the converted” here, but you get my point.


This is how the cool kids are doing it….

Let’s create a new Razor file in our App_Code folder (create one if it doesn’t already exist) called ProductsData.cshtml and add the following code:

 

@using System.Dynamic;

@functions {

    public static Database Catalog {
        get {
            return Database.Open("Catalog");
        }
    }

    public static IEnumerable<dynamic> GetProducts() {
        var sql = "SELECT * FROM Products";
        return Catalog.Query(sql);
    }
}

 

Now if we need to change our product list SQL we only need to do it in one place and it makes the Razor view file much easier to read. The GetProducts() method returns IEnumerable<dynamic> which, in the crazy world of dynamics, binds at runtime so our view now looks like:

<ul>
    @foreach(var product in ProductsData.GetProducts()) {
        <li>@product.Name</li>
    }
</ul>


Now that’s better! What else can we do? Well, we could add a method to ProductsData.cshtml to pull out a single product by passing in it’s Product Id:

public static dynamic GetProductById(int id)
{
    var sql = "SELECT * FROM Products WHERE Id = @0";
    return Catalog.Query(sql, id).Single();
}


Which we can use like this in the content page:


@{
    var productId = 1;
    var product = ProductsData.GetProductById(productId);

}

<div>@product.Name - @product.Price</div>

And again, all our data access code is in one place and our content pages are much cleaner. We have saved many kittens.

Inserts and Updates

We can achieve the same benefits with inserts and updates with a bit of Expando magic! The following method in ProductsData.cshtml gives us inserts:

public static void AddProduct(dynamic product) {
    var sql = "INSERT INTO Products (Name, Description, Price) " +
                "VALUES (@0, @1, @2)";
    Catalog.Execute(sql, product.Name, product.Description, product.Price);
}


The method has one parameter which accepts a dynamic. The awesomeness of dynamics means that we can pass it an ExpandoObject, which will have it’s members resolved at runtime. By using dynamics in this way the compiler simply doesn’t care whether it can resolve product.Name, product.Price, etc. and just leaves it to the runtime to sort out.

In our view we can get the data we need from the user in a “New Product” form and use the AddProduct method like so:

@using System.Dynamic;
@{
    if(IsPost) {

        dynamic product = new ExpandoObject();
        product.Name = Request["Name"];
        product.Description = Request["Description"];
        product.Price = Request["Price"];

        ProductsData.AddProduct(product);
    }
}


Here we just create a new ExpandoObject, add members to it from the submitted form data and pass it to the AddProduct method. Easy peasy.

Updates are achieved in a very similar manner. This is the SaveProduct method:

public static void SaveProduct(dynamic product) {
    var sql = "UPDATE Products SET Name=@0, Description=@1, Price=@2 WHERE Id=@3";
    Catalog.Execute(sql, product.Name, product.Description, product.Price, product.Id);
}


And this is the code in the view:

@using System.Dynamic;
@{
    if(IsPost) {

        dynamic item = new ExpandoObject();
        item.Id = Request["Id"];
        item.Name = Request["Name"];
        item.Description = Request["Description"];
        item.Price = Request["Price"];

        ProductsData.SaveProduct(item);

        Response.Redirect("~/Index.cshtml");
    }
}

 

Again we are creating a new ExpandoObject, adding members to it from the submitted form data and passing it to the SaveProduct method in ProductData.cshtml.

 

Summary

So that’s how we can use dynamics and Razor @functions to create a simple Data Access Layer for our WebMatrix web sites. All kittens saved, time for bed.

Please feel free to download the source code and take a look.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)