ASP.NET Procedure with Parameter
The ADO.NET data providers used to connect to a database, execute commands, and retrieve results. 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.
The main advantage of Stored Procedure is that the Sql Server compiles each stored procedure once and then we can reuse the execution plan again and again. In many cases stored procedures accept input parameters and return multiple values . Parameter values can be supplied if a stored procedure is written to accept them. A sample stored procedure with accepting input parameter is given below :
CREATE PROCEDURE SPCITY
@CITY VARCHAR(20)
AS
SELECT AU_LNAME FROM AUTHORS WHERE CITY = @CITY
GO
The above stored procedure is accepting a city name (@CITY VARCHAR(20)) as parameter and return all the authors from the input city. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.
VB.Net
command.CommandType = CommandType.StoredProcedure
param = New SqlParameter("@CITY", "Berkeley")
param.Direction = ParameterDirection.Input
param.DbType = DbType.String
command.Parameters.Add(param)
C#
command.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@CITY", "Berkeley");
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);
The following ASP.NET program call a procedure and display all authors from Berkeley city.
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();
SqlParameter param;
try
{
connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SPCITY";
param = new SqlParameter("@CITY", "Berkeley");
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);
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 param As SqlParameter
Dim ds As New DataSet
Try
connection.Open()
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.CommandText = "SPCITY"
param = New SqlParameter("@CITY", "Berkeley")
param.Direction = ParameterDirection.Input
param.DbType = DbType.String
command.Parameters.Add(param)
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