Monday, November 30, 2009

ASP.NET MVC Web Application using LINQ to SQL Classes



In this article we'll see how to use with LINQ to SQL Classes.


Get Started - After installing MVC framework SDK you will see a new template in installed visual studio templates 'ASP.NET MVC Web Application'.

Create a new project using ASP.NET MVC Web Application :
Figure1.
1.jpg

Next step if you want create 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.

Figure2.
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.

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

Figure4.
4.jpg

Now time to add a new item 'LINQ to SQL Classes' template.

Figure5.
5.jpg

In this MVC application I am using NORTHWND
database you can copy that from App_Data folder.
This data class will look like this.
Figure6.
6.jpg
Now drag and drop your database table from Server Explorer.

Figure7.
7.jpg

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

Figure8.
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 on controller class.
using MVCUsingLINQToSQL.Models;
Now right click on Index method and click Add View and select View data class name view content.
Figure9.
9.jpg

Add some code to show data in list on 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 records.
Now right click on Create method and click on Add View.
Figure10.
10.jpg

From Create.aspx you have to remove ProductID paragraph because this is auto generated column.

Now add this code in controller class for 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: 




See full details: http://www.c-sharpcorner.com/UploadFile/raj1979/MVCUsingLINQSQL10092009060837AM/MVCUsingLINQSQL.aspx