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
1. I created instance of Data Context class.
2. Input parameter to this method is List of TraineeModels to be inserted.
3. I am inserting the records using InsertAllOnSubmit method on data context class.
4. 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.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataOperationsLibrary
{
public static class CRUDRepository
{
public static List<TraineeModel> GetTrainees()
{
DataClasses1DataContext
context = new DataClasses1DataContext();
var
result = from r in
context.TraineeModels select r;
return
result.ToList();
}
public TraineeModel GetSelectedTrainee(string EmpId)
{
DataClasses1DataContext
context = new DataClasses1DataContext();
TraineeModel
traineeResult = (from r in context.TraineeModels where
r.EmpId == EmpId
select r).First();
return
traineeResult;
}
public static bool
AddTrainee(TraineeModel trainee)
{
try
{
DataClasses1DataContext
context = new DataClasses1DataContext();
context.TraineeModels.InsertOnSubmit(trainee);
context.SubmitChanges();
return true;
}
catch
{
return false;
}
}
public static bool
DeleteTrainee(string empID)
{
try
{
DataClasses1DataContext
context = new DataClasses1DataContext();
TraineeModel obj = (from r
in context.TraineeModels
where r.EmpId.Contains(empID)
select r).First();
if
(obj != null)
{
context.TraineeModels
.DeleteOnSubmit(obj);
context.SubmitChanges();
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}
public static bool
UpdateTrainee(TraineeModel trainee)
{
try
{
DataClasses1DataContext
context = new DataClasses1DataContext();
TraineeModel res = (from r
in context.TraineeModels
where r.EmpId.Contains(trainee.EmpId)
select r
).First();
res.EmpName = trainee.EmpName;
res.Expertise = trainee.Expertise;
res.Native = trainee.Native;
context.SubmitChanges();
return true;
}
catch
{
return false;
}
}
public static bool
AddTrainees(List<TraineeModel>
lstTrainee)
{
try
{
DataClasses1DataContext
context = new DataClasses1DataContext();
context.TraineeModels.InsertAllOnSubmit(lstTrainee);
context.SubmitChanges();
return true;
}
catch
{
return false;
}
}
public static bool
DeleteTrainees(List<TraineeModel> lstTrainee)
{
try
{
DataClasses1DataContext
context = new DataClasses1DataContext();
context.TraineeModels.DeleteAllOnSubmit(lstTrainee);
context.SubmitChanges();
return true;
}
catch
{
return false;
}
}
}
}
I hope, this post was useful. Thanks for reading. Happy Coding.