What is the connection string for localdb for version 11

Question

I'm trying to do the Code First Walkthrough of the entity framework ( http://blogs.msdn.com/b/adonet/archive/2011/09/28/ef-4-2-code-first-walkthrough.aspx ).

I have the latest SQL Server Express and when I check my versions available via command line (sqllocaldb info): I see localdbApp1 and v11.0. When I try to run the walkthrough with a few minor tweaks, I get a can't connect error.

My app.config looks like this:

<parameter value="Server=(LocalDB)\v11.0; Integrated Security=True; MultipleActiveResultSets=True" />

I wrote a simple connection test like below and the code returns the same SQL Connection error ((provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)).

new System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\v11.0; Integrated Security=True; MultipleActiveResultSets=True").Open();

I've tried replacing "Data Source=..." with "Server=..." but to no avail there.

Any ideas what the connection string should be?

Answer 1

1) Requires .NET framework 4 updated to at least 4.0.2. If you have 4.0.2, then you should have

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319\SKUs\.NETFramework,Version=v4.0.2

If you have installed latest VS 2012 chances are that you already have 4.0.2. Just verify first.

2) Next you need to have an instance of LocalDb. By default you have an instance whose name is a single v character followed by the LocalDB release version number in the format xx.x. For example, v11.0 represents SQL Server 2012. Automatic instances are public by default. You can also have named instances which are private. Named instances provide isolation from other instances and can improve performance by reducing resource contention with other database users. You can check the status of instances using the SqlLocalDb.exe utility (run it from command line).

3) Next your connection string should look like:

"Server=(localdb)\\v11.0;Integrated Security=true;"

or

"Data Source=(localdb)\\test;Integrated Security=true;"

from your code. They both are the same. Notice the two \\ required because \v and \t means special characters. Also note that what appears after (localdb)\\ is the name of your LocalDb instance. v11.0 is the default public instance, test is something I have created manually which is private.

1. If you have a database (.mdf file) already:

   "Server=(localdb)\\Test;Integrated Security=true;AttachDbFileName= myDbFile;"
   

2. If you don't have a Sql Server database:

   "Server=(localdb)\\v11.0;Integrated Security=true;"
   

And you can create your own database programmatically:

a) to save it in the default location with default setting:

var query = "CREATE DATABASE myDbName;";

b) To save it in a specific location with your own custom settings:

// your db name
string dbName = "myDbName";

// path to your db files:
// ensure that the directory exists and you have read write permission.
string[] files = { Path.Combine(Application.StartupPath, dbName + ".mdf"), 
                   Path.Combine(Application.StartupPath, dbName + ".ldf") };

// db creation query:
// note that the data file and log file have different logical names
var query = "CREATE DATABASE " + dbName +
    " ON PRIMARY" +
    " (NAME = " + dbName + "_data," +
    " FILENAME = '" + files[0] + "'," +
    " SIZE = 3MB," +
    " MAXSIZE = 10MB," +
    " FILEGROWTH = 10%)" +

    " LOG ON" +
    " (NAME = " + dbName + "_log," +
    " FILENAME = '" + files[1] + "'," +
    " SIZE = 1MB," +
    " MAXSIZE = 5MB," +
    " FILEGROWTH = 10%)" +
    ";";

And execute!

A sample table can be loaded into the database with something like:

 @"CREATE TABLE supportContacts 
    (
        id int identity primary key, 
        type varchar(20), 
        details varchar(30)
    );
   INSERT INTO supportContacts
   (type, details)
   VALUES
   ('Email', '[email protected]'),
   ('Twitter', '@sqlfiddle');";

Note that SqlLocalDb.exe utility doesnt give you access to databases, you separately need sqlcmd utility which is sad..

EDIT: moved position of semicolon otherwise error would occur if code was copy/pasted