ASP.NET - Roll back insert statment - Asked By mostafa hamdy on 11-Mar-12 10:13 AM

Hello all
I have some web site which have some page insert in two sql server DB tables , the  first one I have method which insert into some table called users and then I have to add in some other table called Permissions, and If  the user adding failed I want to roll back from both tables , please how can I do that , please if any body can help me please send me or tell me about some URL may help me in doing that
Regards
Danasegarane Arunachalam replied to mostafa hamdy on 11-Mar-12 11:05 AM
Use the Transction in your code.

1. Enclose the insert and other operation inside the transaction.
2. If the transaction is succeed then commit the transaction
3. Else rollback the transaction


The below example uses the sql transaction


//SQl transaction
SqlTransaction myTransaction = SqlConnectionObject.BeginTransaction();
try
{
//Perform your insert statmements

SqlCommand cmd=new Command("Select * from mytable");
cmd.connection=myconnection;
cmd.Transaction = myTransaction.Transaction;



// If succeed then commit  
   myTransaction
.Commit();
}
catch
{
   
// IF error roolback
    myTransaction
.Rollback();
   
throw;
Venkat K replied to mostafa hamdy on 11-Mar-12 11:16 AM
You need to use transactions for this:
1. Start transaction before Insert statement.
2. After the Schema permissions are successfully inserted then commit the transaction.
3. If the schema permissions are failed then rollback the transaction.

How to use the transactions check this lnk:
http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET

Hope this helps!
[)ia6l0 iii replied to mostafa hamdy on 11-Mar-12 10:57 PM
And when you say , page inserts into two tables - I hope that is done under one single action on a single ASP.net page. Inj this case, you need to call a stored procedure which will either commit/rollback the transaction depending on its sucess.

In your stored procedure, put your insert statements within a try-catch. Then place a "Begin Transaction" statement before the Try. In the case of any error, place a "Rollback" in the Catch block. You can use the "@@Trancount" server variable to determine if there were any errors. Check the following sample.

CREATE PROCEDURE StoredProcedureName 
AS
BEGIN
--Begin the transaction before your insert statements
BEGIN TRANSACTION
BEGIN TRY
--Place the insert statements here.
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    -- Some error occured
    ROLLBACK
END CATCH
--All is fine. Commit the transaction
COMMIT
END

Otherwise, you are going to have to do this on your server side code  - .Net. 

Database db = DatabaseFactory.CreateDatabase(); 
using (DbConnection conn = db.CreateConnection())

   //Open the connection.
   conn.Open(); 
   //Begin a transaction
   Dbtrans trans = conn.Begintrans(); 
   try 
   { 
//db.ExecuteCommand1
//db.ExecuteCommand2

trans.Commit(); 
   } 
   catch 
   { 
      trans.Rollback(); 
   } 
}

Hope the code is explanatory.
Chintan Vaghela replied to mostafa hamdy on 12-Mar-12 12:45 AM

Hello,

Make your SP as following way

 

CREATE PROCEDURE InsertSP

 

(

 

@nameFirst varchar(50),

 

@nameLast varchar(50),

 

@email varchar(50),

@PermissionValue varchar(50)

)

AS

 

BEGIN

Begin Transaction InsertUser

INSERT INTO User_Master( [nameFirst], [nameLast], [email]) VALUES ( @nameFirst, @nameLast, @email)

 

DECLARE @intValue as Int = SCOPE_IDENTITY()

 

IF @intValue > 0

BEGIN

    INSERT INTO Permission( @intValue,PermissionValue) VALUES ( UserID, @PermissionValue)

    COMMIT TRANSACTION InsertUser;

 

   

END

ELSE

BEGIN

 

    ROLLBACK TRANSACTION InsertUser;

END

 

END

Hope this helpful!

Thanks

 

 

 

 

 

Sandeep Mittal replied to mostafa hamdy on 12-Mar-12 12:54 AM
Slight modification in [)ia6l0 iii code's

Change in Code : Remove COMMIT from the second last line (Mark in Red) and write just before END TRY (Mark in Green)
Reason :  If you would write commit in the last line, COMMIT Statement would always execute. Now suppose, if there is any error it would be handled in CATCH and it would rollback the transaction, but COMMIT would still run and you would get unhandled error like this
Msg 3902, Level 16, State 1, Procedure StoredProcedureName, Line 17
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION


Check this code for the reference

CREATE
PROCEDURE StoredProcedureName
AS
BEGIN
  --Begin the transaction before your insert statements
  BEGIN TRANSACTION
  BEGIN TRY
  --Place the insert statements here.
    SELECT 1/0
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
    -- Some error occured
    ROLLBACK
  END CATCH
  --All is fine. Commit the transaction
  COMMIT
END

Modified Code
CREATE PROCEDURE StoredProcedureName
AS
BEGIN
  --Begin the transaction before your insert statements
  BEGIN TRANSACTION
  BEGIN TRY
  --Place the insert statements here.
 --All is fine. Commit the transaction
         COMMIT -- put commit here   
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
  -- Some error occured
  ROLLBACK
END CATCH
COMMIT --remove commit from here
END