The stored procedures are complied code and they will improve the performance of the Web Site to a large extent.The following steps shuld be performed if you want to call the stored procedure from the ASP.Net application.

1. Create a SQL stored procedure that contains the statement that we want to execute.


2. Import the System.Data namespace.


3. Pass the name of the stored procedure to the instance of the command class.


4. Set the CommandType property of the SqlCommand to the value CommandType.StoredProcedure.

Example for calling Stored Procedure

Note:- When a user clicks on the Button, the stored procedure will fetch the data from the database

StoredProcedure.aspx

<% @ Page Language ="VB" AutoEventWireup ="false" CodeFile ="Example7.aspx.vb" Inherits ="Example7" %>

<! 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 runat ="server">

</ head >

< body >

< form id ="form1" runat ="server">

< div >

< asp : Button ID ="btnFire" runat ="server" Text ="Fire StoredProcedure!!!" />

< asp : Label ID ="Label1" runat ="server" Text ="Improving Performance Through Stored Procedure" Width ="318px">

</ asp : Label >

</div>

</ form >

</ body >

</ html >

StoredProcedure: spGetNames

Create procedure spGetNames

as

select * from tblEmp

GO

StoredProcedure.aspx.vb

Imports System

Imports System.Data

Imports System.Data.SqlClient

Partial Class Example7 Inherits System.Web.UI.Page

Protected Sub btnFire_Click( ByVal sender As Object , ByVal e As System.EventArgs) Handles btnFire.Click

Dim con As SqlConnection

Dim cmd As SqlCommand

Dim dtr As SqlDataReader

con = New SqlConnection( "Data Source=localhost;Initial catalog=TestDB;UID=sa;pwd=sa" )

cmd = New SqlCommand()

cmd.Connection = con

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "spGetNames"

con.Open()

dtr = cmd.ExecuteReader

Response.Write( "<B><U>Name</U></B>" )

While dtr.Read

Response.Write(dtr.GetString(1))

Response.Write( "<br />" )

End While

con.Close()

End Sub

End Class