SisoDb and SQL Server Express 2012, LocalDB

This is a verry short introduction to SQL Server Express 2012, LocalDB, and I’ll show you how to use it in conjunction with SisoDb (Simple, Structure, Oriented DB).

Edit: The intentions of LocalDb is as I’ve understood it, for developers and local use. But there are some interesting readings about other scenarios, e.g: http://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-2-instance-ownership.aspx#SharedLocalDB

Also, I’ve been getting questions about how it compares to other editions. Instead of rewriting what’s allready been written: http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx

For impatient readers

This section is for you that just want to get started, this is what I will go through:

Step 0: If you have Visual Studio 2012 RC, you probably allready have SQL Server Express LocalDb installed. Check under “Program and Features“. If you do, feel free to skip ahead to Step 3.

Step 1: If you aren’t already a lucky user of Chocolatey, install it by running this in a command prompt:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://bit.ly/psChocInstall'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin

Step 2: Install SQL Server Express 2012, LocalDb using this Chocolatey package:

cinst SqlServerLocalDb

Step 3: Use it with SisoDb: Install the Sql2012 provider.

install-package SisoDb.Sql2012
using System;
using System.Collections.Generic;
using SisoDb.Sql2012;

namespace DummyConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = @"data source=(localdb)\v11.0;
                       initial catalog=Dummy;
                       integrated security=SSPI;".CreateSql2012Db();
            db.CreateIfNotExists();

            foreach(var message in AskForMessages())
                db.UseOnceTo().Insert(new Message { Text = message });
            
            Console.WriteLine("You have said:");
            foreach (var msg in db.UseOnceTo().Query<Message>().ToArray())
                Console.WriteLine(msg.Text);
        }

        private static IEnumerable<string> AskForMessages()
        {
            while (true)
            {
                Console.WriteLine("What would you like to say?");
                var message = Console.ReadLine();
                if (string.IsNullOrEmpty(message))
                    yield break;

                yield return message;
            }            
        }
    }

    public class Message
    {
        public Guid Id { get; set; }
        public string Text { get; set; }
    }
}

Note! LocalDb can be a bit slow the first time, since it might need to create the instance and then spin up SQLServr.exe as a child process. Once the instance and the Db is created it’s better.

The longer version

That was the quick start version. Now lets take it from the beginning. I stumple upon some users of SisoDb using the SQL Compact provider locally, just because they don’t have SQL Server Express edition or higher installed, since it’s “hard to setup“. I don’t know about that, but Microsoft has listened to you devs out there and with SQL Server 2012 (Denali) they released a concept of LocalDb. I’m not going to go into the details of it, but I would like to emphasize a bit on some aspects found in the docs:

  • LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine.
  • Supports both private and shared instances.
  • The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed.
  • LocalDB cannot be a merge replication subscriber.
  • LocalDB does not support FILESTREAM.
  • LocalDB only allows local queues for Service Broker.

Install not needed?

If you are sitting on Visual Studio 2012 RC, you probably got it installed from the beginning. Otherwise, there are traditional downloads or probably simpler, you could use Microsoft Web Platform Installer. But lets introduce another way (just for fun and simplicity). Lets have a look at Chocolatey.

Install using Chocolatey

Since the installation of LocalDb from Microsoft, should be really simplified, I thought it would be a perfect fit for Chocolatey. After a quick ping to Rob Reynolds (Mr Chocolatey), it’s now present there. Let’s try it. Since I never have used Chocolatey before, I needed to install it. I just fired up a command prompt and ran the command found on the landing page of http://chocolatey.org, which as of this writing was:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://bit.ly/psChocInstall'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin

After that, I just went to the page for LocalDB on Chocolatey and ran the below in a command prompt:

cinst SqlServerLocalDb

Which will result in something looking like this.

That’s it. You now have a SQL Server Express LocalDB 2012 installed on your machine.

Lets consume it with SisoDb

Lets just create something really simple to see how the connection string should look like.

using System;
using System.Collections.Generic;
using SisoDb.Sql2012;

namespace DummyConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = @"data source=(localdb)\v11.0;
                       initial catalog=Dummy;
                       integrated security=SSPI;".CreateSql2012Db();
            db.CreateIfNotExists();

            foreach(var message in AskForMessages())
                db.UseOnceTo().Insert(new Message { Text = message });
            
            Console.WriteLine("You have said:");
            foreach (var msg in db.UseOnceTo().Query<Message>().ToArray())
                Console.WriteLine(msg.Text);
        }

        private static IEnumerable<string> AskForMessages()
        {
            while (true)
            {
                Console.WriteLine("What would you like to say?");
                var message = Console.ReadLine();
                if (string.IsNullOrEmpty(message))
                    yield break;

                yield return message;
            }            
        }
    }

    public class Message
    {
        public Guid Id { get; set; }
        public string Text { get; set; }
    }
}

Lets get acquainted with SQLLocalDb-util

If you just installed LocalDb, then open a new command prompt so that the updated environment path setting is honored. To demo this tool, I’m going to create a shared instance. And when creating a shared instance, you need to run the command prompt in elevated mode.

sqllocaldb create MyFooInstance
sqllocaldb share daniel MyFooInstance MySharedFooInstance

We have now created one named instance and also one shared representation of it. Now lets start them.

sqllocaldb start MySharedFooInstance

If you have a look at the screenshot below, you will see that MyFooInstance is “linked” to the shared instance, as it gets started.

You can now use the following connection strings instead:

--Targetting named instance
var cnString = @"data source=(localdb)\MyFooInstance;
           initial catalog=Dummy;
		   integrated security=SSPI;".CreateSql2012Db();
--Targetting shared named instance
var cnString = @"data source=(localdb)\.\MySharedFooInstance;
           initial catalog=Dummy;
		   integrated security=SSPI;".CreateSql2012Db();

Other tools in the path of simplicity

To inspect contents a really simple and slick tool is LinqPad. It’s quite small, about 4Mb.

Some XSockets and SisoDb fun – Part 2 of 2

This is a continuation of Some XSockets and SisoDb fun – Part 1 of 2 which more was about getting started with XSockets.Net. This post is about putting SisoDb to use. The steps we will go through are:

Get the Code

The code is located at GitHub. Both the SisoDbClient.Js and a sample app matching the sample we will create here.

  • Step 1 – Create a model
  • Step 2 – Inserts – Add support to the socket handler
  • Step 3 – Inserts – Hook up the UI
  • Step 4 – GetById – Add support to the socket handler
  • Step 5 – GetById – Hook up the UI
  • Step 6 – Updates – Add support to the socket handler
  • Step 7 – Updates – Hook up the UI
  • Step 8 – Queries – Add support to the socket handler
  • Step 9 – Queries – Hook up the UI
  • Step 10 – DeleteById – Add support to the socket handler
  • Step 11 – DeleteById – Hook up the UI

Step 1 – Create a model

First of, we need a simple model to work with. Create a class library project named “Model” and add the following class:

public class Article
{
    public Guid ArticleId { get; set; }
    public int PLU { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Now, it’s time to extend our socket handler to support inserts of the newly created model.

Step 2 – Inserts – Add support to the socket handler

It’s time to start using SisoDb and we will now make the handler support inserts. But to do that we will need to setup some plumbing, which of course, also will be used for updates, deletes and queries. Now, lets install the “SisoDb core package” to the “XSocketHandler” project.

To support dynamic lambda expression when querying, we also need the “SisoDb.Dynamic” package so install that to.

Now, lets create the shell method accepting inserts. Add this to the “SisoDbHandler” class in the “XSocketHandler” project:

[HandlerEvent("Insert")]
public void Insert(InsertCommand command)
{
    //Use SisoDb, but resolve it via a configurable Runtime class.
}

Instead of locking down our handler to a specific provider of SisoDb, we will configure this in the application entry point instead, more precisely, in the “Server”. But before that, create a new class-library project named “Core”.

Now, install the “SisoDb core package” to this project as well.

The Core project will define a “Runtime” class that holds some resources that we will consume in our handler. This is a quick solution/hack and you would probably do it another way. Perhaps using MEF or some IoC-container framework. But lets keep it fairly simple. Add a new class called “Runtime” to the “Core project”.

public static class Runtime
{
    public static IResources Resources { get; set; }
}

We need to be able to resolve a ISisoDatabase and, given a type name, locate a certain structure/entity type. Lets define two simple Funcs for this.

public interface IResources
{
    Func<ISisoDatabase> DbResolver { get; set; }
    Func<string, Type> StructureTypeResolver{ get; set; } 
}

Lets initialize the Runtime. Install a specific SisoDb provider into your Server project. I will choose the Sql2012 provider.

To support dynamic lambda expression when querying, we also need the “SisoDb.Dynamic” package so install that to.

Add a class called “Resources” to the Server project and setup the two Func members that resolves a Db and Structure types. NOTE! The ISisoDatabase should be kept long lived since it contains caches of e.g Db-Schemas.

public class Resources : IResources
{
    private readonly ISisoDatabase _db;
    private readonly IDictionary<string, Type> _structureTypes;

    public Func<ISisoDatabase> DbResolver { get; set; }
    public Func<string, Type> StructureTypeResolver{ get; set; }

    public Resources()
    {
        //Func should resolve the SAME ISisoDatabase each time
        //Demo is the name of the connection string in App.Config
        _db = "Demo".CreateSql2012Db();
        _db.CreateIfNotExists();
        DbResolver = () => _db;

        //Simple key-value map for acceptable structure types
        _entityTypes = GetEntityTypes().ToDictionary(t => t.Name);
        StructureTypeResolver= name => _structureTypes[name];
    }

    private IEnumerable<Type> GetStructureTypes()
    {
        //This determines what Entities/Structures that will be accessible
        yield return typeof (Article);
    }
}

Now, assign an instance of the Resource class to the Runtime

static void Main(string[] args)
{
    Runtime.Resources = new Resources();
    //...
    //leave the rest untouched
    //...
}

Add a connection string to the App.Config in the Server project.

<connectionStrings>
  <add name="Demo" connectionString=
       "data source=.;initial catalog=Demo;integrated secuirty=true;"/>
</connectionStrings>

Now, lets fix the handler. I couldn’t get a constructor with arguments to work so we will resolve the Db inside it. Add a default constructor to the “SisoDbHandler” and resolve a Db to be used:

public class SisoDbHandler : XBaseSocket
{
    private readonly ISisoDatabase _db;

    public SisoDbHandler()
    {
        _db = Runtime.Resources.DbResolver();
    }

    //...
    //leave the rest untouched
    //...

Almost there. Fix the Insert method:

[HandlerEvent("Insert")]
public void Insert(InsertCommand command)
{
    var structureType = Runtime
        .Resources
        .StructureTypeResolver(command.StructureName);

    var result = new InsertResult
    {
        StructureName = command.StructureName,
        Json = _db.UseOnceTo().InsertJson(structureType, command.Json)
    };

    this.AsyncSend(result, "OnInserted");
}

The last steps that are missing is to create the “InsertCommand” and the “InsertResult”. Lets add them.

//Namespace: XSocketHandler.Commands
public class InsertCommand
{
    public string StructureName { get; set; }
    public string Json { get; set; }
}
//Namespace: XSocketHandler.Results
public class InsertResult
{
    public string StructureName { get; set; }
    public string Json { get; set; }
}

Our handler is now ready to serve insert calls. Lets consume it.

Step 3 – Inserts – Hook up the UI

The next step is to update our demo.html page and make it use the insert method. This is what we will start out from, with just a few minor adjustments from the previous post:

<!DOCTYPE html>
<html>
<head>
    <title>Demo - SisoDb-XSockets</title>
    <script src="Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
    <script src="Scripts/JXSockets-1.0.4.beta.js" type="text/javascript"></script>
    <script src="http://cloud.github.com/downloads/danielwertheim/
                 SisoDb-XSockets/sisodbclient-debug-v0.3.0.js" 
            type="text/javascript"></script>
        
    <script type="text/javascript">
        var my = {};

        $(function () {
            //Hook up client
            my.client = new SisoDbClient("ws://127.0.0.1:4502/SisoDbHandler");
            my.client.logger.enabled = true;
            my.client.connect();

            //Hookup UI to consume client
            $("#ping button").on("click", function () {
                my.client.ping($("#ping_message").val());
            });

            //Hookup listeners
            my.client.onPinged(function (data) {
                my.dumpResult("onPinged", data.Message);
            });

            //Some helpers
            my.dumpResult = function (action, result) {
                $("<li>").addClass(action)
                         .text(result)
                         .appendTo(".outputs ul:eq(1)");
            };
        });
    </script>
    <link href="Content/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <section class="inputs">
        <fieldset id="ping">
            <legend>Ping</legend>
            <label for="ping_message">Message</label>
            <input type="text" id="ping_message" placeholder="Message" />
            <button>Ping</button>
        </fieldset>
    </section>
    <section class="outputs">
        <ul>
            <li class="onPinged description">Ping result</li>
            <li class="onInserted description">Insert result</li>
            <li class="onDeletedById description">Delete by id result</li>
            <li class="onGetById description">Get by id result</li>
            <li class="onQuery description">Query result</li>
            <li class="onUpdated description">Update result</li>
        </ul>
        <ul></ul>
    </section>
</body>
</html>

First add a new Fieldset below the one for Ping.

<fieldset id="article_insert">
    <legend>Insert</legend>
    <div class="form-item">
        <label for="article_insert_name">Name</label>
        <input type="text" id="article_insert_name" placeholder="Name" />
    </div>
    <div class="form-item">
        <label for="article_insert_plu">PLU</label>
        <input type="text" id="article_insert_plu" placeholder="PLU" />
    </div>
    <div class="form-item">
        <label for="article_insert_price">Price</label>
        <input type="text" id="article_insert_price" placeholder="Price" />
    </div>
    <div class="form-actions">
        <button class="insert">Insert</button>
    </div>
</fieldset>

Now we need to make the UI invoke the socket handler when we click on Insert.

$(".inputs #article_insert .insert").on("click", function () {
    my.client.insert("Article", {
        Name: $("#article_insert_name").val(),
        PLU: $("#article_insert_plu").val(),
        Price: $("#article_insert_price").val()
    });
});

We also want to react on whenever an item is inserted, so lets hook that up:

my.client.onInserted(function (data) {
    my.dumpResult("onInserted", data.Json);
});

Now, fire up the Server and the MVC application and visit the demo.html page. Below I have tried the Ping operation and made a simple insert.

Step 4 – GetById – Add support to the socket handler

This time, most of the plumbing is already in place in the handler. Lets add GetById:

[HandlerEvent("GetById")]
public void GetById(GetByIdCommand command)
{
    var structureType = Runtime
        .Resources
        .StructureTypeResolver(command.StructureName);
    var structureSchema = GetStructureSchema(structureType);

    var id = ConvertId(command.Id, structureSchema);
    var result = new GetByIdResult
    {
        StructureName = command.StructureName,
        Id = command.Id,
        Json = _db.UseOnceTo().GetByIdAsJson(structureType, id)
     };

     this.AsyncSend(result, "OnGetById");
}

The above code needs two helper methods:

private IStructureSchema GetStructureSchema(Type structureType)
{
    return _db.StructureSchemas.GetSchema(structureType);
}

private static object ConvertId(string id, IStructureSchema structureSchema)
{
    return StructureId.Create(id, structureSchema.IdAccessor.IdType).Value;
}

Now, lets finish the GetById support in the handler. Add the GetByIdCommand and GetByIdResult.

//Namespace: XSocketHandler.Commands
public class GetByIdCommand
{
    public string StructureName { get; set; }
    public string Id { get; set; }
}
//Namespace: XSocketHandler.Results
public class GetByIdResult
{
    public string StructureName { get; set; }
    public string Id { get; set; }
    public string Json { get; set; }
}

Step 5 – GetById – Hook up the UI

Time to make the UI support the GetById operation. First add a new FieldSet that lets you enter an Id to return an item for.

<fieldset id="article_getById">
    <legend>Get by Id</legend>
    <div class="form-item">
        <label for="article_getById_id">Id</label>
        <input id="article_getById_id" type="text" placeholder="id"/>
    </div>
    <div class="form-actions">
        <button class="get">Get</button>
    </div>
</fieldset>

Make the UI invoke the handler:

$(".inputs #article_getById .get").on("click", function () {
    my.client.getById("Article", $("#article_getById_id").val());
});

Now, hook up the UI to react on the result of the GetByIdCommand.

my.client.onGetById(function (data) {
    my.dumpResult("onGetById", data.Json);
});

You should now be able to perform GetById operations, which will look like this:

Step 6 – Updates – Add support to the socket handler

Time to fix Updates. Add this to the handler:

[HandlerEvent("Update")]
public void Update(UpdateCommand command)
{
    var structureType = Runtime.Resources.StructureTypeResolver(command.StructureName);
    var structure = _db.Serializer.Deserialize(structureType, command.Json);
    var structureSchema = GetStructureSchema(structureType);

    _db.UseOnceTo().Update(structureType, structure);
            
    var result = new UpdateResult
    {
        StructureName = command.StructureName,
        Id = structureSchema.IdAccessor.GetValue(structure).Value.ToString()
    };

    this.AsyncSend(result, "OnUpdated");
}

Also add the UpdateCommand and the UpdateResult.

//Namespace: XSocketHandler.Commands
public class UpdateCommand
{
    public string StructureName { get; set; }
    public string Json { get; set; }
}
//Namespace: XSocketHandler.Results
public class UpdateResult
{
    public string StructureName { get; set; }
    public string Id { get; set; }
}

Step 7 – Updates – Hook up the UI

Time to make the UI support the Update operation. First add a new FieldSet that lets you enter an Id to load an item for. Then some controls that lets you update the values.

<fieldset id="article_update">
    <legend>Update - Load</legend>
    <div class="form-item">
        <label for="article_update_id">Id</label>
        <input id="article_update_id" type="text" placeholder="id"/>
    </div>
    <div class="form-actions">
        <button class="load">Load</button>
    </div>

    <legend>Update - Save</legend>
    <div class="form-item">
        <label for="article_update_name">Name</label>
        <input type="text" id="article_update_name" placeholder="Name" />
    </div>
    <div class="form-item">
        <label for="article_update_plu">PLU</label>
        <input type="text" id="article_update_plu" placeholder="PLU" />
    </div>
    <div class="form-item">
        <label for="article_update_price">Price</label>
        <input type="text" id="article_update_price" placeholder="Price" />
    </div>
    <div class="form-actions">
        <button class="update">Update</button>
    </div>
</fieldset>

Make the UI invoke the handler:

$(".inputs #article_update .load").on("click", function () {
    my.client.getById("Article", $("#article_update_id").val());
});

$(".inputs #article_update .update").on("click", function () {
    my.client.update("Article", {
        ArticleId: $("#article_update_id").val(),
        Name: $("#article_update_name").val(),
        PLU: $("#article_update_plu").val(),
        Price: $("#article_update_price").val()
    });
});

Now, extend the “onGetById callback”, since it now could represent either “GetById” or “Load”.

my.client.onGetById(function (data) {
    if ($("#article_update_id").val()) {
        var doc = JSON.parse(data.Json);
        $("#article_update_name").val(doc.Name);
        $("#article_update_plu").val(doc.PLU);
        $("#article_update_price").val(doc.Price);
    }
    else
        my.dumpResult("onGetById", data.Json);
});

Also hook up the “onUpdated callback”:

my.client.onUpdated(function (data) {
    my.dumpResult("onUpdated", data.Id.toString());
});

You should now be able to perform Update operations, which will look like this:

Step 8 – Queries – Add support to the socket handler

Time to fix Queries. Add this to the handler:

using SisoDb.Dynamic; //IMPORTANT! IMPORTANT! IMPORTANT!

[HandlerEvent("Query")]
public void Query(QueryCommand command)
{
    var structureType = Runtime
        .Resources
        .StructureTypeResolver(command.StructureName);
            
    var result = new QueryResult
    {
        StructureName = command.StructureName
    };

    using (var session = _db.BeginSession())
    {
        result.Json = session.Query(structureType).Where(command.Predicate).ToArrayOfJson();
    }

    this.AsyncSend(result, "OnQuery");
}

Also add the QueryCommand and the QueryResult.

//Namespace: XSocketHandler.Commands
public class QueryCommand
{
    public string StructureName { get; set; }
    public string Predicate { get; set; }
}
//Namespace: XSocketHandler.Results
public class QueryResult
{
    public string StructureName { get; set; }
    public string[] Json { get; set; }
}

Step 9 – Queries – Hook up the UI

Time to make the UI support Queries with lambda style syntax. First add a new FieldSet that lets you enter a query.

<fieldset id="article_query">
    <legend>Query</legend>
    <div class="form-item">
        <label for="article_query_predicate">Predicate</label>
        <input id="article_query_predicate" type="text" placeholder="predicate"/>
    </div>
    <div class="form-actions">
        <button class="query">Query</button>
    </div>
</fieldset>

Make the UI invoke the handler:

$(".inputs #article_query .query").on("click", function () {
    my.client.query("Article", $("#article_query_predicate").val());
});

Hook up the UI to display the query result for the “onQuery callback”.

my.client.onQuery(function (data) {
    $.each(data.Json, function (i, e) {
        my.dumpResult("onQuery", e);
    });
});

You should now be able to perform queries using syntax like "x => x.PLU >= 100"

Step 10 – DeleteById – Add support to the socket handler

Time to fix DeleteById. Add this to the handler:

[HandlerEvent("DeleteById")]
public void DeleteById(DeleteByIdCommand command)
{
    var structureType = Runtime
        .Resources
        .StructureTypeResolver(command.StructureName);
    var structureSchema = GetStructureSchema(structureType);

    var id = ConvertId(command.Id, structureSchema);

    _db.UseOnceTo().DeleteById(structureType, id);

    var result = new DeleteByIdResult
    {
        StructureName = command.StructureName,
        Id = command.Id
    };

    this.AsyncSend(result, "OnDeletedById");
}

Also add the DeleteByIdCommand and the DeleteByIdResult.

//Namespace: XSocketHandler.Commands
public class DeleteByIdCommand
{
    public string StructureName { get; set; }
    public string Id { get; set; }
}
//Namespace: XSocketHandler.Results
public class DeleteByIdResult
{
    public string StructureName { get; set; }
    public string Id { get; set; }
}

Step 11 – DeleteById – Hook up the UI

Again, lets just add some simple controls to allow you to enter an Id to delete.

<fieldset id="article_deleteById">
    <legend>Delete by Id</legend>
    <div class="form-item">
        <label for="article_deleteById_id">Id</label>
        <input id="article_deleteById_id" type="text" placeholder="id"/>
    </div>
    <div class="form-actions">
        <button class="delete">Delete</button>
    </div>
</fieldset>

Make the UI invoke the handler:

$(".inputs #article_deleteById .delete").on("click", function () {
    my.client.deleteById("Article", $("#article_deleteById_id").val());
});

Now, hook up the UI to react on the result of the GetByIdCommand.

my.client.onDeletedById(function (data) {
    my.dumpResult("onDeletedById", data.Id.toString());
});

You should now be able to perform DeleteById operations, which will look like this:

The SisoDbClient.Js

The client is using the XSockets.JsApi and is written using CoffeeScript. It’s not large, have a look at it on GitHub.

Where to go from here

The code is hosted on GitHub. Both the SisoDbClient.Js (which is authored using CoffeeScript) and the XSocketHandler. If this looks interesting, pull it down and extend the code yourself. E.g adding exception handling, but as of now, I have nothing more to cover in this post. Happy coding!

//Daniel

Some XSockets and SisoDb fun – Part 1 of 2

This is a two post series that will show you how to use XSockets and SisoDb to create a JavaScript client for SisoDb, that let’s you consume the db directly from JavaScript. Not saying it’s what you should do, just showing what you can do. In this first post we will look at getting started with XSockets and getting our custom handler in place. The next post will show the implementation. The steps in this post are:

  • Step 1 – Setup XSockets
  • Step 2 – Create the SisoDbHandler
  • Step 3 – Create a debuggable server
  • Step 4 – Implement a simple Ping method in our SisoDbHandler
  • Step 5 – Use the SisoDbClient.js to invoke our Ping member

Step 1 – Setup XSockets

First create a new empty ASP.Net MVC 3 project. Then install the NuGet package XSockets. Note! Use the Package Manager console

After the installation is done, some scaffolding templates (located under CodeTemplates) has been installed along with a XSocket-server and some web samples (located under XSockets). There’s also a new project “XSocketHandler” which is empty at the moment. This is where we will put our “SisoDbHandler“.

The development server will represent the server endpoint serving client connections. The client connection could come from e.g a JavaScript client using the XSockets.JsApi package or from .Net code using XSockets.External.

Now, lets start the development server via Windows explorer; and have a look at some of it’s commands. Start it and type: “help“.

The commands, currently available, are:

Try the “handlers” command. It will list the currently available handlers. And per default there’s one serving textual data (e.g JSON) and one serving binary data. And when we are done there will be a “SisoDbHandler“.

The included GenericText and GenericBinary handlers will let you, out of the box, be able to get up an running pushing JSON messages around and stream binary data. Hence you could e.g. create chats etc, right out of the box. Have a look at the included examples to see how easy it is.

Step 2 – Create the SisoDbHandler

A handler could be seen as a controller in ASP.Net MVC; and each handler serves one or many requests. To create a handler, turn back to the Package Manager Console and use the “scaffold” command and select “XSocketHandler“.

Name it: “SisoDbHandler“.

This will scaffold a new XSocketHandler named “SisoDbHandler” in the XSocketHandler project. Compile the solution and use “Show all files” in the MVC project, and you will see that the build events of the XSocketHandler project have copied the “XSocketHandler.dll” to the “XSocketServerPlugins” folder.

Now, start the server again and run the “handlers” command. You should now see the new “SisoDbHandler“.

Step 3 – Create a debuggable server

To be able to easily debug our handler and to manage deployment of dependencies during development, we will create a custom Server and not use the precompiled server under the MVC project.

Add a new console project. Name it “Server” and remove client profile for it (I wrote a quick post about how to get rid of it for-ever in about 5minutes.

Now, copy everything inside the folder “$MVCApp$\XSockets\DevelopmentServer” to “$Solution$\XSockets” and rename “XSocketServerPlugins” to “Plugins“. Clean it up so that it looks like this:

Add a reference to the “XSockets.Core.dll” above in the Server project.

Now, add an App.Config to the newly created Server-project. Copy the contents of “$MVCApp$\\XSockets\DevelopmentServer\XSockets.DevelopmentServer.Console.exe.config” to your newly created App.config file.

Locate the app-setting “XSocketServerPluginCatalog” and make it look like this:

<add key="XSocketServerPluginCatalog" value="..\..\..\XSockets\Plugins\" />

Go to the XSocketHandler project and open properties for it. Adjust the Pre- and Post- build events so that the dll gets copied to the Server-project instead.

Pre-build event

IF NOT EXIST "$(SolutionDir)XSockets\Plugins\" mkdir "$(SolutionDir)XSockets\Plugins\"

Post-build event

copy "$(TargetDir)$(TargetName).dll", "$(SolutionDir)XSockets\Plugins\"
copy "$(TargetDir)$(TargetName).pdb", "$(SolutionDir)XSockets\Plugins\"

Create a class Named “Start” under the Server-project. Copy the content from “MVC-Project\XSockets\devservercode.txt” to this newly created class. Fix namespaces and add reference to the “Server\XSockets\XSockets.Core.dll“. You will also need to add references to “System.Componentmodel.Composition“. The namespaces and using statements should look like this:

using System;
using System.ComponentModel.Composition;
using System.Configuration;
using XSockets.Core.Plugin.MEF;
using XSockets.Core.XSocket.Interface;

namespace Server
{
    public class Start : Composable
    {
        //... ... ...
    }
}

It’s time for the final adjustment. Now lets fix Program.cs. In $MVCApp$\XSockets\DevelopmentServer\HOW_DO_I_DEBUG_MY_HANDLERS.txt you can find a snippet around step 7 in that file. Make the Main method look something like this:

static void Main(string[] args)
{
    try
    {
        new Start();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        if (ex.InnerException != null)
            Console.WriteLine(ex.InnerException.Message);
        Console.ReadLine();
    }
}

It should now be ready to serve our client requests. Fire up the Server and run the “handlers” command and ensure that the “SisoDbHandler” is located in there.

Step 4 – Implement a simple Ping method in our SisoDbHandler

Before wrapping this up, let’s add a simple Ping action in our handler. Again, a handler could be seen as a controller in Asp.Net MVC; and it’s members serving socket calls; could be seen as actions in Asp.Net MVC.

Create a new folder/namespace, and call it “Commands“. Add a class called “PingCommand“. This will serve as our model in.

public interface ICommand {}

public class PingCommand : ICommand
{
    public string Message { get; set; }
}

Create a new folder/namespace, and call it “Results“. Add a class called “PingResult“. This will serve as our model out.

public interface IResult {}

public class PingResult : IResult
{
    public string Message { get; set; }
}

Now, add a member in our SisoDbHandler, and make it look like this:

[HandlerEvent("Ping")]
public void Ping(PingCommand command)
{
    this.AsyncSend(new PingResult { Message = command.Message }, "OnPinged");
}

If you now start the Server and run the “handlers” command, you will see that the SisoDbHandler now accepts calls to a member called “Ping“.

Step 5 – Use the SisoDbClient.js to invoke our Ping member

Now, lets use the added Ping member. Create a simple html-page in the root of our MVC-app. Name it “demo.html“. We need script references to jQuery, XSockets and the SisoDbClient.js libs.

<!DOCTYPE html>
<html>
<head>
	<title>Demo - SisoDb-XSockets</title>
	<script src="Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
	<script src="Scripts/JXSockets-1.0.4.beta.js" type="text/javascript"></script>
	<script src="http://cloud.github.com/downloads/danielwertheim/SisoDb-XSockets/sisodbclient-debug-v0.1.0.js" type="text/javascript"></script>
	
	<script type="text/javascript">
		var my = {};

		$(function () {
			//Hook up client
			my.client = new SisoDbClient("ws://127.0.0.1:4502/SisoDbHandler");
			my.client.logger.enabled = true;
			my.client.connect();

			//Hookup listeners
			my.client.onPinged(function (data) {
				$("<li>").text(data.Message).prependTo("ul:first");
			});

			//Hookup UI to consume client
			$("#ping").on("click", function () {
				my.client.ping($("#pingMessage").val());
			});
		});
	</script>
	<link href="Content/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
	<fieldset>
		<legend>Ping</legend>
		<label for="pingMessage">Message</label>
		<input type="text" id="pingMessage"/>
		<button id="ping">Ping</button>
	</fieldset>
	
	<ul></ul>
</body>
</html>

Now, start the server and start the MVC-app and browse to the Html-page. Run the command “status” in the server and you should see something like this:

Lets invoke our UI and just send the text: “Test“. If you have the console running, the SisoDbClient.Js will output some logging. You can turn this off by changing this line:

my.client.logger.enabled = false;

That’s it! The next time we will implement a simple model and the CRUD operations.

//Daniel

SisoDb v9.0 Released.

First lets make one thing clear: “No I’m not chasing Google Chrome versioning!” The reason is that I’m trying to follow Semantic versioning and if there is a change that makes SisoDb more flexible and more performant and it has been asked for, it will go into the codebase. I try to have frequent releases and if it’s a braking change, major version is bumped.

Before going into the meat and the reason for why version 9.0 has been released, lets look at some other stuff. For source code, documentation etc, go to: http://sisodb.com

Denali

The SQL 2008 provider has now been tested against Sql-Server 2012 Express RC0 (Denali) and it works. There will be a separate provider “SisoDb.Sql2012” for this, but if you want to jumpstart, e.g trying out LocalDb features, you can.

Version 9.0, the Changes

This new version, v9.0, has had two major focus areas:

  • New layout in database for indexes-values
  • Adapt API for coming providers not having Transactions

New layout in database for indexes-values

One major focus has been to rewrite how the key-values that are used for queries, are stored. This is the result of feedback from the community. And as a step in gaining better querying performance, each Indexes table has been divided up amongst seven tables, grouping data that is of the samy type together. By doing this more effective indexes can be designed for enhancing queries and at the same time retain insert speed. I know it sounds like a lot of tables, but hey, Siso should keep you out of the database. And for you that are afraid of limitations, you can read here (http://msdn.microsoft.com/en-us/library/ms143432.aspx), that tables counts as objects and the total number of all objects in a SQL2008 database is as high as 2,147,483,647 objects.

More to come for queries and inserts

There’s plans for making it possible for you to hook in a caching implementation, so that queries doesn’t have to touch the database. In forthcoming release, there will be focus on moving the process of inserting values into the Indexes-tables to a background process. Hence upon inserting items, the structure (document) will be inserted transactional and then the indexes will be queued and inserted in the background. Having done this partitioning by dividing each Indexes-table up in several tables, it will be easier to accomplish parallel inserts.

Text vs String

When you design your model and use properties with string, there’s now from version 9 two different strings. Either you use the normal string BCL type or the custom Text type found in SisoDb. Values from the former will end up in [Entity]Strings table and the later in [Entity]Texts table. Strings has a max length of 300 chars while Text doesn’t. This semantic separation is done so that effective indexes for queries could be created for normal strings, which isn’t feasible if it would be nvarchar(max).

The Text type in SisoDb is implicit convertible to and from a string so you don’t have to use it explicitly other than as a marker on your entity property.

Sample

public class BlogPost
{
    public Guid Id { get; set; }
    public string Title { get; set; } //Ends up in BlogPostStrings
    public Text Content { get; set; } //Ends up in BlogPostTexts
}

var post = new BlogPost 
{
    Title = "A title of max 300 chars",
    Content = "Some long text that can exceed 300 chars."
}

Adapt API for coming providers not having Transactions

In coming realeases, v9.x, SisoDb will have providers that hasn’t support for transactions like the RDBMS environment does. Having UnitOfWork and UnitOfWork.Commit in these providers will not make sense and will probably lead to confusion. Therefore UnitOfWork has changed its name to WriteSession and the QueryEngine is now named ReadSession. Furthermore the UnitOfWork.Commit() method has been removed and auto commit behavior is instead used on UnitOfWork.Dispose(), hence UnitOfWork will still be transactional when you target e.g Sql2008 and SqlCe4. Note!A commit will only be performed if no exception has been encountered.

Old code

using(var unitOfWork = db.CreateUnitOfWork())
{
    unitOfWork.Insert(x);
    unitOfWork.Insert(y);
    unitOfWork.Update(z);
    unitOfWork.Commit();
}
using(var q = db.CreateQueryEngine())
{
    var r = q.Query<Customer>().Where(c => c.CustomerNo == "123456").SingleOrDefault();
}

New code

//As long as the underlying provider supports it, a Write session is still transactional.
using(var session = db.BeginWriteSession())
{
    session.Insert(x);
    session.Insert(y);
    session.Update(z);
} //A Write session is implicitly being committed on Dispose().
using(var q = db.BeginReadSession())
{
    var r = q.Query<Customer>().Where(c => c.CustomerNo == "123456").SingleOrDefault();
}

Other changes

There has also been some other minor adjustments for v9.0 and since SisoDb has evolved kind of rapid lately, chances are that you have missed some features and changes of earlier releases, hence some of them are covered below as well.

DbSchemaNamingPolicy

You can now control the global naming of your structures by register a specific Func against the static DbSchemaNamingPolicy class:

DbSchemaNamingPolicy.StructureNameGenerator = 
    schema => return string.Concat("MyPrefix", schema.Name);

UpdateMany

UpdateMany has been rebuilt to take care of update many operations and not migration operations. As a step, you now have to provide a predicate and UpdateManyStatuses isn’t used anymore to control if an item should be kept or not. Also, the UpdateMany method of the UnitOfWork (now WriteSession) taking an old type and an new type has been removed. To get this functionality there’s a DbStructureSetMigrator you can use instead.

var migrator = Database.GetStructureSetMigrator();
migrator.Migrate<ModelComplexUpdates.Person, ModelComplexUpdates.SalesPerson>((p, sp) =>
{
	var names = p.Name.Split(' ');
	sp.Firstname = names[0];
	sp.Lastname = names[1];

	var address = p.Address.Split(
            new[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);

	sp.Office.Street = address[0];
	sp.Office.Zip = address[1];
	sp.Office.City = address[2];

	return StructureSetMigratorStatuses.Keep;
});

Custom non generic collections

Actually, this was released as a patch to v8 but I thought it was worth mentioning. As of now you can have custom, non-generic collections.

public class MyModel
{
    public Guid Id { get; set; }
	public MyCollection Items { get; set; }
}

public class MyItem
{
    public int Value { get; set; }	
}

public class MyCollection : List<MyItem> {}

Custom naming of the Id-property

To clean up your models as of v8 you are not forced to name the Id-property “StructureId”. It will look for the following names:

  • StructureId
  • [TypeName]Id
  • Id
  • I[InterfaceName]Id

Machine name specific connectionstrings

This is also a v8 feature and you can now have connectionstring names prefixed with a machine name. When providing a connection string name, Siso will first try to find one with the value you pass but prefixed with Machine name_.

So if you pass “MyConnectionStringName” it will try and find:

  • MachineName_MyConnectionStringName
  • MyConnectionStringName

Case-sensitive collations

I have tested it against a database with a case-sensitive collation setting, and all tests now passes. There was some SQL where members where written in wrong casing but that has now been fixed.

Removed fields

To make the database schema more friendly for manual updates, the hashed value representing an entity has been removed. The SQL queries has also been rewritten so that the RowId columns no longer is needed.

Misc

There has also been some correction of bugs and performance tweaks, regarding some parallel generation of Id’s, structures etc.

Migrate

Take a backup of your database before porting! Below is some info about the changes made to the storage layout, which could be useful. For more info just contact me.

SisoDbIdentities table

The EntityHash column has been dropped and primary-key is instead EntityName. New layout is:

Structures tables

The RowId column and any associated index has been dropped. New layout is:

Indexes tables

You should be able to just make the adjustments to the other tables and then re-save the structures. That should generate the new indexes tables and put values in them. But again. Be ensured you do have a backup.

For manual porting
The values from the indexes tables needs to be inserted in each dedicated indexes-table. Soo values stored in e.g CustomerIndexes IntegerValue column should be stored in CustomerIntegers Value column. Note! That all values (depending on version of SisoDb) has a representation of it’s value in the StringValue column. Hence, when moving values from the StringValue column to CustomerStrings or CustomerTexts you should only move the once that represents a string, which should be the once that has null in every other value column.

Uniques tables

As with the Indexes tables, if you drop this table and re-save all structures the table should be recreated and populated in the correct manners.

Other than that the only change that has been made is that the RowId column has been dropped. New layout is:

That’s it.

//Daniel