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?

Solution 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 doesn't give you access to databases, you separately need sqlcmd utility which is sad..

Solution 2

I installed the mentioned .Net 4.0.2 update but I got the same error message saying:

A network-related or instance-specific error occurred while establishing a connection to SQL Server

I checked the SqlLocalDb via console as follows:

C:\>sqllocaldb create "Test"
LocalDB instance "Test" created with version 11.0.

C:\>sqllocaldb start "Test"
LocalDB instance "Test" started.

C:\>sqllocaldb info "Test"
Name:               Test
Version:            11.0.2100.60
Shared name:
Owner:              PC\TESTUSER
Auto-create:        No
State:              Running
Last start time:    05.09.2012 21:14:14
Instance pipe name: np:\\.\pipe\LOCALDB#B8A5271F\tsql\query

This means that SqlLocalDb is installed and running correctly. So what was the reason that I could not connect to SqlLocalDB via .Net code with this connectionstring: Server=(LocalDB)\v11.0;Integrated Security=true;?

Then I realized that my application was compiled for DotNet framework 3.5 but SqlLocalDb only works for DotNet 4.0.

After correcting this, the problem was solved.

Solution 3

This is a fairly old thread, but since I was reinstalling my Visual Studio 2015 Community today, I thought I might add some info on what to use on VS2015, or what might work in general.

To see which instances were installed by default, type sqllocaldb info inside a command prompt. On my machine, I get two instances, the first one named MSSQLLocalDB.

C:\>sqllocaldb info
MSSQLLocalDB
ProjectsV13

You can also create a new instance if you wish, using sqllocaldb create "some_instance_name", but the default one will work just fine:

// if not using a verbatim string literal, don't forget to escape backslashes
@"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;"

Solution 4

I had the same problem for a bit. I noticed that I had:

Data Source= (localdb)\v11.0"

Simply by adding one back-slash it solved the problem for me:

Data Source= (localdb)\\v11.0"

Solution 5

In Sql Server 2008 R2 database files you can connect with

Server=np:\\.\pipe\YourInstance\tsql\query;InitialCatalog=yourDataBase;Trusted_Connection=True;

only, but in sql Server 2012 you can use this:

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

and it depended on your .mdf .ldf version.

for finding programmicaly i use this Method that explained in this post

View more solutions

Comments

[Bill Nielsen]

I've already downloaded the latest. Here's what my Help->About ... shows: Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1Rel Microsoft .NET Framework Version 4.0.30319 SP1Rel

[Steve]

I suppose you have the latest then. Just like mine. Checked on Presentation.Core.dll with version 4.0.30319.275 (vs 245 on microsoft update article)

[Mahmood Jenami]

Best answer i have ever seen! I just installed .Net 4.5.2 and everything is Done! With my Love! :))

[Frank Liu]

it seems the connectionstring is case sensitive for me. I have to use "Server=(LocalDb)\v11.0;" to get it to work.

[Wai Ha Lee]

yes - "\v" is a vertical tab. Escaping the `` in some way is important.