What is the connection string for localdb for version 11
C#Sql ServerConnection StringSql Server-2012LocaldbC# Problem Overview
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?
C# Solutions
Solution 1 - C#
-
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.
-
Next you need to have an instance of
LocalDb
. By default you have an instance whose name is a singlev
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 theSqlLocalDb.exe
utility (run it from command line). -
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.
-
If you have a database (.mdf file) already:
"Server=(localdb)\\Test;Integrated Security=true;AttachDbFileName= myDbFile;"
-
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 - C#
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 - C#
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 - C#
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 - C#
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
Solution 6 - C#
You need to install Dot Net 4.0.2 or above as mentioned here.
The 4.0 bits don't understand the syntax required by LocalDB
You can dowload the update here
Solution 7 - C#
This is for others who would have struggled like me to get this working....I wasted more than half a day on a seemingly trivial thing...
If you want to use SQL Express 2012 LocalDB from VS2010 you must have this patch installed http://www.microsoft.com/en-us/download/details.aspx?id=27756
Just like mentioned in the comments above I too had Microsoft .NET Framework Version 4.0.30319 SP1Rel and since its mentioned everywhere that you need "Framework 4.0.2 or Above" I thought I am good to go...
However, when I explicitly downloaded that 4.0.2 patch and installed it I got it working....
Solution 8 - C#
I have connection string Server=(localdb)\v11.0;Integrated Security=true;Database=DB1;
and even a .NET 3.5 program connects and execute SQL successfully.
But many people say .NET 4.0.2 or 4.5 is required.