Simple ServiceStack OrmLite Example

January 4th, 2013 | Posted by Robert Curlette in OrmLite | ServiceStack

I wanted to create a simple app to store data using the elegant OrmList from ServiceStack.  During this process I found a couple of things that cost me some time and I wanted to share these with you.  In this article I will walk through creating a small and simple ServiceStack OrmLite example.  It should take around 5 minutes to complete the example.

Please see the excellent OrmLite official docs for more examples.

Update Jan 6, 2013:  Code samples updated with the great feedback in the comments from Demis Bellot.

Step 1:  Create a Console App and Reference OrmLite.SQLite

– Create a Console app named OrmLiteExample

– Add the OrmLite references from NuGet (  Right-click on the References in the Solution Explorer, Manage NuGet Packages, Online, OrmLite.  Or, from the Package Manager Console (View, Other Windows, Package Manager Console) type ‘Install-Package ServiceStack.OrmLite.Sqlite32’.

Step 2:  Create the DTO (aka Model)

This is the nicest part of working with ServiceStack.  Everything is DTO and Model driven – no config files to create and the code is simple and concise.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServiceStack.DataAnnotations;

namespace OrmLiteExample
    class Note
        [AutoIncrement] // Creates Auto primary key
        public int Id { get; set; }

        public string SchemaUri { get; set; }
        public string NoteText { get; set; }
        public DateTime? LastUpdated { get; set; }
        public string UpdatedBy { get; set; }

Step 3:  In program.cs, add the Using statements

using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;

The using statement ServiceStack.OrmLite.Sqlite will throw an exception if you build now.

Fix:  With NuGet, download and install ServiceStack.OrmLite.Sqlite32 package. Also, go to the properties of your project and make sure the target framework is set to .NET framework 4.  Solved!

Step 4:  Add the connection strings for SqLite

Thanks to the nice Unit Tests in the ServiceStack project I was able to use their example.

public static string SqliteMemoryDb = ":memory:";
// Updated per comment form Jonas.  First connection string below causes an app pool restart!  Use MapHostAbsolutePath instead!
//public static string SqliteFileDb = "~/App_Data/db.sqlite".MapAbsolutePath();  
public static string SqliteFileDb = "~/App_Data/db.sqlite".MapHostAbsolutePath();
Problem:  The MapAbsolutePath is not found.
Fix: Include the missing library:

using ServiceStack.Common.Utils;

Step 5:  Create the connection

//Using Sqlite DB- improved
var dbFactory = new OrmLiteConnectionFactory(
                SqliteFileDb, false, SqliteDialect.Provider);

// Wrap all code in using statement to not forget about using db.Close()
using (var db = dbFactory.Open()) {

Step 6: Create the Table



Step 7:  Insert a record

Note:  This is different than the excellent online examples.  I received a warning message to use the Connection instead of the Command object.

// Insert
	new Note {
		SchemaUri = "tcm:0-0-0",
		NoteText = "Hello world 5",
		LastUpdated = new DateTime(2013, 1, 5),
		UpdatedBy = "RC" });

Step 8:  Read the data

 // Read
var notes = db.Where<Note>(new { SchemaUri = "tcm:0-0-0" });
foreach (Note note in notes)
	Console.WriteLine("note id=" + note.Id + "noteText=" + note.NoteText);

Full Code



You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

7 Responses

  • Hi Robert,

    Nice article! Which I’ve also just added to page.

    Though there are a couple of things I would do differently to make the code a little more terse and read a little better…

    You can use the shorter ‘SqliteDialect.Provider’, e.g.

                var dbFactory = new OrmLiteConnectionFactory(
                    SqliteFileDb, false, SqliteDialect.Provider);

    You can use the shorter `Open()` (Extension method) alias which I like to now declare in a ‘db’ variable:

                IDbConnection db = dbFactory.Open();

    You no longer need to create a IDbCommand since all methods are now off IDbConnection:

                //IDbCommand dbCmd = dbConn.CreateCommand();

    If it saves you from needing a comment you can use the more explicit:


    You may want to use the filter or the parameterized Where/Query API instead (prevent sql injection from user-defined values), e.g:

                var notes = db.Select<Note>("SchemaUri={0}", "tcm:0-0-0");
                var notes = db.Where<Note>(new { SchemaUri = "tcm:0-0-0" });
                var notes = db.Query<Note>("SchemaUri=@schemaUri", new { schemaUri = "tcm:0-0-0" });

    And to not forget about db.Close(), you can put the whole thing in a using, e.g:

        using (var db = dbFactory.Open()) { 
  • Hi Demis,

    Thanks a lot for the great feedback! I incorporated the better code samples into the article. However, I was unable to get the db.Query… working. The error I get is maybe related to SQLite? I also tried this with no luck:

    var notes = db.Query<Note>("SchemaUri=@schemaUri", new { schemaUri = new SQLiteParameter("tcm:0-0-0", DbType.String) });
    System.Data.SQLite.SQLiteException was unhandled
      Message=SQL logic error or missing database
    near "SchemaUri": syntax error
           at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
           at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
           at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
           at System.Data.SQLite.SQLiteDataReader.NextResult()
           at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
           at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
           at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
           at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
           at ServiceStack.OrmLite.OrmLiteReadExtensions.Query[T](IDbCommand dbCmd, String sql, Object anonType) in c:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteReadExtensions.cs:line 381
           at ServiceStack.OrmLite.OrmLiteReadConnectionExtensions.<>c__DisplayClass42`1.<Query>b__41(IDbCommand dbCmd) in c:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteReadConnectionExtensions.cs:line 164
           at ServiceStack.OrmLite.ReadConnectionExtensions.Exec[T](IDbConnection dbConn, Func`2 filter) in c:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\Expressions\ReadConnectionExtensions.cs:line 31
           at ServiceStack.OrmLite.OrmLiteReadConnectionExtensions.Query[T](IDbConnection dbConn, String sql, Object anonType) in c:\src\ServiceStack.OrmLite\src\ServiceStack.OrmLite\OrmLiteReadConnectionExtensions.cs:line 164
           at OrmLiteExample.Program.Main(String[] args) in c:\rc\code\guiExtension\OrmLiteExample\OrmLiteExample\Program.cs:line 37
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()

  • Cool, I’ll add Sqlite/db.Query to my TODO list to try.

  • Cool, just tracked the issue, Query* APIs needed the full SQL Statement, e.g:

        var notes = db.Query<Note>("SELECT * FROM Note WHERE SchemaUri=@schemaUri", 
             new { schemaUri = "tcm:0-0-0" });

    But I just checked in a fix so in the next release this now works:

        var notes = db.Query<Note>("SchemaUri=@schemaUri", new { schemaUri = "tcm:0-0-0" });
  • Jonas Eriksson says:

    Thanks for a nice blog post! I stumbled on a thing I thought I’d let you know. I got terrible insert performance inside my app for some reason. And then I noticed the sqlite path was /bin/App_Data – so each change caused the appool to restart = slow. Simple fix : use MapHostAbsolutePath for asp net hosted usage.

  • Theo Medeiros says:

    Is it possible to call a stored procedure using ServiceStack Sqlite?

  • Sorry, I haven’t tried this before. I would suggest asking in the Google or G+ group here or on StackOverflow:!forum/servicestack

    Best of luck

Leave a Reply

Your email address will not be published. Required fields are marked *