Work With LINQ TO SQL To Perform CRUD Operation

In this article I am explaining the step to work with LINQ to SQL for performing CRUD Operation.

Step to work with LINQ to SQL or OR-Mapping tool to perform CRUD operation.

Step1: Creating DBML file

Create Windows Forms Application,

new

Go to Solution Explorer, click right mouse button, Add, then click New Item.

solution

Select data from Visual C# installed item then select LINQ to SQL classes Template and type the file name Employee.dbml. After click Add,

link

This will create Employee.dbml file as (shown in red Box).

red

Step 2: Connecting to database,

Go to Server Explorer, select data connection and click right mouse button. After that click on Add Connection, then click Change, select Microsoft SQL Server data service and data provider as .NET framework data provider for SQL server. Click OK, type the server name, select Use SQL Server Authentication, type username password or (also can be select Windows Authentication type) activate the check box save my password and select the database name EmployeeDetails. Now click OK.

add

Double click on tables, drag the table Emp on the left side part of DBML file. This will create class with name Emp and properties as the same name as table.

emp

Design the windows form and write the following code,

form

  1. using System;  
  2. usingSystem.Collections.Generic;  
  3. usingSystem.ComponentModel;  
  4. usingSystem.Data;  
  5. usingSystem.Drawing;  
  6. usingSystem.Linq;  
  7. usingSystem.Text;  
  8. usingSystem.Threading.Tasks;  
  9. usingSystem.Windows.Forms;  
  10.   
  11. namespaceWindowsApplicationLTSQL  
  12. {  
  13.     publicpartialclassForm1: Form   
  14.     {  
  15.         EmployeeDataContextobjdc = newEmployeeDataContext();  
  16.         public Form1()  
  17.         {  
  18.             InitializeComponent();  
  19.         }  
  20.         privatevoid Form1_Load(object sender, EventArgs e)   
  21.         {  
  22.   
  23.         }  
  24.         privatevoidbtnInsert_Click(object sender, EventArgs e)   
  25.         {  
  26.             Employee obje = new Employee()  
  27.             Obje.eid = Convert.ToInt32(txtEid.Text);  
  28.             Obje.ename = txtEName.Text;  
  29.             Obje.edesignation = txtEdesignation.Text;  
  30.             Obje.esalary = Convert.ToDouble(txtsalary.Text);  
  31.             Objdc.Employee.InsertOnSubmit();  
  32.             Objdc.SubmitChanges();  
  33.             MessageBox.Show("Data is Inserted to Database ");  
  34.         }  
  35.         privatevoidbtnUpdate_Click(object sender, EventArgs e)  
  36.         {  
  37.             var rec(From Empinobjdc.Employee where Emp.Eid = Convert.ToInt32(txtEid.Text) Select Emp).First();  
  38.             rec.EName = txtEName.Text;  
  39.             rec.EDesignation = txtDesignation.Text;  
  40.             rec.ESalary = Convert.ToInt32(txtESalary.Text);  
  41.             objdc.SubmitChanges();  
  42.             MessageBox.Show("Record is Updated");  
  43.         }  
  44.   
  45.         privatevoidbtnDelete_Click(object sender, EventArgs e)   
  46.         {  
  47.             var rec(From Empinobjdc.Employee where Emp.Eid = Convert.ToInt32(txtEid.Text) Select Emp).First();  
  48.             objdc.Employee.DeleteOnSubmit();  
  49.             objdc.SubmitChanges();  
  50.             MessageBox.Show("Record is Deleted");  
  51.         }  
  52.   
  53.         privatevoidbtnFind_Click(object sender, EventArgs e)  
  54.         {  
  55.             var rec(From Empinobjdc.Employee where Emp.Eid = Convert.ToInt32(txtEid.Text) Select Emp).First();  
  56.             txtEName.Text = rec.EName;  
  57.             txtDesignation.Text = rec.EDesignation;  
  58.             txtESalary.Text = rec.ESalary.ToString();  
  59.         }  
  60.   
  61.     }  
  62. }  
Recap: For working with LINQ to SQL to perform the CRUD operation.

Step 1: Creating dbml file.

Step 2: Connecting to database.