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)
- {
-
- }
- }
- private void GetData()
- {
- SampleDataContext dbContext = new SampleDataContext();
- GridView1.DataSource = dbContext.Employees;
- GridView1.DataBind();
- }
- protected void btnGetData_Click(object sender, EventArgs e)
- {
- GetData();
- }
-
- 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();
- }
-
- 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();
- }
-
- 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.