Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

LINQ to SQL Call Stored Procedure in ASP.Net using C#, VB.Net

Aug 7, 2016
Introduction

Here I will explain how to call stored procedure in LINQ to SQL using
asp.net in c#, vb.net with example or LINQ to SQL use stored procedure in asp.net using c#, vb.net or how to call stored procedure in LINQ using asp.net in c#, vb.net with example. By using LINQ to SQL (dbml) file in our asp.net application we can call stored procedure easily in c#, vb.net.


Before we proceed to call stored procedure using LINQ to SQL in asp.net applications we need to create one new table “userdetails” with data and stored procedure to get data from “userdetails” table.

Following is the script to create new table “userdetails” with data.


create table userdetails(userid int identity,
username varchar(50),
location varchar(50),
education varchar(50)
)

insert into userdetails values('Suresh Dasari','Chennai','B.Tech')
insert into userdetails values('Rohini Alavala','Chennai','Msc')
insert into userdetails values('Praveen Alavala','Guntur','B.Tech')
insert into userdetails values('Sateesh Chandra','Vizag','MS')

Following is the stored procedure “GetUserDetails” to get data from “userdetails” table.


CREATE PROCEDURE GetUserDetails
AS
BEGIN
SELECT * FROM userdetails
END

Once we execute above queries in database now create new web application project for that Open visual studio --> Go to File --> Select New --> Select Project --> Select Asp.net Empty Web Application --> Give name “LINQtoSQLApplication” and Click OK.

Now right click on your asp.net project select Add --> Select New item --> Select LINQ to SQL Classes file under Data sections --> Give name as “UserDetails” and Click OK like as shown below

Once we add LINQ to SQL “UserDetails.dbml” file in application that will be like as shown below

Now we will connect our database in server explorer for that open server explorer --> Right click on Data Connections --> Select Add Connection like as shown below.

Once we click on Add Connection a new data source window will open in that select “Microsoft SQL Server” and click Continue like as shown below.

Once we click Continue new popup will open in that add database connection server by entering all required fields like as shown below.

After entering all the database connection details click on Test Connection button to know whether your connection success or not like as shown below.

If connected database is fine means, click OK to add database to server explorer. After adding database to server explorer that will be like as shown below.

Now drag and drop “userdetails” table and stored procedure “GetUserDetails” to LINQ to SQL dbml like as shown below.

After completion of adding stored procedure and table to LINQ to SQL file 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>Bind Gridview using LINQ to SQL in ASP.Net</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails">
<HeaderStyle CssClass="headerstyle" />
</asp:GridView>
</div>
</form>
</body>
</html>

Now open code behind file and write following code

C# Code


using System;

namespace LINQtoSQLApplication
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
BindGridData();
}
}
protected void BindGridData() {
UserDetailsDataContext userctx = new UserDetailsDataContext();
gvDetails.DataSource = userctx.GetUserDetails();
gvDetails.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 userctx As New UserDetailsDataContext()
gvDetails.DataSource = userctx.GetUserDetails()
gvDetails.DataBind()
End Sub
End Class

Demo

Following is the result of LINQ to SQL example.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

3 comments :

sarath said...

Please provide more posts on MVC..

Unknown said...

How can I write that result to XML file ?

Unknown said...

but in my data tab not shown LinqtoSql class how to add that one please help me

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.