Simple 6 steps to use stored procedure in LINQ

Introduction 

This is an extremely small article which describes how to flourish LINQ objects using 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 'DataContext' class and probably you would like to tweak and consider some options here. You can see more details of it 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 technology @ 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 basics of LINQ to SQL mapping you can read my article to understand the basic LINQ concepts from http://www.c-sharpcorner.com/UploadFile/shivprasadk/789789707022009112548AM/7897897.aspx?ArticleID=98ced0b9-c379-4ce2-92af-1a8b381876c4

Step 1:- Create a stored procedure

Below is the stored procedure which we will be used to flourish LINQ objects.

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

Step 2:- Create the 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 http://www.c-sharpcorner.com/UploadFile/shivprasadk/789789707022009112548AM/7897897.aspx?ArticleID=98ced0b9-c379-4ce2-92af-1a8b381876c4

   [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 DataContext class
 
In order to execute stored procedures LINQ has provided 'ExecuteMethod' call function which belongs to 'DataContext' class. This function returns '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 the 'ExecuteMethod' should be a part of our DAL.

As said the function is purely protected you can only invoke the same by inheritance and not aggregation. I am really not sure why this compulsion is put by Microsoft , so in other words we need to create one more extra class which inherits from 'DataContext' and then put in the corresponding function calls for stored procedures. So below is the code snippet where we have inherited from 'DataContext' class and created a new DAL class called as 'ClsMyContext'.

public class clsMyContext : DataContext { }

Step 4:- Attribute using Function attribute
 
We have created 'GetCustomerAll' function which is attributed with 'Function' attribute from '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 stored procedure or UDF i.e. User defined function. If 'IsComposable' is false that means it's a stored procedure and in case it is true that means it's a user defined function.

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

Step 5:- Invoke Executemethod call
 
Ok now it's time to fill in the empty function 'GetCustomerAll'. Below is the code snippet of how to execute the 'ExecuteMethod' call. This invocation returns back 'IExecuteResult' object.

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

The object returned from 'IExecuteResult' has 'ReturnValue' property from which we can get results collection of '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 client
 
So at the final step we just 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); 
        }

Source code

You can get the source code from top of this article