Over a million developers have joined DZone.

Creating a Data Access Layer using Dynamics in WebMatrix

DZone's Guide to

Creating a Data Access Layer using Dynamics in WebMatrix

· Web Dev Zone
Free Resource

Learn how to build modern digital experience apps with Crafter CMS. Download this eBook now. Brought to you in partnership with Crafter Software

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?):


And we’ll add a few records:


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);

    @foreach (var product in qry) {

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:

    @foreach(var product in ProductsData.GetProducts()) {

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"];


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"];



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.



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.

Crafter is a modern CMS platform for building modern websites and content-rich digital experiences. Download this eBook now. Brought to you in partnership with Crafter Software.


Published at DZone with permission of Steve Lydford, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}