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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s