Creating An ASP.NET MVC Blog With ServiceStack.OrmLite

ServiceStack.OrmLite is a convention-based, configuration-free lightweight ORM that uses standard POCO classes and Data Annotation attributes to infer its table schema. OrmLite uses a set of light-weight C# extension methods around the System.Data.* interfaces. It is designed to persist POCO classes with a minimal amount of intrusion and configuration.

It's core objectives are:
Map a POCO class 1:1 to an RDBMS table, cleanly by conventions, without any attributes required.
Create/Drop DB Table schemas using nothing but POCO class definitions.
Simplicity - typed, wrist friendly API for common data access patterns.
High performance - with support for indexes, text blobs, etc.
Among the fastest Micro ORMs for .NET.
Access to IDbCommand and raw SQL.
Cross platform - supports multiple dbs (Sqlite, Sql Server,Firebird, MySql and Postgres ) running on both .NET and Mono platforms.

In OrmLite: 1 Class = 1 Table. There's no hidden behavior behind the scenes that auto-magically manages hidden references to other tables. Any non-scalar properties (i.e. complex types) are text blobbed in a schema-less text field using .NET's fastest Text Serializer with the JSV Text Format (JSON + CSV). Effectively this allows you to create a table from any POCO type and it should persist as expected in a DB Table with columns for each of the class' First level public properties. So a property List<Comment> of a class BlogPost would get serialized into a varchar column in the BlogPost table. But you can also use regular data annotations as foreign keys to other tables, such as with the [References...] decoration.

The two main reasons I like ServiceStack.OrmLite are that it is very easy to use, and it is just about as fast as Dapper-Dot-Net, the all time Micro-ORM speed freak.

The API is minimal, providing basic shortcuts for the primitive SQL statements:



Nearly all extension methods hang off the implementation-agnostic IDbCommand interface.

CreateTable<T> and DropTable<T> create and drop tables based on a class' type definition.

For a one-time use of a connection, you can query straight off the IDbFactory with:

var customers = dbFactory.Exec(dbCmd => dbCmd.Where<Customer>(new { Age = 30 }));

All Insert, Update, and Delete methods take multiple params, while InsertAll, UpdateAll and DeleteAll take IEnumerables. GetLastInsertId returns the last inserted records auto incremented primary key.

Save and SaveAll will Insert if no record with Id exists, otherwise it Updates. Both take multiple items, optimized to perform a single read to check for existing records and are executed within a single transaction.

Methods containing the word Each return an IEnumerable and are lazily loaded (i.e. non-buffered).

Selection methods containing the word Query or Where use parameterized SQL (other selection methods do not). Anonymous types passed into Where are treated like an AND filter.

You are also free to use custom SQL:
var rows = dbCmd.Select<ShipperTypeCount>("SELECT ShipperTypeId, COUNT(*) AS Total FROM Shippers GROUP BY ShipperTypeId ORDER BY COUNT(*)");

For my ServiceStack.OrmLite Blog demo I have two classes; Post and Comment:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;
using ServiceStack.DataAnnotations;
using ServiceStack.DesignPatterns.Model;
using ServiceStack.OrmLite.SqlServer;

namespace  ServiceStackMvc
{
     [Alias("Posts")]
    public class Post  
    {
         public Post ()
         {
             if (this.Id == Guid.Empty)this.Id = Guid.NewGuid();
         }

        public Guid Id { get; set; }
        public DateTime Date { get; set; }

         [Index(Unique = true)]
        [StringLength(150)]
        public string Title { get; set; }

         [StringLength(350)]
        public string Url { get; set; }

         [StringLength(350)]
        public string Summary { get; set; }

         [UIHint("WYSIWYG")]
        [AllowHtml]
         [StringLength(500)]
         public string Details { get; set; }

         [StringLength(150)]
        public string Author { get; set; }
        public int TotalComments { get; set; }
         public IList<Comment> Comments { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using ServiceStack.DataAnnotations;
using ServiceStack.DesignPatterns.Model;


namespace  ServiceStackMvc
{
    [Alias("Comments")]
    public class Comment   : IHasId<Guid>
    {
        public Guid PostId { get; set; }
        public Guid CommentId { get; set; }

         public DateTime Date { get; set; }

         [StringLength(150)]
        public string Author { get; set; }

         [StringLength(5000)]
        public string Detail { get; set; }

         public Guid Id
        {
            get { return Guid.NewGuid() ; }
        }
    }
}

In the Global class, I create the database tables one time:

            OrmLiteConfig.DialectProvider = new SqlServerOrmLiteDialectProvider();
             using (IDbConnection dbConn = "server=(local);database=TEST;Integrated Security=SSPI".OpenDbConnection())
             using (IDbCommand dbCmd = dbConn.CreateCommand())
            {
                const bool overwrite = false;
                dbCmd.CreateTables(overwrite, typeof(Post));
            }

In this case, we only need the Posts table since the Comments property of a Post (one or more comments) is automatically serialized into a varchar Comments column using the JSV format.

Controller methods are simple:

         [HttpPost]
        public ActionResult Create(Post post)
        {
             if (ModelState.IsValid)
            {
                post.Url = post.Title.GenerateSlug();
                post.Author = User.Identity.Name;
                post.Date = DateTime.Now;
                 _postService.Create(post);  
                 return RedirectToAction("Index");
            }

            return View();
        }

Database operations are handled by Service Classes:

public void Create(Post post)
        {
            post.Comments = new List<Comment>();
             using (IDbConnection dbConn = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString.OpenDbConnection())
             {
                 using (IDbCommand dbCmd = dbConn.CreateCommand())
                 {
                          dbCmd.Insert(post);
                     HttpContext.Current.Session["Id"] = post.Id;
                }
            }
        }

ServiceStack.OrmLite is only one part of the ServiceStack offering. ServiceStack also provides:

REST and RPC Web Services Framework
C# Redis Client
Redis Admin UI and Redis WebServices
ServiceStack.Text JSON, CSV and JSV Serializers
Contrib - Complementary libraries and Utils for developing WebServices
ServiceStack.Logging - Dependency-free logging interface

You can download my Visual Studio 2010 ASP.NET MVC 3 demo here.  To use the demo create a new SQL Server database named "TEST". The application creates the POSTS table automatically. The connection string is in the web.config file.

Check out ServiceStack and it's offerings here: