LocalDB Manager

• Download demo project - 33.7 KB

Introduction

Microsoft SQL Server 2012 Express LocalDB is a special execution mode of SQL Server Express which includes a minimum set of files required to start SQL Server Database Engine. It allows using SQL Server without complex configuration tasks.

SQL Server Express LocalDB instances are managed by using the SqlLocalDB.exe utility. LocalDB can be used to work with SQL Server databases. System database files for a database are stored in the user's local AppData folder, for example:

C:\Users\Azim\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances

and user database files are stored in the user's documents folder, for example:

C:\Users\Azim

To demonstrate the use of LocalDB, I have created a Windows Forms Application using Microsoft Visual Studio Express 2012 for Windows Desktop, which allows a user to manage LocalDB instances.

Background

LocalDB supports two types of instances, Automatic and Named.

Automatic instances are automatically created and managed for the user and can be used by any application. One automatic instance of LocalDB exists for each version of LocalDB installed. There is no need to create the instance because it already exists. Automatic instances are named as the letter 'v' followed by the LocalDB release version in the format xx.x. For example, v11.0.

To connect to an automatic instance using SQL Server Management Studio, use (LocalDB)\v11.0 as the Server name as follows:

Named instances are managed using the SqlLocalDB.exe application. The SqlLocalDB.exe program can be used to create, start, stop, destroy and get information about named instances.

The following command can be used to create a named instance called MYINSTANCE:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" create MYINSTANCE

The newly created instance can be started by the following command:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" start MYINSTANCE

Information about the instance can be found by the following command:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" info MYINSTANCE

which produces the following output.

The instance can be stopped by the following command:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" stop MYINSTANCE

The instance can be removed by the following command:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" delete MYINSTANCE

To connect to a named instance using SQL Server Management Studio, use the Instance Pipe Name as follows:

Using the code

I have a developed a Windows Forms Application in C# using Microsoft Visual Studio Express 2012 for Windows Desktop to manage named instances of LocalDB. The application accepts an instance name from the user and allows the user to create, start, stop, delete and obtain information about the named instance. The output of the commands is displayed in a read-only multiline textbox.

Following is the user interface of the application:

The application uses the System.Windows.Forms.OpenFileDialog class to locate the SqlLocalDB.exe file and the System.Diagnostics.Process class to manage the LocalDB instances.

Following is the full source code of the application:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;

namespace MyLocalDBManager
{
    public partial class Form1 : Form
    {
        FileDialog dialog;
        public Form1()
        {
            InitializeComponent();
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            DoProcess("create");
            // Call the user-defined DoProcess() method to create a named instance.
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            DoProcess("start");		// Start the named instance
        }

        private void btnStop_Click(object sender, EventArgs e)
        {
            DoProcess("stop");		// Stop the named instance
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DoProcess("delete");	// Remove the named instance
        }

        private void btnGetInfo_Click(object sender, EventArgs e)
        {
            DoProcess("info");		// Get Info about the named instance
        }

        private void DoProcess(string activity)
        {
            try
            {
                // Locate the SqlLocalDB.exe utility.
                // It is typically found in the
                // "C:\Program Files\Microsoft SQL Server\110\Tools\Binn" folder.
                dialog = new OpenFileDialog();
                dialog.Title = "Locate SqlLocalDB.exe";
                dialog.FileName = "SqlLocalDB.exe";
                dialog.Filter = "Executable files (*.exe)|*.exe|All files (*.*)|*.*";
                dialog.FilterIndex = 1;
                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    string command = "\"" + dialog.FileName + "\"";
                    ProcessStartInfo info = new ProcessStartInfo(command, " " + 
                      activity + " " + txtInstanceName.Text);	// Create a new ProcessStartInfo object
                    Process p = new Process();	// Create a new Process
                    p.StartInfo = info;		// Specify the StartInfo
                    p.StartInfo.UseShellExecute = false;	// Do not use the OS shell
                    p.StartInfo.RedirectStandardOutput = true;	// Allow writing output to the standard output
                    p.StartInfo.RedirectStandardError = true;	// Allow writing error to the standard error
                    p.Start();			// Start the process
                    p.WaitForExit();		// Wait for the process to exit
                    StreamReader reader = p.StandardOutput;	// Get Standard Output Stream
                    string output = reader.ReadToEnd();	// Read Standard Output
                    reader.Close();		// Close stream
                    if (output.Length == 0)	// If no output then display standard error
                    {
                        reader = p.StandardError;
                        string error = reader.ReadToEnd();
                        reader.Close();
                        txtResult.Text = error;	// Display error
                    }
                    else
                    {
                        txtResult.Text = output;// Display output
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

The above code uses a user-defined function called DoProcess which takes a parameter indicating the activity (create, start, stop, delete, or info) to be performed. This function performs the activity and captures its standard output or standard error and displays it in a multiline textbox.

Following is the typical output of the application:

The instance pipe name can be copied from the multiline textbox and pasted in the Server name in the Connect to Server dialog in SQL Server Management Studio as follows:

Points of Interest

LocalDB simplifies the task of working with Microsoft SQL Server 2012. Also since LocalDB runs under the user's security context, all database files used by a LocalDB instance can be accessed from the user's Windows Account.

I sincerely hope that my article will be helpful to someone out there who wants to work with Microsoft SQL Server 2012.