Part 5 - Insert Update Delete using stored procedures in LINQ to SQL

Suggested Videos
Part 2 - Insert Update Delete using LINQ to SQL
Part 3 - How to view LINQ to SQL generated SQL queries
Part 4 - Using stored procedures with LINQ to SQL



In this video, we will discuss using stored procedures to perform Insert Update and Delete. This is continuation to Part 4. Please watch Part 4 before proceeding.



Step 1 : Create Insert, Update and Delete stored procedures

-- Insert Stored Procedure

Create procedure InsertEmployee

@FirstName nvarchar(50),

@LastName nvarchar(50),

@Gender nvarchar(50),

@Salary int,

@DepartmentId int

as

Begin

     Insert into Employees(FirstName, LastName, Gender, Salary, DepartmentId)

     values (@FirstName, @LastName, @Gender, @Salary, @DepartmentId)

End

GO

-- Update Stored Procedure

Create procedure UpdateEmployee

@ID int,

@FirstName nvarchar(50),

@LastName nvarchar(50),

@Gender nvarchar(50),

@Salary int,

@DepartmentId int

as

Begin

     Update Employees Set

     FirstName = @FirstName, LastName = @LastName, Gender = @Gender,

     Salary = @Salary, DepartmentId = @DepartmentId

     where ID = @ID

End

GO

-- Delete Stored Procedure

Create procedure DeleteEmployee

@ID int

as

Begin

     Delete from Employees where ID = @ID

End

GO

Step 2 : In Visual Studio, click on the "View" menu and select "Server Explorer". Right click on "Data Connection" and  select "Refresh". Expand "Stored Procedures" folder. Here you should find all the stored procedures.


Step 3 : Drag Insert, Update and Delete stored procedures from the Server Explorer window and drop it on the LINQ to SQL class designer. This will automatically create respective methods with the same name as the stored procedures.


Step 4 : Mapping stored procedures to work with LINQ to SQL
a) Right click on Employee entity on LINQ to SQL designer and select "Configure Behavior" option.
b) In the "Configure Bevior" window, set
    Class = Employee
    Behavior = Insert
    Select "Customize" radio button
    From the DropDownList, select InsertEmployee() stored procedure
    Map Method Arguments to Class properties
c) Finally click OK


d) In a similar fashion, configure stored procedures for Update and Delete operations.

At this point, run SQL profiler and start a new trace. Run the application and perform Insert, Update and Delete. In the SQL profiler trace notice that the respective stored procedures are called as expected.