Linq Insert update delete,select in vb.net

In this article we will focus on how to how to use LinqToSql classes to perform Insert,Update,Delete,Select operations on database tables using VB.NET.

Introduction to LINQToSQL
Linq to SQl provides runtime infrastructure for performing database operations such as Insert,Update,Delete, Select using objects. It automatically tracks changes in the background. It is very easy to operations against the database. You need not know the sql query syntax because it creates the T-SQL statements internally using LINQ code you write in your code.

For this demo I have created "EmployeeDB" database and added one table called Emp with the following schema.



Step1
Launch Visual Studio ->File -> New -> Project -> Select Visual Basic on the left hand side -> Select Console Application -> Give some name -> Click on OK. This creates a console based application in VB.NET

Step2
Right click Project in solution explorer-> Add New Item -> Select Data on the left hand side under common items -> Then select LINQ to SQL classes -> Give name "EmpModel.dbml". This creates LinqtoSQl class.

Step3
In the solution explorer -> Double click and open the EmpModel.dbml file and from the server explorer open the EmployeeDB database and then drag and drop the Emp table onto the EmpModel.dbml designer. It looks as shown below:

Step4
Build the project -> Add a New class to the project and name it "ManageEmp.vb". In this class we are going to write code using LinqtoSQL to perform Insert,Update,Delete,Select,Select all operations. We will maintain EmpId as the primary key and Identity column to fetch,update and delete Emp details.
Insert: InsertEmp method will be used to insert Emp details into the Emp table.
Update:UpdateEmp method will be used to update Emp details into the Emp table.
Delete:DeleteEmp method will be used to delete Emp details from the Emp table using the Emp Id field.
Select: GetEmp method will be used to Get specific Emp details from the Emp table.
SelectAll: GetAllEmp method will be used to Get all Emp details from the Emp table.

Below code is used to perform above operations

Public Class ManageEmp
    Dim model As New EmpModelDataContext()

    'To insert new emp details into Emp table
    Public Sub InsertEmp(e As Emp)
        model.Emps.InsertOnSubmit(e)
        model.SubmitChanges()

    End Sub
    'To update emp details with specific empId
    Public Sub UpdateEmp(empId As Integer)

        Dim q = (From existingEmp In model.Emps
        Where existingEmp.EmpId = empId
        Select existingEmp).SingleOrDefault()
        q.EmpName = "testing"
        q.Salary = 8000
        model.SubmitChanges()

    End Sub
    'To delete emp details with specific empId
    Public Sub DeleteEmp(empId As Integer)
        Dim d = (From existingEmp In model.Emps
                Where existingEmp.EmpId = empId
                Select existingEmp).SingleOrDefault()

        model.Emps.DeleteOnSubmit(d)
        model.SubmitChanges()
    End Sub
    'To get employee details with specific empId
    Public Function GetEmp(empId As Integer) As Emp
        Dim d = (From existingEmp In model.Emps
               Where existingEmp.EmpId = empId
               Select existingEmp).SingleOrDefault()
        Return d
    End Function

    'To get all employee details 
    Public Function GetAllEmp() As Emp()
        Return model.Emps.ToArray()

    End Function
End Class

Module Module1

    Sub Main()
        'Create an instance of the ManageEmp class
        Dim objManageEmp As New ManageEmp()

        'Print all emp details
        Dim emps = objManageEmp.GetAllEmp()
        Console.WriteLine("List of employees before Insert/Update/Delete")
        For Each em As Emp In emps
            Console.Write(em.EmpId & "  " & em.EmpName & "  " & em.HireDate.ToShortDateString())
            Console.WriteLine()
        Next

        'Invoke Insert method to insert new record in Emp table. Here EmpId is autogenerated column in database
        Dim e As New Emp()
        e.EmpName = "test"
        e.HireDate = Date.Now()
        e.Salary = 5000
        objManageEmp.InsertEmp(e)

        'Invoke Insert method to insert new record in Emp table. Here EmpId is autogenerated column in database
        Dim e1 As New Emp()
        e1.EmpName = "priya"
        e1.HireDate = Date.Now()
        e1.Salary = 60000
        objManageEmp.InsertEmp(e1)

        Console.WriteLine("List of employees after Insert operation")
        Console.WriteLine("")
        'Print all emp details
        Dim emp1 = objManageEmp.GetAllEmp()

        For Each em1 As Emp In emp1
            Console.Write(em1.EmpId & "  " & em1.EmpName & "  " & em1.HireDate.ToShortDateString())
            Console.WriteLine()
        Next
        Console.WriteLine()

        'Get Employee details
        Dim e2 As Emp = objManageEmp.GetEmp(2)

        'update salary, you can also update other columns by assigning them new value
        e2.Salary = 90000
        'Invoke Update method to update existing record in Emp table
        objManageEmp.UpdateEmp(2)

        Console.WriteLine(" employee details for empID=2 after update operation")
        Console.WriteLine("")


        Console.WriteLine(e2.EmpId & "  " & e2.EmpName & " " & e2.HireDate.ToShortDateString())
        Console.WriteLine()

        'Invoke delete method to delete emp record with empId=6 in Emp table
        objManageEmp.DeleteEmp(1)
        Console.WriteLine("List of employees after delete operation")
        Console.WriteLine("")

        'Print all emp details
        Dim emps2 = objManageEmp.GetAllEmp()
        For Each em3 As Emp In emps2
            Console.Write(em3.EmpId & "  " & em3.EmpName & "  " & em3.HireDate.ToShortDateString())
            Console.WriteLine()
        Next
    End Sub

End Module