Click here to Skip to main content
16,012,028 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am creating a stored procedure that contain insert,update and delete query.
I want to use this single stored procedure to perform insert, update and delete operation in my aspx page.
i know how to use single stored procedure in aspx page but I don't know single store procedure contain multiple query and use in aspx page
pls give me suggestion

Thanks
Ratish kumar jha
Posted
Updated 17-Jul-12 18:20pm
v2
Comments
StianSandberg 18-Jul-12 3:54am    
just a little tips: Don't perform insert, update and delete in same procedure! It will get messy.

Who and why this was suggested: i am create single stored procedure that contain insert,update and delete query.

How is this even possible? Well actually it is only with if-else case on operation type. But having three separate queries in SP for them does not make any sense. Executing your SP will execute all the three queries.

I would suggest to keep separate SP's for each operation. In case you want to club all three operation into one SP then pass on the operation type - insert/update/delete as one of the parameters of stored procedure. Based on this input, run related query only. You can use SQL-If for it.

Details here:
MSDN: IF...ELSE (Transact-SQL)[^]
SQL IF...ELSE Statement [^]
 
Share this answer
 
Hi,
Here is my article which performs the same. Refer the links below:
Sending DataTable to a Stored Procedure[^]
You can view the queries and fornt-end operation in that.

--Amit
 
Share this answer
 
v2
Hi..
You pass certain parameters through the stored procedure then retrieve the parameters then use the 3 queries within a single store proc and execute the single stored proc in the .aspx page too..
 
Share this answer
 
hi...Its very easy.. just take a look at the following Program.
e.g. StudentMaster



Id_int,sName_vcr,Roll_No_int

1 Sumit 100


SQL
-------------------------------------
CREATE Procedure AddUpdateGetStudent
(
  @para_vcr VARCHAR(20)='',
  @Id_int   INT=0,
  @sName_vcr VARCHAR='',
  @Roll_No_int INT=0
)
AS
BEGIN
       IF @para_vcr='Get'
       BEGIN
            SELECT * FROM StudentMaster
       END
       ELSE IF @para_vcr='ADD'
       BEGIN
            INSERT INTO StudentMaster
            (Id_int,sName_vcr,Roll_No_int)
            VALUES
            (@Id_int,@sName_vcr,@Roll_No_int )
       END
       ELSE IF @para_vcr='UPADTE'
       BEGIN
            UPDATE Student_Master
            SET 
                sName_vcr=@sName_vcr,
                Roll_No_int=@Roll_No_int
            WHERE Id_int=@Id_int
       END
       ELSE IF @para_vcr='Delete'
       BEGIN
             DELETE FROM StudentMaster
             WHERE Id_int=@Id_int 
       END    

END
----------------------------------------------


Now In C#

add following namespaces in your Page

C#
using System.Data;
using System.Data.SqlClient;
class ABC
{
SQLConnection con=new SqlConnection("Data Source=.SQLEXPRESS;DataBase=TEST_DB;Integrated Security=true");
  private void btnAdd_Click(object sender,EventArgs e)
  {
         using(SqlCommand cmd=new SqlCommand("AddUpdateStudentMaster",con))
         {
           cmd.CommandType=CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("@para_vcr","Add");
           cmd.Parameters.AddWithValue("@Id_int",int.parse(txtId.Text));
           cmd.Parameters.AddWithValue("@sName_vcr",txtName.Text);                       cmd.Parameters.AddWithValue("@Roll_No_int",int.parse(txtRollNo.Text));
con.Open();
cmd.ExecuteNonQuery();
con.close();

          Response.Write("Record Added Successfully.");
         }
  }
 private void btnAdd_Click(object sender,EventArgs e)
  {
         using(SqlCommand cmd=new SqlCommand("AddUpdateStudentMaster",con))
         {
           cmd.CommandType=CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("@para_vcr","Update");
           cmd.Parameters.AddWithValue("@Id_int",int.parse(txtId.Text));
           cmd.Parameters.AddWithValue("@sName_vcr",txtName.Text);                       cmd.Parameters.AddWithValue("@Roll_No_int",int.parse(txtRollNo.Text));
con.Open();
cmd.ExecuteNonQuery();
con.close();

          Response.Write("Record Updated Successfully.");
         }
  }
}



In this way u can use many conditions as per your requirement.

hope u got it...

have a nice day.
 
Share this answer
 
v2
Comments
_Amy 18-Jul-12 3:32am    
[Edit]Added pre tag.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900