Improving Performance using Stored Procedure

text zoom

Previous: Performing Batch Updates: Using SqlCommandBuilder Filtering and Sorting Data Using DataView/DataTable :Next

Database performance can be improved with the help of stored procedures. Stored procedures in SQL Server are similar to procedures in other programming languages in which they can:

Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

Contain programming statements that perform operations in the database, including calling other procedures.

Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure)

It is always a best practice to use SP for the following reasons:

Stored procedures allow you to prepare queries ahead of time and execute compiled versions of them at run-time resulting in increased performance;

This also helps in separating web content and presentation-layer code from database-layer code.

Stored Procedure can be used to shield your asp.net pages from the particular implementation of the tables in your database. If you make changes in the database tables that are used in your application, you can change your stored procedure without making any changes to your asp.net pages.

The following are the examples of the stored procedures:-

[1] Fetching Records from Database

CREATE PROCEDURE sp_FetchCategories

AS

Select * from categories;

GO

[2] Inserting Records in Database

CREATE PROCEDURE sp_InsertCategories

(

@CategoryName varchar (20),

@Description varchar (20)

)

AS

Insert into categories (CategoryName, Description) values (@CategoryName, @Description)

GO

[3] Updating Record in Database

CREATE PROCEDURE sp_UpdateCategories

(

@CategoryID int

)

AS

Update categories

Set CategoryName='Updated', Description='Updated' where CategoryID = @CategoryID

GO

[4] Deleting Record from Database

CREATE PROCEDURE sp_DeleteCategories

(

@CategoryID int

)

AS

Delete from categories where categoryID=@CategoryID;

GO

ImplementingStoredProcedure.aspx

<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="ImplementingStoredProcedure.aspx.cs" Inherits ="ImplementingStoredProcedure" %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head >

<body >

<div > I mproving Performance: Using Stored Procedure

Fetching Records from Database

<asp:GridView ID ="grdvCategories" runat ="server" AutoGenerateColumns ="False" BackColor ="#DEBA84" BorderColor ="#DEBA84" BorderStyle ="None" BorderWidth ="1px" CellPadding ="3" CellSpacing ="2">

<asp:BoundField DataField ="CategoryId" HeaderText ="CategoryId" />

<asp:BoundField DataField ="CategoryName" HeaderText ="CategoryName" />

<asp:BoundField DataField ="Description" HeaderText ="Description" />

</Columns>

</asp:GridView>

<asp:Button ID ="btnFetch" runat ="server" OnClick ="btnFetch_Click" Text ="Fetching Records" />

Inserting Record in the Database Using StoredProcedure

<asp:Label ID ="Label1" runat ="server" Text ="CategoryName"></ asp : Label >

< asp : TextBox ID ="txtCategoryName" runat ="server"></ asp : TextBox >

<asp:Label ID ="Label2" runat ="server" Text ="Description"></ asp : Label >

<asp:TextBox ID ="txtDescription" runat ="server"></ asp : TextBox >

<asp:Button ID ="btnInsert" runat ="server" OnClick ="btnInsert_Click" Text ="Inserting Records" />

Updating and Deleting the Value in Database

Select CategoryId

<asp:DropDownList ID="ddlCategories" runat ="server" AutoPostBack ="True">

</asp:DropDownList>

<asp:Button ID ="btnUpdate" runat ="server" OnClick ="btnUpdate_Click" Text="Updating Records" />

<asp:Button ID ="btnDelete" runat ="server" OnClick ="btnDelete_Click" Text="Deleting Records" />

</div>

</form >

</body >

</html >

ImplementingStoredProcedure.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class ImplementingStoredProcedure : System.Web.UI. Page

{

protected void Page_Load( object sender, EventArgs e)

{ //Bind Categories Drop Down List

if (!IsPostBack) {

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test" ;

string sqlQuery = "select * from Categories" ;

SqlConnection conn = new SqlConnection (strConn);

SqlDataAdapter ada = new SqlDataAdapter (sqlQuery, conn);

DataTable dtCategories = new DataTable ();

ada.Fill(dtCategories);

//DropDown List

ddlCategories.DataSource = dtCategories.DefaultView;

ddlCategories.DataTextField = "CategoryName" ;

ddlCategories.DataValueField = "CategoryID" ;

ddlCategories.DataBind();

}

protected void btnFetch_Click( object sender, EventArgs e)

{ //Fetching Records from Database

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;

SqlConnection conn = new SqlConnection (strConn);

//Create Data Adapter Instance

SqlDataAdapter ada = new SqlDataAdapter ( "sp_FetchCategories" , conn);

//Create Data Table Instance

DataTable dtCategories = new DataTable ();

ada.Fill(dtCategories);

//GridView

grdvCategories.DataSource = dtCategories.DefaultView;

grdvCategories.DataBind();

}

protected void btnInsert_Click( object sender, EventArgs e)

{ //Inserting Records into Database

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;

SqlConnection conn = new SqlConnection (strConn);

//Command Object

SqlCommand cmd = new SqlCommand ();

cmd.Connection = conn;

cmd.CommandType = CommandType .StoredProcedure;

cmd.CommandText = "sp_InsertCategories" ;

cmd.Parameters.AddWithValue( "@CategoryName" , txtCategoryName.Text);

cmd.Parameters.AddWithValue( "@Description" ,txtDescription.Text);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

Response.Write( "Data Inserted Successfully !!!!" );

}

protected void btnUpdate_Click( object sender, EventArgs e)

{ //Updating Record from Database

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test" ;

SqlConnection conn = new SqlConnection (strConn);

//Command Object

SqlCommand cmd = new SqlCommand ();

cmd.Connection = conn;

cmd.CommandType = CommandType .StoredProcedure;

cmd.CommandText = "sp_UpdateCategories" ;

cmd.Parameters.AddWithValue( "@CategoryId" ,ddlCategories.SelectedValue);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

Response.Write( "Data Updated Successfully !!!!" );

}

protected void btnDelete_Click( object sender, EventArgs e)

{ //Deleting Record from Database

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;

SqlConnection conn = new SqlConnection (strConn);

//Command Object

SqlCommand cmd = new SqlCommand ();

cmd.Connection = conn;

cmd.CommandType = CommandType .StoredProcedure;

cmd.CommandText = "sp_DeleteCategories" ;

cmd.Parameters.AddWithValue( "@CategoryId" ,

ddlCategories.SelectedValue);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

Response.Write( "Data Deleted Successfully !!!!" );

}

Output:

Share This ...

Previous: Performing Batch Updates: Using SqlCommandBuilder Filtering and Sorting Data Using DataView/DataTable:Next

Give feedback and win a prize.

Aspnet Discussion

...more

Aspnet Source Code

...more

Aspnet Related Tutorials

- ASP Tutorial
- ASP.NET Tutorial

...more

New Aspnet Resources

...more

>>Earn money through freelance projects!

Database Tutorial Home

Database Home

ASP.NET Resources

Community

Site

Improving Performance using Stored Procedure

Aspnet Discussion

Aspnet Source Code

Aspnet Related Tutorials

New Aspnet Resources

Member Panel