ServiceStack OrmLite and Redis for handling DB communication
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 of 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 lightweight 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:
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 the database .NET IdbConnection is used. You don’t need to code custom logic for some specific connection type.
The next thing is the setting of the 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.
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 the 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 the Redis database — one of the fastest and most feature-rich NoSQL DBs.
Some application components don’t require a complicated DB structure and all the features proposed by SQL. In this case, it would be appropriate to handle their DB communications with 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 the 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. The client transforms data into the text to store and then retrieves it, casting it to the correct type automatically. Here’s what 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 feature we would like to mention is setting expiration time which causes records 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 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, an active community, and a lot of examples and discussions around the Web, so usually it doesn’t take long to solve any questions.
References
.NET Development
Don’t underestimate the power of the right technology that the right .NET development company