How to use transaction with Dataadapter

  • Thread starter Thread starter KWOK
  • Start date Start date
K

KWOK

Hi All,

I want to add some records in three tables in dataset. I
add new reocrd and update dataset everyTime before add
another table.
So if I get errors while I'm updating the third table. How
can i use transaction.rollBack to undo every thing in
first table and second table??

How can I update three tables using DataAdapter at same
time??


Thanks,
KWOK
 
SqlTransaction tn ; //declare a transaction

const string sql = "INSERT INTO Employees1(EmpID) VALUES (@UserID)";

SqlConnection cn = new SqlConnection("data source=AUG-SQLSRV;initial
catalog=HumanResources;integrated security=SSPI");



try{if(cn.State != ConnectionState.Open){cn.Open();}}

//If we throw an exception on Open, which is a 'risky' operation

//manually make the assertino fail by setting it to false and use

//ex.ToString() to get the information about the exception.

catch (SqlException ex){Debug.Assert(false, ex.ToString());}

//Instantiate command with CommandText and Connection and t
//transaction

tn = cn.BeginTransaction();

SqlCommand cmd = new SqlCommand(sql, cn,tn);

cmd.Parameters.Clear();

cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = 314;



try

{

//You can test for records affected, in this case we know it

//would be at most one record.

int i = cmd.ExecuteNonQuery();

//If successful, commit the transaction

//Loop 5 times and just add the id's incremented each time

for(int x=0; x<5; x++)

{

cmd.Parameters["@UserID"].Value = (315 + x);

cmd.ExecuteNonQuery();

}

cmd.Parameters["@UserID"].Value = (325);

cmd.ExecuteNonQuery();



tn.Commit();

}

catch(SqlException ex){

Debug.Assert(false, ex.ToString());

//If it failed for whatever reason, rollback the //transaction

tn.Rollback();

//No need to throw because we are at a top level call and //nothing is
handling exceptions

}

finally{

//Check for close and respond accordingly

if(cn.State != ConnectionState.Closed){cn.Close();}

//Clean up my mess

cn.Dispose();

cmd.Dispose();

tn.Dispose();

}


VB.NET

Dim tn as
cn.BeginTransaction

Try

da.Update
tn.Commit
Catch ex as System.Exception
tn.Rollback
End Try
 
Back
Top