Using User Instance with a .Net application

How to run a .Net application with a User Instance database.

First of all, be aware that this feature will be removed in a future version of Microsoft SQL Server.

Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

But for reference, this is how you can do this step by step.

First you will need a database of course, so let’s create one with a table and some rows:

create database MyUserinstance

go

use MyUserinstance

create table Person(id int identity, name nvarchar(50))

insert into Person values ('John Johnson')

insert into Person values ('Eric Ericson')

insert into Person values ('Paul Paulson')

Then we a need an application, so create a new C# console application, the code will look like this:

        static void Main(string[] args)

        {

            string mdf = Directory.GetCurrentDirectory() + @"\MyUserinstance.mdf";

            Console.WriteLine("Path {0}", mdf);

            string cs = @"Data Source=.\sqlexpress;Integrated Security=true;User Instance=true;AttachDbFilename=" + mdf;

            using (SqlConnection con = new SqlConnection(cs))

            {

                try

                {

                    con.Open();

  SqlCommand cmd = con.CreateCommand();

                    cmd.CommandText = "SELECT id, name FROM Person";

                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())

                    {

        Console.WriteLine("{0} {1}", rdr.GetInt32(0), rdr.GetString(1));

                    }

                    con.Close();

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

            }

            Console.WriteLine("Any key to quit...");

            Console.ReadKey();

        }

Basically this will look for the user instance in the same directory that contains the .exe.

Then for the sake of this demonstration let’s just copy the whole directory to the target machine (normally you would create a setup of some sort).

First, copy the database and log from the SQL Server DATA directory to the directory that contains the .exe for the application.

The SQL Server DATA directory is found here (if this is a default installation of SQL Server):

C:\Program Files\Microsoft SQL Server\MSSQL10.<your instance>\MSSQL\DATA

Then copy the whole thing to your target machine, which needs to have SQL Server Express running on it but no MyUserinstace.mdf of course.

And run. That is it really.

Some things to keep in mind.

. The only protocol allowed is Shared Memory, which means that SQL Server Express must be local, i.e. you cannot use a remote install.

. The database file (.mdf) must contain the full path. In the example above, I just use the current directory.

. The “|Data Directory|” part can be omitted; this is just a shortcut and will vary depending on the application being used.

. The authentication type must be Windows Authentication, SQL Authentication is not allowed.

. The SQL Express service must be running. Otherwise you will get a:

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

     Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)"

   which can be a bit confusing since this is running locally.

Some references:

"User Instances for Non-Administrators"

https://msdn.microsoft.com/en-us/library/ms143684.aspx

"SQL Server Express User Instances (ADO.NET)"

https://msdn.microsoft.com/en-us/library/ms254504.aspx

"Connection String Syntax (ADO.NET)" -> "Attaching to SQL Server Express User Instances"

https://msdn.microsoft.com/en-us/library/ms254500(VS.80).aspx