Create a model class using LINQ to SQL

Document directory

• What is a model class?
• Create a Movie Database
• Create a LINQ to SQL class
• Use LINQ to SQL in controller operations
• Use repository Mode
• Summary

In this tutorial, we will build a basic movie database application. First, we will create a movie database application as soon as possible. We will directly perform all data access operations from the Controller.

Next, we will learn how to use the repository mode. Using the repository mode requires more work. However, the benefit of adopting this pattern is that built applications are more suitable for changes and are easy to test.

What is a model class?

The MVC model contains all application logic not included in the MVC view or the MVC controller. In particular, the MVC model contains all application business and data access logic.

Different technologies can be used to implement data access logic. For example, you can use the Microsoft Entity Framework, nhib.pdf, subsonic, or ADO. Net class to build the Data Handler class.

In this tutorial, the author queries and updates the database using LINQ to SQL. LINQ to SQL provides a very simple way to interact with Microsoft SQL Server databases. However, we did not attach ASP. net mvc Framework to LINQ to SQL in any way. It is very important to understand this. ASP. net mvc can be compatible with any data access technology.

Create a Movie Database

In this tutorial, we will build a simple Movie Database Application to illustrate how to build a model class. The first step is to create a new database. Right-click the app_data folder in the Solution Explorer window, and select menu optionsAdd, new item. SelectSQL Server databaseTemplate, name it moviesdb. MDF, and clickAddButton (1 ).

Figure 1: Add a new SQL Server database (click to view the chart)

After creating a new database, you can double-click the moviesdb. MDF file in the app_data folder to open the database. Double-click the moviesdb. MDF file to open the Server Explorer window (2 ).

When Visual Web Developer is used, the Server Explorer window is called the Database Explorer window.

Figure 2: Use the Server Explorer window (click to view the large image)

You need to add a table that represents a movie to the database. Right-click the tables folder and select menu optionsAdd new table. Select this menu option to Open Table Designer (3 ).

Figure 3: Table Designer (click to view the large image)

You need to add the following columns to the database table:

Column name	Data Type	Null value allowed
ID	Int	False
Title	Nvarchar (200)	False
Director	Nvarchar (50)	False

The ID column needs to be processed in two special ways. First, you need to mark the ID column as a primary key column by selecting a column in table designer and then clicking the key icon. You must specify the primary key column when inserting or updating a database.

Next, you need to mark the ID column as the identity columnIs identityAttribute Value: Yes (3 ). Each time a new data row is added to a table, a new sequence number is automatically assigned to the identity column.

Create a LINQ to SQL class

Our MVC model will include the LINQ to SQL class that represents the tblmovie database table. To create these LINQ to SQL classes, right-click the models folder and selectAdd, new item, Select the LINQ to SQL classes template, name the class movie. dbml, and clickAddButton (4 ).

Figure 4: Create a LINQ to SQL class (click to view the big picture)

After the movie LINQ to SQL class is created, the object relational designer will appear immediately. You can drag a database table from the Server Explorer window to the object relational designer to create a LINQ to SQL class that represents a special database table. We need to add the tblmovie database table to object relational designer (5 ).

Figure 5: Use object relational designer (click to view the large image)

By default, Object Relational designer creates a class with the same name as the database table dragged to designer. However, we do not want the class name to beTblmovie. Therefore, click the class name in designer and change it to movie.

Finally, remember to clickSaveButton (floppy disk icon) to save the LINQ to SQL class. Otherwise, the object relational designer will not generate the LINQ to SQL class.

Use LINQ to SQL in controller operations

After the LINQ to SQL class is generated, we can use these classes to retrieve data from the database. In this section, we will learn how to use the LINQ to SQL class directly in controller operations. We will display the movie list from the tblmovies database table in the MVC view.

First, we need to modify the homecontroller class. This class is located in the controllers folder of the application. Modify the class to be shown in program list 1.

Program list 1Controllers \ homecontroller. CS

Copy code

using System.Linq;
using System.Web.Mvc;
using MvcApplication1.Models;

namespace MvcApplication1.Controllers
{
     [HandleError]
     public class HomeController : Controller
     {
          public ActionResult Index()
          {
               var dataContext = new MovieDataContext();
               var movies = from m in dataContext.Movies
                    select m;
               return View(movies);
          }
     }
}

In program list 1Index ()The operation uses the LINQ to SQL datacontext class (Moviedatacontext) IndicatesMoviesdbDatabase.MovedatacontextClass is generated by Visual Studio Object Relational designer.

Execute a LINQ query on datacontextTblmoviesSearch all movies in the database table. The movie list is assigned toMovies. Finally, pass the movie list to the view through View data.

To display movies, you need to modify the index view. The index view is located inViews \ home \Folder. Update the index view to program list 2.

Program list 2Views \ home \ index. aspx

Copy code

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" AutoEventWireup="true" CodeBehind="Index.aspx.cs" Inherits="MvcApplication1.Views.Home.Index" %>
<%@ Import Namespace="MvcApplication1.Models" %>
<asp:Content ID="indexContent" ContentPlaceHolderID="MainContent" runat="server">

     <ul>
          <% foreach (Movie m in (IEnumerable)ViewData.Model)
          { %>
               <li> <%= m.Title %> </li>
          <% } %>
     </ul>
</asp:Content>

Note that the modified index view contains<% @ Import namespace %>Command. Import this commandMvcapplication1.modelsNamespace. You must have this namespace before usingModelClass (that isMovieClass ).

View inclusion in program list 2ForeachLoop, which traverses allViewdata. ModelAttribute, showing eachMovieOfTitleAttribute Value.

Please note that,Viewdata. ModelAttribute value is assignedIenumerable. To traverseViewdata. ModelIs required. Another option here is to create a strongly typedView. Create a strongly typedViewTheViewdata. ModelProperty is assigned to a specific type in the code hidden class of a view.

IfHomecontrollerAfter running the application in the class and index view, a blank page is displayed. The reason for the blank page is:TblmoviesThere is no movie record in the database table.

ToTblmoviesAdd some records to the database table. Right-click the record in the Server Explorer window (Database Explorer window in Visual Web Developer ).TblmoviesDatabase table, and then select Show table data from the menu. You can insert a grid that appears.MovieRecord (6 ).

Figure 6: Insert a movie (click to view the big picture)

DirectionTblmoviesAfter adding several database records to the table, run the application. The page shown in 7 is displayed. Database records of all movies are displayed in the project symbol list.

Figure 7: Display a movie in the index view (click to view the large image)

Use repository Mode

In the previous section, we directly used the LINQ to SQL class in the Controller operation. We directlyIndex ()Controller operation usedMoviedatacontextClass. For simple applications, there is nothing wrong with doing so. However, if you need to build more complex applications, you may encounter problems by directly using LINQ to SQL in the Controller class.

Using LINQ to SQL in the Controller class makes it very difficult to switch Data Access Technologies in the future. For example, you may decide not to use Microsoft LINQ to SQL, but to use Microsoft Entity Framework as the data access technology. In this case, you need to rewrite the Controller for each database in the application.

Using LINQ to SQL in the Controller class makes it difficult to build unit tests for applications. Generally, you do not want to interact with the database during unit tests. You want to use unit tests to test the application logic, not the database server.

To Build MVC applications that are more suitable for change and test in the future, the repository mode should be considered. When the repository mode is used, a separate repository class containing all database access logic is created.

When creating a repository class, an interface is created that represents the methods used by all the repository classes. In the controller, code is written for the interface rather than the repository. In this way, you can use different data access technologies to implement the repository in the future.

The Interface Name in program listing 3 isImovierepository, It represents a nameListall ().

Program list 3Models \ imovierepository. CS

Copy code

using System.Collections.Generic;
namespace MvcApplication1.Models
{
         public interface IMovieRepository
         {
              IList<Movie> ListAll();
         }
}

Repository class implementation in program list 4ImovierepositoryInterface. Note that it contains the nameListall ()CorrespondingImovierepositoryMethod required by the API.

Program list 4Models \ movierepository. CS

Copy code

using System.Collections.Generic;
using System.Linq;

namespace MvcApplication1.Models
{
         public class MovieRepository : IMovieRepository
         {
              private MovieDataContext _dataContext;

              public MovieRepository()
              {
                    _dataContext = new MovieDataContext();
              }

              #region IMovieRepository Members

              public IList<Movie> ListAll()
              {
                   var movies = from m in _dataContext.Movies
                        select m;
                   return movies.ToList();
              }

              #endregion
         }
}

FinallyMoviescontrollerClass uses the repository mode. It no longer directly uses the LINQ to SQL class.

Program list 5Controllers \ moviescontroller. CS

Copy code

using System.Web.Mvc;
using MvcApplication1.Models;

namespace MvcApplication1.Controllers
{
         public class MoviesController : Controller
         {
              private IMovieRepository _repository;

              public MoviesController() : this(new MovieRepository())
              {
              }

              public MoviesController(IMovieRepository repository)
              {
                   _repository = repository;
              }

              public ActionResult Index()
              {
                   return View(_repository.ListAll());
              }
         }
}

Note thatMoviescontrollerClass has two constructor methods. Call the first Constructor (parameterless constructor) when the application is running ). Create this ConstructorMovierepositoryClass, and then pass it to the second constructor.

The second constructor has only one parameter:ImovierepositoryParameters. This constructor only assigns the parameter value to a field of the class level. Its name is_ Repository.

MoviescontrollerClass utilizes a software design pattern called dependency injection. The technology used here is called constructor dependency injection. Read the following article by Martin Fowler to learn more about this mode:

Http://martinfowler.com/articles/injection.html

Please note that,MoviescontrollerAll the code of the class (except the first constructor) andImovierepositoryInterface insteadMovierepositoryClass interaction. Code interacts with abstract interfaces instead of specific Implemented interfaces.

If you want to modify the data access technology used by the application, you can simply implement it by using a class that replaces the data access technology.ImovierepositoryInterface. For example, you can createEntityframeworkmovierepositoryClass orSubsonicmovierepositoryClass. Because the Controller class is written for interfaces, you canImovierepositoryThe new implementation is passed to the Controller class, and the class will continue to work.

Further, if you want to testMoviescontrollerClass, you can pass the imitation movie repository classMoviescontroller. It can be used but does not actually access the database, but contains allImovierepositoryClass of the method required by the interfaceImovierepositoryClass. In this way, you canMoviescontrollerClass for unit testing.

Summary

The purpose of this tutorial is to show you how to create an MVC model class using Microsoft LINQ to SQL. We have studied two strategies for displaying database data in ASP. net mvc applications. First, we created the LINQ to SQL class and used it directly in the Controller operation. Using the LINQ to SQL class in the controller can easily display database data in the MVC application.

Next, we studied a method that is a little more difficult, but more advantageous to display database data. We use the repository mode and place all database access logic in a separate repository class. In the controller, we write all the code for the interface rather than the specific class. The advantage of the Repository mode is that you can easily change the database access technology and test the Controller class in the future.

Msdn http://msdn.microsoft.com/zh-cn/dd408820.aspx reprinted