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?

Answer 1

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.