LINQ to SQL - call stored procedure with OUTPUT parameters

LINQ to SQL - call stored procedure with OUTPUT parameters

Before writing a LINQ to SQL query; make sure that the DataContext is created, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is in place we can go ahead and use it to process data from the mapped database.

Assume that we have the following StoredProcedure [GetDepartmentName], in the database, which takes the DepartmentID as input and gives the DepartmentName as the output.

CREATE PROCEDURE [dbo].[GetDepartmentName]

      @DepartmentID int,

      @DepartmentName varchar(50) output

AS

BEGIN

      SELECT @DepartmentName = Name From Department 

      WHERE ID = @DepartmentID

END

Before using LINQ-to-SQL to call the StoredProcedure we need to add the StoredProcedure to the right side panel of the DataContext (.dbml) file.

Open the server Explorer and drag the procedure to the right side panel of the .dbml file, your panel should look as follows.

Once this is done we are ready to call the StoredProcedure from the code, the code is as follows.

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

string strDepartmentName = string.Empty;

dbContext.GetDepartmentName(1, ref strDepartmentName);

Response.Write("Department Name: " + strDepartmentName);

That’s it, we have called a Stored procedure with an OUTPUT parameter using LINQ-to-SQL.