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

as

Begin

     Select @DepartmentName = Name

     from Departments where ID = @DepartmentId

     Select * from Employees

     where DepartmentId = @DepartmentId

End

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.


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
Text=""

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.


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);

    GridView1.DataBind();

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

}