ASP. net mvc music store tutorial (4): model and data access

From http://firechun.blog.163.com/blog/static/318045222011029105328664/

So far, we have only transferred "analog data" from the Controller to the view template. Now, we have to mount the real database. In the tutorial, we demonstrated how to use free SQL Server express as our database engine.CodeIt also applies to the full version of SQL Server.

First, add the app_data directory to the project to store the SQL Server express database file. App_data is a specific directory of ASP. NET. It has set proper access permissions for database access.

Add Database

Right-click the project and choose add> Add ASP. NET folder> app_data"

You can now add database files. The database mvcmusicstore. MDF used for the tutorial has been created and is included in the mvcmusicstore-assets/data directory of the project package for download,: http://mvcmusicstore.codeplex.com

Note: The mvcmusicstore-assets/data directory also contains a T-SQL script that allows you to create an mvcmusicstore database in an SQL server instance if you cannot use SQL Server Express.

(The following describes how to add an existing item to the project. You only need to add the downloaded mvcmusicstore. MDF as an existing item to the app_data directory)

Let's look at the relational diagram of the database:

You can see album, genre, and artist classes that describe music, cart (Shopping Cart), Order (Order), and orderdetails (Order details) that involve warehouse management)

Connect to a database using an object framework

The database has been added to the project. We can write code to query and update the database. Here we use the Entity Framework (EF) in. Net 4 for implementation. EF is a flexible object relational ing (ORM) data API that allows developers to query and update data in the database in an object-oriented manner.

The Development Mode Supported by EF4 is called code priority. Code first allows you to write simple classes to create model objects, or even allow you to dynamically create databases from classes. To use code first, you must install the efcodefirst library.

Note: The Code can first create a database from your model class. In this tutorial, we use an existing database that preloads a music album consisting of complete categories and artist information to obtain an example of data generated from the model, see ASP. net MVC Tutorial:Http://www.asp.net/mvc/tutorials/getting-started-with-mvc-part1.

Install the efcodefirst library with nuget

In this section, we will use nuget Package Manager (by ASP. net MVC 3 Automatic Installation) add efcodefirst to the mvcmusicstore project. nuget Package Manager is installed with ASP. net MVC 3 is installed together.

(If MVC 3 is not installed, there will be no nuget Package Manager. Insert the following section to install nuget Package Manager)

In vs2010, select "tools-> Extension Manager"

In the "Extension Manager" dialog box, select "online library"

 

After searching, find nuget Package Manager, download and install it, and restart vs2010. The steps below are the same as those in the original article.

From the tool menu, select library Package Manager \ add library package reference (if an error occurs, restart vs2010 and try again)

The add library package reference dialog box is displayed.

Select online

There will be several hundred packages. We are only interested in efcodefirst. Enter "efcode" in the search box, select the "efcode" package, and click Install.

After the package is installed, click the "close" button. InstallProgramYou have downloaded the efcodefirst library and added it to the mvcmusicstore project. The efcodefirst library is in the entityframeword. dll file.

 Create a connection string in the web. config file

Add a line in Web. config of the website to let the Entity Framework know how to connect strings, find and double-click the Web. config file in the project root directory:

Scroll to the bottom of the file and add the <connectionstrings> section, as shown below:

<Connectionstrings>
<Add name = "musicstoreentities"
Connectionstring = "Data Source =. \ sqlexpress;
Integrated Security = sspi;
Attachdbfilename = | datadirectory | \ mvcmusicstore. MDF;
User instance = true"
Providername = "system. Data. sqlclient"/>
</Connectionstrings>
</Configuration>

Add context class

Right-click the models folder to add a new class: musicstoreentities. CS

This class is used to present the context of the object framework. The Code is as follows:

Using system. Data. entity;

Namespace mvcmusicstore. Models
{
Public class musicstoreentities: dbcontext
{
Public dbset <album> albums {Get; set ;}
Public dbset <genre> genres {Get; set ;}
}
}

In this way, no other configurations or special interfaces are required. The musicstoreentities class derived from dbcontext handles database operations for us. After learning how to connect to a database, we can add some attributes to the model class to facilitate the append information in the application database.

Update model class

Update the album class as follows:

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 Genre genre {Get; set ;}
}
}

Next, update the genre class.

Using system. Collections. Generic;

Namespace mvcmusicstore. Models
{
Public partial class Genre
{
Public int genreid {Get; set ;}
Public string name {Get; set ;}
Public String description {Get; set ;}
Public list <album> albums {Get; set ;}
}
}

Query a database

Now we can update the storecontroller and replace the "simulated data" with the Information queried from the database ". First, declare a field in the storecontroller class to save the musicstoreentities instance:

Public class storecontroller: Controller
{
Musicstoreentities storedb = new musicstoreentities ();

Use the LINQ query expression in the index method of store

The musicstoreentities class is maintained by the Entity Framework and discloses a set of attributes for each table in the database. In. net, we can use the cool LINQ (integrated language query) to write a strong type query expression for these sets. It can execute the code on the database and return objects, making programming easier.

Now, the string data (category name) generated by hard encoding in the index Action Method in storecontroller is updated to be retrieved from the database, we compile a link query expression as follows to retrieve the name attribute of each category from the database.

Public actionresult index ()
{
VaR genres = storedb. genres. tolist ();
Return view (genres );
}

You do not need to make any changes to the previous index. aspx view, but now we get the data in the database.

Run the project and access the/store URL. We can see all types in the database.

 Use the LINQ Extension Method in the broswe, details, and index methods of store.

To/store/browse? Genre =[Some-Genre]In the action method, we query categories by name. because there cannot be two categories with the same name, we only have one returned result, so we can use. returns the genre object using the single () method. For example:

VaR example = storedb. genres. Single (G => G. Name = "Disco ");

The parameter of the single method is a Lambda expression used to determine a single genre object that matches the name we define. In the above example, we read a genre object named "Disco.

When retrieving a genre object, we can use the characteristics of the object framework to identify the relevant entities of the object. The characteristics of this object framework are called query result shaping (query result shaping? The official Chinese name is not found), which can reduce the number of accesses to the database when we retrieve the information we need from the database. We want to get the relevant records in advance for the retrieved category, so update our query to include genres. Include ("albums") to indicate that we want to obtain the relevant records. Because query records and categories at the same time in a single database request, the performance will be better.

Update browse as follows:

Public actionresult browse (string genre)
{
// Retrieve genre and its associated albums from database
VaR genremodel = storedb. genres. Include ("albums ")
. Single (G => G. Name = genre );

Return view (genremodel );
}

Update the Storer's browse view to display the records under each category. Open the view template (/views/store/browse. aspx) and add the Recording List code:

<H2>
Browsing genre: <%: model. name %> </H2>
<Ul>
<% Foreach (VAR album in model. albums)
{%>
<Li> <%: album. Title %> </LI>
<% }%>
</Ul>
Run the program and browse/store/browse? Genre = JAZZ: displays the data we obtain from the database. All records under the specified category are displayed.

 

Make the same modification to the/store/details/[ID] URL and replace the "simulated data" with the data that matches the id value queried from the database ".

Public actionresult details (int id)
{
VaR album = storedb. albums. Find (ID );

Return view (album );
}

Run the application, browse/store/details/5, and display the results we obtained from the database.

Now the store details page is set to display records by ID, update the Browse view, and link it to the Details View. We use the HTML. actionlink () method, but the complete code is not as follows:

<H2>
Browsing genre: <%: model. name %> </H2>
<Ul>
<% Foreach (VAR album in model. albums)
{%>
<Li> <%: HTML. actionlink (album. title, "details", new {id = Album. albumid}) %> </LI>
<% }%>
</Ul>
Now we can browse the genre page on the store page to list the records and view the details of the records when you click the records.