Is there a way to create a sqllocaldb instance in c#?

Question

Is there a way in c# to directly create a SQLLOCALDB instance? At the moment, we use sqllocaldb.exe, but need to be able to do it without using the external executable.

Placentation

Why can't you use the command line tool? Could you call the executable from C# using Process.Start? – Bagworm

Have a look at SMO (Sql Server Management Objects). codeproject.com/Articles/13755/SMO-Manage-your-SQL-Server – Gambol

Calling out to an external executable is viewed as a security risk so Process.Start to the .EXE won't work for us. – Placentation

There was another entry here: #17407974 that went unanswered where someone was asking about using SMO to create a database instance. The codeproject article seems to be able to address everything after the instance is created. – Placentation

Answer 1

You could use the SQL LocalDB Wrapper library.

It wraps the native APIs exposed by SQL LocalDB in a .NET 4.0 assembly.

It is also now available via NuGet.

Disclaimer: I maintain this library.

Carrasquillo

Answer 2

If you want full control over your LocalDB configuration and don't mind having an additional dependency in your project, just use Martin Costello's NuGet library.

If you just want to ensure that a specific LocalDB instance exists and you know the exact version (and, thus, installation path) of your LocalDB instance, it might be easier to just P/Invoke LocalDBCreateInstance. Here's an example using SQL Server 2019 LocalDB:

[DllImport("C:\\Program Files\\Microsoft SQL Server\\150\\LocalDB\\Binn\\SqlUserInstance.dll", CharSet = CharSet.Unicode, CallingConvention = CallingConvention.Cdecl)]
private static extern int LocalDBCreateInstance(string wszVersion, string pInstanceName, int dwFlags);

...

// LocalDBCreateInstance also returns success (0) if the instance already
// exists, so there's no need to check.
var result = LocalDBCreateInstance("15.0", myInstanceName, 0);

if (result != 0)
{
    throw new InvalidOperationException(
        $"LocalDBCreateInstance returned error code {result:X}.");
}

Bernat

Thanks, I followed this approach. However, I got a PInvokeStackImbalance warning when running this under the debugger. I added CallingConvention=CallingConvention.Cdecl to the DllImport to fix that. – Elspet

@WimCoenen: You're right. I just double-checked with msoledbsql.h and, indeed, it's a cdecl function. Thanks, I've fixed this (in my SO answer and in my own code). ;-) – Bernat

Answer 3

I spotted a couple of projects that wrapper the native localdb API. I have not personally tried either of these but the following may be of use to you.

http://www.nuget.org/packages/LocalDbApi/
http://sqllocaldb.codeplex.com/

English