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.
Description:
In previous articles I explained asp.net call server side code function from javascript, asp.net mvc upload files to server, sql server get string before and after character, connection pooling in asp.net with example, consume web service in asp.net web application, Send html page as email body in asp.net using c#, vb.net and many articles relating to asp.net, c#, vb.net, JavaScript and jQuery. Now I will explain how to call stored procedure using LINQ to SQL in asp.net using c#, vb.net with example.
In previous articles I explained asp.net call server side code function from javascript, asp.net mvc upload files to server, sql server get string before and after character, connection pooling in asp.net with example, consume web service in asp.net web application, Send html page as email body in asp.net using c#, vb.net and many articles relating to asp.net, c#, vb.net, JavaScript and jQuery. Now I will explain how to call stored procedure using LINQ to SQL in asp.net using c#, vb.net with example.
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 Email
|
|||
3 comments :
Please provide more posts on MVC..
How can I write that result to XML file ?
but in my data tab not shown LinqtoSql class how to add that one please help me
Note: Only a member of this blog may post a comment.