Here we will learn how to retrieve data in LINQ to SQL using stored procedure in c#, vb.net with example and how to use stored procedure with or without parameters in LINQ to SQL using c#, vb.net with example.
In LINQ to SQL we can use stored procedures with or without parameters to get 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 EmployeeDetail table.
Write stored procedure like as shown below and execute it 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 above stored procedure in 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 above example, we are using stored procedure “GetEmployeeDetails” with parameters in LINQ to SQL to get data from database.
Following is the result of LINQ to SQL with stored procedure to get data from database.
This is how we can use LINQ to SQL with stored procedures to get required data from database in c#, vb.net.