ServiceStack Database first example

Question

I would like to start new project using Servicestack with existing database (SQL Server) .I really like and also learning from pluralsight servicstack tutorial .I would like to request some help as currently facing some challenges as i am new , I am looking for example/tutorial to get me start with POCO with existing table mapping . very basic example with one table and one service request will enough for me to get started .

Thanks

Answer 1

First to get an overview of OrmLite features I recommend reading the docs on OrmLite's Homepage.

To get started with OrmLite in ServiceStack first install the preferred OrmLite provider from NuGet, e.g to use an InMemory database just install Sqlite from NuGet:

Install-Package ServiceStack.OrmLite.Sqlite.Mono

* OrmLite.Sqlite.Mono supports both Windows and Mono, for a Windows only x86/x64 Sqlite you can use OrmLite.Sqlite.Windows. For SQL Server install OrmLite.SqlServer instead.

Then to use OrmLite in ServiceStack you just need to register a DB Connection Factory with the RDBMS provider and connection string you want to use:

public void Configure(Container container)
{
    container.Register<IDbConnectionFactory>(c => 
        new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider));
}

By default OrmLite supports POCO's that map 1:1 to RDBMS tables, so a simple class like:

public class Simple
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? Age { get; set; }
}

Can be created with:

using (var db = container.Resolve<IDbConnectionFactory>().Open())
{
    //Drops (if exists) and re-creates SimpleTable
    db.DropAndCreateTable<Simple>();  
}

Which will create an RDBMS table with the following definition:

CREATE TABLE "Simple" 
(
  "Id" INTEGER PRIMARY KEY, 
  "Name" VARCHAR(8000) NULL, 
  "Age" INTEGER NULL 
); 

You can further use attributes to customize how the table is created, e.g:

[Alias("CustomTable")]
public class Simple
{
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    [Alias("CustomAge")]
    public int Age { get; set; }
}

Which will generate the following SQL:

CREATE TABLE "CustomTable" 
(
  "Id" INTEGER PRIMARY KEY AUTOINCREMENT, 
  "Name" VARCHAR(100) NOT NULL, 
  "CustomAge" INTEGER NOT NULL 
); 

OrmLite also continues to use the custom Aliases in future API's.

In ServiceStack you can access OrmLite from the ADO.NET Db connection in the Service base class. As OrmLite tables are just POCO's you can if you wish re-use them as Request DTO's (although it's recommended to have purpose-specific Request DTO's).

E.g. this ServiceStack Service below returns a single Simple row if Id is provided, otherwise returns all Simple entries:

public class MyServices : Service
{
    public object Any(Simple request)
    {
        return request.Id != default(int)
            ? Db.SingleById<Simple>(request.Id)
            : Db.Select<Simple>();
    }
}

Most of ServiceStack example projects utilize OrmLite, to get started I recommend going through these well documented examples first:

• Email Contacts
• Http Benchmarks