How to use Stored Procedure with DataContext in LINQ

No.of Views5457
Bookmarked0 times
Downloads 
Votes0
By  Dhananjay Kumar   On  07 Jan 2011 07:01:51
Tag : LINQ , Utilities
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.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

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 

Image Loading

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 

Image Loading

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 

Image Loading

And we can call this stored procedure like below, 

Image Loading

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 

Image Loading

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

 
Sign Up to vote for this article
 
About Author
 
Dhananjay Kumar
Occupation-Software Engineer
Company-Infosys Technolgies,Pune
Member Type-Gold
Location-India
Joined date-20 Jul 2009
Home Page-http://dhananjaykumar.net/
Blog Page-http://dhananjaykumar.net/
Dhananjay Kumar is Microsoft MVP on connected system. He blogs at http://dhananjaykumar.net/ . You can follow him http://twitter.com/debugmode_/ and reach him at dhananjay.25july@gmail.com
 
 
Other popularSectionarticles
Comments
By:AlessandroDate Of Posted:3/10/2012 8:29:44 AM
Annotation
Pardon for my english but is not the correct way to use a data context. A data context contains resource that necessity to be deallocated. It is important to use correctly the method Dispose().
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top