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