Service-based data adapter for LLBLGen entities using ServiceStack

This post is about leveraging the LLBLGen Pro and ServiceStack tools to allow users to use the existing LLBLGen Adapter API on the client to manage, manipulate, query and update entities on remote servers, across database platforms even (SQL, MySql, Oracle, etc…). This allows LLBLGen users to fully take advantage of the query API they are familiar with and write code manipulating entities as if they were on the server.

Intro

LLBLGen is a full-service ORM that produces a statically typed entity api to work with entities on top of relational databases (SQL, MySql, Oracle, DB2, etc…). ServiceStack is an open-source services framework that makes it easy to pass data between servers, applications, on and off the web. Put together, these 2 tools can be extremely powerful. LLBLGen makes working with databases a breeze, and ServiceStack makes building service-based interfaces and applications really simple. This post is about leveraging these 2 tools to allow users to use LLBLGen apis on the client to remotely manipulate entities (full CRUD) on your server(s), across database platforms even (SQL, MySql, Oracle, etc…). Let’s jump right in.

Use-case / Scenario

The use-case / scenario is the following:

• Developers should be able to fetch, update, save, and delete entities from their desktop/laptop computers without ever logging onto any servers, and without having to learn a new API
• The business is willing to set up one or more http service endpoints for developers to connect to as long as they can secure the service endpoints appropriately

Solution

Instead of subjecting you, the reader, to a lengthy overview of the implementation details to meet the use-case described above, I’ll just post the solution right now, and leave it up to you to read the implementation details if you’re interested. So, the solution? A single assembly that contains client-side and server-side logic for transmitting LLBLGen data across the wire using ServiceStack, allowing LLBLGen developers to use the exact same API they are already familiar with on the client! I’ve named the assembly: LLBLGen.DataServices.Contrib.dll, and the source is included as a download to this post. Use it, abuse it, refactor it, improve it. The download includes:

• source code for the primary library
• 2 host applications to simulate the server: a web application host, a console application which hosts an http listener
• an NUnit test project, with sample usage code, which fires up an http listener to run CRUD and Unit of work tests
  • LLBLGen entity assemblies for the Northwind database, and SQL files to create a northwind database to run the tests

Usage

Basic CRUD:

LLBLGen developers are already familiar with the following code which allows them to interact with their entities and the database:

var customer = new CustomerEntity("CHOPS");
using (var adapter = new DataAccessAdapter())
{
    // fetch
    adapter.FetchEntity(customer);

    // modify and save
    customer.Address = "123 Mills Lane";
    adapter.SaveEntity(customer);

    // delete entity
    adapter.DeleteEntity(customer);
}

The code to do the same thing across the wire, in the new service-based paradigm looks identical:

var customer = new CustomerEntity("CHOPS");
using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345"))
{
    // fetch
    adapter.FetchEntity(ref customer);

    // modify and save
    customer.Address = "123 Mills Lane";
    adapter.SaveEntity(customer);

    // delete entity
    adapter.DeleteEntity(customer);
}

As you can see, instead of using your DataAccessAdapter, you use a wrapper adapter called DataAccessAdapterServiceWrapper, and the method calls are all essentially the same (there are just a few methods where I had to introduce a “ref” parameter).

Transaction and Unit of Work:

For transaction support, at this time, you would use the Unit of Work paradigm, with a new class “UnitOfWorkWrapper” class, which works the same way as the UnitOfWork2 class:

var uow = new UnitOfWorkWrapper();

var category = new CategoryEntity{ CategoryName = newCategoryName };
uow.AddForSave(category);
uow.AddDeleteEntitiesDirectlyCall(typeof(CategoryEntity),
  new RelationPredicateBucket(CategoryFields.CategoryName == categoryNameToDelete));

using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345"))
{
    uow.Commit(adapter);
}

Security

LLBLGen has some validation and authorization hooks that can be leveraged server-side within the entity model to create some custom fine-grained access rules. In our use-case though, the requirement is to primarily secure the endpoint, and ServiceStack has all the hooks needed for that. The DataAccessAdapterServiceWrapper wraps a ServiceStack client. To add some basic authentication to your calls, use a custom method on the data adapter:

using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345", "dev_db"))
{
    // authenticate with basic authentication
    adapter.SetCredentials(username, password);
}

Or, access the service client from ServiceStack and leverage all the hooks you need (see the authentication and autorization wiki page for ServiceStack), for example:

using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345", "dev_db"))
{
    // authenticate with iis authentication
    adapter.ServiceClient.Credentials = new System.Net.NetworkCredential(username, password, domain);
}

One service interface for all your database:

You can use one service interface for any or all of your entities across databases, making it really easy to work with data, regardless of where the data lives: MySql, SQL, Oracle, DB2, etc… You can also use the same interface if you want to work on your dev, staging, prod databases as well, all you have to do is tell the server where to connect. Tell the server which connection to use with the constructor override, as follows:

using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345", "dev_db"))
{
    // you are working against your dev database
}
using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345", "staging_db"))
{
    // you are working against your staging database
}
using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345", "Northwind"))
{
    // you are working against your Northwind database
}
using (var adapter = new DataAccessAdapterServiceWrapper("http://service_endpoint:12345", "BugsDbOnMySql"))
{
    // you are working against your Bugs database on MySql
}

Well, that’s it folks for the general overview, download the code if you want and take it for a spin. Read on for more implementation details where I’ll describe a little more how this is put together.

Code Download

Implementation Details

Server Implementation

Have a look at the code in the download for more details, but essentially the service side of things is textbook ServiceStack, so if you get familiar with that library, this will be nothing new to you. You can instantiate the server application host with something as simple as the following:

private static void Main(string[] args)
{
    var hostManager = new DataAccessAdapterServiceHostInitTeardown(port);

    // if you want to hook up an authentication / authorization provider, do it here before you initialize the host
    // see samples in the downloadable code, and/or visit the ServiceStack site

    hostManager.Init();
}

To tell the server which implementations are supported, add the following configuration to your app.config, or web.config, whichever applies:

  <configsections>
    <section type="LLBLGen.Contrib.DataServices.ConfigClasses.LLBLGenContribSection, LLBLGen.Contrib.DataServices" name="llblgen.contrib" />
  </configsections>
  <connectionstrings>
    <add name="NorthwindConnectionString" providername="System.Data.SqlClient" connectionstring="data source=localhost;initial catalog=northwind;integrated security=SSPI;persist security info=False;packet size=4096" />
    <add name="MyCustomOracleDB" ... />
    <add name="MyCustomMySqlDB" ... />
  </connectionstrings>
  <appsettings>
    <add value="2" key="CatalogNameUsageSetting" />
    <add value="2" key="SchemaNameUsageSetting" />
    <add value="0" key="ForceLLBLRouteAuthentication" />
  </appsettings>
  <llblgen.contrib>
    <adapterfactories defaultfactory="Northwind">
      <adapterfactory key="Northwind" connectionstringname="NorthwindConnectionString" adaptertype="Northwind.Data.DatabaseSpecific.DataAccessAdapter, Northwind.DataDBSpecific, Version=1.0.4712.34952, Culture=neutral, PublicKeyToken=null" />
      <adapterfactory key="MyOracleApp" connectionstringname="MyCustomOracleDB" adaptertype="My.Custom.Oracle.App.DatabaseSpecific.DataAccessAdapter, My.Custom.Oracle.App.DataDBSpecific, Version=1.0.4712.34952, Culture=neutral, PublicKeyToken=null" />
      <adapterfactory key="MySqlApp" connectionstringname="MyCustomMySqlDB" adaptertype="My.Custom.MySql.App.DatabaseSpecific.DataAccessAdapter, My.Custom.MySql.App.DataDBSpecific, Version=1.0.4712.34952, Culture=neutral, PublicKeyToken=null" />
    </adapterfactories>
  </llblgen.contrib>

Now, from the client, you can just use the appropriate adapter factory key as your 2nd parameter in the DataAccessAdapterServiceWrapper previously discussed. If no 2nd parameter is passed, the default factory in the configuration above is used.

How the data is passed between the client and the server

In order for this all to work, I considered several serialization options for passing data. I started by creating DTOs to mimick the PrefetchPaths, Predicate expression, and Relations, but I basically realized I was going down the road of re-creating complex object graphs and having to map these back and forth to the LLBLGen objects was simply more work than I was willing to do. So I basically just decided to use binary serialization and use ServiceStack's capability to directly compose and intercept, serialize and deserialize messages into and from the request and response streams. It turns out that using binary formatting and serialization did just the trick. The entire serialization and deserialization and stream parsing for all methods is done in one simple extension method:

public static class DataAccessAdapterServiceExtensions
{
    public static TResponse ExecuteRemoteDataAccessAdapterMethod<trequest  , TResult TResponse,>(
        this JsonServiceClient client, object objectToStreamInRequest)
        where TResult : IStreamWriter
        where TRequest : IRequiresRequestStream, IReturn<tresult>, new()
        where TResponse : class, IHasResponseStatus, new()
    {
        client.LocalHttpWebRequestFilter = httpReq =>
            {
                httpReq.AllowWriteStreamBuffering = false;
                httpReq.SendChunked = true;
                httpReq.ContentType = "multipart/form-data;";
                httpReq.Timeout = int.MaxValue;

                using (var m = new MemoryStream())
                {
                    LLBLSerializationHelper.SerializeToStream(m, objectToStreamInRequest);
                    m.WriteTo(httpReq.GetRequestStream());
                }
            };

        object response = null;
        client.LocalHttpWebResponseFilter = httpResp =>
            {
                using (var memoryStream = new MemoryStream())
                {
                    Stream responseStream = httpResp.GetResponseStream();
                    if (responseStream != null)
                    {
                        responseStream.CopyTo(memoryStream);
                        response = LLBLSerializationHelper.DeserializeFromStream(memoryStream);
                    }
                }
            };

        var request = new TRequest();
        try
        {
            client.Post(request);
        }
        catch (WebServiceException serviceException)
        {
            if (serviceException.ResponseStatus != null &&
                !string.IsNullOrEmpty(serviceException.ResponseStatus.ErrorCode))
            {
                var responseStatus = serviceException.ResponseStatus;
                throw new ApplicationException(string.Concat(responseStatus.ErrorCode, ": ", responseStatus.Message));
            }
            throw;
        }
        return response as TResponse;
    }
}

The "LLBLSerializationHelper.SerializeToStream" method just does binary serialization:

public static class LLBLSerializationHelper
{
    public static Stream SerializeToStream(Stream stream, object o)
    {
        if (stream == null)
            stream = new MemoryStream();
        var formatter = new BinaryFormatter();
        SerializationHelper.Optimization = SerializationOptimization.Fast;
        formatter.Serialize(stream, o);
        SerializationHelper.Optimization = SerializationOptimization.None;
        return stream;
    }

    public static object DeserializeFromStream(Stream stream)
    {
        var formatter = new BinaryFormatter();
        stream.Seek(0, SeekOrigin.Begin);
        SerializationHelper.Optimization = SerializationOptimization.Fast;
        object o = formatter.Deserialize(stream);
        SerializationHelper.Optimization = SerializationOptimization.None;
        return o;
    }
}

There's a little more to the code, but that's the essentials. If you have questions, ask in the comments and/or ping me directly. Take care! C#, llblgen, orm, servicestack, web api