Perform CRUD Operations in Database Using LINQ

In this article, I explain how to select, insert , update and delete records in a database using LINQ.

The Backend

Step 1

First we need a table for doing the select, insert , update and delete operations. So we need to create a table with the name of "Employees". The following script will create and insert data into the table.

Create table Employees
(
ID int primary key identity,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
Salary int,
DepartmentId int foreign key references Departments(Id)
)

Insert into Employees values ('Jai', 'Reddy', 'Male', 32000, 1)
Insert into Employees values ('Ajay', 'k', 'Male', 23000, 3)
Insert into Employees values ('Vijay', 'j', 'Male', 8000, 1)
Insert into Employees values ('Sujay', 'v', 'Male', 8000, 2)
Insert into Employees values ('Kumari', 'k', 'Female', 12000, 2)

In Application (in Visual Studio):

Step 2

Create a new empty ASP.Net web application project and Name it LINQtoSQL.

Step 3

Next go to View -> Open Server Explorer then right-click on Data Connections. It opens a new window.

In that set the Server name, User name, Password and Database then click on "Test Connection" to check whether the connection was established or not.



Step 4

Go to Solution Explorer and right-click on your project then select Add New item then select "LINQ to SQL Classes" and name it Sample.dbml.



Step 5

Open Server Explorer, refresh your databases and drag the "Employees" table in Sample.dbml as shown in the following diagram.



Step 6

Add a WebForm to your project. Copy the following code inside the Body section.

<div>
    <asp:Button ID="btnGetData" runat="server" Text="GetData" OnClick="btnGetData_Click" />
    <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
    <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
    <asp:Button ID="Delete" runat="server" Text="btnDelete" OnClick="Button4_Click" />
    <br />
    <asp:GridView ID="GridView1" runat="server"></asp:GridView>
</div>

Step 7

Copy and paste the following code into your code behind file.

using System;
using System.Linq;
using System.Web.UI;
using LINQtoSQL.LinqToSQLFiles;
namespace LINQtoSQL.Application.Examples
{
    public partial class LinqSQL1: System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                //GetData();
            }
        }
        private void GetData()
        {
            SampleDataContext dbContext = new SampleDataContext();
            GridView1.DataSource = dbContext.Employees;
            GridView1.DataBind();
        }
        protected void btnGetData_Click(object sender, EventArgs e)
        {
            GetData();
        }
//Insert
        protected void btnInsert_Click(object sender, EventArgs e)
        {
            using(SampleDataContext dbContext = new SampleDataContext())
            {
                Employee emp = new Employee
                {
                    FirstName = "Jaipal",
                    LastName = "Reddy",
                    Gender = "Male",
                    Salary = 55000,
                    DepartmentId = 6,
                };
                dbContext.Employees.InsertOnSubmit(emp);
                dbContext.SubmitChanges();
            }
            GetData();
        }
//Update
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            using(SampleDataContext dbc = new SampleDataContext())
            {
                Employee emp = dbc.Employees.SingleOrDefault(x = > x.ID == 6);
                emp.Salary = 95000;
                dbc.SubmitChanges();
            }
            GetData();
        }
//Delete
        protected void Button4_Click(object sender, EventArgs e)
        {
            using(SampleDataContext dbc = new SampleDataContext())
            {
                Employee emp = dbc.Employees.SingleOrDefault(x = > x.ID == 6);
                dbc.Employees.DeleteOnSubmit(emp);
                dbc.SubmitChanges();
            }
            GetData();
        }
    }
}

I hope you enjoy this. Please provide your valuable suggestions and feedback if you found this article helpful.