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
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
[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;
}
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