LINQ to SQL Stored Procedure

In LINQ to SQL, we can use stored procedures with or without parameters to get the required data from database tables.

 

Before we start using LINQ to SQL with a stored procedure, we need to create a database with required tables and map those tables to LINQ to SQL file (.dbml). If you don't know the process check this link create and map database tables to LINQ to SQL file (.dbml).

 

Once our database is ready with the required tables, we will create a simple stored procedure to get employee details from the EmployeeDetail table.

 

Write stored procedures like as shown below and execute them in your database.

 

CREATE PROCEDURE [dbo].[GetEmployeeDetails]
@EmpId int = 0
AS
IF (@EmpId >0)
SELECT * FROM EmployeeDetails WHERE EmpId=@EmpId
ELSE
SELECT * FROM EmployeeDetails

Once we execute the above stored procedure in the database, that will be like as shown below.

 

LINQ to SQL adding store procedure to database in c#, vb.net

 

Now drag and drop that stored procedure on the right-hand pane of the LINQ to SQL dbml class, as shown below.

 

After adding stored procedure to LINQ to SQL dbml file

 

Once we create and map required tables and stored procedures to the .dbml file now, we will show data in our application for that Right click on the application à select Add à New Item à Select Web Form à Give name as Default.aspx and click OK button.

 

Now open the Default.aspx page and write the code as shown below.

 

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>LINQ to SQL with Stored Procedure Example</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView runat="server" ID="gvDetails"></asp:GridView>
</div>
</form>
</body>
</html>

Now open the code behind file and write the code as shown below.

 

C# Code

 

using System;
using System.Web.UI;

namespace LINQtoSQL
{
  public partial class _Default : System.Web.UI.Page
  {
    EmployeeDBDataContext db = new EmployeeDBDataContext();
    protected void Page_Load(object sender, EventArgs e)
    {
      if (!Page.IsPostBack)
      {
        BindGridview(0);
      }
    }
    // LINQ to SQL Select Operation
    protected void BindGridview(int id)
    {
      gvDetails.DataSource = db.GetEmployeeDetails(id);
      gvDetails.DataBind();
    }
  }
}

VB.NET Code

 

Public Class WebForm1
Inherits System.Web.UI.Page
Private db As New EmployeeDBDataContext()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGridview(0)
End If
End Sub
Protected Sub BindGridview(ByVal id As Integer)
Dim result = db.GetEmployeeDetails(id)
gvDetails.DataSource = result
gvDetails.DataBind()
End Sub
End Class

If you observe the above example, we are using stored procedure “GetEmployeeDetails” with parameters in LINQ to SQL to get data from the database.

Result of LINQ to SQL with Stored Procedure

Following is the result of LINQ to SQL with a stored procedure to get data from the database.

 

LINQ to SQL Stored Procedure returned result example in asp.net

 

This is how we can use LINQ to SQL with stored procedures to get the required data from the database in c#, vb.net.