Exercise 1: Adding a Database

In this exercise, you will learn how to add a database with the tables of the MusicStore application to the solution in order to consume its data. Once adding the database and generating the Model it will represent, you will make the proper adjustments in the StoreController class to provide the View template with the data taken from the database instead of hard-coded one.

Task 1 – Adding a Database

In this task, you will add an already created database with the main tables of the MusicStore application to the solution.

  1. Start Microsoft Visual Web Developer 2010 Express from Start | All Programs | Microsoft Visual Studio 2010 Express | Microsoft Visual Web Developer 2010 Express.
  2. In the File menu, choose Open Project. In the Open Project dialog, browse to Source\Ex01-AddingADatabaseDBFirst\Begin, select MvcMusicStore.sln and click Open.
  3. Add an App_Data folder to the project to hold the SQL Server Express database files. App_Data is a special folder in ASP.NET which already has the correct security access permissions for database access. To add the folder, right-click MvcMusicStore project, point to Add then to Add ASP.NET Folder and finally click App_Data.

    Figure 2

    Adding an App_Data folder

  4. Add MvcMusicStore database file. In this hands-On Lab, you will use an already created database called MvcMusicStore.mdf. To do that, right-click the new App_Data folder, point to Add and then click Existing Item. Browse to \Source\Assets\ and select the MvcMusicStore.mdf file.

    Figure 3

    Adding an Existing Item

    Figure 4

    MvcMusicStore.mdf database file

  5. The database has been added to the project. Even when the database is located inside the solution, you can query and update it as it was hosted in a different database server.

    Figure 5

    MvcMusicStore database in Solution Explorer

  6. Verify the connection to the database. To do this, open the Database Explorer (CTRL+ALT+S), and then double-click the MvcMusicStore.mdf. The connection is established.

    Figure 6

    Connecting to MvcMusicStore.mdf

    If you get an error like the following, please follow the steps below.

    1. Open the Windows Services console. To do that, open the Run command from Start | All Programs | Accessories | Run, type services.msc and then click OK.

    Figure 7

    Running services.msc

    2. Right-click the SQL Server (SQLEXPRESS) service and select Properties.

    Figure 8

    SQL Server (SQLEXPRESS) service

    3. Open the Log On tab, select Local System account as the account to log on with and click OK. Accept the dialog by clicking OK again.

    Figure 9

    Changing the log on account

    4. Restart the SQL Server (SQLEXPRESS) service.

    Figure 10

    Restarting SQL Server (SQLEXPRESS) service

    5. Once the service is restarted, close the Services console and verify the connection to the database. To do this, select the Database Explorer, and then double-click the MvcMusicStore.mdf. The connection is established.

    Figure 11

    Connecting to MvcMusicStore.mdf

Task 2 – Creating a Data Model

In this task, you will create a data model to interact with the database added in the previous task.

  1. Create a data model that will represent the added database. To do this, in Solution Explorer right-click the Models folder, point to Add and then click New Item. In the Add New Item dialog, select the Data template and then the ADO.NET Entity Data Model item. Change the data model name to StoreDB.edmx and click Add.

    Figure 12

    Adding the StoreDB ADO.NET Entity Data Model

  2. The Entity Data Model Wizard appears. This wizard will guide you through the creation of the model layer. Since the model should be created based on the existing database added in the last task, select Generate from database and click Next.

    Figure 13

    Choosing the model content

  3. Since you are generating a model from a database, you will need to specify which database to use. The wizard detects the database in the App_Data folder, so it fills in the correct connection information for that database. The generated class will have the same name as the entity connection string, so change it to MusicStoreEntities and click Next.

    Figure 14

    Choosing the data connection

  4. Choose the database objects to use. Since the Entity Model will use just the database’s tables, check the Tables checkbox and make sure that the Include foreign key columns in the model checkbox is also checked. Change the Model Namespace to MvcMusicStoreModel and click Finish.

    Figure 15

    Choosing the database objects

  5. An entity diagram for the database appears. A separate class that maps to each table within the database will be created. For example, the Albums table will be represented by an Album class with each column in the table mapping to a property on the class. This will allow you to query and work with objects that represent rows within the database. You will see other classes that you might not use in the Hands-on Lab but belong to the Music Store application.

    Figure 16

    Entity diagram

Task 3 – Building the Application

In this task, you will check that although you have removed the Album and Genre model classes, the project gets built successfully, by using the classes in the data model.

  1. Delete the placeholder Album and Genre classes. To do this, in the Solution Explorer, expand the Models folder, right-click Album and select Delete. Repeat this procedure with the Genre class.

    Figure 17

    Deleting placeholder classes

  2. Build the project by selecting the Debug menu item and then Build MvcMusicStore.

    Figure 18

    Building the project

  3. The project builds successfully. Why does still work? It works because the database tables have fields which include the properties you were using in the earlier Album and Genre classes manually removed. Data model classes are a drop-in replacement.

    Figure 19

    Builds succeeded

  4. While the designer displays the entities in a diagram format, they are really C# classes. Expand the StoreDB.edmx node in the Solution Explorer, and you will see a file called StoreDB.Designer.cs.

    Figure 20

    StoreDB.Designer.cs file

Task 4 – Querying the Database

In this task, you will update the StoreController class so that instead of using hard-coded data, it queries the database to retrieve all its information.

  1. Open Controllers\StoreController.cs and add the following field to the class to hold an instance of the MusicStoreEntities class, named storeDB:

    (Code Snippet – ASP.NET MVC Models and Data Access – Ex1 storeDB – CSharp)


    public class StoreController : Controller
    FakePre-f78b994ababe4617b33e045baa778b7f-08714aba7b6e471d92de57e61af85b71 MusicStoreEntities storeDB = new MusicStoreEntities();

  2. The MusicStoreEntities class exposes a collection property for each table in the database. Update StoreController’s Index action method to retrieve all Genre names in the database. This was done previously by hard-coding string data. Now you can instead write a LINQ query expression like below which retrieves the Name property of each Genre within the database:

    (Code Snippet – ASP.NET MVC Models and Data Access – Ex1 Store Index – CSharp)


    FakePre-d2e59acf9c93456892d82cfd94f8d4ea-735cf3fe76434d54b623d49bb52d6e1bFakePre-582688ff2f3a4bc596b07edac2ce1b03-8812d9ff671f41ada25350e978c923cfFakePre-518511fa8dcf4618b9a1f14a552b051b-435406041eea4a42963394783371782aFakePre-280990413e5c4327860f356f0407a707-a26c87cab04f49ed944450540c0f031b // Retrieve the list of genres var genres = from genre in storeDB.Genres select genre.Name;FakePre-4e1bf98959c7481dad1d3f5e0672327e-bef7162fe07449cbbd79f7f009fc7438FakePre-92e0ca30bc114c2c9941a72fa13782b7-a6723edc0ca1435eb028e1942e73db06FakePre-cde55975408a4ea4bcefaae76aa66137-778eb0b5564240168c1e1a01472caad4

    You are using a capability of .NET called LINQ (language-integrated query) to write strongly-typed query expressions against these collections – which will execute code against the database and return objects that you can program against.

    For more information about LINQ, please visit the msdn site.

  3. Transform the collection of genres to a list. To do this, replace the following code:

    (Code Snippet – ASP.NET MVC Models and Data Access – Ex1 Store Index ToList – CSharp)


    public ActionResult Index()
    FakePre-61c36c907abf4483b88be81168e83713-bde4b01888144ace94ef71644ea726b6FakePre-a9c5bc4f2ccb4e33b666683d1257fe42-ca147c242b6148bca1bf8c789ce7fd87FakePre-0cbb5553c27348f2a3544701478532cf-ad0dfb6d3ce64fe1ad505c3321cc8291FakePre-46f00dfdeeae4c45ab6237d8499273a3-a09885bc795b48cfab98d289663df33cFakePre-1dda301e458747fbaa923e2414886325-c64b141c3b1342d69f96bd6afaefa68eFakePre-5f1cbebc06874242816b6fe2ef3c0098-fe6bfb171e1346ad9e7282bee5045a15FakePre-d0d644440e4a4f878ff7bdfea80d8d52-503eb69418c54d70b7cbcb6b35686375FakePre-3ca51a2187474354872cc8d7ec0f9459-bcf65d41aff145e594b71e8d341a038c Genres = genres.ToList(), NumberOfGenres = genres.Count()FakePre-d20fcd723c0541bebc79554d422906bf-3b79cb083c984a4eb37d6fdfc0e6224dFakePre-ac7325a1187a412bb08f3a46b522fc49-913ab5d27a864a9ca437dd7712f3adf8FakePre-eecefd6230c045cfaa9aad385cd352f0-6a1769b5545f4065b553976550625885FakePre-5d6727e76e134d9f8067e9d41c8eb620-e630f32dccf6470cbc7b6223d80d694f

Task 5 – Running the Application

In this task, you will check that the Store Index page will now display the Genres stored in the database instead of the hard-coded ones. There is no need of changing the View template because the StoreController is returning the same StoreIndexViewModel as before, although this time the data will come from the database.

  1. Press F5 to run the Application.
  2. The project starts in the Home page. Change the URL to /Store to verify that the list of Genres is no longer the hard-coded list, else the ones taken from the database.

    Figure 21

    Browsing Genres from the database

Next Step

Exercise 2: Adding a Database Using Code First