Simple 6 steps to use stored procedure in LINQ

Simple 6 steps to use stored procedure in LINQ

Introduction

LINQ basics

Step 1:- Create a stored procedure

Step 2:- Create the LINQ Entity

Step 3 :- Inherit from DataContext class

Step 4:- Attribute using Function attribute

Step 5:- Invoke Executemethod call

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

Source code

 

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.dotnetfunda.com/articles/article446-onemany-and-oneone-relationship-using-linq-to-sql.aspx  
 

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.dotnetfunda.com/articles/article446-onemany-and-oneone-relationship-using-linq-to-sql.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 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