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- 28 views