1. Onlinebuff
2. DOT.NET Interview Questions
3. Step by Step to use Stored Procedures in Asp.Net C# with example

Use of Stored procedure in asp.net c# with example

• By Gurunatha Dogi in DOT.NET
• Jul 1st, 2014
• 124537
• 13

Hey Friends, recently one of our valuable @onlinebuff.com friend/visitor mailed me and asked me a question how to implement Stored Procedure in ASP.NET C#. He is a fresher in (Dot.NET) resides at Manhattan, New York, United States of America. So i thought to help him out by writing a blog post on stored procedures using asp.net c# example.

If you guys have any doubt or want me to explain any topic via my blog post kindly feel free to drop me a question via e-mail or comment box below. I would be grateful to write an article on your questions. The more questions you drop the more knowledge we can share on our @onlinebuff.com.

What are Stored Procedures?

In DBMS (database management system), a stored procedure is a set of Structured Query Language i.e. (SQL). They are stored in database server (SQL Server). Stored procedure is a group of T-SQL statements which performs one or more specific task in a single execution plan. We can create group of Transact-SQL statements and Store in SP. There are 3 different types of SP's (System SP's, User Defined SP's and Extended SP's).

Note : In DBMS Stored Procedure is denoted by SP.

System SP's : It is stored in master database (pre-fixed "sp_" and usually perform the task for sql server functions.

User Defined SP's : These custom SP's are created by user for implementing certain task in their applications (like INSERT, UPDATE, DELETE and SELECT).

Extended SP's : Depreciated (No longer in USE).

Types of Parameters available in Stored Procedures

Input Parameter : We can pass any number of input parameters to SP function.

Output Parameter : We can output any number of output parameters from SP function.

Return Parameter : But we can return only one/single return parameter from SP function.

Now let's understand what are the advantages of using SP in asp.net c# code.

Advantages of using Stored Procedures in ASP.NET

Stored procedures are special objects available in database server and they are very fast when compared to ordinary t-sql statements and thus helps to improve performance both sql and application.

Stored Procedures offers great performance since they are pre-compiled code for other subsequent calls and they are not pre-compiled code for first time but for other subsequent call they are pre-compiled because when we execute SP for first time in database server, database server makes an entry of SP in its cache memory once an entry is made in cache memory for other subsequent call it will call that SP directly from the cache memory. Hence stored procedures are pre-compiled for other subsequent calls. If input value changes or output value changes SP will remain in cache memory with the same entry. If you are looking out to execute a group of sql query with out compromising the memory then SP will provide a great performance. It is a best practise to use SP's in the DOT.NET projects.

Since SP are located at database server we can call that SP for any of asp.net c# pages for any number of time.

Stored procedures are faster as compared to normal T-SQL statements.

In a single SP execution plan we can execute a bunch of SQL statements.

Stored procedures are easy to maintain. If any changes occurs just we need to update single stored procedure located at database server, single update will reflect in all pages. Instead of going and changing all queries in all the pages just we need to update a single SP file.

We can reuse the SP code again and again means we can call SP function again and again without compromising the performance and memory.

It provides query encapsulation and offer simple sp function. So it means sp provide better security to your data queries.

SP enhances the security controls means users can be granted permission to execute a stored procedure which makes better security to data.

It trims down network traffic by executing a single line of function name over the network instead of executing hundred lines of T-SQL statements.

So these are some of the advantages of using SP in asp.net c# if you guys know more benefits of using sp in dot net feel free to drop your suggestion in the below comment box.

Implementing Stored Procedures in ASP.NET

In order to implement sp in asp.net project first we need to create a sp in database server or sql server. So let's open up Microsoft SQL Server 2008 and select a database (if you have not created a database then create a new one). Now maximize the database folder and also maximize the "programmability" folder inside the "programmability" you will find stored procedure folder.

Sample Code SP

 
CREATE PROCEDURE usp_sample
   
   @var1 varchar(100),
   @var2 int
 
AS
BEGIN
Select * From Employee Where Employee_ID=@var1
END
GO

Now let's see an example of using sp's in asp.net C#

Step by Step Insert data using stored procedure in asp.net c#

1st Step : Create a Simple Screen on ASP.NET Page

First step create a simple input screen to insert the data into database in asp.net page. Here in this example i have created the input put form in default.aspx page.

   <table border="1" cellpadding="5" style="border-collapse:collapse; font-family:Verdana; font-size:12px;">
             
             

             <tr>
             <td>Property Name</td>
             <td><asp:TextBox ID="txtpropertyname" runat="server"></asp:TextBox></td>
             </tr>
             <tr>
             <td>Property Address</td>
             <td><asp:TextBox ID="txtpropertyaddress" runat="server"></asp:TextBox></td>
             </tr>
             <tr>
                
             <td>Property Price</td>
             <td><asp:TextBox ID="txtpropertyprice" runat="server"></asp:TextBox></td>
             </tr>

             <tr>
             <td>Available units (1 BHK) </td>
             <td><asp:TextBox ID="txtavailableunits" runat="server"></asp:TextBox></td>
             </tr>
             
             <tr>
             <td colspan="2">
              <asp:HiddenField ID="txtPropertyID" runat="server" />
             </td>
             
             </tr>
             <tr>
             <td colspan="2">
              
              
                 <asp:Button ID="btn_Update" runat="server" Text="Update Property" 
                     onclick="btn_Update_Click" />
                     
                     &nbsp;
                     
                     <asp:Button ID="btn_Insert" runat="server" Text="Add Property" 
                     onclick="btn_Insert_Click" />
              
              
             </td>
             </tr>
             <tr>

             <td>Output : </td>    
               <td>   <asp:Label ID="lbloutput" runat="server" Text=""></asp:Label></td>
             </tr>
             </table>

As you see from above code and image that we have successfully created an input screen in "Default.aspx" page.

2nd Step : Create a SQL Table For Above Screen

In this step we have to create a sql table for above input screen.

 

CREATE TABLE [dbo].[propertylisting](
	[property_id] [int] IDENTITY(1,1) NOT NULL,
	[propertyname] [varchar](150) NULL,
	[propertyaddress] [varchar](500) NULL,
	[propertyprice] [money] NULL,
	[availableunits] [varchar](50) NULL,
 CONSTRAINT [PK_propertylisting] PRIMARY KEY CLUSTERED 
(
	[property_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

3rd Step : Create a Stored Procedure with Input Parameter, Output Parameter and Return Parameter

 
USE [property]
GO
/****** Object:  StoredProcedure [dbo].[usp_insertpropertylisting]    Script Date: 06/29/2014 18:05:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[usp_insertpropertylisting] 
	@propertyname varchar(150),
	@propertyaddress varchar(500),
	@propertyprice money,
	@availableunits varchar(100),
	@propertyID int output
AS
BEGIN
	
	INSERT into propertylisting(propertyname,propertyaddress,
	propertyprice,availableunits)

	VALUES

	(@propertyname,@propertyaddress, @propertyprice,@availableunits)
	
	SET @propertyID = SCOPE_IDENTITY()
	
	RETURN @propertyID
END

As you see from above sql code that we have successfully created a new sp called "usp_insertpropertylisting" with input, output and return parameter.

4th Step : Call Stored Procedure in ASP.NET C# Code

Now let's call the stored procedure in default.aspx page.In order to call stored procedure in asp.net page, first we need to create a sql connection and import the "using System.Data.SqlClient;" namespace.

  <connectionStrings>
    <add name="Conn" connectionString="Data Source=guru\\sqlexpress;Initial Catalog=property;Integrated Security=True"/>
  </connectionStrings>

 
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

In our input form which we have created in default.aspx page there we have a submit button. Now inside submit button click we will call our stored procedure code.

 
    protected void btn_Insert_Click(object sender, EventArgs e)
    {
        string conn = "";
        conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
        SqlConnection objsqlconn = new SqlConnection(conn);
        try
        {
            if (txtpropertyname.Text != "")
            {

                objsqlconn.Open();
                SqlCommand objcmd = new SqlCommand("usp_insertpropertylisting", objsqlconn);
                objcmd.CommandType = CommandType.StoredProcedure;

                SqlParameter property_id = objcmd.Parameters.Add("@propertyID", SqlDbType.Int);

                property_id.Direction = ParameterDirection.Output;
               


                SqlParameter property_name = objcmd.Parameters.Add("@propertyname", SqlDbType.VarChar);
                property_name.Value = txtpropertyname.Text;

                SqlParameter property_addr = objcmd.Parameters.Add("@propertyaddress", SqlDbType.VarChar);
                property_addr.Value = txtpropertyaddress.Text;


                SqlParameter property_price = objcmd.Parameters.Add("@propertyprice", SqlDbType.Money);
                property_price.Value = txtpropertyprice.Text;

                SqlParameter available_units = objcmd.Parameters.Add("@availableunits", SqlDbType.VarChar);
                available_units.Value = txtavailableunits.Text;

                objcmd.ExecuteNonQuery();


                ClearAll();
                //Response.Redirect("Default.aspx");

                lbloutput.Text = "Record inserted successfully. ID = " + property_id.Value.ToString();
            }




        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
        }
        finally
        {
            objsqlconn.Close();
        }

    }

As you see from above source code that we have called sp "usp_insertpropertylisting" using SQLCommand where we specified command type as "Stored Procedure".

SP input parameter variables are added by using class "SqlParameter" and those added "SqlParameter" variables are assigned to form input text boxes respectively.

 

  SqlParameter available_units = objcmd.Parameters.Add("@availableunits", SqlDbType.VarChar); 
  available_units.Value = txtavailableunits.Text;

As you see from above code that we have passed input form values to sp input parameters by using "SqlParameter" class. We have also output the sp parameter value by using "SqlParameter" class as shown below.

 

 SqlParameter property_id = objcmd.Parameters.Add("@propertyID", SqlDbType.Int);

 property_id.Direction = ParameterDirection.Output;
               
 lbloutput.Text = "Record inserted successfully. ID = " + property_id.Value.ToString();

As you see we have successfully demonstrated passing the parameters using sp input parameters and displaying parameter using the sp output and return parameter.

In this above example you saw how to pass the input values to stored procedures. Now let's do one more example of displaying the all records from database using stored procedure.

Display data using stored procedure in asp.net c#

1st Step : Write Stored Procedure

 
USE [property]
GO
/****** Object:  StoredProcedure [dbo].[usp_loadproperties]    Script Date: 07/01/2014 00:03:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_loadproperties]
	@PropertyID int
AS
BEGIN
    
    
	
	if(@PropertyID != 0)
	begin
	SELECT * from propertylisting where property_id=@PropertyID
	end
	else
	begin
	SELECT * from propertylisting
	end

END

As you see we have created new sp called "usp_loadproperties" which has got one input parameter called "@PropertyID". if "@PropertyID" has some value then select query will execute with where clause or else only select query will execute.

2nd Step : Displaying Records in ASP.NET Page

To display records in aspx page we will use gridview tool from toolbox. So let's add a gridview tool to aspx page.

 

<asp:GridView ID="grdloadproperties" runat="server"></asp:GridView>

3rd Step : Call the Stored Procedure

In the default.aspx page load event we will load the gridbox as shown in below code snippet.

 

 protected void Page_Load(object sender, EventArgs e)
 {
        string conn = "";
        conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();
        SqlConnection objsqlconn = new SqlConnection(conn);
        try
        {
           
                objsqlconn.Open();
                DataSet ds = new DataSet();
                SqlCommand objcmd = new SqlCommand("usp_loadproperties", objsqlconn);
                objcmd.CommandType = CommandType.StoredProcedure;

                SqlParameter property_id = objcmd.Parameters.Add("@PropertyID", SqlDbType.Int);
                property_id.Value = 0;

                SqlDataAdapter objAdp = new SqlDataAdapter(objcmd);

                objAdp.Fill(ds);

                grdloadproperties.DataSource = ds;
                grdloadproperties.DataBind();

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
        }
        finally
        {
            objsqlconn.Close();
        }


  }

As you see from above code that we have successfully filled the gridbox with the data fetched from sp.

Sample Stored Procedure Insert and Update Query

 

USE [ado]
GO
/****** Object:  StoredProcedure [dbo].[usp_InsertUpdate]    Script Date: 07/01/2014 00:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_InsertUpdate]
	(
	@Employee_ID int,
	@Employee_Name varchar(100),
	@Employee_Address varchar(500),
	@Employee_Sal money
	)
AS
	if(@Employee_ID = 0)
	begin
	Insert into Employee(Employee_Name,Employee_Address,Employee_Salary) 
	
	Values 
	
	(@Employee_Name,@Employee_Address,@Employee_Sal)
	end 
	
	else 
	
	begin
	
	Update Employee set Employee_Name=@Employee_Name,Employee_Address=@Employee_Address,Employee_Salary=@Employee_Sal
	
	Where Employee_ID=@Employee_ID
	
	end
	
	RETURN

Sample Stored Procedure Delete Query

 

USE [ado]
GO
/****** Object:  StoredProcedure [dbo].[usp_Delete]    Script Date: 07/01/2014 00:27:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Delete]
	(
	@Employee_ID int
	)
AS
	Delete from Employee Where Employee_ID = @Employee_ID
	RETURN

So friends this is all about using stored procedure in asp.net c# using input/output/return parameter examples. If you have any doubts feel free to ask me via comments or via email. If you have any suggestion or feed-back regarding this article feel free to update me. If you want me to write any article on onlinebuff.com on any topic for you then just kindly drop the question/topic whenever will get time surely i will update my article on your valuable question.

Kindly share this article with you friends and dont forget to vote onlinebuff on facebook and google+. Thank you..!

Gurunatha Dogi

Gurunatha Dogi is a software engineer by profession and founder of Onlinebuff.com, Onlinebuff is a tech blog which covers topics on .NET Fundamentals, Csharp, Asp.Net, PHP, MYSQL, SQL Server and lots more..... read more

Comments

By Islam on 2016-02-11

It is good but need to write more code. We can do without stored procedures and sql command can write with sql connection. any way thank you.

By Rahul on 2015-09-15

I want to know about static members,functions,methods all...i am confused that what is static and how and where we can use it..please help me out with easy wordings and examples..thank-you..

By Mohd sameer on 2015-03-30

please write a blog on connected or disconnected architecture in asp .net

By Pradip on 2015-03-15

i m a beginner

By Radhakrishna on 2015-02-18

Can you write about triggers in sql as am a begginer level write in a basic level. So that i can understand eaiser. Thanks in advance

By Parth Patel on 2014-12-08

Hii... recently i have completed my study and now i am finding job. i like to read your articals. pls can u give one full CRUD artical in which use Store Procedure with 3-tier arch. also that include Controls like Dropdown, Radiobutton, Checkbox, File Upload etc. i am serch on net for that kind artical but m not satisfied with that code b'coz that doesn't gave me proper understanding. i think you can do this very well b'coz i like your way to write code and also to giving understanding for each line of code. Please Dude give this kind of articles Thank you.

By Sreeja on 2014-10-06

Thank you sir thank you very much

By Ashish.S on 2014-09-16

it very helpful,thank u

By Garima gupta on 2014-09-11

very helpful in solving the problem, easy to understand too.

By Shruti on 2014-07-29

Sir can u please help me in creating a account ledger havin opening, closing balance in asp.net with c#. Pls cn u mail me by tonight m in nid of it urgently.

By Kalpit patel on 2014-07-23

its good and very easy to understand, i like it

By Nis-h on 2014-07-02

very nice explanation

By Muneendra on 2014-07-01

Very helpfull thnqs