SQL Server Database issues

Jan 17, 2012 at 1:59 AM

I am trying to get the MVC Music Store application to work with SQL Server Express.

This task is proving far trickier than PHP / MySQL.

I am running Windows XP SP3, SQL Server Express 2008 with Management Studio (no SP)

and Visual Studio Express 2010 with MVC 3.0 (no SP but hotfixes and updates applied)

I have downloaded and unzipped MvcMusicStore-v3.0.zip

I do not want SQL Server compact edition.

To my way of thinking I should be able to

1) attach the MvcMusicStore.mdf in the assets folder to SQL Express

or

2) copy the MvcMusicStore.mdf file to the App_Data folder and 'include in project'

then

modify the connection string in the web.config to point to either database

and run.

Failing either the above I should be able to in SQL Express create a new database

called MvcMusicStore and then run the MvcMusicStore-Create.sql file found in the

Assets/Data folder to populate the database.

Again, modify the web.config file to point to this database and off we go.

There maybe an additional step where I give the ASPNET account permissions

in SQL Server or configure Integrated Security.

After trying various combinations of the above I have failed to get the application to work.

Error messages include

'Model compatibility cannot be checked because the database does not contain model metadata.'

 

The databse does not look complicated as far as I can see there are only 6 tables and no stored

procedures.

A search on this site and ASP.Net and Google has not provided a simple, straightforward answer.

 

Apart from not using PHP/MySQL what else am I doing wrong?

 

Thanks for your patience

Charlie

Jan 18, 2012 at 1:24 AM

Hey!

I finally got it working in SQL Express!

Here are the steps I used if anyone is interested:

1) create MvcMusicStore db in SQLExpress and run the MvcMusicStore-Create.sql script found in the Assets/Data folder
don't worry about all the errors at the start.

2) Connection String in web.config (obviously change CHARLIECOMPUTER to your machine name

    <add name="MusicStoreEntities"
     connectionString="Data Source=CHARLIECOMPUTER\SQLEXPRESS;Initial Catalog=MvcMusicStore;Integrated Security=SSPI;"
      providerName="System.Data.SqlClient"/>

I have CHARLIECOMPUTER\ASPNET as a database user for MvcMusicStore if that's relevant.

3)  comment out line 34 in global.asax

//System.Data.Entity.Database.SetInitializer(new MvcMusicStore.Models.SampleData());

4)  add to MusicStoreEntities.cs

using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Conventions;

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }


and it works!

My thanks to
http://stevesmithblog.com/blog/database-cannot-be-opened-version-655/
and
http://stevesmithblog.com/blog/entity-framework-invalid-object-name-dbo-albums/


Now that I have got it to work I don't think I will waste any more time on it as it looks terrible!
If there are too many albums, as in Classical compared to Pop, it displays them differently in my browser (FFox).

I just have a feeling if it is this difficult to get it to work there will be lots more problems and bad habits down the line.
The 'Remove from Cart' function is playing up already.

I can't see any great steps forward over well written and unit tested webforms applications to be honest.
And I am not looking to get into a debate about Dependency Injection - thanks.

Thanks for efforts Jon - it must be a thankless task at times.

Charlie

Coordinator
Jan 18, 2012 at 9:11 AM
Glad you got it to work, Charlie. I think you'd find things would go easier if you used the recommended software prereq's. Also, this really is written as a tutorial that's meant to be followed step by step. If you do that, you wouldn't run into database problems since the database is automatically built for you.
Also, the tutorial focus explains some of the choices I had to make along the way - for instance, I slimmed the CSS way down for people who would need to type it in, but that does cause rendering problems in Firefox as you noticed. Will fix that in the next release.
I would agree that you can build great, well written applications with Web Forms. I will say that - having built a lot of sites on both technologies, and in a few cases built essentially the exact same app in both - there's a transition point where the structure and patterns focus in MVC make it very worthwhile. Large Web Forms applications take a good amount of discipline to keep your architecture clean, whereas MVC makes that the default behavior. Also, while ASP.NET 4 Web Forms do improve the HTML markup, it's definitely easier to write nice HTML in MVC.
On Tue, Jan 17, 2012 at 5:24 PM, charlie303 <notifications@codeplex.com> wrote:

From: charlie303

Hey!

I finally got it working in SQL Express!

Here are the steps I used if anyone is interested:

1) create MvcMusicStore db in SQLExpress and run the MvcMusicStore-Create.sql script found in the Assets/Data folder
don't worry about all the errors at the start.

2) Connection String in web.config (obviously change CHARLIECOMPUTER to your machine name

<add name="MusicStoreEntities"
connectionString="Data Source=CHARLIECOMPUTER\SQLEXPRESS;Initial Catalog=MvcMusicStore;Integrated Security=SSPI;"
providerName="System.Data.SqlClient"/>

I have CHARLIECOMPUTER\ASPNET as a database user for MvcMusicStore if that's relevant.

3) comment out line 34 in global.asax

//System.Data.Entity.Database.SetInitializer(new MvcMusicStore.Models.SampleData());

4) add to MusicStoreEntities.cs

using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Conventions;

protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}


and it works!

My thanks to
http://stevesmithblog.com/blog/database-cannot-be-opened-version-655/
and
http://stevesmithblog.com/blog/entity-framework-invalid-object-name-dbo-albums/


Now that I have got it to work I don't think I will waste any more time on it as it looks terrible!
If there are too many albums, as in Classical compared to Pop, it displays them differently in my browser (FFox).

I just have a feeling if it is this difficult to get it to work there will be lots more problems and bad habits down the line.
The 'Remove from Cart' function is playing up already.

I can't see any great steps forward over well written and unit tested webforms applications to be honest.
And I am not looking to get into a debate about Dependency Injection - thanks.

Thanks for efforts Jon - it must be a thankless task at times.

Charlie

Read the full discussion online.

To add a post to this discussion, reply to this email (mvcmusicstore@discussions.codeplex.com)

To start a new discussion for this project, email mvcmusicstore@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Feb 22, 2012 at 4:44 PM
Edited Feb 22, 2012 at 4:50 PM

Done! it works! THANKS!!!!

Mar 3, 2012 at 8:06 PM

Good Job Charlie, I tried to get it work without success, but you help me!!!.

Mar 31, 2012 at 10:47 AM

Thanks it works great!

There is an error with remove from cart. This is because the jquery script files included are  version 1.5.1.

The muscistore project itself is using jquery-1.4.4.min.js in the views.

Guess a little error. Replace all instances of 1.4.4 with 1.5.1 and all works well.

May 4, 2012 at 8:19 PM

I'm trying to add on to the work I've done with SQL Server Express, and also using this opportunity to learn Entity Framework Code First, so I'm trying to learn multiple new things.  Is there an older version of the tutorial that includes the setup for SQL Server Express and does Code First?  

Aug 6, 2012 at 11:48 PM

Thanks Charlie. Your steps help me.

Sep 20, 2012 at 3:33 AM

Thanks so much, Charlie. Your steps give me great happiness.

Nov 1, 2013 at 9:45 PM
Edited Nov 1, 2013 at 10:58 PM
I created MvcMusicStore as a database in SQL Server 2012.

I ran the script to create the MusicStore database from CodePlex but it did not create the tables and/or insert data into those tables.

Unless I see the tables with data in SQL Server 2012 I have to assume I still have an empty database.

Was this script designed to load a database in SQL Server Compact Edition only?

I am not going to load another SQL Server version on my laptop. It only creates conflicts.

I'm using a free WROX MVC 4 for beginners which uses the MvcMusicStore database but being experienced with SQL Server I like having full control over my databases and all of them are handled the same way when I connect to my database engine. I like knowing I can see all of my tables there then go to Visual Studio 2010 and connect to my database and verify what I see in SQL Server Management Studio.

Anyway I might just create the tables manually then pull parts of the scripts T-SQL commands and build the indexes and constraints before inserting the data.

What scares me most about the WROX book on MVC 4 is suggesting the creation of a local database from model classes.

I don't like the idea of a generated database at all.

I build my databases first with a sound relational model then build my applications then configure the connections to the database and finally build my SQL statments and adapters as I need them all using the one connection string through the web configuration file.

Researching.....
Nov 1, 2013 at 10:45 PM
Edited Nov 1, 2013 at 10:56 PM
(removed)
Nov 1, 2013 at 10:51 PM
Edited Nov 1, 2013 at 10:57 PM
I did get one table created but I had to build the script myself. So I think I can salvage this by going into the create script provided by CodePlex and making adjustments based on the one generated script that worked. After all manually building all this would be a monumental pain.