CODEWITHSUNDEEP
c#visual-studio-2010sql-server-2008
Olivarsham
Olivarsham

Reputation: 1731

Rollback on insert error from C# code

I am inserting a series of rows with roll back option if any error occurs. 

BEGIN TRY
BEGIN TRANSACTION

    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);


COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRAN --RollBack 

END CATCH

in C#, i am inserting rows in for loop. Is it possible to attain the same feature here also? 

foreach(string lst in str[])
{
    //insert query
    dbl.ExecSqlNonQuery("sp_tbltest", CommandType.StoredProcedure);
}

if any error occurs in loop, all rows shoud roll back.

Upvotes: 2

Views: 952

Answers (2)

daryal
daryal

Reputation: 14929

try
    {            
        using (TransactionScope scope = new TransactionScope())
        {
            //// create the connection
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                //// open the connection
                connection1.Open();

                foreach(string lst in str[])
                {
                     //insert query
                     connection1.ExecSqlNonQuery("sp_tbltest", CommandType.StoredProcedure);
                }                    
            }

            scope.Complete();

        }

    }
    catch (Exception)
    {
        scope.Rollback();
    }

Upvotes: 1

Urban Björkman
Urban Björkman

Reputation: 2105

Yes, you can commit and rollback on error using SqlTransaction

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

Upvotes: 1

Related Questions