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.

    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.

Advertisements

8 thoughts on “7 queries on LINQ to SQL class

  1. It has helped me a lot…..to get the flow of “LINQ to SQL”…….Thanks.

  2. Thanks a lot Dhananjay for this very useful article.Any beginner in Linq can get a very good idea about linq from this article.The way you expalining things from very basic level is fabulous

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s