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,



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



Step 3 - Open server explorer from view,



Step 4 - Connect your database and expand it,



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



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



Sample Code 

using System.Collections.Generic;
using System.Linq;

namespace DataAccessLayer
{
    public class Sample
    {
    //Insert method
        public void Insert(string data)
        {
            LINQToSQLDataContext dataObject = new LINQToSQLDataContext();
            dataObject.TPG_TeamDetails.InsertOnSubmit(
                           new TPG_TeamDetail
                           {
                               InternalProjectID = 1,
                               Role = "Dummy",
                               TeamMemberID = 2,
                               UserID = 2
                           });
            dataObject.SubmitChanges();
        }

// Delete method
        public void Delete(int teamMemberID)
        {
            LINQToSQLDataContext dataObject = new LINQToSQLDataContext();
            TPG_TeamDetail TPG_TeamDetailDO =
                      dataObject.TPG_TeamDetails.Where(p => p.TeamMemberID == teamMemberID).First();
            dataObject.TPG_TeamDetails.DeleteOnSubmit(TPG_TeamDetailDO);
            dataObject.SubmitChanges();
        }

// Select method
        public List<UserData> Select()
        {
            LINQToSQLDataContext dataObject = new LINQToSQLDataContext();
            return
           (from s in dataObject.TPG_TeamDetails
            select new UserData { UserID = s.UserID }
            ).ToList<UserData>();

        }

// Update method
        public void Update(int teamMemberID, int internalProjectID, int userID)
        {
            LINQToSQLDataContext dataObject = new LINQToSQLDataContext();
            TPG_TeamDetail teamDetailDO =
             dataObject.TPG_TeamDetails.Where(p => p.TeamMemberID == teamMemberID).First();
            teamDetailDO.InternalProjectID = internalProjectID;
            teamDetailDO.UserID = userID;
            dataObject.SubmitChanges();
        }
    }

    public class UserData
    {
        public int UserID { get; set; }
    }
}

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.