We have a strored 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

clip_image002

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

clip_image004

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

clip_image005

And we can call this stored procedure like below

clip_image006

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

clip_image008

Advertisements

One thought on “Using Stored Procedure in LINQ

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s