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: