LINQ to SQL Stored Procedure

  Total Views : 153
  Comments : 0
 
  Keywords : Linq to sql stored procedure with example in c# / vb.net, How to call stored procedure in linq to sql using c# / vb.net with example, Access stored procedure using linq to sql in asp.net

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.

LINQ to SQL Stored Procedure

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

AS

IF (@EmpId >0)

SELECT * FROM EmployeeDetails WHERE EmpId=@EmpId

ELSE

SELECT * FROM EmployeeDetails

Once we execute above stored procedure in 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 like as shown below.

 

After adding stored procedure to LINQ to SQL dbml file

 

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

 

<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 code behind file and write the code like 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

' LINQ to SQL Select Operation

Protected Sub BindGridview(ByVal id As Integer)

Dim result = db.GetEmployeeDetails(id)

gvDetails.DataSource = result

gvDetails.DataBind()

End Sub

End Class

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

Output of LINQ to SQL with Stored Procedure

Following is the result of LINQ to SQL with stored procedure to get data from 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 required data from database in c#, vb.net.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User