CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ASP.Net Article » Using Stored Procedure with LINQDatasource Control in ASP.Net   You are not logged in.
Search
 

Technologies
 

Sponsors
 

CodeDigest Navigation
 

Technology News
 

Community News
No News Feeds available at this time.
 
Using Stored Procedure with LINQDatasource Control in ASP.Net

By Satheesh Babu
Posted On Oct 01,2009
Article Rating: (Login)
Be first to rate
this article.
No of Comments: 5
Print this article.
Category: ASP.Net

Subscribe to our feed!

Using Stored Procedure with LINQDataSource Control in ASP.Net

 

LINQ is a new feature released with .Netframework 3.5. LINQ stands for Language Integrated Query. It is a data querying methodology that provides querying capabilities to .Net languages which have similar syntax like SQL query. It has a set of querying operators that can be used to query in memory object collections, Sql database, XML, etc. The LINQ processing engine will then convert the LINQ query to native query specific to the database on execution. Since, the querying feature is integrated with the language; one can build an efficient query with the language of their choice. To support LINQ data operations, there is a new datasource control added to the existing datasource control set called LINQDataSource control.

 

Like any other datasource control, this control can be declaratively configured for all the database operations like select, insert, update and delete. LINQDataSource control will inturn works with LINQ to SQL or LINQ Entity class (O/R mapping) to complete the required operations. At times, it may be required to use stored procedures for the data operations for certain conditions with LINQDataSource control. It is possible to configure the LINQDataSource to use stored procedures instead of the default LINQ to do the database interactions through the LINQ to SQL classes. Moving forward, we will use stored procedures with LINQDataSource control in 3 different scenarios,

1.      Using a simple stored procedure with LINQDataSource control.

2.      Using stored procedure that returns multiple resultset.

3.      Custom paging through stored procedure in LINQDataSource Control.

 

To understand the topic, we will create a SQL express database in App_Data folder with Employee and Dept table.

 

Steps

1.      To start with, in Visual Studio create a new Asp.Net website project.

2.      Next, create a new SQL express database in App_Data Folder with Employee and Dept table.

2.      Design the LINQ to SQL objects from the above database objects. Once created, it will be like below. Refer here to know more about LINQ to SQL.

Refer the below figure.

LINQDataSource control with stored procedure

 

Using Stored procedures with LINQDataSource control

In this section, we will create a simple stored procedure that returns all employees from Employee table and consume it through LINQDataSource control.

 

To add a new Stored Procedure in SqlExpress database, right click the “Stored Procedure” folder in Server Explorer. Create the SP and save it.

 

Select the list of employees Using SP

CREATE PROCEDURE dbo.GetAllEmployees

       AS

       SELECT * FROM Employee

       RETURN

 

Consuming the SP

From the Server explorer, drag and drop the stored procedure to the right pane of your dbml designer.

This will create the below method in the designer file,

[Function(Name = "dbo.GetAllEmployees")]

    public ISingleResult<GetAllEmployeesResult> GetAllEmployees()

    {

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

        return ((ISingleResult<GetAllEmployeesResult>)(result.ReturnValue));

    }

 

How to call from LINQDataSource control?

The LINQDataSource control exposes many events before and after databinding which gives us the flexibility to do some business logic or other required operations during the databinding. The OnSelecting event is raised before every database operation which can be used in our case to call the SP and populate the result.

 

Hence, we need to call the above method from OnSelecting event of LINQDataSource control.

Refer the code below,

ASPX

    <asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSource1">

    </asp:GridView>

    <asp:LinqDataSource ID="LinqDataSource1" runat="server"

        ContextTypeName="DataClassesDataContext"

        onselecting="LinqDataSource1_Selecting">

    </asp:LinqDataSource>

 

CodeBehind

protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)

    {

        DataClassesDataContext context = new DataClassesDataContext();

        e.Result = context.GetAllEmployees();

    }

 

Execute the page and you can see the employee information populated on the GridView control.

There are chances where the SP may return multiple result sets. Next section, will help you to handle that.

 

Using Stored Procedure that returns multiple resultset

Consider the below SP that returns 2 table, Employee and Dept.

 

SP’s returning multiple Table/Results

ALTER PROCEDURE dbo.GetAllEmployeeWithDepts    

AS

              SELECT * FROM Employee

              SELECT * FROM Dept

       RETURN

 

From the Server explorer, drag and drop the stored procedure to the right pane of your dbml designer.

This will create the below method in the designer file similar to the previous section (Single resultset),

[Function(Name="dbo.GetAllEmployeeWithDepts")]

    public ISingleResult<GetAllEmployeeWithDeptsResult> GetAllEmployeeWithDepts()

   {

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

       return ((ISingleResult<GetAllEmployeeWithDeptsResult>)(result.ReturnValue));

    }

 

To make the above method to return both the table, we need to change the return type from ISingleResult<GetAllEmployeeWithDeptsResult> to IMultipleResults in System.Data.Linq namespace and ResultType attributes to specify the type of returned result sets.

 

The above method can be rewritten as,

[Function(Name = "dbo.GetAllEmployeeWithDepts")]

    [ResultType(typeof(Employee))]

    [ResultType(typeof(Dept))]

    public IMultipleResults GetAllEmployeeWithDepts()

    {

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

        return ((IMultipleResults)(result.ReturnValue));

    }

To call this stored procedure,

ASPX

<asp:GridView ID="GridView2" runat="server" DataSourceID="LinqDataSource2">

    </asp:GridView>

    <asp:LinqDataSource ID="LinqDataSource2" runat="server"   

        onselecting="LinqDataSource2_Selecting">

    </asp:LinqDataSource>

    <asp:GridView ID="GridView3" runat="server">

    </asp:GridView>

CodeBehind

protected void LinqDataSource2_Selecting(object sender, LinqDataSourceSelectEventArgs e)

    {

        DataClassesDataContext context = new DataClassesDataContext();

        IMultipleResults results = context.GetAllEmployeeWithDepts();

        e.Result = results.GetResult<Employee>().ToList();      

        GridView3.DataSource = results.GetResult<Dept>();

        GridView3.DataBind();

    }

 

Execute the page and you can see both the GridView populate with employee and department information.

 

With this knowledge, we will move forward and built a custom paging implementation using stored procedure with the LINQDataSource control, a practical usage of multiple resultset with LINQDataSource control.

 



ASP.Net Hosting

Recent Articles

Custom Paging through Stored Procedure in LINQDataSource Control

ALTER PROCEDURE dbo.GetEmployees

       (

              @PageIndex INT,

              @PageSize INT

       )

AS

BEGIN

       SELECT EmpId, EmpName, Age, DeptID

       FROM (

              SELECT EmpId, EmpName, Age, DeptID, ROW_NUMBER () OVER (

                     ORDER BY EmpId

              ) AS RowNumber

              FROM Employee       

       ) AS Results

       WHERE RowNumber BETWEEN @PageIndex + 1 AND @PageIndex + @PageSize

       SELECT COUNT(*) FROM Employee

END

 

Now, drag and drop the stored procedure to the right pane of your dbml designer from the server explorer.

Again, the method signature should be changed to return IMultipleResults(Refer the previous section).

 

The final code will be,

[Function(Name = "dbo.GetEmployees")]

    [ResultType(typeof(Employee))]

    [ResultType(typeof(int))]

    public IMultipleResults GetEmployees([Parameter(Name = "PageIndex", DbType = "Int")] System.Nullable<int> pageIndex, [Parameter(Name = "PageSize", DbType = "Int")] System.Nullable<int> pageSize)

    {

        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pageIndex, pageSize);

        return ((IMultipleResults)(result.ReturnValue));

    }

 

To use the SP, we need to call this method from OnSelecting event of the LINQDataSource Control.

 

Refer the code below,

ASPX

  <asp:GridView ID="GridView1" AllowPaging="true" PageSize="2" runat="server" DataSourceID="LinqDataSource1">

    </asp:GridView>

    <asp:LinqDataSource ID="LinqDataSource1" AutoPage="false" runat="server"

        onselecting="LinqDataSource1_Selecting">

    </asp:LinqDataSource>

CodeBehind

  protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)

    {

        DataClassesDataContext context = new DataClassesDataContext();

        IMultipleResults results = context.GetEmployees(e.Arguments.StartRowIndex, e.Arguments.MaximumRows);

        e.Result = results.GetResult<Employee>().ToList();

        e.Arguments.TotalRowCount = results.GetResult<int>().Single();

    }

 

Execute the page and you can see custom pagination in action.

 

Note

As a better practise, you can move the stored procedure implementation into a separate partial class DataClassesDataContext. This is because, whenever we do any change on dbml the LINQ to SQL classes are recreated, which means the changes we done will always be reverted back which will make us to repeat the code change again and again for the SP’s(For example, changing return type to IMultipleResults). Download the source attached in this article to understand it better.

 

Downloads

Download Source 

Conclusion

The introduction of LINQ gave us one another method for data accessing that simplified the database operation using.net language syntax. Also, the new LINQDataSource control further simplified the data operations and which also bought the flexibility to use SP’s at times when needed. Download the source attached with this article and see it in action.

Happy Coding!!!

 

Similar Articles
  • You can contribute to CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    variable name
    Hey that procedure's variable name should be RowIndex instead of PageIndex. I wasted a lot of time realizing that :)
    Superb!
    The paging solution should solve the error I was getting:

    " the query results cannot be enumerated more than once"

    Thanks for sharing this!!!
    mine requires table name
    It works for me without setting the tablename property in LINQDatasource control ...
    mine requires table name
    As you do above, I left out the TableName property from my LinqDataSource but received the error message "The TableName property of LinqDataSource '[source name]' must specify a table property or field on the data context type."
    Mr
    Excellent! Thank you for sharing the knowledge.