Simple ServiceStack OrmLite Example

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 (http://www.nuget.org).  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

 

db.CreateTableIfNotExists<Note>();

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
db.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);
}
Console.ReadLine();

Full Code

Program.cs

Note.cs

7 thoughts on “Simple ServiceStack OrmLite Example

  1. Hi Robert,

    Nice article! Which I’ve also just added to https://github.com/ServiceStack/ServiceStack/wiki/Community-Resources 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:

                db.CreateTableIfNotExists<Note>();
    

    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()) { 
            ...
        }
    
  2. 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
      Source=System.Data.SQLite
      ErrorCode=1
      StackTrace:
           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()
      InnerException: 
    

  3. 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" });
    
  4. 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 asp.net 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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>