ASP.NET MVC 4 Models and Data Access

Article
07/11/2022

This Hands-on Lab assumes you have basic knowledge of ASP.NET MVC. If you have not used ASP.NET MVC before, we recommend you to go over ASP.NET MVC 4 Fundamentals Hands-on Lab.

This lab walks you through the enhancements and new features previously described by applying minor changes to a sample Web application provided in the Source folder.

Note

All sample code and snippets are included in the Web Camps Training Kit, available at Microsoft-Web/WebCampTrainingKit Releases. The project specific to this lab is available at ASP.NET MVC 4 Models and Data Access.

In ASP.NET MVC Fundamentals Hands-on Lab, you have been passing hard-coded data from the Controllers to the View templates. But, in order to build a real Web application, you might want to use a real database.

This Hands-on Lab will show you how to use a database engine in order to store and retrieve the data needed for the Music Store application. To accomplish that, you will start with an existing database and create the Entity Data Model from it. Throughout this lab, you will meet the Database First approach as well as the Code First approach.

However, you can also use the Model First approach, create the same model using the tools, and then generate the database from it.

Database First vs. Model First

Database First vs. Model First

After generating the Model, you will make the proper adjustments in the StoreController to provide the Store Views with the data taken from the database, instead of using hard-coded data. You will not need to make any change to the View templates because the StoreController will be returning the same ViewModels to the View templates, although this time the data will come from the database.

The Code First Approach

The Code First approach allows us to define the model from the code without generating classes that are generally coupled with the framework.

In code first, model objects are defined with POCOs, "Plain Old CLR Objects". POCOs are simple plain classes that have no inheritance and do not implement interfaces. We can automatically generate the database from them, or we can use an existing database and generate the class mapping from the code.

The benefits of using this approach is that the Model remains independent from the persistence framework (in this case, Entity Framework), as the POCOs classes are not coupled with the mapping framework.

Note

This Lab is based on ASP.NET MVC 4 and a version of the Music Store sample application customized and minimized to fit only the features shown in this Hands-On Lab.

If you wish to explore the whole Music Store tutorial application you can find it in MVC-Music-Store.

Prerequisites

You must have the following items to complete this lab:

Microsoft Visual Studio Express 2012 for Web or superior (read Appendix A for instructions on how to install it).

Setup

Installing Code Snippets

For convenience, much of the code you will be managing along this lab is available as Visual Studio code snippets. To install the code snippets run .\Source\Setup\CodeSnippets.vsi file.

If you are not familiar with the Visual Studio Code Snippets, and want to learn how to use them, you can refer to the appendix from this document "Appendix C: Using Code Snippets".

Exercises

This Hands-on Lab is comprised by the following exercises:

Exercise 1: Adding a Database
Exercise 2: Creating a Database using Code First
Exercise 3: Querying the Database with Parameters

Note

Each exercise is accompanied by an End folder containing the resulting solution you should obtain after completing the exercises. You can use this solution as a guide if you need additional help working through the exercises.

Estimated time to complete this lab: 35 minutes.

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 the database is generated with the model, and added to the solution, you will modify the StoreController class to provide the View template with the data taken from the database, instead of using hard-coded values.

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.

Open the Begin solution located at Source/Ex1-AddingADatabaseDBFirst/Begin/ folder.
1. You will need to download some missing NuGet packages before continue. To do this, click the Project menu and select Manage NuGet Packages.
2. In the Manage NuGet Packages dialog, click Restore in order to download missing packages.
3. Finally, build the solution by clicking Build | Build Solution.
  
  Note
  
  One of the advantages of using NuGet is that you don't have to ship all the libraries in your project, reducing the project size. With NuGet Power Tools, by specifying the package versions in the Packages.config file, you will be able to download all the required libraries the first time you run the project. This is why you will have to run these steps after you open an existing solution from this lab.
Add MvcMusicStore database file. In this Hands-on Lab, you will use an already created database called MvcMusicStore.mdf. To do that, right-click App_Data folder, point to Add and then click Existing Item. Browse to \Source\Assets and select the MvcMusicStore.mdf file.

Adding an Existing Item

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.

MvcMusicStore database in Solution Explorer
Verify the connection to the database. To do this, double-click MvcMusicStore.mdf to establish a connection.

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.

Create a data model that will represent the 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.

Adding the StoreDB ADO.NET Entity Data Model
The Entity Data Model Wizard will appear. This wizard will guide you through the creation of the model layer. Since the model should be created based on the existing database recently added, select Generate from database and click Next.

Choosing the model content
Since you are generating a model from a database, you will need to specify the connection to use. Click New Connection.
Select Microsoft SQL Server Database File and click Continue.

Choose data source dialog
Click Browse and select the database MvcMusicStore.mdf you located in the App_Data folder and click OK.

Connection properties
The generated class should have the same name as the entity connection string, so change its name to MusicStoreEntities and click Next.

Choosing the data connection
Choose the database objects to use. As the Entity Model will use just the database's tables, select the Tables option, and make sure that the Include foreign key columns in the model and Pluralize or singularize generated object names options are also selected. Change the Model Namespace to MvcMusicStore.Model and click Finish.

Choosing the database objects

Note

If a Security Warning dialog is shown, click OK to run the template and generate the classes for the model entities.
An entity diagram for the database will appear, while a separate class that maps each table to the database will be created. For example, the Albums table will be represented by an Album class, where each column in the table will map to a class property. This will allow you to query and work with objects that represent rows in the database.

Entity diagram

Note

The T4 templates (.tt) run code to generate the entities classes and will overwrite the existing classes with the same name. In this example, the classes "Album", "Genre" and "Artist" were overwritten with the generated code.

Task 3 - Building the Application

In this task, you will check that, although the model generation have removed the Album, Genre and Artist model classes, the project builds successfully by using the new data model classes.

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

Building the project
The project builds successfully. Why does it still work? It works because the database tables have fields that include the properties that you were using in the removed classes Album and Genre.

Builds succeeded
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 then StoreDB.tt, you will see the new generated entities.

Generated files

Task 4 - Querying the Database

In this task, you will update the StoreController class so that, instead of using hardcoded data, it will query the database to retrieve the information.

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

(Code Snippet - Models And Data Access - Ex1 storeDB)
```
public class StoreController : Controller
{
    private MusicStoreEntities storeDB = new MusicStoreEntities();
```
The MusicStoreEntities class exposes a collection property for each table in the database. Update Browse action method to retrieve a Genre with all of the Albums.

(Code Snippet - Models And Data Access - Ex1 Store Browse)
```
public ActionResult Browse(string genre)
{
    // Retrieve Genre and its Associated Albums from database
    var genreModel = new Genre
    {
        Name = genre,
        Albums = this.storeDB.Albums.ToList()
    };

    return this.View(genreModel);
}
```
Note

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.
Update Index action method to retrieve all the genres.

(Code Snippet - Models And Data Access - Ex1 Store Index)
```
public ActionResult Index()
{
    var genres = this.storeDB.Genres;

    return this.View(genres);
}
```

Update Index action method to retrieve all the genres and transform the collection to a list.

(Code Snippet - Models And Data Access - Ex1 Store GenreMenu)

// GET: /Store/GenreMenu
[ChildActionOnly]
public ActionResult GenreMenu()
{
    var genres = this.storeDB.Genres.Take(9).ToList();

    return this.PartialView(genres);
}

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 hardcoded ones. There is no need to change the View template because the StoreController is returning the same entities as before, although this time the data will come from the database.

Rebuild the solution and press F5 to run the Application.
The project starts in the Home page. Verify that the menu of Genres is no longer a hardcoded list, and the data is directly retrieved from the database.

Browsing Genres from the database
Now browse to any genre and verify the albums are populated from database.

Browsing Albums from the database

Exercise 2: Creating a Database Using Code First

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

Once the model is generated, you will modify the StoreController to provide the View template with the data taken from the database, instead of using hardcoded values.

Note

If you have completed Exercise 1 and have already worked with the Database First approach, you will now learn how to get the same results with a different process. The tasks that are in common with Exercise 1 have been marked 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 - Populating Sample Data

In this task, you will populate the database with sample data when it is initially created using Code-First.

Open the Begin solution located at Source/Ex2-CreatingADatabaseCodeFirst/Begin/ folder. Otherwise, you might continue using the End solution obtained by completing the previous exercise.
1. If you opened the provided Begin solution, you will need to download some missing NuGet packages before continue. To do this, click the Project menu and select Manage NuGet Packages.
2. In the Manage NuGet Packages dialog, click Restore in order to download missing packages.
3. Finally, build the solution by clicking Build | Build Solution.
  
  Note
  
  One of the advantages of using NuGet is that you don't have to ship all the libraries in your project, reducing the project size. With NuGet Power Tools, by specifying the package versions in the Packages.config file, you will be able to download all the required libraries the first time you run the project. This is why you will have to run these steps after you open an existing solution from this lab.
Add the SampleData.cs file to the Models folder. To do that, right-click Models folder, point to Add and then click Existing Item. Browse to \Source\Assets and select the SampleData.cs file.

Sample data populate code
Open the Global.asax.cs file and add the following using statements.

(Code Snippet - Models And Data Access - Ex2 Global Asax Usings)
```
using MvcMusicStore.Models;
using System.Data.Entity;
```

In the Application_Start() method add the following line to set the database initializer.

(Code Snippet - Models And Data Access - Ex2 Global Asax SetInitializer)

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();

    WebApiConfig.Register(GlobalConfiguration.Configuration);
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);

    Database.SetInitializer(new SampleData());
}

Task 2 - Configuring the connection to the Database

Now that you have already added a database to our project, you will write in the Web.config file the connection string.

Add a connection string at Web.config. To do that, open Web.config at project root and replace the connection string named DefaultConnection with this line in the <connectionStrings> section:

Web.config file location

Web.config file location

<configuration>
...
  <connectionStrings>
    <add name="MusicStoreEntities" connectionString="data source=(LocalDb)\v11.0;initial catalog=MvcMusicStore;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\MvcMusicStore.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>
...

Task 3 - Working with the Model

Now that you have already configured the connection to the database, you will link the model with the database tables. In this task, you will create a class that will be linked to the database with Code First. Remember that there is an existent POCO model class that should be modified.

Note

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.

Open the POCO model class Genre from Models project folder and include an ID. Use an int property with the name GenreId.

(Code Snippet - Models And Data Access - Ex2 Code First Genre)
```
namespace MvcMusicStore.Models
{
    using System.Collections.Generic;

    public class Genre
    {
      public int GenreId { get; set; }

      public string Name { get; set; }

      public string Description { get; set; }

      public List<Album> Albums { get; set; }
    }
}
```
Note

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

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

Now, open the POCO model class Album from Models project folder and include the foreign keys, create properties with the names GenreId and ArtistId. This class already have the GenreId for the primary key.

(Code Snippet - Models And Data Access - Ex2 Code First Album)

namespace MvcMusicStore.Models
{
    public class Album
    {
        public int AlbumId { get; set; }

        public int GenreId { get; set; }

        public int ArtistId { get; set; }

        public string Title { get; set; }

        public decimal Price { get; set; }

        public string AlbumArtUrl { get; set; }

        public virtual Genre Genre { get; set; }

        public virtual Artist Artist { get; set; }
    }
}

Open the POCO model class Artist and include the ArtistId property.

(Code Snippet - Models And Data Access - Ex2 Code First Artist)

namespace MvcMusicStore.Models
{
    public class Artist
    {
        public int ArtistId { get; set; }

        public string Name { get; set; }
    }
}

Right-click the Models project folder and select Add | Class. Name the file MusicStoreEntities.cs. Then, click Add.

Adding a new item

Adding a class

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
...

Replace the class declaration to extend the DbContext class: declare a public DBSet and override OnModelCreating method. 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 - Models And Data Access - Ex2 Code First MusicStoreEntities)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace MvcMusicStore.Models
{
    public class MusicStoreEntities : DbContext
    {
        public DbSet<Genre> Genres { get; set; }

        public DbSet<Album> Albums { get; set; }

        public DbSet<Artist> Artists { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Genre>().ToTable("Genres");
            modelBuilder.Entity<Album>().ToTable("Albums");
            modelBuilder.Entity<Artist>().ToTable("Artists");

            base.OnModelCreating(modelBuilder);
        }
    }
}

Note

With Entity Framework DbContext and DBSet you will be able to query the POCO class Genre. By extending OnModelCreating method, you 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 article: link

Task 4 - Querying the Database

In this task, you will update the StoreController class so that, instead of using hardcoded data, it will retrieve 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 in both approaches (Database first or Code first). They are different in how the data is linked with the model, but the access to data entities is yet transparent from the controller.

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

(Code Snippet - Models And Data Access - Ex1 storeDB)
```
public class StoreController : Controller
{
    private MusicStoreEntities storeDB = new MusicStoreEntities();
    ...
}
```
The MusicStoreEntities class exposes a collection property for each table in the database. Update Browse action method to retrieve a Genre with all of the Albums.

(Code Snippet - Models And Data Access - Ex2 Store Browse)
```
public ActionResult Browse(string genre)
{
     // Retrieve Genre and its Associated Albums from database
     var genreModel = new Genre
    {
        Name = genre,
        Albums = this.storeDB.Albums.ToList()
    };

     return this.View(genreModel);
}
```
Note

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.
Update Index action method to retrieve all the genres.

(Code Snippet - Models And Data Access - Ex2 Store Index)
```
public ActionResult Index()
{
    var genres = this.storeDB.Genres;

    return this.View(genres);
}
```

Update Index action method to retrieve all the genres and transform the collection to a list.

(Code Snippet - Models And Data Access - Ex2 Store GenreMenu)

// GET: /Store/GenreMenu
[ChildActionOnly]
public ActionResult GenreMenu()
{
    var genres = this.storeDB.Genres.Take(9).ToList();

    return this.PartialView(genres);
}

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 hardcoded ones. There is no need to change the View template because the StoreController is returning the same StoreIndexViewModel as before, but this time the data will come from the database.

Rebuild the solution and press F5 to run the Application.
The project starts in the Home page. Verify that the menu of Genres is no longer a hardcoded list, and the data is directly retrieved from the database.

Browsing Genres from the database
Now browse to any genre and verify the albums are populated from database.

Browsing Albums from the database

Exercise 3: Querying the Database with Parameters

In this exercise, you will learn how to query the database using parameters, and how to use Query Result Shaping, a feature that reduces the number database accesses retrieving data in a more efficient way.

Note

For further information on Query Result Shaping, visit the following msdn article.

Task 1 - Modifying StoreController to Retrieve Albums from Database

In this task, you will change the StoreController class to access the database to retrieve albums from a specific genre.

Open the Begin solution located at the Source\Ex3-QueryingTheDatabaseWithParametersCodeFirst\Begin folder if you want to use Code-First approach or Source\Ex3-QueryingTheDatabaseWithParametersDBFirst\Begin folder if you want to use Database-First approach. Otherwise, you might continue using the End solution obtained by completing the previous exercise.
1. If you opened the provided Begin solution, you will need to download some missing NuGet packages before continue. To do this, click the Project menu and select Manage NuGet Packages.
2. In the Manage NuGet Packages dialog, click Restore in order to download missing packages.
3. Finally, build the solution by clicking Build | Build Solution.
  
  Note
  
  One of the advantages of using NuGet is that you don't have to ship all the libraries in your project, reducing the project size. With NuGet Power Tools, by specifying the package versions in the Packages.config file, you will be able to download all the required libraries the first time you run the project. This is why you will have to run these steps after you open an existing solution from this lab.
Open the StoreController class to change the Browse action method. To do this, in the Solution Explorer, expand the Controllers folder and double-click StoreController.cs.

Change the Browse action method to retrieve albums for a specific genre. To do this, replace the following code:

(Code Snippet - Models And Data Access - Ex3 StoreController BrowseMethod)

public ActionResult Browse(string genre)
{
    // Retrieve Genre and its Associated Albums from database
    var genreModel = this.storeDB.Genres.Include("Albums")
        .Single(g => g.Name == genre);

    return this.View(genreModel);
}

Note

To populate a collection of the entity, you need to use the Include method to specify you want to retrieve the albums too. You can use the .Single() extension in LINQ because in this case only one genre is expected for an album. The Single() method takes a Lambda expression as a parameter, which in this case specifies a single Genre object such that its name matches the value defined.

You will take advantage of a feature that allows you to indicate other related entities you want loaded as well when the Genre object is retrieved. This feature is called Query Result Shaping, and enables you to reduce the number of times needed to access the database to retrieve information. In this scenario, you will want to pre-fetch the Albums for the Genre you retrieve.

The query includes Genres.Include("Albums") to indicate that you want related albums as well. This will result in a more efficient application, since it will retrieve both Genre and Album data in a single database request.

Task 2 - Running the Application

In this task, you will run the application and retrieve albums of a specific genre from the database.

Press F5 to run the Application.
The project starts in the Home page. Change the URL to /Store/Browse?genre=Pop to verify that the results are being retrieved from the database.

Browsing /Store/Browse?genre=Pop

Task 3 - Accessing Albums by Id

In this task, you will repeat the previous procedure to get albums by their Id.

Close the browser if needed, to return to Visual Studio. Open the StoreController class to change the Details action method. To do this, in the Solution Explorer, expand the Controllers folder and double-click StoreController.cs.

Change the Details action method to retrieve albums details based on their Id. To do this, replace the following code:

(Code Snippet - Models And Data Access - Ex3 StoreController DetailsMethod)

// GET: /Store/
public ActionResult Details(int id)
{
    var album = this.storeDB.Albums.Find(id);

    if (album == null)
    {
        return this.HttpNotFound();
    }

    return this.View(album);
}

Task 4 - Running the Application

In this task, you will run the Application in a web browser and obtain album details by their Id.

Press F5 to run the Application.
The project starts in the Home page. Change the URL to /Store/Details/51 or browse the genres and select an album to verify that the results are being retrieved from the database.

Browsing /Store/Details/51

Note

Additionally, you can deploy this application to Windows Azure Web Sites following Appendix B: Publishing an ASP.NET MVC 4 Application using Web Deploy.

Summary

By completing this Hands-on Lab you have learned the fundamentals of ASP.NET MVC Models and Data Access, using the Database First approach as well as the Code First Approach:

How to add a database to the solution in order to consume its data
How to update Controllers to provide View templates with the data taken from the database instead of hard-coded one
How to query the database using parameters
How to use the Query Result Shaping, a feature that reduces the number of database accesses, retrieving data in a more efficient way
How to use both Database First and Code First approaches in Microsoft Entity Framework to link the database with the model

Appendix A: Installing Visual Studio Express 2012 for Web

You can install Microsoft Visual Studio Express 2012 for Web or another "Express" version using the Microsoft Web Platform Installer. The following instructions guide you through the steps required to install Visual studio Express 2012 for Web using Microsoft Web Platform Installer.

Go to [https://go.microsoft.com/?linkid=9810169](https://go.microsoft.com/?linkid=9810169). Alternatively, if you already have installed Web Platform Installer, you can open it and search for the product "Visual Studio Express 2012 for Web with Windows Azure SDK".
Click on Install Now. If you do not have Web Platform Installer you will be redirected to download and install it first.
Once Web Platform Installer is open, click Install to start the setup.

Install Visual Studio Express
Read all the products' licenses and terms and click I Accept to continue.

Accepting the license terms
Wait until the downloading and installation process completes.

Installation progress
When the installation completes, click Finish.

Installation completed
Click Exit to close Web Platform Installer.
To open Visual Studio Express for Web, go to the Start screen and start writing "VS Express", then click on the VS Express for Web tile.

VS Express for Web tile

Appendix B: Publishing an ASP.NET MVC 4 Application using Web Deploy

This appendix will show you how to create a new web site from the Windows Azure Management Portal and publish the application you obtained by following the lab, taking advantage of the Web Deploy publishing feature provided by Windows Azure.

Task 1 - Creating a New Web Site from the Windows Azure Portal

Go to the Windows Azure Management Portal and sign in using the Microsoft credentials associated with your subscription.

Note

With Windows Azure you can host 10 ASP.NET Web Sites for free and then scale as your traffic grows. You can sign up here.

Log on to Windows Azure Management Portal
Click New on the command bar.

Creating a new Web Site
Click Compute | Web Site. Then select Quick Create option. Provide an available URL for the new web site and click Create Web Site.

Note

A Windows Azure Web Site is the host for a web application running in the cloud that you can control and manage. The Quick Create option allows you to deploy a completed web application to the Windows Azure Web Site from outside the portal. It does not include steps for setting up a database.

Creating a new Web Site using Quick Create
Wait until the new Web Site is created.
Once the Web Site is created click the link under the URL column. Check that the new Web Site is working.

Browsing to the new web site

Web site running
Go back to the portal and click the name of the web site under the Name column to display the management pages.

Opening the Web Site management pages
In the Dashboard page, under the quick glance section, click the Download publish profile link.

Note

The publish profile contains all of the information required to publish a web application to a Windows Azure website for each enabled publication method. The publish profile contains the URLs, user credentials and database strings required to connect to and authenticate against each of the endpoints for which a publication method is enabled. Microsoft WebMatrix 2, Microsoft Visual Studio Express for Web and Microsoft Visual Studio 2012 support reading publish profiles to automate configuration of these programs for publishing web applications to Windows Azure websites.

Downloading the Web Site publish profile
Download the publish profile file to a known location. Further in this exercise you will see how to use this file to publish a web application to a Windows Azure Web Sites from Visual Studio.

Saving the publish profile file

Task 2 - Configuring the Database Server

If your application makes use of SQL Server databases you will need to create a SQL Database server. If you want to deploy a simple application that does not use SQL Server you might skip this task.

You will need a SQL Database server for storing the application database. You can view the SQL Database servers from your subscription in the Windows Azure Management portal at Sql Databases | Servers | Server's Dashboard. If you do not have a server created, you can create one using the Add button on the command bar. Take note of the server name and URL, administrator login name and password, as you will use them in the next tasks. Do not create the database yet, as it will be created in a later stage.

SQL Database Server Dashboard
In the next task you will test the database connection from Visual Studio, for that reason you need to include your local IP address in the server's list of Allowed IP Addresses. To do that, click Configure, select the IP address from Current Client IP Address and paste it on the Start IP Address and End IP Address text boxes and click the button.

Adding Client IP Address
Once the Client IP Address is added to the allowed IP addresses list, click on Save to confirm the changes.

Confirm Changes

Task 3 - Publishing an ASP.NET MVC 4 Application using Web Deploy

Go back to the ASP.NET MVC 4 solution. In the Solution Explorer, right-click the web site project and select Publish.

Publishing the web site
Import the publish profile you saved in the first task.

Importing publish profile
Click Validate Connection. Once Validation is complete click Next.

Note

Validation is complete once you see a green checkmark appear next to the Validate Connection button.

Validating connection
In the Settings page, under the Databases section, click the button next to your database connection's textbox (i.e. DefaultConnection).

Web deploy configuration
Configure the database connection as follows:
- In the Server name type your SQL Database server URL using the tcp: prefix.
- In User name type your server administrator login name.
- In Password type your server administrator login password.
- Type a new database name.
  
  Configuring destination connection string
Then click OK. When prompted to create the database click Yes.

Creating the database
The connection string you will use to connect to SQL Database in Windows Azure is shown within Default Connection textbox. Then click Next.

Connection string pointing to SQL Database
In the Preview page, click Publish.

Publishing the web application
Once the publishing process finishes, your default browser will open the published web site.

Appendix C: Using Code Snippets

With code snippets, you have all the code you need at your fingertips. The lab document will tell you exactly when you can use them, as shown in the following figure.

Using Visual Studio code snippets to insert code into your project

Using Visual Studio code snippets to insert code into your project

To add a code snippet using the keyboard (C# only)

Place the cursor where you would like to insert the code.
Start typing the snippet name (without spaces or hyphens).
Watch as IntelliSense displays matching snippets' names.
Select the correct snippet (or keep typing until the entire snippet's name is selected).
Press the Tab key twice to insert the snippet at the cursor location.

Start typing the snippet name

Start typing the snippet name

Press Tab to select the highlighted snippet

Press Tab to select the highlighted snippet

Press Tab again and the snippet will expand

Press Tab again and the snippet will expand

To add a code snippet using the mouse (C#, Visual Basic and XML) 1. Right-click where you want to insert the code snippet.

Select Insert Snippet followed by My Code Snippets.
Pick the relevant snippet from the list, by clicking on it.

Right-click where you want to insert the code snippet and select Insert Snippet

Right-click where you want to insert the code snippet and select Insert Snippet

Pick the relevant snippet from the list, by clicking on it

Pick the relevant snippet from the list, by clicking on it

Share via

ASP.NET MVC 4 Models and Data Access

Prerequisites

Setup

Exercises

Exercise 1: Adding a Database

Task 1 - Adding a Database

Task 2 - Creating a Data Model

Task 3 - Building the Application

Task 4 - Querying the Database

Task 5 - Running the Application

Exercise 2: Creating a Database Using Code First

Task 1 - Populating Sample Data

Task 2 - Configuring the connection to the Database

Task 3 - Working with the Model

Task 4 - Querying the Database

Task 5 - Running the Application

Exercise 3: Querying the Database with Parameters

Task 1 - Modifying StoreController to Retrieve Albums from Database

Task 2 - Running the Application

Task 3 - Accessing Albums by Id

Task 4 - Running the Application

Summary

Appendix A: Installing Visual Studio Express 2012 for Web

Appendix B: Publishing an ASP.NET MVC 4 Application using Web Deploy

Task 1 - Creating a New Web Site from the Windows Azure Portal

Task 2 - Configuring the Database Server

Task 3 - Publishing an ASP.NET MVC 4 Application using Web Deploy

Appendix C: Using Code Snippets

Feedback

Additional resources