{"id":371,"date":"2011-11-11T21:45:09","date_gmt":"2011-11-11T10:45:09","guid":{"rendered":"http:\/\/ntsblog.homedev.com.au\/?p=371"},"modified":"2013-01-24T01:11:14","modified_gmt":"2013-01-23T14:11:14","slug":"how-to-run-mvcmusicstore-v3-0-with-sql-server-express","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2011\/11\/11\/how-to-run-mvcmusicstore-v3-0-with-sql-server-express\/","title":{"rendered":"How to run MvcMusicStore v3.0 with SQL Server Express"},"content":{"rendered":"<div id=\"ntsbl-599149603\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p>I&#8217;ve been trying to do the MVCMusicStore v3.0b tutorial, and I wanted to use SQLExpress rather than SqlServerCe.4.0.<br \/>\nWhen trying to do this I ran into a number of problems but I finally figured it out.<\/p>\n<p>\nThis guide assumes you already have a working copy of SQL Server Express and that your user account is a System Administrator for the database.<\/p>\n<p>\nThis is the link to the PDF document for the v3.0b tutorial. <a href=\"http:\/\/mvcmusicstore.codeplex.com\/releases\/view\/64379#DownloadId=228002\">MvcMusicStore v3.0<\/a><\/p>\n<p>\n(*all page numbers quoted as on the document, page 45 is actually Page 46 when listed in Acrobat)<\/p>\n<p>\nBelow is what I did and the 2 solutions that I found..<\/p>\n<ol>\n<li>As on page 45* I created the AppData Folder.\n<li>I downloaded the MVC assets and extracted the files from the zip file.\n<li>Right click on AppData and add an existing file, and add in the MvcMusicStore.mdf.\n<li>Edit the web.config and added the following connection string:<br \/>\n\t[xml]<br \/>\n&lt;connectionStrings&gt;<br \/>\n  &lt;add name=&quot;MusicStoreDb&quot;<br \/>\n    connectionString=&quot;Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|MvcMusicStore.mdf;Integrated Security=True;&quot;<br \/>\n    providerName=&quot;System.Data.SqlClient&quot; \/&gt;<br \/>\n&lt;\/connectionStrings&gt;<br \/>\n\t[\/xml]<\/p>\n<li>Added the MusicStoreEntities.cs as on page 47.\n<\/ol>\n<p>At this point Debug the site and I was getting errors. First I was getting errors about meta data failures.<br \/>\nI 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. <\/p>\n<p>Then I noticed the following 2 issues:<\/p>\n<ol>\n<li>Write some code like the following<br \/>\n\t\t[csharp]<br \/>\n\t\t\tMusicStoreEntities db = new MusicStoreEntities();<br \/>\n\t\t\tstring connection = db.Database.Connection.ConnectionString;<br \/>\n\t\t[\/csharp]<br \/>\n\t\tRun up in debug and check the value stored in the connection string.<br \/>\n\t\tNote that the database name is related to the application MvcMusicStore, &#8220;MvcMusicStore.Models.MusicStoreEntities&#8221;? WTF?\n\t<\/li>\n<li>\n\t\tPut a watch on the db.Database.Genres object and note the sql statement is is going to execute. The table name is &#8220;dbo.Genres&#8221;.<br \/>\n\t\tThe issue? Your datbase doesn&#8217;t have a &#8220;Genres&#8221; table it has a &#8220;Genre&#8221; table (minus the &#8216;s&#8217;).\n\t<\/li>\n<\/ol>\n<h2>Solution 1<\/h2>\n<p>So to fix these issues I have done the following:<\/p>\n<ol>\n<li>\n\t\tCreate a default constructor in the MvcMusicStoreEntities class like follows:<br \/>\n[csharp]<br \/>\npublic MusicStoreEntities()<br \/>\n{<br \/>\n    this.Database.Connection.ConnectionString =<br \/>\n           ConfigurationManager.ConnectionStrings[&quot;MusicStoreDb&quot;].ConnectionString;<br \/>\n}<br \/>\n[\/csharp]<br \/>\n\t\tThis over-rides the connection string with the one that we have specified in the web.config.<br \/>\n\n\t<\/li>\n<li>\n\t\tPluralise your database. <\/p>\n<p>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.<br \/>\n\t\tRight click on each table and select &#8220;Open Table Definition&#8221;. Press F4 to view the properties window, Edit the &#8220;Name&#8221; property and change the name of the table and add an &#8216;s&#8217; to the end, Genre -> Genres, Album -> Albums, Artist -> Artists etc. Save the file to apply the name change.\n\t<\/li>\n<li>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<br \/>\nIn your MusicStoreEntities class add the following<br \/>\n[csharp]<br \/>\n        using System.Data.Entity.ModelConfiguration.Conventions;<\/p>\n<p>        protected override void OnModelCreating(DbModelBuilder modelBuilder)<br \/>\n        {<br \/>\n            modelBuilder.Conventions.Remove&lt;PluralizingTableNameConvention&gt;();<br \/>\n        }<br \/>\n[\/csharp]\n<\/li>\n<p>\tDebug your site and it should all work now.\n<\/ol>\n<h2>Solution 2<\/h2>\n<p>In attempting to work out the above solution I also found a second solution.<\/p>\n<ol>\n<li>\n\tEnsure the constructor is removed from the MusicStoreEntities class.\n\t<\/li>\n<li>\n\t\tEdit the web.config file and add the following connection string<br \/>\n\t\t[xml]<br \/>\n\t\t&lt;connectionStrings&gt;<br \/>\n\t\t\t&lt;add name=&quot;MusicStoreDb&quot;<br \/>\n\t\t\t  connectionString=&quot;Data Source=.\\SQLEXPRESS;Integrated Security=True;&quot;<br \/>\n\t\t\t  providerName=&quot;System.Data.SqlClient&quot; \/&gt;<br \/>\n\t\t&lt;\/connectionStrings&gt;<br \/>\n\t\t[\/xml]\n\t<\/li>\n<li>\n\t\tAdd the Sample Data as on page 47 &#038; 48 of the PDF guide.\n\t<\/li>\n<p>\tDebug your site and it should work. <\/p>\n<p>\n\tLaunch 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.<\/p>\n<\/ol>\n<p>Hope this works for you as it did for me. Happy MVC&#8217;ing.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5,20],"tags":[],"class_list":["post-371","post","type-post","status-publish","format-standard","hentry","category-c","category-mvc"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/371","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=371"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/371\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}