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;
  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. }
  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. }
  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>();
  42. }
  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. }
  56. public class UserData
  57. {
  58. public int UserID { get; set; }
  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.