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

Robert Curlette

Initiated into the Tridion ways at a young age, Robert has traveled the world using his Tridion knowledge to help other new Tridion initiates learn the secrets of building websites with the Tridion CMS. He also organizes the Tridion Developer Summit, where the Tridion community gets together to share their stories and experience.

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>