Part 6 Stored procedures with output parameters in LINQ to SQL

Показать описание

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

LINQ to SQL Tutorial - All Text Articles & Slides

LINQ to SQL Tutorial Playlist

Dot Net, SQL, Angular, JavaScript, jQuery and Bootstrap complete courses

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 1, @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.

Step 5 : Finally in the code-behind file, call GetEmployeesByDepartment() method using the DataContext class instance.
protected void btnGetEmployeesByDepartment_Click(object sender, EventArgs e)
{
using (SampleDataContext dbContext = new SampleDataContext())
{
string deptName = string.Empty;
GridView1.DataSource = dbContext.GetEmployeesByDepartment(1, ref deptName);
GridView1.DataBind();
lblDept.Text = "Department = " + deptName;
}
}