MVC Music Store - Connect to Compact SQL DB with SQL Manager

Dec 5, 2011 at 5:28 PM

I'd like to open up the Database a part of the MVC Music Store project with SQL Manager.  I'm not having any luck. SQL Manager has the option to connect to a Compact SQL DB but also asks for a password. I'm not sure if or what password is established with this project. Any ideas on how I can do this? Here is was I can gleam from the config.

web.config

  <connectionStrings>
    <add name="MusicStoreEntities"
      connectionString="Data Source=|DataDirectory|MvcMusicStore.sdf"
      providerName="System.Data.SqlServerCe.4.0"/>
  </connectionStrings>

Dec 7, 2011 at 1:14 AM
Edited Dec 7, 2011 at 1:53 AM

@Billjenner, How did you manage to open the SDF file in SSMS? I have SSMS with 2008 R2 installed and unable to open the SDF file.

@mvcmusicstore Can you guys provide the MDF file just to avoid these these kind of problems? 

[EDIT] - I could see MvcMusicStore.mdf DB provided under MvcMusicStore-Assets\Data folder! :)

Thanks in Advance!

Dec 7, 2011 at 8:19 AM

@pavangayakwad - No, I'm not able to open the SDF DB file with SSMS either (same problem as you). SSMS doesn't appear to allow integrated security with a compact server. User ID and password is required in this mode. Please share if you have a work around.

I have had luck with the following:

1) Open SDF through Visual Studio "Connect to DataBase" option. Then I can browse tables with minimum functionality.

2) Create a MDF DB instead using default SQLExpress engine: A) Delete the existing SDF DB, B) set the SampleData class to inherit from DropCreateDatabaseAlways insead of if model changes (just to force a new DB), C) Delete the entire web.config connectionstring to force default to SQLExpress and MDF.

 <connectionStrings>
    <add name="MusicStoreEntities"
      connectionString="Data Source=|DataDirectory|MvcMusicStore.sdf"
      providerName="System.Data.SqlServerCe.4.0"/>
  </connectionStrings>

Alternatively, you can probably change the connection string to ".... MvcMusicStore.mdf ... providerName="System.Data.SqlClient" , but I have not tried this yet myself.