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 stored procedure first 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 don't worry check this link create and map database tables to LINQ to SQL file (.dbml).
Once our database ready with required tables now 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
IF (@EmpId >0)
SELECT * FROM EmployeeDetails WHERE EmpId=@EmpId
SELECT * FROM EmployeeDetails
Once we execute the above stored procedure in the database that will be like as shown below
Now drag and drop that stored procedure on the right-hand pane of the LINQ to SQL dbml class like as shown below.
Once we create and map required tables and stored procedures to .dbml file now we will show data in our application for that Right click on application → select Add → New Item → Select Web Form → Give name as Default.aspx and click OK button.
Now open Default.aspx page and write the code like as shown below
<head id="Head1" runat="server">
<title>LINQ to SQL with Stored Procedure Example</title>
<form id="form1" runat="server">
<asp:GridView runat="server" ID="gvDetails"></asp:GridView>
Now open code behind file and write the code like as shown below
public partial class _Default : System.Web.UI.Page
EmployeeDBDataContext db = new EmployeeDBDataContext();
protected void Page_Load(object sender, EventArgs e)
// LINQ to SQL Select Operation
protected void BindGridview(int id)
gvDetails.DataSource = db.GetEmployeeDetails(id);
Public Class WebForm1
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
' LINQ to SQL Select Operation
Protected Sub BindGridview(ByVal id As Integer)
Dim result = db.GetEmployeeDetails(id)
gvDetails.DataSource = result
If you observe the above example, we are using stored procedure “GetEmployeeDetails” with parameters in LINQ to SQL to get data from the database.
Following is the result of LINQ to SQL with a stored procedure to get data from the database.
This is how we can use LINQ to SQL with stored procedures to get the required data from the database in c#, vb.net.