SDL Tridion Consulting and Solutions
Header image
viagra online buy cialis viagra online cialis onlinecialis buy viagra onlinebuy cialis buy viagra online generic viagra

Simple ServiceStack OrmLite Example

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 (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

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 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()) {
            ...
        }
    
  • 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:
    

  • 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 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.

  • 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: https://groups.google.com/forum/#!forum/servicestack

    Best of luck



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> <pre class="">

viagra online buy cialis viagra online cialis onlinecialis buy viagra online buy cialis buy viagra online generic viagra