servicestack-ormlite-usage

name	servicestack-ormlite-usage
description	Uses OrmLite via the implicit Db connection and understands transaction semantics and rollback behavior.

ServiceStack OrmLite Usage

OrmLite is a lightweight Micro-ORM that maps POCOs to database tables.

Data Models (POCOs)

• Map 1 class to 1 table.
• Use attributes for schema control: [Alias], [PrimaryKey], [AutoIncrement], [Index], [Required], [StringLength].
• Use [Reference] for relationships (one-to-one, one-to-many).

public class Customer
{
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
}

Using the Db Connection

In a Service class, the Db property is automatically available and managed.

• Querying: Db.Select<Customer>(), Db.SingleById<Customer>(1), Db.LoadSelect<Customer>(c => c.Name.StartsWith("A")).
• Modifying: Db.Insert(new Customer { ... }), Db.Update(customer), Db.DeleteById<Customer>(1).

Transactions

• Always use using blocks for transactions.
• Transactions are automatically rolled back if not committed.

using var trans = Db.OpenTransaction();
try {
    Db.Insert(obj1);
    Db.Insert(obj2);
    trans.Commit();
} catch (Exception) {
    // trans.Rollback() is called implicitly on Dispose if Commit() wasn't called
    throw;
}

Best Practices

• POCO Reuse: OrmLite models are often clean enough to be reused as Response DTOs, but be careful with leaking sensitive fields. Use [IgnoreDataMember] or discrete DTOs if necessary.
• Naming Conventions: OrmLite uses strict naming (unless [Alias] is used). Match your POCO property names to DB column names.
• Async API: Always use Async variants (e.g., Db.SelectAsync, Db.InsertAsync) in high-concurrency environments.