Linq Select ,Insert, Update, Delete on Sql Server

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 

1. Create a new project of type class library. 
2. Right click on the project and add new item and select LINQ to SQL Class from Data tab. 
3. Select Server Explorer. Then Server Explorer will get open. Click on Data connection and Add new connection. 
4. In Server name give name of your database server and from drop down select the database.
5. 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 

1. I created instance of Data Context class. 
2. 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 

1. I created instance of Data Context class. 
2. I applied simple LINQ query to retrieve all the records from the table TraineeModels in data context. 
3. I applied where clause to filter the data. 

Query 3 # inserting a single record 

Explanation 

1. I created instance of Data Context class. 
2. Input parameter to this method is object of TraineeModel class. 
3. I am using InsertOnSubmit method on the datacontext class to insert one record. 
4. Then finally, I am calling the submitchanges to commit the database on datacontext. 

Query 4 # Updating a Record

Explanation 

1. I created instance of Data Context class. 
2. Input parameter to this method is object of TraineeModel class. 
3. I am retrieving the object to be modified using where clause. 
4. After modification, calling the submitchanges on data context. 

Query 5 # Deleting a Record

Explanation 

1. I created instance of Data Context class. 
2. Input parameter to this method is empid as string to be deleted.  
3. I am retrieving the object to be deleted using where clause. 
4. 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 

1. I created instance of Data Context class. 
2. Input parameter to this method is List of TraineeModels to be deletd.  
3. I am deleting the records using deleteAllOnSubmit method on data context class. 
4. 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;

            }

        }

    }

}