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

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

01	CREATE PROCEDURE usp_sample

02

03	@var1 varchar(100),

04

@var2 int

05

06

AS

07

BEGIN

08	Select * From Employee Where Employee_ID=@var1

09

END

10

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.

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

02

03

04

05

<tr>

06	<td>Property Name</td>

07	<td><asp:TextBox ID="txtpropertyname" runat="server"></asp:TextBox></td>

08

</tr>

09

<tr>

10	<td>Property Address</td>

11	<td><asp:TextBox ID="txtpropertyaddress" runat="server"></asp:TextBox></td>

12

</tr>

13

<tr>

14

15	<td>Property Price</td>

16	<td><asp:TextBox ID="txtpropertyprice" runat="server"></asp:TextBox></td>

17

</tr>

18

19

<tr>

20	<td>Available units (1 BHK) </td>

21	<td><asp:TextBox ID="txtavailableunits" runat="server"></asp:TextBox></td>

22

</tr>

23

24

<tr>

25	<td colspan="2">

26	<asp:HiddenField ID="txtPropertyID" runat="server" />

27

</td>

28

29

</tr>

30

<tr>

31	<td colspan="2">

32

33

34	<asp:Button ID="btn_Update" runat="server" Text="Update Property"

35	onclick="btn_Update_Click" />

36

37

 

38

39	<asp:Button ID="btn_Insert" runat="server" Text="Add Property"

40	onclick="btn_Insert_Click" />

41

42

43

</td>

44

</tr>

45

<tr>

46

47	<td>Output : </td>

48	<td>   <asp:Label ID="lbloutput" runat="server" Text=""></asp:Label></td>

49

</tr>

50

</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.

01	CREATE TABLE [dbo].[propertylisting](

02	[property_id] [int] IDENTITY(1,1) NOT NULL,

03	[propertyname] [varchar](150) NULL,

04	[propertyaddress] [varchar](500) NULL,

05	[propertyprice] [money] NULL,

06	[availableunits] [varchar](50) NULL,

07	CONSTRAINT [PK_propertylisting] PRIMARY KEY CLUSTERED

08

(

09	[property_id] ASC

10	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

11	) ON [PRIMARY]

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

This is the main step of this example because here where we will create a stored procedure with input parameters to accept the values from the asp.net screen and output parameter which will be output with last inserted record ID.

01	USE [property]

02

GO

03	/****** Object:  StoredProcedure [dbo].[usp_insertpropertylisting]    Script Date: 06/29/2014 18:05:03 ******/

04	SET ANSI_NULLS ON

05

GO

06	SET QUOTED_IDENTIFIER ON

07

GO

08	CREATE  PROCEDURE [dbo].[usp_insertpropertylisting]

09	@propertyname varchar(150),

10	@propertyaddress varchar(500),

11	@propertyprice money,

12	@availableunits varchar(100),

13	@propertyID int output

14

AS

15

BEGIN

16

17	INSERT into propertylisting(propertyname,propertyaddress,

18	propertyprice,availableunits)

19

20

VALUES

21

22	(@propertyname,@propertyaddress, @propertyprice,@availableunits)

23

24	SET @propertyID = SCOPE_IDENTITY()

25

26	RETURN @propertyID

27

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.
SQL Connection

1	<connectionStrings>

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

3	</connectionStrings>

SQL Namespace

1	using System.Configuration;

2	using System.Data;

3	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.

01	protected void btn_Insert_Click(object sender, EventArgs e)

02

{

03	string conn = "";

04	conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();

05	SqlConnection objsqlconn = new SqlConnection(conn);

06

try

07

{

08	if (txtpropertyname.Text != "")

09

{

10

11	objsqlconn.Open();

12	SqlCommand objcmd = new SqlCommand("usp_insertpropertylisting", objsqlconn);

13	objcmd.CommandType = CommandType.StoredProcedure;

14

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

16

17	property_id.Direction = ParameterDirection.Output;

18

19

20

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

22	property_name.Value = txtpropertyname.Text;

23

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

25	property_addr.Value = txtpropertyaddress.Text;

26

27

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

29	property_price.Value = txtpropertyprice.Text;

30

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

32	available_units.Value = txtavailableunits.Text;

33

34	objcmd.ExecuteNonQuery();

35

36

37	ClearAll();

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

39

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

41

}

42

43

44

45

46

}

47	catch (Exception ex)

48

{

49	Response.Write(ex.Message.ToString());

50

}

51

finally

52

{

53	objsqlconn.Close();

54

}

55

56

}

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.

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

2	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.

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

2

3	property_id.Direction = ParameterDirection.Output;

4

5	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

01	USE [property]

02

GO

03	/****** Object:  StoredProcedure [dbo].[usp_loadproperties]    Script Date: 07/01/2014 00:03:42 ******/

04	SET ANSI_NULLS ON

05

GO

06	SET QUOTED_IDENTIFIER ON

07

GO

08	CREATE PROCEDURE [dbo].[usp_loadproperties]

09	@PropertyID int

10

AS

11

BEGIN

12

13

14

15	if(@PropertyID != 0)

16

begin

17	SELECT * from propertylisting where property_id=@PropertyID

18

end

19

else

20

begin

21	SELECT * from propertylisting

22

end

23

24

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.

1	<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.

01	protected void Page_Load(object sender, EventArgs e)

02

{

03	string conn = "";

04	conn = ConfigurationManager.ConnectionStrings["Conn"].ToString();

05	SqlConnection objsqlconn = new SqlConnection(conn);

06

try

07

{

08

09	objsqlconn.Open();

10	DataSet ds = new DataSet();

11	SqlCommand objcmd = new SqlCommand("usp_loadproperties", objsqlconn);

12	objcmd.CommandType = CommandType.StoredProcedure;

13

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

15	property_id.Value = 0;

16

17	SqlDataAdapter objAdp = new SqlDataAdapter(objcmd);

18

19	objAdp.Fill(ds);

20

21	grdloadproperties.DataSource = ds;

22	grdloadproperties.DataBind();

23

24

}

25	catch (Exception ex)

26

{

27	Response.Write(ex.Message.ToString());

28

}

29

finally

30

{

31	objsqlconn.Close();

32

}

33

34

35

}

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

Sample Stored Procedure Insert and Update Query

01

USE [ado]

02

GO

03	/****** Object:  StoredProcedure [dbo].[usp_InsertUpdate]    Script Date: 07/01/2014 00:06:02 ******/

04	SET ANSI_NULLS ON

05

GO

06	SET QUOTED_IDENTIFIER ON

07

GO

08	CREATE PROCEDURE [dbo].[usp_InsertUpdate]

09

(

10	@Employee_ID int,

11	@Employee_Name varchar(100),

12	@Employee_Address varchar(500),

13	@Employee_Sal money

14

)

15

AS

16	if(@Employee_ID = 0)

17

begin

18	Insert into Employee(Employee_Name,Employee_Address,Employee_Salary)

19

20

Values

21

22	(@Employee_Name,@Employee_Address,@Employee_Sal)

23

end

24

25

else

26

27

begin

28

29	Update Employee set Employee_Name=@Employee_Name,Employee_Address=@Employee_Address,Employee_Salary=@Employee_Sal

30

31	Where Employee_ID=@Employee_ID

32

33

end

34

35

RETURN

Sample Stored Procedure Delete Query

01

USE [ado]

02

GO

03	/****** Object:  StoredProcedure [dbo].[usp_Delete]    Script Date: 07/01/2014 00:27:35 ******/

04	SET ANSI_NULLS ON

05

GO

06	SET QUOTED_IDENTIFIER ON

07

GO

08	CREATE PROCEDURE [dbo].[usp_Delete]

09

(

10	@Employee_ID int

11

)

12

AS

13	Delete from Employee Where Employee_ID = @Employee_ID

14

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.