Home

SQL LocalDB Wrapper

Introduction

The System.Data.SqlLocalDb assembly provides a managed .NET API for calling the native C/C++ API of Microsoft SQL Server LocalDB.

This makes it easy for .NET projects to use SQL LocalDB to more easily perform lightweight operations with SQL Server where access to a full installation of Microsoft SQL Server (Express) is either unavailable or undesirable.

For example, the primary motivation for this assembly to be created in the first place was to allow for use of SQL LocalDB instances to test .NET code for SQL Server-based data access in continuous integration test scenarios where I wanted to remove the overhead of maintaining an SQL Server instance on which to run such tests.

Features

The assembly exposes all of the features of the SQL Server Express LocalDB Instance API as well as other convenience methods to provide a more object-oriented API than that of the native API. Other functionality is included to make it easy to connect to instances and use them, such as for manipulating connection strings.

Runtime Requirements

The assembly targets .NET 3.5 Service Pack 1 (SP1), so is supported for use in projects targeting .NET 3.5 SP1 and later. Therefore the .NET 2.0 Runtime with .NET Framework 3.5 SP1 is required to be installed on the local computer. Using the assembly with an earlier version of the .NET Framework may cause MissingMethodException to be thrown at runtime due to the use of functionality in .NET 3.5 SP1 being used internally, such as a number of security annotation attributes.

Both the 2012 and 2014 versions of Microsoft SQL Server LocalDB are supported by the assembly. At least one of these must be installed on the local computer to use the functionality of the assembly. If both versions are installed, then unless overridden by configuration, the latest version of the native API's DLL is always loaded. Once loaded by the process, it is not possible to unload the native API to change the SQL LocalDB Instance API version in use.

Examples

The Basics

First install the NuGet package into your project from the NuGet Package Manager:

Install-Package System.Data.SqlLocalDb

Then bring the assembly's namespace into scope in your code file:

using System.Data.SqlLocalDb

Now let's add a few lines of code to create a scope that we can use to create a named instance of SQL LocalDB and start it up ready for us to connect to and use:

ISqlLocalDbProvider provider = new SqlLocalDbProvider();
ISqlLocalDbInstance instance = provider.GetOrCreateInstance("MyInstance");

instance.Start();

using (SqlConnection connection = instance.CreateConnection())
{
    connection.Open();

    // Use the connection...
}

instance.Stop();

That's all there is to it!

The assembly contains richer functionality for more fine-grained control over SQL LocalDB, but the above is all you need to create and start an instance and obtain a SqlConnection instance pointing to it.

If the instance is throw-away and you don't need to use it outside the immediate scope, then you can simplify it even further like so:

using (var instance = TemporarySqlLocalDbInstance.Create())
{
    using (SqlConnection connection = instance.CreateConnection())
    {
        connection.Open();

        // Use the connection...
    }
}

Further Examples

More detailed examples of how to use the assembly can be found here.

Configuration

In the majority of cases, no additional configuration is required to use the assembly to manage SQL LocalDB instances.

The default behavior of SQL LocalDB, however, is to keep the files on-disk (such as logs) that are generated by SQL LocalDB instances during the existence. In a continuous integration usage scenario, where instances are frequently created and deleted, this may create large number of redundant files on the local file system that use up large amounts of disk space.

Since v1.11.0.0 it has been possible to change this behavior programmatically via the public API surface.

From v1.13.0.0 it is possible to configure this behavior for the entire consuming application using the <system.data.sqlLocalDb> configuration section. This is the recommended way to set up the assembly for such a usage scenario.

Similarly, stopping instances of SQL LocalDB takes a few seconds with the default settings which retain the default behavior of the native SQL LocalDB Instance API. If you are frequently stopping instances, for example in a continuous integration test scenario, you may wish to change the stop timeout and options to improve performance. An example configuration that improves performance for such a scenario is shown below:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="system.data.sqlLocalDb" type="System.Data.SqlLocalDb.Configuration.SqlLocalDbConfigurationSection, System.Data.SqlLocalDb" />
  </configSections>
  <system.data.sqlLocalDb stopOptions="NoWait" stopTimeout="00:00:10" />
</configuration>

For further details about configuration settings see here.