SQL Server Express connection string for Entity Framework Code First

Question

I am working in Visual Web Developer 2010 Express, and using the Entity Framework code-first CTP. I am able to do this with the new SQL Server CE but I am unable to find a connection string to work with SQL Server Express.

This one, using the SQL Server CE beta, works fine (database is created and recreated on model changes).

<add name="TrempimModel"
     connectionString="data source=|DataDirectory|TrempimModel.sdf"
     providerName="System.Data.SqlServerCe.4.0" />

This one, which I copied from the aspnetdb connections string,

<add name="TrempimModel"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
     AttachDBFilename=|DataDirectory|TrempimModel.mdf;User Instance=true"
     providerName="System.Data.SqlClient" />

Gives the following message on startup:

Unable to complete operation. The supplied SqlConnection does not specify an initial catalog

So how to make it work with SQL Server Express?

marc_s · Accepted Answer · 2010-11-07T10:25:06.440

62

The problem with your connection string here is:

<add name="TrempimModel"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
                       AttachDBFilename=|DataDirectory|aspnetdb.sdf;
                       User Instance=true"
     providerName="System.Data.SqlClient" />

You're basically defining what "server" you're connecting to - but you're not saying what database inside the file to connect to. ~~Also - the file extension for SQL Server Express database files is .mdf (not .sdf - that's SQL Server Compact Edition) - you need to take that into account, too!~~ (was a typo, according to comment by OP).

You need to define an extra database=.... (or Initial Catalog=.....) in your connection string:

<add name="TrempimModel"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
                       database=YourDatabaseName;
                       AttachDBFilename=|DataDirectory|aspnetdb.mdf;
                       User Instance=true"
     providerName="System.Data.SqlClient" />

Then it should work just fine.

For more background and tons of samples, check out the ConnectionStrings web site.

edited Nov 07 '10 at 10:25

answered Nov 07 '10 at 09:28

marc_s

732,580
175
1,330
1,459

a) the sdf extension was a typo. I was using mdf in the connection string - edited the question to reflect that. the suggestion to specify the database solved my problem - thanks. I'm using the same name as the file name - is that correct or can it cause some problem down the line? – GilShalit Nov 07 '10 at 10:14
@GilShalit: it depends on what the database inside your MDF is really called; chances are high that it's the same name as the file name. You would have to verify that by attaching the MDF to a full-fledged SQL Server instance and see what the database stored inside the MDF is really called. – marc_s Nov 07 '10 at 10:27
as you seem to know a lot about this, I don't suppose if you have a moment you can take a look at my question... I can get the connection string to work but then if I try to edit/use the database, I get so many errors :( - http://stackoverflow.com/questions/5924553/entity-framework-error-connecting-to-sql-server-express-after-mounting-connecting and reposted to MSDN - http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/1ec3ff63-d8a1-4844-942d-c2c05ed8078e – Wil May 10 '11 at 12:42

SQL Server Express connection string for Entity Framework Code First

1 Answers1

Linked