Click here to Skip to main content
15,867,288 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all in my application I am having the following scenario

C#
for(int i=0;i<empIds.Count;i++)
{
    class1 obj=new class1();
    class2 obj1=new class2();
    insert in to first table
    obj.Insert();
    //Getting some data required and looping as follows
    foreach (DataRow EmpDetails in m_oDataSet.Tables["Details"].Rows)
    {
       insert in to second table
       obj1.Insert();
    }
}


In the above scenario assume I have two employees and while iterating data for the fist employee received correctly and should get insert in the tables. In any case for second employee while coming to the inner loop if any error occurs I would like to roll back then previously inserted record for the second employee in the first table.

But if I use transaction every thing is getting roll-backed including first employee.

So what should I choose for my requirement
Posted
Updated 13-Apr-12 20:12pm
v2

1 solution

hi,
I you need to use the transaction. try as below.

C#
SqlTransaction objTran;
SqlConnection _SqlCOnn = new SqlCOnnection("COnnection String");
SqlCommand _SqlCmd = new SqlCommand(); 
_SqlCOnn.Open();
for(int i=0;i<empids.count;i++)>
{
objTran = _SqlCOnn.BeginTransaction(IsolationLevel.RepeatableRead);
_SqlCmd.Transaction=objTran;
try
{
    insert in to first table
    //Getting some data required and looping as follows
    foreach (DataRow EmpDetails in m_oDataSet.Tables["Details"].Rows)
    {
       insert in to second table
       objTran.Commit();
    }
}
catch(Exception ex)
{
    objTran.RollBack();
}
}


So, This will commit/rollback the particular transaction. It would't not affect the previous commited transactions.

hope this helps.
 
Share this answer
 
Comments
demouser743 14-Apr-12 2:12am    
Will this be applicable if I am inserting data through different classes, I made a few changes check once

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