LINQ CRUD in asp.net MVC using LINQ to SQL

LINQ CRUD in asp.net MVC using LINQ to SQL

Step1. Create new Empty MVC Project

Open Visual Studio->New Project->Visual C#->Web->ASP.NET Web application->Give Project Name->Select Empty Template->Check MVC Checkbox->Click OK

Step 2. Now to we will add LINQ to sql classes(dbml) in Models folder .

Right click on Model folder =>select new Item->Select Data tab at left side in opened window->Select LINQ to sql classes in middle.->Give the name of your dbml file(here DataClasses1.dbml)




Step 3. Connect with sql database .We have to create sql database in sql server management studio.I supposed you  already created it so I am not telling way to create it.

Open server explorer(Ctrl+alt+s)->Right click on data connection and select add connection->Enter your sql server name->Select Database(my Database name is Ganesha).

(In this process DataClasses1DataContext will be created in models folder)

Create table sql query

CREATE TABLE [dbo].[Employee](
	[empid] [int] IDENTITY(1,1) NOT NULL,
	[empname] [varchar](50) NULL,
	[empfathername] [varchar](50) NULL,
	[empsalary] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 




I want to perform CRUD on this table.

Step 4. In server explorer window expand newly added(ganesha) database->drop Employee Table on design serface.+Ctrl S

(It will add employee model in DataClasses1DataContext)

Step 5. Create controller in controller folder-> Select Create MVC controller with read write action->Controller Name(Here Employee)=>OK

Pass namespace using LINQCRUD.Models

Write following code in Employee Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using LINQCRUD.Models;

namespace LINQCRUD.Controllers
{
    public class EmployeeController : Controller
    {
        //
        // GET: /Employee/

        //CReate Object of DataClasses1dataContext 
        DataClasses1DataContext db = new DataClasses1DataContext();
        public ActionResult Index()
        {
            //Fetch Records from Employee Table
            var empdata = from emp in db.Employees select emp;
            return View(empdata);
        }

        //
        // GET: /Employee/Details/5
        public ActionResult Details(int id)
        {
            //Lambda Expression
         var empdetails = db.Employees.Single(x => x.empid == id);       

            return View(empdetails);
        }

        //
        // GET: /Employee/Create
        public ActionResult Create()
        {
            return View();
        }

        //
        // POST: /Employee/Create
        [HttpPost]
        public ActionResult Create(Employee collection)
        {
            try
            {
                // TODO: Add insert logic here
                db.Employees.InsertOnSubmit(collection);
                db.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Employee/Edit/5
        public ActionResult Edit(int id)
        {
            var empdetails = db.Employees.Single(x => x.empid == id);

            return View(empdetails);
       
        }

        //
        // POST: /Employee/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, Employee collection)
        {
            try
            {
                // TODO: Add update logic here
                Employee emp = db.Employees.Single(x => x.empid == id);
                emp.empname = collection.empname;
                emp.empfathername = collection.empfathername;
                emp.empsalary = collection.empsalary;
                db.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Employee/Delete/5
        public ActionResult Delete(int id)
        {
            var empdetails = db.Employees.Single(x => x.empid == id);

            return View(empdetails);
        }

        //
        // POST: /Employee/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add delete logic here
                var empdetails = db.Employees.Single(x => x.empid == id);
                db.Employees.DeleteOnSubmit(empdetails);
                db.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

Add Views with Scaffolding with respective actions.

It will gererate html markup and it will show employee data.

Note: Using these scaffolding options we can create views for create, details, edit and delete  actions.

Note: Please insert some dummy rows in employee table to see results.

 




Download Source Code