ARROYOCODE

Micro ORM Data Mapping with PetaPoco and ASP.NET MVC 4

Introduction

user

ARROYOCODE

A former IT/IS manager turned developer, husband, and co-creator of three whom have yet to use a WYSIWYG.

Micro ORM Data Mapping with PetaPoco and ASP.NET MVC 4

Posted by Greg Arroyo on .
Featured

Micro ORM Data Mapping with PetaPoco and ASP.NET MVC 4

Posted by Greg Arroyo on .

Like others, I spend a LOT of time in and out of EntityFramework for Silverlight and MVC application development. Though it has its place in the development ecosystem, there are those times that it may be overkill, cumbersome and/or down right annoying to use (i.e.: auto-generated strongly typed metadata, lack of stored procedure support without blank tables/views come to mind). Welcome to Micro ORM land, a place that allows you the developer to wrap your data objects as you see fit, use standard SQL syntax, even map complex datasets to local objects (POCOs). Today we're playing with PetaPoco, but feel free to check post references for info on additional Micro ORMs.

PetaPoco is a single file solution for mapping your database objects without the overhead of more mainstream ORMs. It provides flexibility for data access including querying, joining, CRUD operations and much more with your application POCO (Plain Old CLR Object) objects. You control and/or map what data/objects are needed, how they should be used without the all-or-nothing approach of more mainstream ORMs (i.e.: EntityFramework). As an application developer, you can both derive a dataset consisting of joins, functions, etc using standard SQl syntax or map individual objects and use LinQ to achieve similar results. PetaPoco supports SQL Server, SQL Server CE, MySQL, PostgreSQL and Oracle.

With the beta release of ASP.NET MVC 4 framework, what better time to build a sample Micro ORM Data Mapping application using PetaPoco, a stock AdventureWorks SQL database and the default MVC 4 Internet application. For post brevity, we'll skip the stock MVC 4 Internet application creation step in Visual Studio and focus on the integration of PetaPoco onward to final display content.

Installing PetaPoco

PetaPoco is available in two NuGet formats, the full normal version including T4 templates for generating POCOs from your database or just the core class (PetaPoco.cs). It is also available directly from GitHub. Following the "single file" methodology, our sample solution includes only the PetaPoco.cs class file within the application ~/Models/ directory as downloaded from the GitHub repo. We will hand-craft our own POCOs for demonstration needs.

If you wish to use NuGet, execute either of the following in Visual Studio's Package Manager console:

PM> Install-Package PetaPoco
PM> Install-Package PetaPoco.Core

Building the Product POCO

POCOs (Plain Old CLR Objects) are typically hand-crafted class files that create objects unencumbered by inheritance or attributes used by specific frameworks. Our sample application makes use of a single Product POCO (shown below).

using System;

namespace MicroORM.Sample.Models
{
    [PetaPoco.TableName("SalesLT.Product")]
    [PetaPoco.PrimaryKey("ProductID")]
    public class Product
    {
        // Matching AdventureWorksLT.SalesLT.Product table schema
        public int ProductID { get; set; }
        public string Name { get; set; }
        public string ProductNumber { get; set; }
        public string Color { get; set; }
        public Double StandardCost { get; set; }
        public Double ListPrice { get; set; }
        public string Size { get; set; }
        public Double Weight { get; set; }
        public string Description { get; set; }
        public string ThumbnailPhotoFileName { get; set; }
    }
}

Pretty self-explanatory, but what's with the [PetaPoco*] mojo there? PetaPoco allows you to "decorate" your POCOs to eliminate the need for specifying table names and primary keys every time you need to perform a CRUD operation. Though our sample application doesn't demo CRUD, it does bring about consideration. Take the following non-decorated vs decorated scenario for example:

// create the product
var a = new Product();
a.Name = "Cool New Product";
a.ProductNumber = "123xyz";

// insert product
db.Insert("SalesLT.Product", "ProductID", a); // include Table, PrimaryKey and Product() info; every time, really?

And now using a decorated Product POCO, we can simply execute the following to perform the same Insert:

var a = new Product();
a.Name = "Cool New Product";
a.ProductNumber = "123xyz";
db.Insert(a); // look ma, no Table and Primarykey info!

Considering that most queries start with "SELECT * FROM TABLE", why not use the same decorated POCO's TableName attribute and automatically create the SELECT part of the statement:

var a = db.SingleOrDefault<Product>("SELECT * FROM SalesLT.Product WHERE ProductID = @0, 123);

can then be shortened to this:

var a = db.SingleOrDefault<Product>("WHERE ProductID = @0", 123);

PetaPoco doesn't actually generate the "SELECT" statement, it picks the column names of the POCO and only queries for those columns! PetaPoco provides a ton of additional functionality including Ignore, ExplicitColumns, T4 Templates and more, all of which warrant your consideration if you're interested in a mainstream ORM alternative.

Creating the ViewModel

We'd like our sample application to display a "Featured Product" and a simple Product List, both of which should be visible on application load. We'd also like to take advantage of Razor intellisense and a strongly typed View. To accomplish this, we'll create our DisplayViewModel class (~/Models/DisplayViewModel.cs) that will contain both a single Product POCO and a list of Product POCOs (List<Product>) for use with our default Index View (~/Views/Home/Index.cshtml).

using System.Collections.Generic;

namespace MicroORM.Sample.Models
{
    public class DisplayViewModel
    {
        public List<Product> FeaturedProduct { get; set; }
        public List<Product> ProductList { get; set; }

        public DisplayViewModel()
        {
            FeaturedProduct = new List<Product>();
            ProductList = new List<Product>();
        }
    }
}

Using a Data Manager Class

Next, we'll create a SqlDataManager class to decouple the data access layer from HomeController logic.

using System.Collections.Generic;

namespace MicroORM.Sample.Models
{
    public class SqlDataManager
    {
        private PetaPoco.Database db = new PetaPoco.Database("DbConnection");

        public List<Product> GetProducts()
        {
            var products = db.Fetch<Product>("SELECT TOP 10* FROM SalesLT.Product");
            return products;
        }

        public List<Product> GetProductById(int id)
        {
            // standard T-SQL syntax against context; join needed for Featured Product Description field
            // let's use PetaPoco's Sql.Bulder to do it!
            var sql = PetaPoco.Sql.Builder
                .Append("SELECT a.ProductID, a.Name, a.ProductNumber, a.Color, a.StandardCost, a.ListPrice, a.Size, a.Weight, b.Description, a.ThumbnailPhotoFileName")
                .Append("FROM SalesLT.Product a")
                .Append("LEFT OUTER JOIN SalesLT.vProductAndDescription b ON a.ProductID = b.ProductID")
                .Append("WHERE a.ProductID=@productId AND b.Culture = @culture",
                    new
                        {
                            productId = id,
                            culture = "en"
                        });
            var product = db.Fetch<Product>(sql);
            return product;
        }
    }
}

Sure, we could use the following PetaPoco syntax for the GetProductById method:

var product = db.Fetch<Product>("SELECT a.ProductID, a.Name, a.ProductNumber, a.Color, a.StandardCost, a.ListPrice, a.Size, a.Weight, b.Description, a.ThumbnailPhotoFileName FROM SalesLT.Product a LEFT OUTER JOIN SalesLT.vProductAndDescription b ON a.ProductID = b.ProductID WHERE a.ProductID=@0 AND b.Culture = 'en'", id);
return product;

But why not take advantance of PetaPoco's Sql.Builder and Named Parameters syntax which grants us the ability to conditionally build SQL, make formatting SQL strings easy and provide a mechanism to use proper parameter replacements to protect from SQL injection.

Additionally, there are methods for building every day SQL syntax:

var sql = PetaPoco.Sql.Builder()
    .Select("*")
    .From("SalesLT.Product")
    .Where("ProductID = @0", id);

Populating the ViewModel with Data

With data mapping in place, it's time to populate our DisplayViewModel via our SqlDataManager and return it to our strongly typed Index() View. By decoupling the PetaPoco data access from the HomeController, we are free to change and/or update our core data storage architecture without affecting our Controller logic.

using System.Web.Mvc;
using MicroORM.Sample.Models;

namespace MicroORM.Sample.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "A tiny, fast, single file micro-ORM for your .NET POCOs.";

            var viewModel = new DisplayViewModel();
            var manager = new SqlDataManager();

            viewModel.FeaturedProduct = manager.GetProductById(745); //a randomly selected ProductID from DB
            viewModel.ProductList = manager.GetProducts();

            return View(viewModel);
        }
    }
}

Final View Integration

Our second to last step involves adding the following to our ~/Views/Home/Index.cshtml View for displaying our DisplayViewModel data to the user.

@model MicroORM.Sample.Models.DisplayViewModel

is added to the top of our View providing strongly typed validation and Razor syntax. And finally, both our "Feature Product" and "Product Listing" code blocks are added inline to display content to the viewer.

...
<h3>Featured Product:</h3>
@foreach (var item in Model.FeaturedProduct) {
<p><img src="/Images/@item.ThumbnailPhotoFileName" alt="@item.ProductNumber : @item.Name" border="0" /></p>
<p><b>@item.Name</b></p>
<p>@item.Description</p>
<p><b>Price:</b> @String.Format("{0:c}", @item.ListPrice)</p>
}
...

...
<h3>Products List <i>(Limited to 10 results for demo brevity)</i></h3>
<table>
<tr>
    <th>Product ID:</th>
    <th>Product Name:</th>
    <th>Product #:</th>
    <th>Color:</th>
    <th>Size:</th>
    <th>Weight:</th>
    <th>Price:</th>
</tr>
    @foreach (var item in Model.ProductList) {
        <tr>
            <td>@item.ProductID</td>
            <td>@item.Name</td>
            <td>@item.ProductNumber</td>
            <td>@item.Color</td>
            <td>@item.Size</td>
            <td>@item.Weight</td>
            <td>@String.Format("{0:c}", @item.ListPrice)</td>
        </tr>
    }
</table>
...

With some minor CSS modifications, our final sample application looks something this:

Final sample application layout

Download Example Code from GitHub

user

Greg Arroyo

A former IT/IS manager turned developer, husband, and co-creator of three whom have yet to use a WYSIWYG.