How to use Stored Procedure with DataContext in LINQ

Introduction

In this article I will show, how to use the Stored Procedure with DataContext in LINQ to retrieve records from database. The Stored Procedure is most supportive to data driven application to manage performance and keep secured data retrieval.

Implementation

We have a stored procedure like below , It is a very simple SP returning grades of the student. This stored procedure name is GetStudentGrade

USE [School]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetStudentGrades]
            @StudentID int
            AS
            SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
            WHERE StudentID = @StudentID

So to call this stored procedure, we need to create instance of DataContext class and call the stored procedure as normal function 

In intellisense stored procedure name can be seen.

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

            DataClasses1DataContext context = new DataClasses1DataContext();
            var result = context.GetStudentGrades(2);
            foreach (var r in result)
            {
                Console.WriteLine(r.Grade);
            }
            Console.ReadKey(true);
        }
    }
}

Output 

Stored Procedure with out parameter

Let us say we have one stored procedure which is returning count of students in output parameter.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetStudentCount     
    @StudentCount int OUTPUT
AS
BEGIN    
    SET NOCOUNT ON;   
    SELECT @StudentCount = COUNT(*) from dbo.Person 
END
GO

Now at using this stored procedure, you can see we need to pass ref variable to get the output 

And we can call this stored procedure like below, 

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            DataClasses1DataContext context = new DataClasses1DataContext();
            int? abc = 1;
            context.GetStudentCount(ref abc);
            Console.WriteLine(abc);
            Console.ReadKey(true);
        }
    }
}

Output 

That's all. hopes and help.thank you for reading.