OrmLite API Overview

OrmLite's APIs are minimal, providing basic shortcuts for the primitive SQL statements:

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

Create Tables Schemas

OrmLite is able to CREATE, DROP and ALTER RDBMS Tables from your code-first Data Models with rich annotations for controlling how the underlying RDBMS Tables are constructed.

The Example below utilizes several annotations to customize the definition and behavior of RDBMS tables based on a POCOs public properties:

public class Player
{
    public int Id { get; set; }                     // 'Id' is PrimaryKey by convention

    [Required]
    public string FirstName { get; set; }           // Creates NOT NULL Column

    [Alias("Surname")]                              // Maps to [Surname] RDBMS column
    public string LastName { get; set; }

    [Index(Unique = true)]                          // Creates Unique Index
    public string Email { get; set; }

    public List<Phone> PhoneNumbers { get; set; }   // Complex Types blobbed by default

    [Reference]
    public List<GameItem> GameItems { get; set; }   // 1:M Reference Type saved separately

    [Reference]
    public Profile Profile { get; set; }            // 1:1 Reference Type saved separately
    public int ProfileId { get; set; }              // 1:1 Self Ref Id on Parent Table

    [ForeignKey(typeof(Level), OnDelete="CASCADE")] // Creates ON DELETE CASCADE Constraint
    public Guid SavedLevelId { get; set; }          // Creates Foreign Key Reference

    public ulong RowVersion { get; set; }           // Optimistic Concurrency Updates
}

public class Phone                                  // Blobbed Type only
{
    public PhoneKind Kind { get; set; }
    public string Number { get; set; }
    public string Ext { get; set; }
}

public enum PhoneKind
{
    Home,
    Mobile,
    Work,
}

[Alias("PlayerProfile")]                            // Maps to [PlayerProfile] RDBMS Table
[CompositeIndex(nameof(Username), nameof(Region))]  // Creates Composite Index
public class Profile
{
    [AutoIncrement]                                 // Auto Insert Id assigned by RDBMS
    public int Id { get; set; }

    public PlayerRole Role { get; set; }            // Native support for Enums
    public Region Region { get; set; }
    public string Username { get; set; }
    public long HighScore { get; set; }

    [Default(1)]                                    // Created in RDBMS with DEFAULT (1)
    public long GamesPlayed { get; set; }

    [CheckConstraint("Energy BETWEEN 0 AND 100")]   // Creates RDBMS Check Constraint
    public short Energy { get; set; }

    public string ProfileUrl { get; set; }
    public Dictionary<string, string> Meta { get; set; }
}

public enum PlayerRole                              // Enums saved as strings by default
{
    Leader,
    Player,
    NonPlayer,
}

[EnumAsInt]                                         // Enum Saved as int
public enum Region
{
    Africa = 1,
    Americas = 2,
    Asia = 3,
    Australasia = 4,
    Europe = 5,
}

public class GameItem
{
    [PrimaryKey]                                    // Specify field to use as Primary Key
    [StringLength(50)]                              // Creates VARCHAR COLUMN
    public string Name { get; set; }

    public int PlayerId { get; set; }               // Foreign Table Reference Id

    [StringLength(StringLengthAttribute.MaxText)]   // Creates "TEXT" RDBMS Column 
    public string Description { get; set; }

    [Default(OrmLiteVariables.SystemUtc)]           // Populated with UTC Date by RDBMS
    public DateTime DateAdded { get; set; }
}

public class Level
{
    public Guid Id { get; set; }                    // Unique Identifier/GUID Primary Key
    public byte[] Data { get; set; }                // Saved as BLOB/Binary where possible
}

We can drop the existing tables and re-create the above table definitions with:

using var db = dbFactory.Open();

if (db.TableExists<Level>())
    db.DeleteAll<Level>();                      // Delete ForeignKey data if exists

//DROP and CREATE ForeignKey Tables in dependent order
db.DropTable<Player>();
db.DropTable<Level>();
db.CreateTable<Level>();
db.CreateTable<Player>();

//DROP and CREATE tables without Foreign Keys in any order
db.DropAndCreateTable<Profile>();
db.DropAndCreateTable<GameItem>();

var savedLevel = new Level
{
    Id = Guid.NewGuid(),
    Data = new byte[]{ 1, 2, 3, 4, 5 },
};
db.Insert(savedLevel);

var player = new Player
{
    Id = 1,
    FirstName = "North",
    LastName = "West",
    Email = "north@west.com",
    PhoneNumbers = new List<Phone>
    {
        new Phone { Kind = PhoneKind.Mobile, Number = "123-555-5555"},
        new Phone { Kind = PhoneKind.Home,   Number = "555-555-5555", Ext = "123"},
    },
    GameItems = new List<GameItem>
    {
        new GameItem { Name = "WAND", Description = "Golden Wand of Odyssey"},
        new GameItem { Name = "STAFF", Description = "Staff of the Magi"},
    },
    Profile = new Profile
    {
        Username = "north",
        Role = PlayerRole.Leader,
        Region = Region.Australasia,
        HighScore = 100,
        GamesPlayed = 10,
        ProfileUrl = "https://www.gravatar.com/avatar/205e460b479e2e5b48aec07710c08d50.jpg",
        Meta = new Dictionary<string, string>
        {
            {"Quote", "I am gamer"}
        },
    },
    SavedLevelId = savedLevel.Id,
};
db.Save(player, references: true);

This will add a record in all the above tables with all the Reference data properties automatically populated which we can quickly see by selecting the inserted Player record and all its referenced data by using OrmLite's Load APIs, e.g:

var dbPlayer = db.LoadSingleById<Player>(player.Id);

dbPlayer.PrintDump();

Which uses the Dump Utils to quickly display the populated data to the console:

{
    Id: 1,
    FirstName: North,
    LastName: West,
    Email: north@west.com,
    PhoneNumbers: 
    [
        {
            Kind: Mobile,
            Number: 123-555-5555
        },
        {
            Kind: Home,
            Number: 555-555-5555,
            Ext: 123
        }
    ],
    GameItems: 
    [
        {
            Name: WAND,
            PlayerId: 1,
            Description: Golden Wand of Odyssey,
            DateAdded: 2018-01-17T07:53:45-05:00
        },
        {
            Name: STAFF,
            PlayerId: 1,
            Description: Staff of the Magi,
            DateAdded: 2018-01-17T07:53:45-05:00
        }
    ],
    Profile: 
    {
        Id: 1,
        Role: Leader,
        Region: Australasia,
        Username: north,
        HighScore: 100,
        GamesPlayed: 10,
        Energy: 0,
        ProfileUrl: "https://www.gravatar.com/avatar/205e460b479e2e5b48aec07710c08d50.jpg",
        Meta: 
        {
            Quote: I am gamer
        }
    },
    ProfileId: 1,
    SavedLevelId: 7690dfa4d31949ab9bce628c34d1c549,
    RowVersion: 2
}

Feel free to continue experimenting with this Example Live on Gistlyn.

Query Examples

If your SQL doesn't start with a SELECT statement, it is assumed a WHERE clause is being provided, e.g:

var tracks = db.Select<Track>("Artist = @artist AND Album = @album",
    new { artist = "Nirvana", album = "Heart Shaped Box" });

Which is equivalent to:

var tracks = db.Select<Track>("SELECT * FROM track WHERE Artist = @artist AND Album = @album", 
    new { artist = "Nirvana", album = "Heart Shaped Box" });

Use Sql* APIs for when you want to query custom SQL that is not a SELECT statement, e.g:

var tracks = db.SqlList<Track>("EXEC GetArtistTracks @artist, @album",
    new { artist = "Nirvana", album = "Heart Shaped Box" });

Select returns multiple records:

List<Track> tracks = db.Select<Track>()

Single returns a single record:

Track track = db.Single<Track>(x => x.RefId == refId)

Dictionary returns a Dictionary made from the first two columns:

Dictionary<int, string> trackIdNamesMap = db.Dictionary<int, string>(
    db.From<Track>().Select(x => new { x.Id, x.Name }))

Dictionary<int, string> trackIdNamesMap = db.Dictionary<int, string>(
    "select Id, Name from Track")

Lookup returns an Dictionary<K, List<V>> made from the first two columns:

Dictionary<int, List<string>> albumTrackNames = db.Lookup<int, string>(
    db.From<Track>().Select(x => new { x.AlbumId, x.Name }))

Dictionary<int, List<string>> albumTrackNames = db.Lookup<int, string>(
    "select AlbumId, Name from Track")

Column returns a List of first column values:

List<string> trackNames = db.Column<string>(db.From<Track>().Select(x => x.Name))

List<string> trackNames = db.Column<string>("select Name from Track")

HashSet returns a HashSet of distinct first column values:

HashSet<string> uniqueTrackNames = db.ColumnDistinct<string>(
    db.From<Track>().Select(x => x.Name))

HashSet<string> uniqueTrackNames = db.ColumnDistinct<string>("select Name from Track")

Scalar returns a single scalar value:

var trackCount = db.Scalar<int>(db.From<Track>().Select(Sql.Count("*")))

var trackCount = db.Scalar<int>("select count(*) from Track")

Anonymous types passed into Where are treated like an AND filter:

var track3 = db.Where<Track>(new { AlbumName = "Throwing Copper", TrackNo = 3 })

SingleById(s), SelectById(s), etc provide strong-typed convenience methods to fetch by a Table's Id primary key field.

var track = db.SingleById<Track>(1);
var tracks = db.SelectByIds<Track>(new[]{ 1,2,3 });

Edit this page on GitHub

OrmLite API Overview

Create Tables Schemas​

Query Examples​

On This Page

Create Tables Schemas

Query Examples