Call Stored Procedure in LINQ using Asp.net in C# and VB.Net with Example

In this tutorial i am going to explain how to create or call stored procedure in LINQ to SQL using asp.net in c# and vb.net with simple example.

Now before call stored procedure using LINQ to SQL in asp.net we need to create one new datatable “studentdetail” with some data and also create stored procedure to get data from “studentdetail” table.

Now Follow this syntax to create datatable with some data.

Our datatable name is studentdetail

studentname varchar(50),
branch varchar(50),
city varchar(50)
)
insert into studentdetail values('Nikunj Satasiya','Surat','B.Tech-CE')
insert into studentdetail values('Hiren Dobariya','Rajkot','B.Tech-CE')
insert into studentdetail values('Vera Proreto','UK','MCA')
insert into studentdetail values('Sarah Demola','US','M.Tech-CE')

Now follow this syntax to create stored procedure “GetstudentDetail” to get data from “studentdetail” table.

CREATE PROCEDURE GetStudentDetail
AS
BEGIN
SELECT * FROM studentdetail
END

After execute above queries in database now we will create new web application or project.
Follow some simple steps first Open visual studio -->> Go to File -->> New -->> Project --> Asp.net Empty Web Application --> Give name “DEMO” and Click OK.

Now Press right click on your asp.net project from solution exploler and select Add -->> New item -->> LINQ to SQL Classes file under the Data sections -->> Give name as “StudentDetail.dbml” and Click OK.(You can give any name which you want).
Note: File extension is must be .dbml

after add LINQ to SQL “StudentDetail.dbml” file in your application that will be look like as shown in this image.

Now after all those process we will connect our database in server explorer.
open server explorer -->> Right click on Data Connections -->> Add Connection

After click on Add Connection a new data source window will open.
From that select “Microsoft SQL Server” and click Continue.

Now after click on Continue button new popup window will open.
From that add database connection server by entering all required fields.

Once entering all the required database connection details click on the Test Connection button.
To know whether your connection is success or not.

If connected database is fine it mean click OK to add database to server explorer.
Now drag and drop “studentdetail” table and stored procedure “GetStudentDetails” to LINQ to SQL dbml
Once the completion of adding stored procedure and table to LINQ to SQL file open the Default.aspx page and write simple code.

HTML Source Code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>LINQ to SQL Example xin ASP.Net</title>
</head>
<body>
    <form id="form1" runat="server">
        
        <asp:gridview height="224px" id="GridView1" runat="server" width="831px">
            <headerstyle backcolor="#0066FF">
        </headerstyle></asp:gridview>
    </form>
</body>
</html>

C# Code:

using System;
using System.Data;
 
namespace LINQtoSQL
{
 public partial class _Default : System.Web.UI.Page
 {
  protected void Page_Load(object sender, EventArgs e)
 {
 if (!IsPostBack) 
 {
  BindGridData();
 }
}
 protected void BindGridData() 
 {
  StudentDetailDataContext stu = new UserDetailsDataContext();
  Gridview1.DataSource = stu.GetUserDetails();
  Gridview1.DataBind();
 }
}

VB.Net Code:

Partial Class VBCode
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindGridData()
        End If
    End Sub
 
    Protected Sub BindGridData()
 
        Dim stu As New UserDetailsDataContext()
        Gridview1.DataSource = userctx.GetUserDetails()
        Gridview1.DataBind()
    End Sub
End Class

DEMO:

About Nikunj Satasiya

I am a passionate programmer and a Software Engineer who love developing applications in Microsoft Technologies, blogging and other related technologies like Asp.Net, VB.Net, SEO, Sql Server,My SQL,Asp.Net-MVC, LINQ, C#.Net, Stored Procedures, Ajax, JavaScript, JQuery, xml etc.

Project Plazza