Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.
Insert Update Delete using stored procedures in LINQ to SQL

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.
linq to sql insert update delete example

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
configure stored procedure in linq to sql

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.

LINQ to SQL Tutorial

3 comments:

  1. Aswesome video, thanks, Why did not you use sp to delete,update,insert?

    ReplyDelete
  2. Thanks Alot bro, you do great job,
    about this video I did not understand why you did not use SP, we have the adhock code written and although for example delete is happening through SP but if we dont have ad hock code the app will not work, so what is the use having SP?

    ReplyDelete
  3. Thanks, your video are great, but LINQ TO SQL Video No-5 you did not use stored procedure , why?

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.