In this article, I am going to show you how we can perform various operations using LINQ to SQL. 
Database description
Let us say, I have created a database called ILPTrainee. This table got only one table with below structure
EmpId is primary key here.
For our explanation, I have put few records in the table. So table with records is as below
Using LINQ to SQL Class
- Create a new project of type class library.
- Right click on the project and add new item and select LINQ to SQL Class from Data tab.
- Select Server Explorer. Then Server Explorer will get open. Click on Data connection and Add new connection.
- In Server name give name of your database server and from drop down select the database.
-
Once you are done with above steps, you can see a dbml file got generated in solution explorer.
Follow the diagrams below, in order left to right and top to down.
LINQ to SQL class is an ORM which creates a class representing your table from the database. It will create a datacontext class. We will apply query against generated data context class.
TraineeModels class is representation of TraineeModel table.
Now querying
To perform query add a new class in the same class library project. Make this class as static. Add various static methods that will be performing different types of query.
Query 1 # retrieving all the records from table
Explanation
- I created instance of Data Context class.
- I applied simple LINQ query to retrieve all the records from the table TraineeModels in data context.
Query 2 # retrieving selected records from table
Explanation
- I created instance of Data Context class.
- I applied simple LINQ query to retrieve all the records from the table TraineeModels in data context.
- I applied where clause to filter the data.
Query 3 # inserting a single record
Explanation
- I created instance of Data Context class.
- Input parameter to this method is object of TraineeModel class.
- I am using InsertOnSubmit method on the datacontext class to insert one record.
- Then finally, I am calling the submitchanges to commit the database on datacontext.
Query 4 #Updating a Record
Explanation
- I created instance of Data Context class.
- Input parameter to this method is object of TraineeModel class.
- I am retrieving the object to be modified using where clause.
- After modification, calling the submitchanges on data context.
Query 5 #Deleting a Record
Explanation
- I created instance of Data Context class.
- Input parameter to this method is empid as string to be deleted.
- I am retrieving the object to be deleted using where clause.
- After deletion, calling the submitchanges on data context.
Query 6# Adding List of Records
Explanation
- I created instance of Data Context class.
- Input parameter to this method is List of TraineeModels to be inserted.
- I am inserting the records using InsertAllOnSubmit method on data context class.
- After insertion, calling the submitchanges on data context.
Query 7# Deleting List of Records
Explanation
- I created instance of Data Context class.
- Input parameter to this method is List of TraineeModels to be deletd.
- I am deleting the records using deleteAllOnSubmit method on data context class.
- After insertion, calling the submitchanges on data context.
Up to this step, I have written all possible LINQ query to be used for CRUD operation. Now to use this query just call the methods with class name.
For your reference, whole code is given below.
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace DataOperationsLibrary
7 {
8 public static class CRUDRepository
9 {
10 public static List<TraineeModel> GetTrainees()
11 {
12 DataClasses1DataContext context = new DataClasses1DataContext();
13
14 var result = from r in context.TraineeModels select r;
15 return result.ToList();
16
17 }
18
19 public TraineeModel GetSelectedTrainee(string EmpId)
20 {
21 DataClasses1DataContext context = new DataClasses1DataContext();
22
23 TraineeModel traineeResult = (from r in context.TraineeModels where r.EmpId == EmpId
24 select r).First();
25
26 return traineeResult;
27 }
28
29
30 public static bool AddTrainee(TraineeModel trainee)
31 {
32 try
33 {
34 DataClasses1DataContext context = new DataClasses1DataContext();
35 context.TraineeModels.InsertOnSubmit(trainee);
36 context.SubmitChanges();
37 return true;
38 }
39 catch
40 {
41 return false;
42 }
43 }
44 public static bool DeleteTrainee(string empID)
45 {
46 try
47 {
48 DataClasses1DataContext context = new DataClasses1DataContext();
49 TraineeModel obj = (from r in context.TraineeModels
50 where r.EmpId.Contains(empID)
51 select r).First();
52 if (obj != null)
53 {
54 context.TraineeModels .DeleteOnSubmit(obj);
55 context.SubmitChanges();
56 return true;
57 }
58 else
59 {
60 return false;
61 }
62
63 }
64 catch
65 {
66 return false;
67 }
68 }
69 public static bool UpdateTrainee(TraineeModel trainee)
70 {
71 try
72 {
73 DataClasses1DataContext context = new DataClasses1DataContext();
74 TraineeModel res = (from r in context.TraineeModels
75 where r.EmpId.Contains(trainee.EmpId)
76 select r
77 ).First();
78
79
80 res.EmpName = trainee.EmpName;
81 res.Expertise = trainee.Expertise;
82 res.Native = trainee.Native;
83
84 context.SubmitChanges();
85 return true;
86 }
87 catch
88 {
89 return false;
90 }
91
92
93 }
94 public static bool AddTrainees(List<TraineeModel> lstTrainee)
95 {
96 try
97 {
98 DataClasses1DataContext context = new DataClasses1DataContext();
99 context.TraineeModels.InsertAllOnSubmit(lstTrainee);
100 context.SubmitChanges();
101 return true;
102 }
103 catch
104 {
105 return false;
106 }
107 }
108
109 public static bool DeleteTrainees(List<TraineeModel> lstTrainee)
110 {
111 try
112 {
113 DataClasses1DataContext context = new DataClasses1DataContext();
114 context.TraineeModels.DeleteAllOnSubmit(lstTrainee);
115 context.SubmitChanges();
116 return true;
117 }
118 catch
119 {
120 return false;
121 }
122 }
123 }
124 }
125
I hope, this post was useful. Thanks for reading. Happy Coding.
Dhananjay Kumar 
Leave a Reply