In Focus

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 .


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.

1.gif

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

2.gif

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

3.gif

And we can call this stored procedure like below

4.gif

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

5.gif