ASP.NET Stored Procedures

By: Rajesh P.S.

A stored procedure in database management systems is a collection of Transact-SQL statements that are compiled into a single execution plan. It provides a way to encapsulate and execute repetitive or complex tasks within the database.

Stored Procedures

By writing and saving a stored procedure in the database, you can simplify the execution of commonly used queries or operations. Instead of writing the same query multiple times, you can define a stored procedure once and then invoke it whenever needed. This not only improves code reusability but also enhances performance by reducing the need to compile and optimize the query each time it is executed. 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.

Intermediary between users and the underlying database tables

Stored procedures also offer security benefits by acting as an intermediary between users and the underlying database tables. Users can be granted access to execute specific stored procedures without requiring direct access to the underlying tables. This provides a layer of abstraction and allows for better control and management of database access.

In ADO.NET, the Command object provides various Execute methods that facilitate the execution of SQL queries and stored procedures. These methods allow you to perform SQL operations in a flexible and customizable manner. You can use parameters to pass values to the stored procedure and retrieve results as needed.

To call a stored procedure from ASP.NET , set the CommandType of the Command object to Stored Procedure.

command.CommandType = CommandType.StoredProcedure;

Some of the commonly used Execute methods in the Command object include ExecuteNonQuery, ExecuteScalar, and ExecuteReader. These methods enable you to execute SQL queries and stored procedures and retrieve the results in different ways, depending on your requirements.

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

Conclusion

The use of stored procedures and the Execute methods in ADO.NET can increase the scalability, security and the overall maintainability in the database. It offers the order of query processing and assists in the code reuse technique, therefore this leads to better and faster database operations within your applications.

Next > ASP.NET Procedure with Parameter

Related Topics

More Related Topics.....

ASP.NET Image to Database