OrmLite walk through example

In its simplest usage, OrmLite can persist any POCO type without any attributes required:

public class SimpleExample
	public int Id { get; set; }
	public string Name { get; set; }

//Set once before use (i.e. in a static constructor).
OrmLiteConfig.DialectProvider = SqliteDialect.Provider;

using (IDbConnection db = "/path/to/db.sqlite".OpenDbConnection())
	db.Insert(new SimpleExample { Id=1, Name="Hello, World!"});
	var rows = db.Select<SimpleExample>();

	Assert.That(rows, Has.Count(1));
	Assert.That(rows[0].Id, Is.EqualTo(1));

To get a better idea of the features of OrmLite lets walk through a complete example using sample tables from the Northwind database. _ (Full source code for this example is available here.) _

So with no other configuration using only the classes below:

public class Shipper
	: IHasId<int>
	public int Id { get; set; }

	[Index(Unique = true)]
	public string CompanyName { get; set; }

	public string Phone { get; set; }

	public int ShipperTypeId { get; set; }

public class ShipperType
	: IHasId<int>
	public int Id { get; set; }

	[Index(Unique = true)]
	public string Name { get; set; }

public class SubsetOfShipper
	public int ShipperId { get; set; }
	public string CompanyName { get; set; }

public class ShipperTypeCount
	public int ShipperTypeId { get; set; }
	public int Total { get; set; }

Creating tables

Creating tables is a simple 1-liner:

using (IDbConnection db = ":memory:".OpenDbConnection())

/* In debug mode the line above prints:
  "Name" VARCHAR(40) NOT NULL 
DEBUG: CREATE UNIQUE INDEX uidx_shippertypes_name ON "ShipperTypes" ("Name" ASC);
  "CompanyName" VARCHAR(40) NOT NULL, 
  "Phone" VARCHAR(24) NULL, 
  "ShipperTypeId" INTEGER NOT NULL, 

  CONSTRAINT "FK_Shippers_ShipperTypes" FOREIGN KEY ("ShipperTypeId") REFERENCES "ShipperTypes" ("ShipperID") 
DEBUG: CREATE UNIQUE INDEX uidx_shippers_companyname ON "Shippers" ("CompanyName" ASC);

Transaction Support

As we have direct access to IDbCommand and friends - playing with transactions is easy:

var trainsType = new ShipperType { Name = "Trains" };
var planesType = new ShipperType { Name = "Planes" };

//Playing with transactions
using (IDbTransaction dbTrans = db.OpenTransaction())


using (IDbTransaction dbTrans = db.OpenTransaction(IsolationLevel.ReadCommitted))
    db.Insert(new ShipperType { Name = "Automobiles" });
    Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(3));
Assert.That(db.Select<ShipperType>(), Has.Count(2));

CRUD Operations

No ORM is complete without the standard crud operations:

	//Performing standard Insert's and Selects
  db.Insert(new Shipper { CompanyName = "Trains R Us", Phone = "555-TRAINS", ShipperTypeId = trainsType.Id });
  db.Insert(new Shipper { CompanyName = "Planes R Us", Phone = "555-PLANES", ShipperTypeId = planesType.Id });
  db.Insert(new Shipper { CompanyName = "We do everything!", Phone = "555-UNICORNS", ShipperTypeId = planesType.Id });

  var trainsAreUs = db.Single<Shipper>("ShipperTypeId = @Id", new { trainsType.Id });
  Assert.That(trainsAreUs.CompanyName, Is.EqualTo("Trains R Us"));
  Assert.That(db.Select<Shipper>("CompanyName = @company OR Phone = @phone", 
        new { company = "Trains R Us", phone = "555-UNICORNS" }), Has.Count.EqualTo(2));
  Assert.That(db.Select<Shipper>("ShipperTypeId = @Id", new { planesType.Id }), Has.Count.EqualTo(2));

  //Lets update a record
  trainsAreUs.Phone = "666-TRAINS";
          Assert.That(db.SingleById<Shipper>(trainsAreUs.Id).Phone, Is.EqualTo("666-TRAINS"));
  //Then make it dissappear
          Assert.That(db.SingleById<Shipper>(trainsAreUs.Id), Is.Null);

  //And bring it back again

Performing custom queries

And with access to raw sql when you need it - the database is your oyster 😃

var partialColumns = db.Select<SubsetOfShipper>(typeof(Shipper), 
    "ShipperTypeId = @Id", new { planesType.Id });
Assert.That(partialColumns, Has.Count.EqualTo(2));

//Select into another POCO class that matches sql
var rows = db.Select<ShipperTypeCount>(
    "SELECT ShipperTypeId, COUNT(*) AS Total FROM Shippers GROUP BY ShipperTypeId ORDER BY COUNT(*)");

Assert.That(rows, Has.Count.EqualTo(2));
Assert.That(rows[0].ShipperTypeId, Is.EqualTo(trainsType.Id));
Assert.That(rows[0].Total, Is.EqualTo(1));
Assert.That(rows[1].ShipperTypeId, Is.EqualTo(planesType.Id));
Assert.That(rows[1].Total, Is.EqualTo(2));

//And finally lets quickly clean up the mess we've made:

Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(0));
Assert.That(db.Select<ShipperType>(), Has.Count.EqualTo(0));

Soft Deletes

Select Filters let you specify a custom SelectFilter that lets you modify queries that use SqlExpression<T> before they're executed. This could be used to make working with "Soft Deletes" Tables easier where it can be made to apply a custom x.IsDeleted != true condition on every SqlExpression.

By either using a SelectFilter on concrete POCO Table Types, e.g:

SqlExpression<Table1>.SelectFilter = q => q.Where(x => x.IsDeleted != true);
SqlExpression<Table2>.SelectFilter = q => q.Where(x => x.IsDeleted != true);

Or alternatively using generic delegate that applies to all SqlExpressions, but you'll only have access to a IUntypedSqlExpression which offers a limited API surface area but will still let you execute a custom filter for all SqlExpression<T> that could be used to add a condition for all tables implementing a custom ISoftDelete interface with:

OrmLiteConfig.SqlExpressionSelectFilter = q =>
    if (q.ModelDef.ModelType.HasInterface(typeof(ISoftDelete)))
        q.Where<ISoftDelete>(x => x.IsDeleted != true);

Both solutions above will transparently add the x.IsDeleted != true to all SqlExpression<T> based queries so it only returns results which aren't IsDeleted from any of queries below:

var results = db.Select(db.From<Table>());
var result = db.Single(db.From<Table>().Where(x => x.Name == "foo"));
var result = db.Single(x => x.Name == "foo");

Check Constraints

OrmLite includes support for SQL Check Constraints which will create your Table schema with the [CheckConstraint] specified, e.g:

public class Table
    public int Id { get; set; }

    [CheckConstraint("Age > 1")]
    public int Age { get; set; }

    [CheckConstraint("Name IS NOT NULL")]
    public string Name { get; set; }

Bitwise operators

The Typed SqlExpression bitwise operations support depends on the RDBMS used.

E.g. all RDBMS's support Bitwise And and Or operators:

db.Select<Table>(x => (x.Flags | 2) == 3);
db.Select<Table>(x => (x.Flags & 2) == 2);

All RDBMS Except for SQL Server support bit shift operators:

db.Select<Table>(x => (x.Flags << 1) == 4);
db.Select<Table>(x => (x.Flags >> 1) == 1);

Whilst only SQL Server and MySQL Support Exclusive Or:

db.Select<Table>(x => (x.Flags ^ 2) == 3);