ServiceStack is a .NET framework consisting of several components: JSON serializers, Redis as a data store, OrmLite, Service Clients and Web Services Framework. In our article we are going to give an overview for two of them — OrmLite and Redis — in terms of usage for handling database communication.

OrmLite

As its GitHub page says, OrmLite is a set of light-weight C# extension methods around System.Data.* interfaces which is designed to persist POCO classes with a minimal amount of intrusion and configuration.

Here are the main reasons for OrmLite to be attractive:

It’s fast. According to measurements obtained using Dapper, OrmLite performance is very good. Here is the comparison of different DB layer technologies using POCO serialization:

Method Duration
Hand coded (using a SqlDataReader) 47ms
Dapper ExecuteMapperQuery<Post> 49ms
ServiceStack.OrmLite (QueryById) 50ms
PetaPoco 52ms
BLToolkit 80ms
SubSonic CodingHorror 107ms
NHibernate SQL 104ms
Linq 2 SQL ExecuteQuery 181ms
Entity framework ExecuteStoreQuery 631ms

It’s versatile. OrmLite can be used to communicate with SQL Server, MySQL, PostgreSQL, SQLite Windows, SQLite Mono, Oracle or Firebird.

It’s simple. To connect to database .NET IdbConnection is used. You don’t need to code custom logic for some specific connection type. Next thing is setting structure of POCO objects. In OrmLite one class equals one table. So there is no hidden behavior or intermediate classes. By default, appropriate tables are searched by the name set with the Alias attribute. The same approach (setting an Alias) is used for custom property names if you want them to differ from the table columns’ names. Then you can operate with connection and POCO entities. Below are some examples to show how it all looks.

Let’s start with some basic commands:

// All Insert, Update, and Delete methods take multiple params. InsertAll, UpdateAll and DeleteAll take IEnumerables
var item1 = new Item { Id = Guid.NewGuid(), Name = "item1" };
var item2 = new Item { Id = Guid.NewGuid(), Name = "item2" };
var items = new List<Item> { item1, item2 };

conn.Insert(item1);
conn.Insert(item2);
// or
conn.Insert(item1, item2);
// or
conn.InsertAll(items);
// Update and Delete methods have similar behavior.

// To Update item or create it if it doesn't exist (by Id) we use Save method.
conn.Save(item1, item2);

// Unlike Update(item), which updates entity properties by Id, in this case all properties will be set by condition passed via second parameter
conn.Update(item1, i => i.Name == "name");
// So we'll have SQL script like: UPDATE Items SET Id = '(item1.Id)', Name = '(item1.Name)' WHERE Name = 'name'

// But if you want to update only some fields:
conn.Update<Item>(new { Name = "newName" }, i => i.Name == "name");
// or
conn.UpdateOnly(new Item { Name = "newName" }, i => i.Name == "name");

Selection commands:

// There are different ways to get entities from DB. Here are some of the most popular ones:
var id = Guid.NewGuid();
var item = conn.GetById<Item>(id);

var items = conn.Select<Item>().Where(i => i.Name == "name");

var fodItem = conn.FirstOrDefault<Item>(i => i.Name == "name");

var sqlItem = conn.Select<Item>("Name = {0} OR Name = {1}", "name", "newName");

OrmLite executes the command right after being called. But sometimes you need to store or modify its script before executing. In that case the following expressions should be used:

var expr = conn.CreateExpression<Item>().Where(i => i.Name == "name");
if (orderAscending)
{
    expr.OrderBy(i => i.Name);
}
else
{
    expr.OrderByDescending(i => i.Name);
}
var items = conn.Select(expr);

You can fill your custom object with “JOIN” using built-in JoinSqlBuilder:

// Here is a CustomItem class. It looks like this:
public class CustomItem
{
    public Guid? Id { get; set; }
    public string Name { get; set; }
    public string UserName { get; set; }
    public string CompanyName { get; set; }
}

// We can fill it with data from DB, selecting pack of custom entities with two joins by foreign keys
var expr = new JoinSqlBuilder<Item, User>()
    .Join<Item, User>(i => i.UserId, u => u.Id,
        i => new { i.Id, i.Name },
        u => new { UserName = u.Name })
    .Join<User, Company>(u => u.CompanyId, c => c.Id,
        null,
        c => new { CompanyName = c.Name })
    .Where<Item>(i => i.Id != someId)
    .Where<Company>(c => c.CreatedAt > createdDate)
    .OrderByDescending<Item>(i => i.Name)
    .OrderByDescending<User>(u => u.Name)
    .ToSql();
// First two parameters of Join() are keys to use in JOIN ON condition, next two are for expressions that define fields to select.

var items = conn.Select<CustomItem>(expr);

Redis

ServiceStack.Redis is an Open Source C# Redis client containing methods and extensions to cooperate with Redis database — one of the fastest and most feature-rich NoSQL DBs.

Some application components don’t require complicated DB structure and all the features proposed by SQL. In this case it would be appropriate to handle their DB communications by something fast and lightweight. That’s where Redis shows itself best.

Redis has got unofficial Windows support and it’s possible to run it on win-based OS as a service. To connect Redis to a project RedisClient class from ServiceStack library is required. Usually it should be injected to be used in the same way as IdbConnection implementations. Then the following basic operations can be performed:

redisClient.Add("key", "value");
redisClient.GetValue("key");

It is possible to operate data using different containers. For example, here is the way to get hashes:

var client = redisClient.As<string>();

var hash = client.GetHash<string>("hashkey");

hash.Add("key", "value");
var allValues = hash.GetAll();

But one of the most convenient Redis’ features is Redis Lists. ServiceStack Redis proposes a layer to work with Redis DB as if it was an SQL one, creates business objects and works with typed clients. First, let’s define our business object:

public class Item
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public int Value { get; set; }
}

The only required part here is Id, which will be used as a primary key. Notice that it’s possible to use any property types common to POCO entities. Client transforms data into the text to store and then retrieves it, casting to correct type automatically. Here’s how calling typed client and reading/writing data looks like:

var client = redisClient.As<Item>();
var key = Guid.NewGuid();
var item = new Item { Id = key, Name = "Name", Value = 5 };
client.Store(item);

var readItem = client.GetById(key);

The last one feature we would like to mention is setting expiration time which causes record in Redis to be removed automatically after some period of time, or on a particular date and time. It is useful for storing temporary content, confirmation keys, security items, etc.:

// Setting item to be deleted after 30 minutes
client.ExpireIn(item.Id, TimeSpan.FromMinutes(30));

ServiceStack products are relatively new and evolve fast, but you can already use a stable versions. Currently the latest version is version 4th, which is paid for commercial use, but the 3rd one is available for free. There is source code and wiki on GitHub, active community and a lot of examples and discussions around the Web, so usually it doesn’t take long to solve any questions.

References

  1. https://servicestack.net/ – ServiceStack homesite.
  2. https://github.com/ServiceStack/ServiceStack.OrmLite — OrmLite on GitHub.
  3. https://github.com/ServiceStack/ServiceStack.Redis — Redis on GitHub.
  4. https://github.com/ServiceStack/ServiceStack/wiki — ServiceStack wiki.
  5. https://github.com/SamSaffron/dapper-dot-net — Dapper performance tests.