A Simple MVC Application using LINQ to SQL

• Download source code - 3.4 MB

Introduction

This article introduces how to create an MVC application using LINQ to SQL. You will create a simple MVC application for Book registration with a Publisher where the Publisher and Book have one-to-many relationships.

Database Design

You need to create two tables, one is the Publisher table that stores all the publisher's information and another is a BOOK table that stores all the book's information. The script to create the publisher table is:

CREATE TABLE Publisher
(
    Id int Primary Key NOT NULL IDENTITY(1,1),
    Name nvarchar(50) NOT NULL,
    [Year] nvarchar(4) NOT NULL
) 

The publisher table has three fields, the Id is the primary key for it. Now let’s see the basic terms relevant to the table creation script.

• Primary Key: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Id field in a table with no more than one record per Publisher) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in a Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination. You can only have one primary key, but you can have multiple columns in your primary key.
• NOT NULL Constraint: By default, a table column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. In other words, you cannot insert a new record, or update a record without adding a value to this field. 
• IDENTITY Property: Creates an identity column in a table. It is used in CREATE TABLE and ALTER TABLE statements. The IDENTITY property has two arguments, one is the seed and the other is the increment. For example: IDENTITY [ ( seed , increment ) ]. Seed is the value for the very first row loaded into the table and the increment is the incremental value added to the identity value of the previous row that was loaded. In the publisher table, we are using IDENTITY(1,1) that means that the publisher table's very first row id column will have value 1 and the next row id column will have a value 1 greater than the previous row id value (the next row id = the previous row id + 1).
• NVARCHAR: It can store any Unicode data. An NVARCHAR column is fast in read and writes because all modern operating systems and development platforms use Unicode internally. By using NVARCHAR, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

The BOOK table create script is:

 CREATE TABLE BOOK
(
    Id int Primary Key NOT NULL IDENTITY(1,1),
    Title nvarchar(50) NOT NULL,
    Auther nvarchar(50)NOT NULL,
    [Year] nvarchar (4) NOT NULL,
    Price decimal(6,2)NOT NULL,
    PublisherId int NOT NULL
) 

The BOOK table has six fields; the Id is the primary key for it. Now let’s see the basic terms relevant to the table creation script.

• Decimal: The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value. The two attributes that define decimal columns, variables, and parameters are decimal(p,s): p specifies the precision, or the number of digits the object can hold and s specifies the scale or number of digits that can be placed to the right of the decimal point. The Price column decimal(6,2) means 6 is the total number of digits allowed and 2 is the number digits to the right of decimal point.

Now you can define a relationship between the Publisher and BOOK table. We define one-to-many relationships between them, in other words a Publisher can be associated with multiple books but a single book can be associated with only one publisher so we define the foreign key on publisher id.

ALTER TABLE BOOK
ADD CONSTRAINT FK_BOOK_PUBLISHER FOREIGN KEY (PublisherId)
REFERENCES Publisher(Id)ON DELETE CASCADE 

• Foreign Key: A FOREIGN KEY (FK) is a column or combination of columns that establish and enforce a link between the data in two tables. A FOREIGN KEY constraint does not need to be linked only to a PRIMARY KEY constraint in another table, it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, then verification of all values that make up the FOREIGN KEY constraint is skipped. To ensure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns. A FOREIGN KEY constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, then the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, that links the foreign key to other primary key data.
• ON DELETE CASCADE: A Foreign Key constraint is a referential integrity constraint. In other words, when you want to delete a row from a parent table and that row has associated child rows in another table then you first need to delete all associated rows from the child table that has a relationship with the parent table then delete the parent table row. To avoid this situation, you can use the ON DELETE CASCADE option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions for.

Create MVC Application

I will create an MVC application using Visual Studio 2012. So let’s see step-by-step how to create an MVC application.

Step 1: Go to "File" - "New" then click on "Project".

Step 2: Choose “ASP.NET MVC 4 Web Application” from the list, then provide the application name “LinqToSQLMvcApplication” and set the path in the location input where you want to create the application.

Step 3: Now choose the Project Template “Empty” and select “Razor” as the view engine from the dropdown list.

Step 4: Create a ContextData file using Object Relational Designer.

Entity classes are created and stored in LINQ to SQL Classes files (.dbml files). The O/R Designer opens when you open a .dbml file. It is a DataContext class that contains methods and properties for connecting to a database and manipulating the data in the database.

The DataContext name corresponds to the name that you provided for the .dbml file.

Step 4.1: Right-click on the Models folder in the Solution Explorer then go to "Add" and click on "Class..".

Step 4.2: Choose "LINQ to SQL Classes" from the list and provide the name "Operation" for the dbml name. Then click on "Add".

Step 4.3: After clicking the "Add" button, the ContextData file is created. Now we should drag all the tables to the left hand side of the designer and save (as shown in the figure below). This will create all the mappings and settings for each table and their entities.

The .dbml file’s database connection string is defined in the web.config file as in the following:

<connectionStrings>
<add name="DevelopmentConnectionString" 
connectionString="Data Source=sandeepss-PC;Initial Catalog=Development;User ID=sa;
Password=*******" providerName="System.Data.SqlClient" />
</connectionStrings> 

We can use a connection string in the web.config file or we can pass a connection string as a parameter in the constructor of the DataContext class to create an object of the DataContext class.

Now, create an action method in the controller (StudentController class under the Controllers folder) that returns a view with a model after the post request.

Create Publisher for Application

After creating an empty MVC application, I will create a Model, View and Controller to add a new publisher and show a list of departments. Use the following procedure to do that.

Step 1: Create Model "PublisherModel.cs"

The MVC Model contains all application logic (business logic, validation logic and data access logic), except pure view and controller logic. We create a class for PublisherModel (PublisherModel.cs file) under the Models folder. The PublisherModel class is in the Models folder; that file name is PublisherModel.cs as in the following:

namespace LinqToSQLMvcApplication.Models
{
    public class PublisherModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Year { get; set; }
    }
} 

Step 2: Create Controller for Publisher

Now I create a Publisher controller that has actions for creating and showing a list of publishers.

using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using LinqToSQLMvcApplication.DAL;
using LinqToSQLMvcApplication.Models; 

namespace LinqToSQLMvcApplication.Controllers
{
    public class PublisherController : Controller
    {
        private OperationDataContext context;
        public PublisherController()
        {
            context = new OperationDataContext();
        }

        public ActionResult Index()
        {
         IList<PublisherModel> publisherList = new List<PublisherModel>();
         var query = from publisher in context.Publishers
                        select publisher;           

            var publishers = query.ToList();
            foreach(var publisherData in publishers )
            {
               publisherList.Add(new PublisherModel()
                {
                    Id= publisherData.Id,
                    Name = publisherData.Name,
                    Year = publisherData.Year
                });
            }
            return View(publisherList);
        }       

        public ActionResult Create()
        {
            PublisherModel model = new PublisherModel();
            return View(model);
        }

        [HttpPost]
        public ActionResult Create(PublisherModel model)
        {
            try
            {
              Publisher publisher = new Publisher()
                {
                    Name = model.Name,
                    Year = model.Year
                };
                context.Publishers.InsertOnSubmit(publisher);
                context.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View(model);
            }
        }
    }
}  

Now we create a view to add a new publisher. To create the view, use the following procedure:

1. Right-click on the Action Method Create (GET).
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
5. Choose the Model class "PublisherModel" so it can be bound with the view.
6. Choose "Create" from the Scaffold template so we can do rapid development and we get the view for creating the new user.
7. Check the checkbox "Use a layout or master page".

@model LinqToSQLMvcApplication.Models.PublisherModel
@{
   ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>PublisherModel</legend>
        <div class="editor-label">
            @Html.LabelFor(model => model.Name)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Name)
            @Html.ValidationMessageFor(model => model.Name)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.Year)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Year)
            @Html.ValidationMessageFor(model => model.Year)
        </div>
        <p>
            <input type="submit" value="Create" />
        </p>
    </fieldset>
}
<div>
    @Html.ActionLink("Back to List", "Index")
</div>  

Now we create a view. To create the view for the list of publishers, use the following procedure:

1. Compile the source code successfully
2. Right-click on "Action Method Index".
3. The View Name is already filled in so don't change it.
4. The View Engine has already selected Razor so don't change it.
5. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
6. Choose the Model class "PublisherModel" so it can be bound with the view.
7. Choose "List" from the Scaffold template so rapid development can be done and we get the view with the code for showing the list of Users.
8. Check the checkbox "Use a layout or master page".

 @model IEnumerable<LinqToSQLMvcApplication.Models.PublisherModel>
@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Year)
        </th>
        <th></th>
    </tr>
@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Year)
        </td>
    </tr>
}
</table> 

Create Books for Application

After creating the publishers, I will create a Model, View and Controller to add a new book and show a list of books. So let’s see step-by-step.

Step 1: Create Model "BookModel.cs"

The MVC Model contains all the application logic (business logic, validation logic and data access logic) except for the pure view and controller logic. We create a class for BookModel (BookModel.cs file) under the Models folder, The BookModel class is in the Models folder; that file name is BookModel.cs as in the following:

 using System.Collections.Generic;
using System.ComponentModel;
using System.Web.Mvc;
namespace LinqToSQLMvcApplication.Models
{
    public class BookModel
    {
         public BookModel()
         {
             Publishers = new List<SelectListItem>();
         }

        public int Id { get; set; }
        public string Title { get; set; }
        public string Auther { get; set; }
        public string Year { get; set; }
        public decimal Price { get; set; }
        [DisplayName("Publisher")]
        public int PublisherId { get; set; }
        public string PublisherName { get; set; }
        public IEnumerable<SelectListItem> Publishers { get; set; }
    }
} 

Step 2: Create Controller for Book

Now, I create a Book controller that has actions for all the CRUD operations of the book.

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using LinqToSQLMvcApplication.DAL;
using LinqToSQLMvcApplication.Models;

namespace LinqToSQLMvcApplication.Controllers
{
    public class BookController : Controller
    {
        private OperationDataContext context;

        public BookController()
        {
            context = new OperationDataContext();
        }

        private void PreparePublisher(BookModel model)
        {
            model.Publishers = context.Publishers.AsQueryable<Publisher>().Select(x =>
                    new SelectListItem()
                    {
                             Text = x.Name,
                             Value = x.Id.ToString()
                    });
        } 

        public ActionResult Index()
        {
            IList<BookModel> BookList = new List<BookModel>();
            var query = from book in context.BOOKs
                        join publisher in context.Publishers
                        on book.PublisherId equals publisher.Id
                        select new BookModel {
                                                Id = book.Id,Title=book.Title,
                                                PublisherName=publisher.Name,Auther = book.Auther,
                                                Year = book.Year,Price=book.Price
                                            };
             BookList = query.ToList();
             return View(BookList);
        }

        public ActionResult Details(int id)
        {
            BookModel model = context.BOOKs.Where(x => x.Id == id).Select(x =>
                                                new BookModel()
                                                {
                                                    Id= x.Id,
                                                    Title=x.Title,
                                                    Auther=x.Auther,
                                                    Price =x.Price,
                                                    Year =x.Year,                                                    
                                                   PublisherName=x.Publisher.Name
                                                }).SingleOrDefault();         

            return View(model);
        }     

        public ActionResult Create()
        {
            BookModel model = new BookModel();
            PreparePublisher(model);
            return View(model);
        } 

        [HttpPost]
        public ActionResult Create(BookModel model)
        {
            try
            {
                BOOK book = new BOOK()
                {
                  Title = model.Title,
                  Auther = model.Auther,
                  Year = model.Year,
                  Price = model.Price,
                  PublisherId = model.PublisherId
                };
                context.BOOKs.InsertOnSubmit(book);
                context.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View(model);
            }
        }
        public ActionResult Edit(int id)
        {
            BookModel model = context.BOOKs.Where(x => x.Id == id).Select(x =>
                                new BookModel()
                                {
                                    Id = x.Id,
                                    Title = x.Title,
                                    Auther = x.Auther,
                                    Price = x.Price,
                                    Year = x.Year,
                                    PublisherId = x.PublisherId
                                }).SingleOrDefault();

            PreparePublisher(model);
            return View(model);
        }       

        [HttpPost]
        public ActionResult Edit(BookModel model)
        {
            try
            {

                BOOK book = context.BOOKs.Where(x => x.Id == model.Id).Single<BOOK>();
                book.Title = model.Title;
                book.Auther = model.Auther;
                book.Price = model.Price;
                book.Year = model.Year;
                book.PublisherId = model.PublisherId;
                context.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View(model);
            }
        }

        public ActionResult Delete(int id)
        {

            BookModel model = context.BOOKs.Where(x => x.Id == id).Select(x =>
                                  new BookModel()
                                  {
                                      Id = x.Id,
                                      Title = x.Title,
                                      Auther = x.Auther,
                                      Price = x.Price,
                                      Year = x.Year,
                                      PublisherName = x.Publisher.Name
                                  }).SingleOrDefault();
            return View(model);
        }
        [HttpPost]
        public ActionResult Delete(BookModel model)
        {
            try
            {
                BOOK book = context.BOOKs.Where(x => x.Id == model.Id).Single<BOOK>();
                context.BOOKs.DeleteOnSubmit(book);
                context.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View(model);
            }
        }
    }
} 

Now, I will create a view for each operation of the book. Let’s see each view with code.

1. Create a view to add a new book to the store

I am creating a view "Create.cshtml" under the Views/Book folder that uses two action methods (Create) of the controller for the Get request and another for the Post request.

 @model LinqToSQLMvcApplication.Models.BookModel
 @{
    ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm()) {
       <fieldset>
        <legend>BookModel</legend>
        <div class="editor-label">
            @Html.LabelFor(model => model.Title)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Title) 
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.Auther)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Auther)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.Year)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Year)
        </div> 

        <div class="editor-label">
            @Html.LabelFor(model => model.Price)
        </div>

        <div class="editor-field">
            @Html.EditorFor(model => model.Price)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.PublisherId)
        </div>

        <div class="editor-field">
            @Html.DropDownListFor(model => model.PublisherId,Model.Publishers)
        </div>
        <p>
         <input type="submit" value="Create" />
        </p>
    </fieldset>
} 
<div>
    @Html.ActionLink("Back to List", "Index")
</div> 

2. Show list of all books

I am creating a view "Index.cshtml" under the Views/Book folder that uses one action method (Index) of the controller for the Get request.

 @model IEnumerable<LinqToSQLMvcApplication.Models.BookModel>
@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Title)

        </th>
        <th>
            @Html.DisplayNameFor(model => model.Auther)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Year)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Price)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.PublisherName)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Title)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Auther)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Year)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Price)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.PublisherName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
} 
</table> 

3. Edit a book

I am creating a view “Edit.cshtml” under the Views/Book folder that uses two action methods (Edit) of the controller for the Get request and another for the Post request.

 @model LinqToSQLMvcApplication.Models.BookModel
@{
    ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm()) {
    <fieldset>
        <legend>BookModel</legend>
        @Html.HiddenFor(model => model.Id)
        <div class="editor-label">
            @Html.LabelFor(model => model.Title)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Title)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.Auther)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Auther)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.Year)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Year)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.Price)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Price)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.PublisherId)
        </div>
        <div class="editor-field">
            @Html.DropDownListFor(model => model.PublisherId,Model.Publishers)
         </div>
        <p>
            <input type="submit" value="Save" />
        </p>
    </fieldset>
}
<div>
    @Html.ActionLink("Back to List", "Index")
</div> 

4. Detail of a Book

I am creating a view "Details.cshtml" under the Views/Book folder that uses one action method (Details) of the controller for the Get request.

 @model LinqToSQLMvcApplication.Models.BookModel
 @{
    ViewBag.Title = "Details";
}

<h2>Details</h2>
<fieldset>
    <legend>BookModel</legend>
    <div class="display-label">
         @Html.DisplayNameFor(model => model.Title)
    </div>

    <div class="display-field">
        @Html.DisplayFor(model => model.Title)
    </div>

 

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Auther)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Auther)
    </div>

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Year)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Year)
    </div>
    <div class="display-label">
         @Html.DisplayNameFor(model => model.Price)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Price)
    </div> 

    <div class="display-label">
         @Html.DisplayNameFor(model => model.PublisherName)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.PublisherName)
    </div>
</fieldset>
<p>
    @Html.ActionLink("Edit", "Edit", new { id=Model.Id }) |
    @Html.ActionLink("Back to List", "Index")
</p> 

5. Delete a Book

I am creating a view "Delete.cshtml" under the Views/Book folder that uses two action methods (Delete) of the controller for the Get request and another for the Post request.

 @model LinqToSQLMvcApplication.Models.BookModel
@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
    <legend>BookModel</legend>
    <div class="display-label">
         @Html.DisplayNameFor(model => model.Title)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Title)
    </div> 

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Auther)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Auther)
    </div> 

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Year)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Year)
    </div> 

    <div class="display-label">
         @Html.DisplayNameFor(model => model.Price)
    </div>

    <div class="display-field">
        @Html.DisplayFor(model => model.Price)
    </div>  

    <div class="display-label">
         @Html.DisplayNameFor(model => model.PublisherName)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.PublisherName)
    </div>
</fieldset>
@using (Html.BeginForm()) {
    <p>
        <input type="submit" value="Delete" /> |
        @Html.ActionLink("Back to List", "Index")
    </p>
} 

Conclusion

This article explained the basic operations for a database entity (Book) and how it relates an entity publisher to another entity book. I hope that it will be helpful to you.