Using Stored Procedures with ASP.NET

Stored procedures are series of SQL statements grouped into a single unit, we can pass parameters to the stored procedures same like the passing parameters to the functions
Simple stored procedure

USE [databasename]
GO
/****** Object: StoredProcedure [dbo].[user_insert_mycomment] Script Date: 11/19/2008 22:45:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[user_insert_mycomment]
-- Add the parameters for the stored procedure here
(
@comment VARCHAR(500),
@eventID BIGINT
)
AS
BEGIN
BEGIN TRY
 
Insert into mycomment(eventID, comment) 
values(@eventID, @comment)
 
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
END

This stored procedure is taking two input parameters first is “comment” and second is “eventID” , these values are inserting into “mycomment” table
The next thing is that how we call this stored procedure into our asp.net page, so we are moving into next things now

Step1: Add 2 labels, 2 textbox and one command button into the page and change the property of these controls according to the following list

1.) TextBox1,change Property - ID=”tbEventID”
2.) TextBox2,change Property - Text=”tbComment”
3.) Label1,change Property - Text=”event”
4.) Label2,change Property - Text=”comment”
5.) Command button 1, change text to submit

Step2: double click on the command button and write the below code in it

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StrCon"].ToString());
 
SqlCommand cmd = new SqlCommand("NAME_OF_STORED_PROCEDURE ", con);
cmd.CommandType = CommandType.StoredProcedure;
 
// Parameters list
cmd.Parameters.Clear();
cmd.Parameters.Add("@eventID", SqlDbType.BigInt).Value = Request.QueryString["id"];
cmd.Parameters.Add("@comment", SqlDbType.VarChar, 500).Value = tbComment.Text.Trim();
 
// end parameters
 
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Explanation:
1) SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StrCon"].ToString());

The above line will create a connection name”con”

2) SqlCommand cmd = new SqlCommand("NAME_OF_STORED_PROCEDURE ", con);
cmd.CommandType = CommandType.StoredProcedure;

This will create command object with name of the stored procedure in it and then we have to declare the type of command in our case i.e. StoredProcedure

3). cmd.Parameters.Clear();
The above line will clear all the parmaters

4) cmd.Parameters.Add("@eventID", SqlDbType.BigInt).Value = Request.QueryString["id"];
cmd.Parameters.Add("@comment", SqlDbType.VarChar, 500).Value = tbComment.Text.Trim();

the above paramters command will used to pass paramters (eventID and Comment) to the stored procedure

5) con.Open();
cmd.ExecuteNonQuery();
con.Close();

above three line will open the connection, run the stored procedure and then in last close the connection

the above article will give you the basic of using a stored procedure with asp.net

About the author:

PlanetSourceCode.in is a place for all developer providing free source codes, articles, complete projects,complete application in PHP, C/C++, Javascript, Visual Basic, Cobol, Pascal, ASP/VBScript, AJAX, SQL, Perl, Python, Ruby, Mobile Development