Home

SQL LocalDB Wrapper

Introduction

The MartinCostello.SqlLocalDb library 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 Server 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 Server 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 and manage instances.

Runtime Requirements

The library targets .NET Standard 2.0, so can be referenced from any .NET application that supports .NET Standard 2.0, such as .NET Core 2.0 and later and .NET Framework 4.6.1 and later.

Versions of Microsoft SQL Server LocalDB from 2012 are supported by the library.

At least one version must be installed on the computer running an application using the library to be able use the functionality.

If multiple versions are installed then, unless overridden by configuration, the latest version of the native API's DLL is always loaded.

Examples

The Basics

First install the NuGet package into your project:

Project File

<PackageReference Include="MartinCostello.SqlLocalDb" Version="2.0.0" />

.NET Core SDK

dotnet add package MartinCostello.SqlLocalDb

Visual Studio NuGet Package Manager

Install-Package MartinCostello.SqlLocalDb

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

using MartinCostello.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 Server LocalDB and start it up ready for us to connect to and use:

using (var localDB = new SqlLocalDbApi())
{
    ISqlLocalDbInstanceInfo instance = localDB.GetOrCreateInstance("MyInstance");
    ISqlLocalDbInstanceManager manager = instance.Manage();

    if (!instance.IsRunning)
    {
        manager.Start();
    }

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

        // Use the SQL connection...
    }

    manager.Stop();
}

That's all there is to it!

The library contains richer functionality for more fine-grained control over SQL Server 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 localDB = new SqlLocalDbApi())
{
    using (TemporarySqlLocalDbInstance instance = localDB.CreateTemporaryInstance(deleteFiles: true))
    {
        using (var connection = new SqlConnection(instance.ConnectionString))
        {
            connection.Open();

            // Use the SQL connection...
        }
    }
}

Further Examples

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

Configuration

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

The default behaviour of SQL Server LocalDB, however, is to keep the files on-disk (such as logs) that are generated by SQL Server LocalDB instances during their 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.

Similarly, stopping instances of SQL Server LocalDB takes a few seconds with the default settings which retain the default behaviour of the native SQL Server 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:

var options = new SqlLocalDbOptions()
{
    StopOptions = StopInstanceOptions.NoWait,
    StopTimeout = TimeSpan.FromSeconds(10),
};

var localDB = new SqlLocalDbApi(options);

For further details about configuration settings see here.