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

Add new comment