Reader Level:
Articles

ASP.NET MVC Web Application Using LINQ to SQL Classes

By Raj Kumar Beniwal on October 11, 2009
This article will demonstrate how to use an ASP.NET MVC Web Application using LINQ to SQL Classes.
  • 1
  • 0
  • 50983
Download Files:
 

My previous article ASP.NET MVC Web Application using ADO.NET Entity Data Model was a basic introduction to MVC and how to use them with the ADO.NET Entity Data Model. In this article we'll see how to use them with LINQ to SQL Classes.


Getting Started: After installing MVC framework SDK you will see a new template in the installed Visual Studio templates "ASP.NET MVC Web Application".

Create a new project using ASP.NET MVC Web Application.

Figure 1.

1.jpg

The next step is to decide if you want create a unit test project or not. When you create a new ASP.NET MVC application, the Create Unit Test Project dialog appears (see Figure 2). This dialog enables you to create a separate project in your solution for testing your ASP.NET MVC application. Select the option "No", do not create a unit test project and click the "OK" button.

Figure 2.

2.jpg

After the new ASP.NET MVC application is created, you will see several folders and files in the Solution Explorer. There are five folders by default Content, Controllers, Models, Scripts, and Views.

Figure 3.

3.jpg
After execution the output will look like this.

Figure 4.

4.jpg

Now it is time to add a new item "LINQ to SQL Classes" template.

Figure 5.

5.jpg

In this MVC application I am using the "Northwnd" database; you can copy that from the App_Data folder.

This data class will look like this.

Figure 6.

6.jpg
Now drag and drop your database table from the Server Explorer.

Figure 7.

7.jpg

Now add a new Controller in the Controllers folder and check that Add action for Create, Update and Details scenario and click Add.

Figure 8.

8.jpg

HomeController.cs will show some methods like this.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Web.Mvc.Ajax;

 

namespace MVCUsingLINQToSQL.Controllers

{

    public class HomeController : Controller

    {

        //

        // GET: /Home/

 

        public ActionResult Index()

        {

            return View();

        }

 

        //

        // GET: /Home/Details/5

 

        public ActionResult Details(int id)

        {

            return View();

        }

 

        //

        // GET: /Home/Create

 

        public ActionResult Create()

        {

            return View();

        }

 

        //

        // POST: /Home/Create

 

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Create(FormCollection collection)

        {

            try

            {

                // TODO: Add insert logic here

 

                return RedirectToAction("Index");

            }

            catch

            {

                return View();

            }

        }

 

        //

        // GET: /Home/Edit/5

 

        public ActionResult Edit(int id)

        {

            return View();

        }

 

        //

        // POST: /Home/Edit/5

 

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Edit(int id, FormCollection collection)

        {

            try

            {

                // TODO: Add update logic here

 

                return RedirectToAction("Index");

            }

            catch

            {

                return View();

            }

        }

    }

}

 

First of all add a namespace in the controller class.

using MVCUsingLINQToSQL.Models;

Now right-click on the Index method and click Add View and select View data class name view content.

Figure 9.

9.jpg

Add some code to show data in the list in the Index method.

public ActionResult Index()

        {

            var dataContext = new ProductsDataContext();

            var products = (from m in dataContext.Products

                       select m).ToList();

            return View(products);

        }

And run your application; you will see all the records.

Now right-click on the Create method and click on Add View.

Figure 10.

10.jpg

From Create.aspx you must remove the ProductID paragraph because this is an auto-generated column.

Now add this code in the controller class for the Create method.

//

        // GET: /Home/Create

 

        public ActionResult Create()

        {

            return View();

        }

 

        //

        // POST: /Home/Create

 

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Create(Product product)

        {          

            if (ModelState.IsValid)

            {

                try

                {

                    // TODO: Add insert logic here

                    var dataContext = new ProductsDataContext();

                    dataContext.Products.InsertOnSubmit(product);

                    dataContext.SubmitChanges();

                    return RedirectToAction("Index");

                }

                catch

                {

                    return View(product);

                }

            }

            return View(product);

          

        }

 

Here is your Edit method code:

//

        // GET: /Home/Edit/5

        public ActionResult Edit(int id)

        {

            var dataContext = new ProductsDataContext();

            var products = dataContext.Products.SingleOrDefault(x => x.ProductID == id);

            return View(products);           

        }

 

        //

        // POST: /Home/Edit/5

        [AcceptVerbs(HttpVerbs.Post)]

        public ActionResult Edit(int id, FormCollection collection)

        {

            var dataContext = new ProductsDataContext();

            var products = dataContext.Products.SingleOrDefault(x => x.ProductID == id);

            try

            {

                // TODO: Add update logic here

                UpdateModel(products);

                dataContext.SubmitChanges();

                return RedirectToAction("Index");

            }

            catch

            {

                return View(products);

            }

        }

Delete Method Code:

public ActionResult Delete(int id)

        {

            // Get movie to delete

            var dataContext = new ProductsDataContext();

            var productToDelete = dataContext.Products.First(m => m.ProductID == id);

 

            // Delete

            dataContext.Products.DeleteOnSubmit(productToDelete);

            dataContext.SubmitChanges();

 

            // Show Index view

            return RedirectToAction("Index");

        }

We are done with the LINQ to SQL Class here.

Article Extensions
Contents added by Raj Kumar Beniwal on Oct 14, 2009

Sorry folks i forgot to tell you about Details method. Right Click on Controller class Details method and Add View and select View Content Details that will add a new Details.aspx page in Home directory.

21.JPG  
Here is your Details.aspx page content.

Details.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<MVCUsingLINQToSQL.Models.Product>" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Details
</asp:Content>
<
asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<h2>Details</h2>
<fieldset>
<legend>Fields</legend>
<p>
ProductID:
<%
= Html.Encode(Model.ProductID) %>
</p>
<p>
ProductName:
<%
= Html.Encode(Model.ProductName) %>
</p>
<p>
SupplierID:
<%
= Html.Encode(Model.SupplierID) %>
</p>
<p>
CategoryID:
<%
= Html.Encode(Model.CategoryID) %>
</p>
<p>
QuantityPerUnit:
<%
= Html.Encode(Model.QuantityPerUnit) %>
</p>
<p>
UnitPrice:
<%
= Html.Encode(String.Format("{0:F}", Model.UnitPrice)) %>
</p>
<p>
UnitsInStock:
<%
= Html.Encode(Model.UnitsInStock) %>
</p>
<p>
UnitsOnOrder:
<%
= Html.Encode(Model.UnitsOnOrder) %>
</p>
<p>
ReorderLevel:
<%
= Html.Encode(Model.ReorderLevel) %>
</p>
<p>
Discontinued:
<%
= Html.Encode(Model.Discontinued) %>
</p>
</fieldset>
<p>
<%=Html.ActionLink("Edit", "Edit", new { id=Model.ProductID }) %> |
<%
=Html.ActionLink("Back to List", "Index") %></p>
</
asp:Content>

Now put this code in controller class Detail Method.

//

// GET: /Home/Details/5

public ActionResult Details(int id)
{
var dataContext = new ProductsDataContext();
var products = dataContext.Products.SingleOrDefault(x => x.ProductID == id);
return View(products);
}
Now run your application and click on Details link.
22.JPG

Raj Kumar Beniwal

Raj Kumar Beniwal is two times Microsoft MVP(Client App Dev) and four times C# Corner MVP,  working as asst project manager with lots of hands on experience using ASP.NET 2.0/3.5, AJAX, MVC, C#, Visual Basic .NET, S... Read more

COMMENT USING

Trending up