Insert Update Delete using LINQ and SQL Classes with Lambda Expression

Till now, you have learned various parts of LINQ and in this chapter, you will learn how to work with SQL database using LINQ and lambda expression with complete programming example.

In this chapter, I took an example of product store in which you are assigned to make a website that could Insert, Update and Delete items.

Parts of Example

1. Creating a Database and Table
2. Adding LINQ to SQL Classes
3. Creating Model
4. Creating Controller
5. Adding View
6. Run Project

As, you can see, I have divided this project in 6 parts and I will explain each part with complete programming example.

1. Creating Database and Table

Create a New MVC Web Project and Add following database and Table.

1. Click on View > Server Explorer

2. Right click on Data Connections > Create New SQL Server Database…

3. Select Server and Create Database myshop.

4. Expand myshop database in server explorer and right click on Tables > Add New Table.

5. Create Table ProductStore as follows:

CREATE TABLE [dbo].[ProductStore] (
    [Id]           INT           NOT NULL,
    [productName]  NVARCHAR (50) NULL,
    [productPrice] INT           NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

2. Adding LINQ to SQL Classes

LINQ to SQL Classes is an Object Relational Mapping method and it is a good alternative of entity framework. It is easy to use and worked with wide range of data source. LINQ to SQL classes provides an easy way to model and visualize a database in Designer.

1. Go to Solution Explorer; Right click on Project Name > Add > New Item.

2. Select LINQ to SQL Classes and give the name products.dbml and click on Add button.

3. Drag the Table ProductStore in Designer.

4. Now, you can see that the <connectionStrings> is automatically added in Web.config

3. Creating Model

In this part, we will create a Model that will be mapped with this database table and will be used for CRUD (Create, Read, Update and Delete) operation.

Right click on Model folder in Solution Explorer and create new class productModel.cs

namespace WebApplication1.Models
{
    public class productModel
    {
        public int Id { get; set; }
        public string ProductName { get; set; }
        public int ProductPrice { get; set; }
    }
}

4. Creating Controller

This part is most important part, because this part will contain all the programming logic for CRUD operation.

1. Right click on Controllers folder in solution explorer > Add > Controller.

2. Select MVC 5 Controller with read/write actions and give controller name as ProductController.cs

3. Now paste the following code in your controller.

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

namespace WebApplication1.Controllers
{
    public class ProductController : Controller
    {

        productsDataContext db = new productsDataContext();
        // GET: Product
        public ActionResult Index()
        {
            IList<productModel> productList = new List<productModel>();
            var query = from s in db.ProductStores
                        select s;
            var listdata = query.ToList();

            foreach (var pdata in listdata)
            {
                productList.Add(new productModel()
                {
                    Id = pdata.Id,
                    ProductName = pdata.productName,
                    ProductPrice = (int)pdata.productPrice,
                });
            }
            return View(productList);
        }

        // GET: Product/Details/5
        public ActionResult Details(int id)
        {
            productModel pmodel = db.ProductStores.Where(val => val.Id == id).Select(val => new productModel
            {
                Id = val.Id,
                ProductName = val.productName,
                ProductPrice = (int)val.productPrice,
            }).SingleOrDefault();
            return View(pmodel);
        }

        // GET: Product/Create
        public ActionResult Create()
        {
            productModel pModel = new productModel();
            return View(pModel);
        }

        // POST: Product/Create
        [HttpPost]
        public ActionResult Create(productModel pmd)
        {
            try
            {
                ProductStore pModel = new ProductStore();
                // TODO: Add insert logic here
                pModel.Id = pmd.Id;
                pModel.productName = pmd.ProductName;
                pModel.productPrice = pmd.ProductPrice;

                db.ProductStores.InsertOnSubmit(pModel);
                db.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View("Index");
            }
        }

        // GET: Product/Edit/5
        public ActionResult Edit(int id)
        {
            productModel pmodel = db.ProductStores.Where(val => val.Id == id).Select(val => new productModel
            {
                Id = val.Id,
                ProductName = val.productName,
                ProductPrice = (int)val.productPrice
            }).SingleOrDefault();
            return View(pmodel);
        }

        // POST: Product/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        // GET: Product/Delete/5
        public ActionResult Delete(int id)
        {
            productModel pmodel = db.ProductStores.Where(val => val.Id == id).Select(val => new productModel
            {
                Id = val.Id,
                ProductName = val.productName,
                ProductPrice = (int)val.productPrice
            }).SingleOrDefault();
            return View(pmodel);
        }

        // POST: Product/Delete/5
        [HttpPost]
        public ActionResult Delete(productModel pmd)
        {
            try
            {
                ProductStore pstore = db.ProductStores.Where(val => val.Id == pmd.Id).Single<ProductStore>();
                db.ProductStores.DeleteOnSubmit(pstore);
                db.SubmitChanges();
                // TODO: Add delete logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

5. Adding View

This time is for adding scaffolding view item for each action method.

1. Right click on each action method and select Add View.

2. Fill following details in add view dialog box.

3. Add navigation link. Go to Solution Explorer > Shared Folder > _Layout.cshtml

4. Add the following line.

6. Run Your Project.

Press F5 to run your project.

Summary

In this tutorial, I explained how can you insert, update and delete row in SQL Database using lambda expression and LINQ.