Getting Started - Data Access

Different ways for getting started with ServiceStack: Project templates, Walkthroughs, Docs and Videos, choose what's best for you

Introducing OrmLite

OrmLite's goal is to provide a convenient, DRY, config-free, RDBMS-agnostic typed wrapper that retains a high affinity with SQL, exposing intuitive APIs that generate predictable SQL and maps cleanly to (DTO-friendly) disconnected POCO's.

This approach makes easier to reason-about your data access making it obvious what SQL is getting executed at what time, whilst mitigating unexpected behavior, implicit N+1 queries and leaky data access prevalent in Heavy ORMs.

In OrmLite: 1 Class = 1 Table. There should be no surprising or hidden behaviour, the Typed API that produces the Query doesn't impact how results get intuitively mapped to the returned POCO's which could be different to the POCO used to create the query, e.g. containing only a subset of the fields you want populated.

Learn more

Supported Databases

OrmLite supports most popular RDBMS platforms including:

  • PostgreSQL
  • Microsoft SQL Server
  • MySQL
  • Sqlite

When registering your IDbConnectionFactory in your IoC, you can specify which Provider to use. These providers are available in the respective NuGet packages.

These packages contain both .NET Framework v4.5> and .NET Standard 2.0 versions and supports both .NET Framework and .NET Core projects.

Learn more 
[assembly: HostingStartup(typeof(MyApp.ConfigureDb))]

namespace MyApp;

public class ConfigureDb : IHostingStartup
{
  public void Configure(IWebHostBuilder builder) => builder
      .ConfigureServices((context, services) => {

          services.AddSingleton<IDbConnectionFactory>(new OrmLiteConnectionFactory(
              context.Configuration.GetConnectionString("DefaultConnection"),
              PostgreSqlDialect.Provider));
          
          // Enable built-in Database Admin UI at /admin-ui/database
          services.AddPlugin(new AdminDatabaseFeature());
      });
}

API Overview

OrmLite makes available most of its functionality via extension methods to add enhancments over ADO.NET's IDbConnection, providing a Typed RDBMS-agnostic API that transparently handles differences in each supported RDBMS provider.

OrmLite public API's also have async equivalents of the same name and an additional conventional *Async suffix. The Async API's also take an optional CancellationToken, where you just need to add the Async suffix and await keyword.

Whilst OrmLite aims to provide a light-weight typed wrapper around SQL, it offers a number of convenient features that makes working with RDBMS's a clean and enjoyable experience, including:

  • Typed SqlExpression support for JOINs
  • References by attributes
  • Self References
  • Merge results
  • Nested JOIN table expressions
Learn more
OrmLite API overview

Example queries

Strongly typed LINQ query expressions give an intuitive way to query your database. Equal, GreaterThan, LessThan translate as expected as well as server side utility functions like StartsWith, EndsWith and Contains.

Sql.In enables the ability to easily perform IN queries with static lists. Other helpful functions like aggregations of SUM, AVG, MIN and MAX are also easily accessible.

Learn more 
var dbFactory = HostContext.Resolve<IDbConnectionFactory>();
using var db = dbFactory.OpenDbConnection();

int year = DateTime.Today.AddYears(-20).Year;
db.Select<Author>(x => x.Birthday >= new DateTime(year,1,1)
                   && x.Birthday <= new DateTime(year,12,31));
                   
db.Select<Author>(x => Sql.In(x.City, "London","Madrid","Rome"))

db.Select<Author>(x => x.Earnings <= 50);

db.Select<Author>(x => x.Name.StartsWith("A"));

db.Select<Author>(x => x.Name.EndsWith("garzon"));

db.Select<Author>(x => x.Name.Contains("Benedict"));

//implicit Server SQL string casting
db.Select<Author>(x => x.Rate.ToString() == "10"); 

//Server SQL string concatenation
db.Select<Author>(x => "Rate " + x.Rate == "Rate 10");
Author.cs 
public class Author
{
   public Author(){}
   [AutoIncrement]
   [Alias("AuthorID")]
   public Int32 Id { get; set;}
   [Index(Unique = true)]
   [StringLength(40)]
   public string Name { get; set;}
   public DateTime Birthday { get; set;}
   public DateTime ? LastActivity  { get; set;}
   public Decimal? Earnings { get; set;}  
   public bool Active { get; set; } 
   [StringLength(80)]
   [Alias("JobCity")]
   public string City { get; set;}
   [StringLength(80)]
   [Alias("Comment")]
   public string Comments { get; set;}
   public Int16 Rate { get; set;}
}
Fundamentals
Previous