Using Stored Procedure in LINQ

We have a strored procedure as 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 an instance of the 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 a 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 when 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