LINQ to SQL Using Visual Studio

Introduction

This article helps us to learn about  “LINQ to SQL” and takes you through the step by step process to explain how to create the LINQ to SQL using Visual Studio and also how toachieve the basic Insert, Select, Update and delete operations using the concept of LINQ to SQL.

LINQ to SQL

We have seen the option of “LINQ to SQL” in visual studio 2010 onwards, many may not use it and some may get the chance to use it. “L2S” – [LINQ to SQL] is an Object Relational Mapping [ORM - like other frameworks], which is used to create the strongly typed models automatically with the .net classes based on SQL data tables of the refereed or strongly typed databases. That's simply to say if you provide the Database and map it using LINQ to SQL, then this framework will provide you the classes with properties similar to the data tables.

We can use this LINQ to SQL and can achieve the CRUD operation like Select, Insert, Update, Delete using C# language.

Sample LINQ to SQL 

Please follow the steps and try the basic operation with your database tables,

Step 1 - Do right click on your project and choose new item option,

new

Step 2 - In the template wizard choose “LINQ to SQL Classes” template and name it as you wish

template

Step 3 - Open server explorer from view,

explorer

Step 4 - Connect your database and expand it,

database

Step 5 - Select all your database tables and place inside your dbml file. Just drag all the tables and place into your dbml file,

tables

Step 6 - Now you can see all the tables in dbml file with relationships,

file

Sample Code 

  1. using System.Collections.Generic;  
  2. using System.Linq;  
  3.   
  4. namespace DataAccessLayer  
  5. {  
  6.     public class Sample  
  7.     {  
  8.     //Insert method  
  9.         public void Insert(string data)  
  10.         {  
  11.             LINQToSQLDataContext dataObject = new LINQToSQLDataContext();  
  12.             dataObject.TPG_TeamDetails.InsertOnSubmit(  
  13.                            new TPG_TeamDetail  
  14.                            {  
  15.                                InternalProjectID = 1,  
  16.                                Role = "Dummy",  
  17.                                TeamMemberID = 2,  
  18.                                UserID = 2  
  19.                            });  
  20.             dataObject.SubmitChanges();  
  21.         }  
  22.   
  23. // Delete method  
  24.         public void Delete(int teamMemberID)  
  25.         {  
  26.             LINQToSQLDataContext dataObject = new LINQToSQLDataContext();  
  27.             TPG_TeamDetail TPG_TeamDetailDO =  
  28.                       dataObject.TPG_TeamDetails.Where(p => p.TeamMemberID == teamMemberID).First();  
  29.             dataObject.TPG_TeamDetails.DeleteOnSubmit(TPG_TeamDetailDO);  
  30.             dataObject.SubmitChanges();  
  31.         }  
  32.   
  33. // Select method  
  34.         public List<UserData> Select()  
  35.         {  
  36.             LINQToSQLDataContext dataObject = new LINQToSQLDataContext();  
  37.             return  
  38.            (from s in dataObject.TPG_TeamDetails  
  39.             select new UserData { UserID = s.UserID }  
  40.             ).ToList<UserData>();  
  41.   
  42.         }  
  43.   
  44. // Update method  
  45.         public void Update(int teamMemberID, int internalProjectID, int userID)  
  46.         {  
  47.             LINQToSQLDataContext dataObject = new LINQToSQLDataContext();  
  48.             TPG_TeamDetail teamDetailDO =  
  49.              dataObject.TPG_TeamDetails.Where(p => p.TeamMemberID == teamMemberID).First();  
  50.             teamDetailDO.InternalProjectID = internalProjectID;  
  51.             teamDetailDO.UserID = userID;  
  52.             dataObject.SubmitChanges();  
  53.         }  
  54.     }  
  55.   
  56.     public class UserData  
  57.     {  
  58.         public int UserID { getset; }  
  59.     }  
  60. }  
Conclusion

Hope this may have helped you to try the simple application for achieving the basic operation with the database table you have referred from the SQL.