CTS – Your Technology Partner

Getting Started with ServiceStack Part 2

Written by Lydon Bergin on May 7, 2014

Creating a Data Access Layer using OrmLite

Introduction

In Part 1, we installed and configured the ServiceStack Web Services Framework and ServiceStack OrmLite. In Part 2 we will create a simple data access layer using ServiceStack’s OrmLite ORM.

OrmLite is a code-first ORM, which means that we as developers will write simple POCO (Plain-Old CLR Object) classes, which map 1:1 with database tables. This means that we will not be manually creating the tables in our database; OrmLite will handle that for us!

NOTE: Although OrmLite is designed as a code-first ORM, you can use it as a database-first ORM by using T4 Text Templates to generate POCO classes from existing database tables.

Create a Person Class

For this post, we will only use a single POCO class, which will map to a single database table. Create a new class in your solution called Person.cs.

using ServiceStack.DataAnnotations;

 

namespace ServiceStack.QuickStart

{

    publicclassPerson

    {

        [AutoIncrement]

        publicint ID { get; set; }

        publicstring FirstName { get; set; }

        publicstring LastName { get; set; }

        publicstring EmailAddress { get; set; }

    }

}

Notice the [AutoIncrement] annotation (part of the ServiceStack.DataAnnotations namespace) – this marks the column as an auto-increment identity column in the database. Next, we will use OrmLite to create a new database table that matches the definition of our Person class.

Update the ApplicationHost.cs Class

Now that we have created our Person class, we need to let OrmLite know that we want to have that class persisted to the database as a table. As with the Dependency Injection container in Part 1, the developers of ServiceStack recommend that this is done in the AppHostHttpListenerBase.Configure method. Navigate to the Configure method in your ApplicationHost.cs file and modify it to match this code:

 

        /// <summary>

        /// This method is used to configure things like Inversion-of-Control Containers

        /// </summary>

        /// <param name=“container”>Inversion of control container</param>

        publicoverridevoid Configure(Funq.Container container)

        {

            // Add our IDbConnectionFactory to the container, this will

            // allow all of our services to share a single connection factory

            container.Register<IDbConnectionFactory>

                (newOrmLiteConnectionFactory

                    (Properties.Settings.Default.LocalSQLConnectionString,

                    SqlServerOrmLiteDialectProvider.Instance));

 

            // Below we refer to the connection factory that we just registered

            // with the container and use it to create our table(s).

            using (var db = container.Resolve<IDbConnectionFactory>().Open())

            {

                // We’re just creating a single table, but you could add

                // as many as you need.  Also note the “overwrite: false” parameter,

                // this will only create the table if it doesn’t already exist.

                db.CreateTable<Person>(overwrite: false);

            }

        }

Notice that we are using the container.Resolve method to access the IDbConnectionFactory that we had previously registered with the Funq Container. When the Configure method is called (each time the application starts), the db.CreateTable<Person> method will be called, and OrmLite will create a Person table in the SQL Server database that we created which matches the layout of our Person class. By passing the “overwrite: false” parameter, we tell OrmLite to only create the Person table if it does not already exist.

Now, run the application. You will see the same ServiceStack metadata page as before, but when you stop the application there will be a new Person table in the SQL Server Database!

clip_image002

Create Sample Data

At this point, we don’t have any data in our table so let’s go ahead and add some seed data for our testing. Right-Click on the Person table in Server Explorer and click the Show Table Data option, and create some sample data as I have done below:

clip_image004

Create a Data Worker Class

Next, we will create a class that will handle the database CRUD functions for our service using OrmLite for database access. Add a new class to your solution called PersonDataWorker:

using System.Collections.Generic;

using ServiceStack.OrmLite;

 

namespace ServiceStack.QuickStart

{

    /// <summary>

    /// A helper class that uses the ServiceStack.OrmLite Object Relational Mapper

    /// to support CRUD operations on the Person table.

    /// </summary>

    publicclassPersonDataWorker

    {

        // The IDbConnection passed in from the IOC container on the service

        System.Data.IDbConnection _dbConnection;

 

        // Store the database connection passed in

        public PersonDataWorker(System.Data.IDbConnection dbConnection)

        {

            _dbConnection = dbConnection;

        }

 

        // Inserts a new row into the Person table

        publicint AddPerson(Person p)

        {

            return (int)_dbConnection.Insert<Person>(p, selectIdentity: true);

        }

 

        // Return a list of people from our DB

        // (this is the equivilent of “SELECT * FROM Person”)

        publicList<Person> GetPeopleList()

        {

            return _dbConnection.Select<Person>();

        }

 

        // Return a single person given their ID

        publicPerson GetPersonByID(int id)

        {

            return _dbConnection.SingleById<Person>(id);

        }

 

        // Updates a row in the Person table. Note that this call updates

        // all fields, in order to update only certain fields using OrmLite,

        // use an anonymous type like the below line, which would only

        // update the FirstName and LastName fields:

        // _dbConnection.Update(new { FirstName = “Gene”, LastName = “Rayburn” });

        publicPerson UpdatePerson(Person p)

        {

            _dbConnection.Update<Person>(p);

            return p;

        }

 

        // Deletes a row from the Person table

        publicint DeletePersonByID(int id)

        {

            return _dbConnection.Delete(id);

        }

    }

}

OrmLite is implemented as a set of Extension methods in the System.Data.* classes. More information about all available extension methods can be found on the ServiceStack GitHub Page. The class above uses the Select and SingleById methods for reading data from the Person table; and the Insert, Update, and Delete methods to alter rows on the Person table.

In Part 3 we will use the PersonDataWorker class to read and write to the database from our web services.

Conclusion

In Part 1, we installed and configured the ServiceStack Web Services Framework and ServiceStack OrmLite. In Part 2 we created a simple data access layer using ServiceStack’s OrmLite ORM. In Part 3 of this post, we will create a few web services that use our data access layer to create a full Web API for our Person class!

Comments

comments