Six simple steps to use a Stored Procedure in LINQ

• Download source code - 1.06 MB

Table of contents

• Introduction
• LINQ basics
• Step 1: Create a Stored Procedure
• Step 2: Create a LINQ Entity
• Step 3: Inherit from the DataContext class
• Step 4: Attribute using Function attribute
• Step 5: Invoke the Executemethod call
• Step 6: Finally we call the data context in the client

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.

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

For further reading do watch the below interview preparation videos and step by step video series.

• ASP.NET MVC Interview Questions & Answers
• C# Interview Questions with Answers
• Angular Interview Questions with Answers
• Learn Azure step by step.
• Learn MVC 5 Step by Step
• MVC Core Step by Step Tutorial
• SQL Server step by step
• Session & Viewstate in ASP.NET.