Yoda Yoda - 1 month ago 11
SQL Question

What providerName and connectionStrings should be used for MS SQL Compact Edition in MVC 4 in Visual Studio 2013 in ASP.NET MVC 4?

What

providerName
should be used in
Web.config
file for MS SQL Compact Edition in MVC 4 in Visual Studio 2013?

Introduction


When I use:

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


my website database works and
Genre
objects are retrieved from database and displayed

enter image description here

after I change of
providerName
to
providerName="System.Data.SqlClient
like this:

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


I get:

enter image description here

So WHY I want to change anything?



Actual problem:


If I leave
providerName="System.Data.SqlServerCe.4.0"
when I want to add new controller by this menu:

enter image description here

I get:

enter image description here

but if I set
providerName="System.Data.SqlClient
the
StoreManagerController
is generated but the database does not work at all.

Question: What should I do to generate
StoreManagerController
? I follow this tutorial: http://www.asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-5 and link to the working project just before that step it: http://www.speedyshare.com/RGdqH/MvcMusicStore.zip

Answer

So bare in mind the "Mvc Music Store" was MVC 101 since the beginning of MVC. Given there have been a lot of changes, the chances of the documentation/walk-throughts being a little off are unfavorable. With that said:

The T4 template for the data-driven controller is obviously failing due to your connection string. This is most likely because SQL Express (in it of itself) has gone through a lot of cycles and is now following LocalDB. Given you're using MVC4, it's probably a good time to upgrade, and this means you can have all the benefits of SQL Express (including the System.Data.SqlClient provider) without the possible headaches from previous revisions.

With that said, change your connection string to use the new format:

<connectionStrings>
    <add name="MusicStoreEntities"
         connectionString="Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=|DataDirectory|MvcMusicStore.mdf"
         providerName="System.Data.SqlClient" />
</connectionStrings>

And (probably) re-run your DatabaseInitializer (if I recall correctly, this should happen automatically since it's using EF. If not, you can go to your Package Manager console and run Update-Database).

This now gives you the provider the Controller template wants while still keeping it as a local (dev) database.