How can we execute stored procedures using LINQ?

 Posted by ArticlesMaint on 9/29/2009 | Category: LINQ Interview questions | Views: 11750


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. 
 

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


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response