ASP.NET Procedure with Parameter
ADO.NET data providers serve as the bridge between applications and databases, enabling connectivity, command execution, and result retrieval. The Command object in ADO.NET plays a crucial role by offering a range of Execute methods to perform SQL queries in various ways.
Stored procedures
One notable advantage of using stored procedures is that SQL Server compiles them once, resulting in improved performance. The execution plan generated during the initial compilation is reused for subsequent invocations, eliminating the need for repetitive compilation. This can significantly enhance the efficiency of executing common or complex tasks within the database. 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.
Stored procedures often accept input parameters, allowing for flexibility and customization. By defining parameters in the stored procedure, values can be supplied when executing it, enabling dynamic and parameterized queries. This empowers developers to create reusable and adaptable code that can be tailored to specific scenarios or conditions.
Moreover, stored procedures have the capability to return multiple values. In addition to performing data retrieval, they can produce result sets or output parameters containing various pieces of information. This flexibility enables stored procedures to act as powerful tools for data manipulation and extraction.
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);
Stored procedures could potentially be combined with Command methods like Execute that are provided by the ADO.NET object. This will enhance the efficacy of executing queries, parameter passing and handling of the results. It does so by the generation of adaptable, reusable, and feature-rich code that seamlessly interacts with the database and boosts performance.
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
Conclusion
It is worth noting that the usage of stored procedures should be balanced with considerations such as security, maintenance, and readability. While they offer advantages in terms of performance and flexibility, it is important to carefully design and manage stored procedures to ensure optimal database interactions and maintainable codebases.