Click here to Skip to main content
Click here to Skip to main content

Six simple steps to use a Stored Procedure in LINQ

, 7 Jul 2009
Rate this:
Please Sign up or sign in to vote.
Six simple steps to use a Stored Procedure in LINQ.

Table of contents

Introduction

This is an extremely small article which describes how to flourish LINQ objects using a Stored Procedure. What provoked me to write this article is the ExecuteMethodCall function which helps to execute Stored Procedures in LINQ. As this is a protected function, it changes the way you architect the DAL using the DataContext class and probably you would like to tweak and consider some options here. You will see more details when you read through the steps below. I am writing a huge series of LINQ FAQ and these small articles form small sprints to complete the huge FAQ series. I hope you enjoy it.

Watch my 200 videos on Microsoft technologies @ http://www.questpond.com.

LINQ basics

This article assumes that you have a basic knowledge of how entity objects can be flourished using LINQ. In case you are not aware of the basics of LINQ to SQL mapping, you can read my article to understand the basic LINQ concepts from OneManyandOneOneLINQ.aspx.

Step 1: Create a Stored Procedure

Below is the Stored Procedure which we will use to flourish LINQ objects:

Create PROCEDURE dbo.usp_SelectCustomer
AS
Select CustomerId,CustomerCode,CustomerName from Customer
RETURN

Step 2: Create a LINQ Entity

The above Stored Procedure returns CustomerId, CustomerCode, and CustomerName, so we need to prepare a LINQ entity as per the returning Stored Procedure data. In case you are not aware of LINQ entities, please read the basics at OneManyandOneOneLINQ.aspx.

[Table(Name = "Customer")]
public class clsCustomerEntity
{
    private int _CustomerId;
    private string _CustomerCode;
    private string _CustomerName;

    [Column(DbType = "nvarchar(50)")]
    public string CustomerCode
    {
        set
        {
            _CustomerCode = value;
        }
        get
        {
            return _CustomerCode;
        }
    }

    [Column(DbType = "nvarchar(50)")]
    public string CustomerName
    {
        set
        {
            _CustomerName = value;
        }
        get
        {
            return _CustomerName;
        }
    }

    [Column(DbType = "int", IsPrimaryKey = true)]
    public int CustomerId
    {
        set
        {
            _CustomerId = value;
        }
        get
        {
            return _CustomerId;
        }
    }
}

Step 3: Inherit from the DataContext class

In order to execute Stored Procedures, LINQ has provided an ExecuteMethod call function which belongs to the DataContext class. This function returns an ISingleresult of an entity collection. The ExecuteMethod call function is a protected function and can only be invoked through inheritance. Methods and functions from which we call our Stored Procedures normally forms our DAL. In other words, ExecuteMethod should be a part of our DAL.

As said, the function is purely protected, you can only invoke it by inheritance and not aggregation. I am really not sure why this compulsion is put by Microsoft, in other words, we need to create an extra class which inherits from DataContext and then put in the corresponding function calls for Stored Procedures. Below is the code snippet where we inherit from the DataContext class and create a new DAL class called ClsMyContext.

public class clsMyContext : DataContext
{}

Step 4: Attribute using the Function attribute

We have created the GetCustomerAll function which is attributed with the Function attribute from the System.Data.Linq.Mapping namespace. The Function attribute has a name parameter which specifies the Stored Procedure name; currently the Stored Procedure is usp_SelectCustomer, as defined in the previous steps.

The IsComposable parameter defines whether this method call is for a Stored Procedure or UDF, i.e., User Defined Function. If IsComposable is false, that means it’s a Stored Procedure, and if it is true, that means it’s a user defined function.

[Function(Name = "usp_SelectCustomer", IsComposable = false)]
public ISingleResult<clsCustomerEntity> getCustomerAll()
{
}

Step 5: Invoke the ExecuteMethod call

Now it’s time to fill in the empty function GetCustomerAll. Below is the code snippet to execute the ExecuteMethod call. This invocation returns back an IExecuteResult object.

IExecuteResult objResult = this.ExecuteMethodCall(this,(MethodInfo)(MethodInfo.GetCurrentMethod()));

The object returned from IExecuteResult has a ReturnValue property from which we can get the results collection of the ClsCustomerEntity type.

ISingleResult<clsCustomerEntity> objresults = (ISingleResult<clsCustomerEntity>) objResult.ReturnValue;

Below is the complete code snippet with the function:

[Function(Name = "usp_SelectCustomer", IsComposable = false)]
public ISingleResult<clsCustomerEntity> getCustomerAll()
{
    IExecuteResult objResult = 
      this.ExecuteMethodCall(this,(MethodInfo)(MethodInfo.GetCurrentMethod()));

    ISingleResult<clsCustomerEntity> objresults = 
        (ISingleResult<clsCustomerEntity>) objResult.ReturnValue;
    return objresults;
}

Step 6: Finally we call the data context in the client

In the final step, we create the context object, call our function, and loop through the object collection display data.

clsMyContext objContext = new clsMyContext(strConnectionString);
foreach(var row in objContext.getCustomerAll())
{
    Response.Write(row.CustomerCode);
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Shivprasad koirala
Architect http://www.questpond.com
India India

I am a Microsoft MVP for ASP/ASP.NET and currently a CEO of a small
E-learning company in India. We are very much active in making training videos ,
writing books and corporate trainings. Do visit my site for 
.NET, C# , design pattern , WCF , Silverlight
, LINQ , ASP.NET , ADO.NET , Sharepoint , UML , SQL Server  training 
and Interview questions and answers


Comments and Discussions

 
GeneralGood work PinprofessionalMenon Santosh6-Dec-13 19:17 
QuestionDataContext being Open/Closed PinmemberMember 1041029618-Nov-13 21:16 
QuestionPassing Parameters? Pinmemberoalbad13-Jan-12 8:19 
GeneralMy vote of 3 Pinmemberprabhatmishra3-Jan-11 22:45 
QuestionMaster - Detail Entries using Stored Procedures Pinmemberravi@hitsoft26-Aug-10 21:00 
QuestionUsing stored procedure in LINQ PinmemberTapas Ranjan Singh14-Sep-09 19:45 
GeneralThank you PinmemberEricFromMars14-Jul-09 22:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140705.1 | Last Updated 8 Jul 2009
Article Copyright 2009 by Shivprasad koirala
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid