ASP.NET Stored Procedures
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. You can write stored procedure and save it into the database. If you are in a situation to using the same query over and over again, it is better to create a stored procedure instead. A sample Stored Procedure is given below :
CREATE PROCEDURE SPAUTHORS
AS
SELECT AU_LNAME FROM AUTHORS
GO
The above code create a procedure named as 'SPAUTHORS' and it execute SQL statement that select all authors last name from authors table from the PUB database. Coding business logic into a single stored procedure offers a single point of control for ensuring that business rules are correctly enforced and improve performance.
Stored procedures can also shield users from needing to know the details of the tables in the database, users unable to access the tables directly and they can just execute the stored procedures only. The command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.
To call a stored procedure from ASP.NET , set the CommandType of the Command object to Stored Procedure.
command.CommandType = CommandType.StoredProcedure;
From the following source code you can see how to call a stored procedure from an ASP.NET application.
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
<br />
<asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Full Source | C#
using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SPAUTHORS";
adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
connection.Close();
ListBox1.DataSource = ds.Tables[0];
ListBox1.DataTextField = "au_lname";
ListBox1.DataBind();
}
catch (Exception ex)
{
Label1.Text = "Error in execution " + ex.ToString();
}
}
}
Full Source | VB.NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString As String
Dim connection As SqlConnection
connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
Dim adapter As SqlDataAdapter
Dim command As New SqlCommand
Dim ds As New DataSet
Try
connection.Open()
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.CommandText = "SPAUTHORS"
adapter = New SqlDataAdapter(command)
adapter.Fill(ds)
connection.Close()
ListBox1.DataSource = ds.Tables(0)
ListBox1.DataTextField = "au_lname"
ListBox1.DataBind()
Catch ex As Exception
Label1.Text = "Error in execution " & ex.ToString
End Try
End Sub
End Class
Click the following links to see full source code
C# Source Code
VB.NET Source Code