SisoDb – Using DbPipe to add compression

In v16.1.0 of SisoDb I’ve added a new interface SisoDb.IDbPipe which lets you hook into the process of writing and reading the string-data representing a document (default JSON) to and from the DB. You could use that for encryption, compression, ….? Now I will just show you how to add compression.
Continue reading

SisoDb – Cacheable queries

This weekend I just release SisoDb v16.1.0 and about a week before that v16.0.0. I thought we might take a look at some new features with regards to caching. From version 16.0.0 of SisoDb you can not only make use of a cache provider for GetById, GetByIds but also for queries. First you need to enable caching. You can easily write one on your own, by implementing SisoDb.Caching.ICacheProvider and SisoDb.Caching.ICache. But for this post we will use an existing one, provided via NuGet.
Continue reading

SisoDb v16.0.0 is out

I’ve been working on some changes lately on SisoDb. Mostly it’s been about cleaning the code base up but also bring in support for spatial data as well as cachable queries. There’s a bunch of changes and I will cover some of the here soon. You can have a check at the release notes here: http://sisodb.com/wiki/release-notes

While typing this I’m currently running an app that uses these new features, against a SQL Azure database. Once you have deployed the DB, SisoDb seems to work fine. Not tested all the features though.

//Daniel

SisoDb – Simple tip on how to write more performant queries

Time for a really quick tip when it comes to queries in SisoDb that will give you better performance. When firing of a query with lots of parameters in your expression, move the logic into your model instead. That way the expression only has to lookup one index-value for your structure, and it will probably also state what you are doing. Example

session
    .Query<WorkOrder>()
    .Where(w => w.Status == WoStatus.Done || w.Status == WoStatus.Trashed);

should be

session
    .Query<WorkOrder>()
    .Where(w => w.IsClosed);

This was just a super simple example, but really. Try and get it down to one param. You will gain performance.

//Daniel

Writing own libs for SisoDb – SisoDb.Spatials

I’m in the process of finalizing a v16.0.0 of SisoDb and there’s a first pre-release out on NuGet (SQLCE4, SQL2005, SQL2008, SQL2012). I’m will soon get a pre-release 2 out which have one simple change in code but it allows you to write third party libs to SisoDb. Almost anything can allready be swapped out for your implementations. But what if you just want to provide some new functions via a NuGet? Lets say I would like to create SisoDb.Spatial and provide some simple helpers for spatial data. You should then extend the ISession and make use of the Session.ExecutionContext. It exposes all you need via the Session, but more importantly, exposes two Try methods that you should wrap your methods within. Why? Well SisoDb uses Auto-commit and rollback on exception, but to be sure states are updated correctly, the exception should be caught by that specific try methods.

Lets look at an example. First I extend ISession and pass along the ExecutionContext (available from v16.0.0-pre2)

public static class SessionExtensions
{
    public static ISisoSpatials Spatials(this ISession session)
    {
        return new SqlServerSisoSpatials(session.ExecutionContext);
    }
}

Lets define some operations

public interface ISisoSpatials 
{
    void UpsertFor<T>() where T : class;
    void DropFor<T>() where T : class;
    bool PointExistsInPolygonFor<T>(object id, Tuple<double, double> coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void DeleteGeoIn<T>(object id) where T : class;
    void InsertPolygonTo<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void UpdatePolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void SetPolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
}

Now just implement it and make use of the API exposed by the ExecutionContext.

public class SqlServerSisoSpatials : ISisoSpatials
{
    protected readonly ISessionExecutionContext ExecutionContext;
    protected ISession Session { get { return ExecutionContext.Session; } }
    protected readonly ISqlStatements SqlStatements;

    protected internal SqlServerSisoSpatials(ISessionExecutionContext executionContext)
    {
        ExecutionContext = executionContext;
        SqlStatements = SpatialSqlStatements.Instance;
    }

    public virtual void SetPolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class
    {
        ExecutionContext.Try(() =>
        {
            var schema = Session.GetStructureSchema<T>();
            var sidParam = CreateStructureIdParam<T>(id);
            var geoParam = CreatePolygonParam(coords, srid);
            var sql = SqlStatements.GetSql("SetGeo").Inject(GenerateTableName(schema));
            Session.DbClient.ExecuteNonQuery(sql, sidParam, geoParam);
        });
    }

    //...
    //Some stuffe left out.
    //...

    protected virtual string GenerateTableName(IStructureSchema schema)
    {
        return string.Concat(DbSchemaNamingPolicy.GenerateFor(schema.Name), "Spatials");
    }

    protected virtual DacParameter CreateStructureIdParam<T>(object id) where T : class
    {
        return new DacParameter("id", id);
    }

    protected virtual GeographyDacParameter CreatePointParam(Tuple<double, double> coords, int srid)
    {
        return new GeographyDacParameter("geo", SqlGeography.Point(coords.Item1, coords.Item2, srid));
    }
}

That’s pretty much it.

//Daniel

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.

Core concepts of indexing in SisoDb – Screencast

Time for episode number three. In this 8 minute episode, I’ll give you some short information about the concept of “Indexing” in SisoDb. I’ll show you how to use the Glimpse plugin to inspect information about structure schemas.

//Daniel

IoC with SisoDb in ASP.Net MVC

I just put together a short screencast (about 4min) showing you have to configure SisoDb with an IoC-container in ASP.Net MVC using “One session per HttpRequest”. For this demo I will use Ninject.

The screencast is hosted in the SisoDb channel at Vimeo.

Updated: Mike Paterson has a GitHub repository with code for the episode, found here: https://github.com/devlife/Sandbox/tree/master/SisoDb

Summarized

After having installed the “Ninject.MVC3 NuGet package”, I added a NinjectModule named “DbConfig” under a new folder/namespace “IoCConfig”.

public class DbConfig : NinjectModule
{
    public override void Load()
    {
        var db = "CoreConcepts".CreateSql2012Db();
        Kernel.Bind<ISisoDatabase>()
            .ToMethod(ctx => db)
            .InSingletonScope();

        db.CreateIfNotExists();

        Kernel.Bind<ISession>()
            .ToMethod(ctx => db.BeginSession())
            .InRequestScope();
    }
}

After that we just need to ensure the module is loaded by Ninject in the bootstraper, which is installed by the Ninject.MVC3 NuGet, under “App_Start”. The only change that is needed is adding one row to the “CreateKernel” member, so that it look like this:

private static IKernel CreateKernel()
{
    var kernel = new StandardKernel();
    kernel.Bind<Func<IKernel>>().ToMethod(ctx => () => new Bootstrapper().Kernel);
    kernel.Bind<IHttpModule>().To<HttpApplicationInitializationHttpModule>();

    //ADD THIS TO LOAD OUR MODULE(s)
    kernel.Load(typeof(MvcApplication).Assembly);

    RegisterServices(kernel);
    return kernel;
}

We are now all set and can take a dependency on “ISession” in our controllers. Either in the constructor or by resolving it using a static class/method/service locator concept against the Ninject IoC-container. Since my sample uses Db-access in each action, I used constructor injection.

public class CustomerController : Controller
{
    private readonly ISession _dbSession;

    public CustomerController(ISession dbSession)
    {
        _dbSession = dbSession;
    }

    //...
}

That’s it.

//Daniel

Vimeo channel with screencasts of SisoDb

Just created a channel on Vimeo, for screencasts about SisoDb. The first contribution is a short (8 minutes) Getting started episode.

Updated: Mike Paterson has a GitHub repository with code for the episode, found here: https://github.com/devlife/Sandbox/tree/master/SisoDb

I’ll keep filling this channel with short screencasts, hopefully never longer than 10minutes.

//Daniel

SisoDb v11.4.0 – with new named queries

So, the versions of SisoDb keeps pumping out and in this post I will cover a new feature introduced in v11.4.0. You can always have a look at the release notes to get full details of what has changed.

Refactoring friendly Named Queries

A named query in SisoDb is the invocation of a stored procedure returning JSON. As of v11.4.0 you can let SisoDb generate them as well as invoke them using lambda expressions. Lets have a look.

Normal Query
This is a basic query not using stored procedures. It can be consumed in two variations, via Session or via UseOnceTo.

using (var session = database.BeginSession())
{
    return session.Query<Customer>().Where(c => 
            c.CustomerNo >= customerNoFrom 
            && c.CustomerNo <= customerNoTo 
            && c.DeliveryAddress.Street == "The delivery street #544").ToArray();
}
return database.UseOnceTo().Query<Customer>().Where(c => 
        c.CustomerNo >= customerNoFrom 
        && c.CustomerNo <= customerNoTo 
        && c.DeliveryAddress.Street == "The delivery street #544").ToArray();

Named Query
A named query is represented by the INamedQuery interface. It’s very slim, and there’s one default implemantation of it, namely “NamedQuery”.

public interface INamedQuery
{
    string Name { get; }
    IEnumerable<IDacParameter> Parameters { get; }
    void Add(params IDacParameter[] parameters);
}

Consuming them means magic strings representing e.g properties of your models. This could of course be avoided if you implement/inherit any of these types and create a typed named query. But then you have a query that is detached from your model but implicitly dependent on any changes in it. Is there a better way? Sure!

Nicer Named Queries
First, we will let SisoDb create the stored procedure for us, using our model and lambda expressions.

using (var session = database.BeginSession())
{
    session.Advanced.UpsertNamedQuery<Customer>("CustomersViaSP", builder => 
        builder.Where(c =>
            c.CustomerNo >= customerNoFrom
            && c.CustomerNo <= customerNoTo
            && c.DeliveryAddress.Street == "The delivery street #544"));
}

This little snippet will create the stored procedure for us in the database. If the stored procedure exists, it will be overwritten. Now, lets consume it, and again using our model and lambda expressions:

using (var session = database.BeginSession())
{
    return session.Advanced.NamedQuery<Customer>("CustomersViaSP", c => 
        c.CustomerNo >= customerNoFrom
        && c.CustomerNo <= customerNoTo
        && c.DeliveryAddress.Street == "The delivery street #544").ToArray();
}

Does this have any impact on the performance of the query? Yes! The case below contains a rather small set of Customers. Only 5000 (read more about performance here) First, there’s two rows representing the manual query. Then there’s two rows representing the stored procedure. The first row in each pair takes a penalty hit, cause it’s the first time it’s executed. This is why your application should hold ONE LONG LIVED INSTANCE OF the ISisoDatabase.

The result: 0.0025s vs 0.0006s

That’s all about the new Named queries.

If any of the links above doesn’t work. The documentation is duplicated on GitHub.

//Daniel