Home > c#, MVC > How to run MvcMusicStore v3.0 with SQL Server Express

How to run MvcMusicStore v3.0 with SQL Server Express

I’ve been trying to do the MVCMusicStore v3.0b tutorial, and I wanted to use SQLExpress rather than SqlServerCe.4.0.
When trying to do this I ran into a number of problems but I finally figured it out.

This guide assumes you already have a working copy of SQL Server Express and that your user account is a System Administrator for the database.

This is the link to the PDF document for the v3.0b tutorial. MvcMusicStore v3.0

(*all page numbers quoted as on the document, page 45 is actually Page 46 when listed in Acrobat)

Below is what I did and the 2 solutions that I found..

  1. As on page 45* I created the AppData Folder.
  2. I downloaded the MVC assets and extracted the files from the zip file.
  3. Right click on AppData and add an existing file, and add in the MvcMusicStore.mdf.
  4. Edit the web.config and added the following connection string:
    <connectionStrings>
      <add name="MusicStoreDb"
        connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MvcMusicStore.mdf;Integrated Security=True;"
        providerName="System.Data.SqlClient" />
    </connectionStrings>
    	
  5. Added the MusicStoreEntities.cs as on page 47.

At this point Debug the site and I was getting errors. First I was getting errors about meta data failures.
I managed to play a bit more and somehow fixed the meta data issues so that it was no longer erroring, but the queries were not returning any data.

Then I noticed the following 2 issues:

  1. Write some code like the following
    			MusicStoreEntities db = new MusicStoreEntities();
    			string connection = db.Database.Connection.ConnectionString;
    		

    Run up in debug and check the value stored in the connection string.
    Note that the database name is related to the application MvcMusicStore, “MvcMusicStore.Models.MusicStoreEntities”? WTF?

  2. Put a watch on the db.Database.Genres object and note the sql statement is is going to execute. The table name is “dbo.Genres”.
    The issue? Your datbase doesn’t have a “Genres” table it has a “Genre” table (minus the ‘s’).

Solution 1

So to fix these issues I have done the following:

  1. Create a default constructor in the MvcMusicStoreEntities class like follows:

    public MusicStoreEntities()
    {
        this.Database.Connection.ConnectionString = 
               ConfigurationManager.ConnectionStrings["MusicStoreDb"].ConnectionString;
    }
    

    This over-rides the connection string with the one that we have specified in the web.config.

  2. Pluralise your database.

    If your hosting your database on SQL Server you can use the SQL Management studio, or if you are hosting it in AppData then use the server explorer.
    Right click on each table and select “Open Table Definition”. Press F4 to view the properties window, Edit the “Name” property and change the name of the table and add an ‘s’ to the end, Genre -> Genres, Album -> Albums, Artist -> Artists etc. Save the file to apply the name change.

  3. Edit: A bit more digging and I also found this solution to the Pluralised problem, this removes the pluralised table names. Probably a bit easier than the above solution
    In your MusicStoreEntities class add the following

            using System.Data.Entity.ModelConfiguration.Conventions;
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            }
    
  4. Debug your site and it should all work now.

Solution 2

In attempting to work out the above solution I also found a second solution.

  1. Ensure the constructor is removed from the MusicStoreEntities class.
  2. Edit the web.config file and add the following connection string

    		<connectionStrings>
    			<add name="MusicStoreDb"
    			  connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;"
    			  providerName="System.Data.SqlClient" />
    		</connectionStrings>
    		
  3. Add the Sample Data as on page 47 & 48 of the PDF guide.
  4. Debug your site and it should work.

    Launch SQL Mangement Studio and refresh the list of databases, you will see you new database [MvcMusicStore.Models.MusicStoreEntities] and look at the tables and they have pluralised names.

Hope this works for you as it did for me. Happy MVC’ing.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
Categories: c#, MVC Tags:
  1. Irving
    December 17th, 2012 at 02:26 | #1

    Hi, i would like to know why you add the mdf file to the appdata folder? i didnt included and
    it still works and create the database so im confused about why you added..

    Thanks in advance

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  2. Irving
    December 17th, 2012 at 02:56 | #2

    if im using slqexpress 2008 do i need the appdata folder? because i even deleted the appdata folder and it still creates the db and works fine

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • jcrawfor74
      December 18th, 2012 at 00:31 | #3

      Hi Irving,
      I will answer both your questions here.
      As you work through the MVC solution you will get to page 46 and it suggests.
      “App_Data is a special directory in ASP.NET which already has the correct security access permissions for database access”

      In the solution 1 example on the above article, note the connection string..
      connectionString=”Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MvcMusicStore.mdf;Integrated Security=True;” providerName=”System.Data.SqlClient” />

      With this connection string you are telling SQL Express to Attach a Database file called MvcMusicStore.mdf which is contained in the “DataDirectory”.
      The data directory is the AppData folder which you should have created and copied in the MvcMusicStore.mdf, (which is contained in the MvcMusicStore-Assets.zip found here http://mvcmusicstore.codeplex.com/releases/view/64379).

      So if you are using the connection string in solution 1, then you should create the app_data directory.

      However, if you use solution 2, you are using a connection string that just points @ SQL Express. In this example the database is created the first time you debug the application and the .mdf file is located where ever you have SQL Server Express configured to store its databse files. If you have SQL Management Studio you should be able to list all your databases and find the database, [MvcMusicStore.Models.MusicStoreEntities]. This is your MVC database and therefore you do not require the App_Data Folder for this example.

      Regards
      John

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  3. javier
    September 4th, 2015 at 06:13 | #4

    The solution 1 function okey !! thanks

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  1. No trackbacks yet.