Post
Ask Question

CRUD Operations Using LINQ to SQL In ASP.NET

In this article I will show you step by step roadmap of creating an ASP.NET application in which we will perform CRUD using LINQ to SQL assuming that you have prior basic knowledge of LINQ.
 
Let's begin!
 
Step 1: Let's create a table UserDetails to perform CRUD operations with the following attributes and types.

 
Step 2: Go to project and Add, New Item, then Data and select LINQtoSQL Class.
 
 
Step 3: New Window with Server Explorer opens, now click on server explorer.

 
Step 4: Add New Connection. 
 
 
Step 5: Provide the Server name and Database name, here we are using local database (Windows Authentication).
 
 
Step 6: Test Connection.
 
 
Step 7: Selected database will be displayed in Server Explorer.
 

Step 8: You can see the table under selected database.
 
 
Step 9: Now drag and drop that table over DataClasses1.dbml. Here table name converted into a typical C# class and its attributes converted into properties.
 
 
 
Step 10: Now add a webform to retrieve that table data.
 
 
Step 11: Add GridView data tool from the toolbox.
 
 
Added buttons for Select, Insert, Update and Delete.
 
 
Step 12: You can check the automatically generated connection string in Web.Config.
 
 
Step 13: Check DataClasses1DataContext class, which is the entry point.

 
Apply the following snippet 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7.   
  8. namespace JQueryImageRotator  
  9. {  
  10.     public partial class WebForm1 : System.Web.UI.Page  
  11.     {  
  12.         protected void Page_Load(object sender, EventArgs e)  
  13.         {  
  14.   
  15.               
  16.         }  
  17.   
  18.         private void RetrieveUserDetails()  
  19.         {  
  20.             DataClasses1DataContext dtContext = new DataClasses1DataContext();  
  21.            GridView1.DataSource = dtContext.UserDetails;  
  22.             //GridView1.DataSource = from userDetails in dtContext.UserDetails  
  23.             //                       where userDetails.City == "noida"  
  24.             //                       select userDetails;  
  25.             GridView1.DataBind();  
  26.         }  
  27.   
  28.         protected void btnSelect_Click(object sender, EventArgs e)  
  29.         {  
  30.             RetrieveUserDetails();  
  31.         }  
  32.   
  33.         protected void btnInsert_Click(object sender, EventArgs e)  
  34.         {  
  35.             using (DataClasses1DataContext dtContext = new DataClasses1DataContext())  
  36.             {  
  37.                 UserDetail Udetails = new UserDetail {   
  38.                     
  39.                 UserName="Shridhar",  
  40.                 City="New Delhi",  
  41.                 Designation="SE"  
  42.                 };  
  43.   
  44.                 dtContext.UserDetails.InsertOnSubmit(Udetails);  
  45.                 dtContext.SubmitChanges();  
  46.             }  
  47.             RetrieveUserDetails();  
  48.         }  
  49.   
  50.         protected void btnUpdate_Click(object sender, EventArgs e)  
  51.         {  
  52.             using (DataClasses1DataContext dtContext = new DataClasses1DataContext())  
  53.             {  
  54.                 UserDetail Udetails = dtContext.UserDetails.SingleOrDefault(x => x.UserId == 6);  
  55.                 Udetails.Designation = "Leader";                 
  56.                 dtContext.SubmitChanges();  
  57.             }  
  58.             RetrieveUserDetails();  
  59.         }  
  60.   
  61.         protected void btnDelete_Click(object sender, EventArgs e)  
  62.         {  
  63.             using (DataClasses1DataContext dtContext = new DataClasses1DataContext())  
  64.             {  
  65.                 UserDetail Udetails = dtContext.UserDetails.SingleOrDefault(x => x.UserId == 5);  
  66.                 dtContext.UserDetails.DeleteOnSubmit(Udetails);  
  67.                 dtContext.SubmitChanges();  
  68.             }  
  69.             RetrieveUserDetails();  
  70.   
  71.         }  
  72.     }  
  73. }  
Use this snippet to perform CRUD.
 
Closure

In this article we learned how we can perform CRUD using LINQ to SQL. I hope you liked this. Comments and complements are always welcomed.  


Similar Articles