Exercise 2: Adding a Database Using Code First

In this exercise, you will learn how to use the Code First approach to add a database with the tables of the MusicStore application to consume its data.

Once adding the database and generating the model, you will make the proper adjustments in the StoreController to provide the View template with the data taken from the database instead of hardcoding it.

If you have completed Exercise 1 and have already worked with Database approach, you will now learn how to get the same results with a different process. Some tasks will be repeated with Exercise 1, so they are marked appropriately to make your reading easier.

If you have not completed Exercise 1 but would like to learn the Code First approach, you can start from this exercise and get a full coverage of the topic.

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.

This task is in common with Exercise 1.
  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\Ex02-AddingADatabaseCodeFirst\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 22

    Adding an App_Data folder

  4. Add MvcMusicStore database file. In this 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 23

    Adding an Existing Item

    Figure 24

    MvcMusicStore.mdf database file

    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 25

    MvcMusicStore database in Solution Explorer

  5. 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 26

    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 27

    Running services.msc

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

    Figure 28

    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 29

    Changing the log on account

    4. Restart the SQL Server (SQLEXPRESS) service.

    Figure 30

    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 31

    Connecting to MvcMusicStore.mdf

  6. Close the connection now. To do that, in Database Explorer right-click on the MvcMusicStore database and select Close Connection.

    Figure 32

    Closing the connection

Task 2 – Including Code First in the Solution and Connecting the Database

Now that we have already added a database to our project, we will write in the Web.config the connection string. Then will include an external library for Entity Framework 4 Code First.

If you completed Exercise 1, you will note that these steps were automatically generated by a wizard. As Code First is not providing a wizard, you will have to implement many of them manually.

  1. Add a connection string at Web.config. To do that, open Web.config at project root and include these lines at the end of the file:

    Figure 33

    Web.config file location


    <connectionStrings> <add name="MusicStoreEntities" connectionString="data source=.\SQLEXPRESS;initial catalog=MvcMusicStore;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\MvcMusicStore.mdf;User instance=true" providerName="System.Data.SqlClient" /> </connectionStrings>FakePre-420f1625c77a4643a8ba3079242f80ad-ea8deedd11ab48759d5e5638577841a6FakePre-91746df24c424189bde9dcabe42d7d11-3b4eecd719ca4b89a3d88c969338a9fe

  2. Add a new reference to the Code First assembly EntityFramework.dll. To do this, right-click References and select Add Reference:

    Figure 34

    Adding a reference

  3. In the Add Reference dialog, click the Browse tab and navigate to Source\Assets\EntityFrameworkCTP5 and select EntityFramework.dll. Then, click OK:

    Figure 35

    Adding EntityFramework assembly

    Figure 36

    EntityFramework assembly added to the project

Task 3 – Working with the Model

Now that we have already included code first library and connected the database, we will link the model with the database tables. In this task, we will create a class that will be linked to the database with Code First. Remember we already have a POCO model class that should be modified.

If you completed Exercise 1, you will note that this step was performed by a wizard. By doing Code First, you will manually create classes that will be linked to data entities.

  1. Open the POCO model class Genre from /Models project folder and include an ID, a description attribute, and also an album’s collection.

    (Code Snippet – ASP.NET MVC Models and Data Access – Ex2 Code First Genre – CSharp)


    using System;
    FakePre-9d737d8be59d4d8db505f87782518f5b-3355d77315ee4e899f76eae4deea69c0FakePre-64164970136e48e7b0f84fb10691c861-40d3bf8110ad4246b04c9458b7ea946cFakePre-6bbb3b38591d46dd97f71b158c814761-570a1cba3c934e6fae3fc8c0e46a3e4fFakePre-a2bebba4fcb24ad4a51121ac37614eb8-895d8be1f9b44ee2834a507e076b8f16FakePre-c46c9d99b5d74a059f794d3c754287a4-16429a6180074de6939c45f6539d648aFakePre-0443320255484b06be8cb9f1075b5330-4c91aa5c0b52458fb86ad1d6d722151dFakePre-1b4abc2de01c4255b5c7d8d1ac0a032d-f718a19416cf4a378c5ec96d762fa1beFakePre-a8aab0ae68a540ee968ecb8973e6f073-8e3bb2e0929c49e7b339bc1d15e7465c public int GenreId { get; set; } public string Description { get; set; } public virtual ICollection<Album> Albums { get; set; }FakePre-197f1d65e03943b1afcb0f80484e8543-b88eae4a95a6479183deef5eac994825 public int GenreId { get; set; } public string Description { get; set; } public virtual ICollection<Album> Albums { get; set; }FakePre-2b98a8f00fab4fbba28b5f672f4f3e3d-f76e20bc76c54cbb996715550f54aa3dFakePre-12d4d1e9be4c4c84ae35e1fc70e18579-a691776236b140f9864859e05cc597b0

    To work with Code First conventions, Genre must have a primary key property that will be automatically detected.

    You can read more about Code First Conventions in this msdn blog article.

  2. Now, open the POCO model class Album from /Models project folder and include the AlbumId and GenreId properties.

    (Code Snippet – ASP.NET MVC Models and Data Access – Ex2 Code First Album – CSharp)


    using System;
    FakePre-137e1295474e4b649ca2ff91bb5b02f2-cefd9e4c2c26481ab8f3c870cbaf7295FakePre-99bb3be2b2cb4a19849c3110a3b75d0a-e4fbf2f9880a4656a60ea84dd5cc18a5FakePre-b99147366119420982e033b1f58b84f5-9fe9165ec8b74eb494ea88313910a3d8FakePre-02407a5762d44acda7ff6b66b7797873-923ccf2be1454dac9e11151d06782b03FakePre-c93f48a937ad4cf79de2ea75d159357a-2b95d5dba02f4c46b4e4d51d648d569aFakePre-f26c24c9ffdc45ecb5821e5b4e16c2ce-a32b6c9a9e724c63adae34f4ee4058adFakePre-03c0cbbe7f7e42659ac83054261d1a91-8d01f7e943b34e59bc911b826c46f89dFakePre-bc2f933aa8b34521b0ae062e9f160439-16fb02872d8743fcba13d9c2d3802754 public int AlbumId { get; set; } public int GenreId { get; set; }FakePre-7da1af0ea0194956a4ef057e51c7b8ad-8b74ac5ec9f14225984df51c0055d99c public int AlbumId { get; set; } public int GenreId { get; set; }FakePre-717cdbde86254b02b8dbc2e34efe9eab-e58e2893a02348298c9aab24fe23aec1FakePre-80e191b6987f40c58b446d26cee3e7d6-82d8d05e6a784cacb6eab590951d6293FakePre-a9c31e61b7dc4dac8c83bb7a1e9d4248-a8185fc575af4d27bec1682fc2700313

  3. Right-click the Models project folder point to Add and then click Class to add a new class. Name it MusicStoreEntities.cs and click Add.

    Figure 37

    Adding a class

    Figure 38

    Adding a class(2)

  4. Open the class you have just created, MusicStoreEntities.cs, and include the namespace System.Data.Entity.


    using System;
    FakePre-36eafa9e075c4c3cb8cc57b40b028b9b-75e64b3902544cbbb858839838b4f748FakePre-3eb8a9b5061949d3991acfd32e22432b-a9fcbae990f9459f8d77cefcad1441daFakePre-b5eed6ae1cc14201865dab7346b848f4-c9e14128bcee41b1b4010162dd536325using System.Data.Entity; using System.Data.Entity.Database;FakePre-e9b8f79f77fd40089b4a3a8c4b8d75f5-4b0ef63416424d39930cb84f3df5b090

  5. Replace the class declaration to extend DbContext class: declare a public DBSet and override the method OnModelCreating. After this step you will get a domain class that will link your model with the Entity Framework. In order to do that, replace the class code with the following:

    (Code Snippet – ASP.NET MVC Models and Data Access – Ex2 Code First MusicStoreEntities – CSharp)


    using System;
    FakePre-045537bed2354fea81b7a97ca1e6b6a7-2111860825db494fb36ee93c5909cd87FakePre-11a6bdff01374eccac9444f1731e13dc-6b31da2d7265417db806a0dd38ab8421FakePre-50d037b5f5a840e9bfdab2387a5c7b03-67b248db915e4584abfab66e80981416FakePre-b339f87d91014a4d88b60df573e7543e-7b25647a580d49f995057ee59fd4c8fdFakePre-970019f75568454e95dd02cbdbf3931e-43d93deb9791426fad0c46d3580b89daFakePre-f61fb134675a42e3a19fb2b5780d7433-beadf35013034b6483636df9f4256f6eFakePre-1add4dfb65bb41f3a05be0053b296ebb-00a88239d16b4a88b2281f8aa244f59aFakePre-b5cdf4afe7a7475e89f3870f091f71df-570a6b5e69be49b0943643dc9aa56cf6 public class MusicStoreEntities : DbContext { public DbSet<Genre> Genres { get; set; } public DbSet<Album> Albums { get; set; } protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<IncludeMetadataConvention>(); modelBuilder.Entity<Genre>().ToTable("Genre"); modelBuilder.Entity<Album>().ToTable("Album"); base.OnModelCreating(modelBuilder); } }FakePre-697261904c1044cabb7bf2eeac4be2e1-ca1c36bf6e7e4d118235a1bcefd471edFakePre-ae800681b01046e19c2fc76955e998e6-5f17e5d836564ea889ef3d2515e15f32

     With Entity Framework DbContext and DBSet you will be able to query the POCO class Genre.

    By extending OnModel Creating we are specifying in the code how Genre will be mapped to a database table.

    You can find more information about DBContext and DBSet in this msdn blog article: link

Task 4 – Querying the Database

In this task, you will update the StoreController class so that instead of using hard-coded data, it can consume it from the database.

Note: This task is in common with Exercise 1.

If you completed Exercise 1 you will note these steps are the same, independently from the approach (Database first or Code first). They are different in how the data is linked with the model, but the access to data entities has to be the transparent from the controller.

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

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


    public class StoreController : Controller
    FakePre-b2064005a9ea4b158d5ad62927cd7e9a-2e1d73972aa34793a1380d16241e8316 MusicStoreEntities storeDB = new MusicStoreEntities();

  2. The MusicStoreEntities class exposes a collection property for each table in the database. Update StoreController’s Index action 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 the one below which retrieves the Name property of each Genre within the database:

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


    FakePre-ab903d858b754268913d5ea6105070e0-8a2bac3a3cde46daae93b88728f4ecb3FakePre-14355d2a1f7741398182d174be57b54b-af57ae40bd0c4b74977e35116bb8e963FakePre-6846403e44414db38ad68ec856421fa1-2af3dd94836d43289dfc87902c942590FakePre-1567087c077842e485d754326d6671bb-aa6c0d8900be473e9c530f1fda94e188 // Retrieve the list of genres var genres = from genre in storeDB.Genres select genre.Name;FakePre-0e8a3965b87e4bc2a4cde91c56ad8789-db1a179419c242f9ab48b9e47d2f965cFakePre-a3285323f08a47da9c41bf1436e1e0fd-d6592ff09ecf4d10a5487c7936b015e5FakePre-df2edae66a12473db447b2f817c6bd5e-895a3805d9c545dc9f4c9792eaf8a11a

    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 – Ex2 Code First Genres to List – CSharp)


    public ActionResult Index()
    FakePre-72488568e46c414dadd17515ba84fd78-7105bf512c024977855d04681e762c7eFakePre-4a3241df03fb4dd1842a0ba06d839ab0-deee7d39cc224e17830d9c10f1428d3fFakePre-e10796d0c3594942af96b083c01c7275-43f726f6e94340f2b89b9133c57b9238FakePre-2b59038b8f4b44279b0d91879a739d64-46b5ebf075bf40909c52952051f0e16cFakePre-89404de182604e4795740447914792f7-df290a2387b5431994af0bc04c1c7111FakePre-faef016d585241648ea956adfc8464e9-06a6a33ec2274815b9a6f67476b5cfebFakePre-f2780faeaaec49c382dbfb1fc1825fc0-d71ca715b4c64360859ac78712f09671FakePre-e04d36df55a549fb85db92a8ecd2ca7a-aa19b7b5c55c41fd98c7fa41251d45da Genres = genres.ToList(), NumberOfGenres = genres.Count()FakePre-0e0ff039d7e84326a725cf50cf444fd1-96703a84c03b40b5bab582ee91edcd59FakePre-8b72e8b6559349ea8afad934d22920a3-817123149dff40c68f0190090434c97eFakePre-7c7d4a59db0b4d1f813bb4beabfbf116-2066302749384c0aa09ebc5e5415915fFakePre-15c1b927fdc1474ab870865d6d6c2efe-8d129226f0b64e0dab0075e6caea6154

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 39

    Browsing Genres from the database

Next Step

Exercise 3: Querying the Database with Parameters