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

Part 6 - Stored procedures with output parameters in LINQ to SQL

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

In this video, we will discuss how to call stored procedures with output parameters using LINQ to SQL. This is continuation to Part 5. Please watch Part 5 before proceeding.

Step 1 : Create the stored procedure
Create procedure GetEmployeesByDepartment
@DepartmentId int,
@DepartmentName nvarchar(50) out
     Select @DepartmentName = Name
     from Departments where ID = @DepartmentId

     Select * from Employees
     where DepartmentId = @DepartmentId


Use the following SQL to test the stored procedure
Declare @DeptName nvarchar(50)
Execute GetEmployeesByDepartment 2, @DeptName out
Select  @DeptName

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 "GetEmployeesByDepartment" stored procedure.
linq to sql stored procedure output parameter

Step 3 : Drag "GetEmployeesByDepartment" stored procedure from the Server Explorer window and drop it on the LINQ to SQL class designer. This will automatically create a method with the same name as the stored procedure.

Step 4 : Drag and drop a button and a label control on the webform. 

For the button, change the following properties
ID = lblDept

For the button, change the following properties
Text = Get Employees By Department
ID = btnGetEmployeesByDepartment

Double click the button control to generate the click event handler method.

If you are following along the design of the webform, should be as shown below.
linq to sql execute stored procedure with output parameter

Step 5 : Finally in the code-behind file, call GetEmployeesByDepartment() method using the DataContext class instance.
using (SampleDataContext dbContext = new SampleDataContext())
    string deptName = string.Empty;
    GridView1.DataSource = dbContext.GetEmployeesByDepartment(1, ref deptName);

    lblDept.Text = "Department Name = " + deptName;

LINQ to SQL Tutorial

No comments:

Post a Comment

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.