SqlDataAdapter aand transaction

[Guest]

Hello,
I'm using SqlDataAdapter to insert data from one table into another with
SqlCommand after I start a transaction.
If I have convertion error for the insertion of the data, The transaction is
no longer valid, I'ts rolled back.
Is there a property to leave the transaction open ?

my code :

SqlTransaction trans = m_Conn.BeginTransaction();
SqlCommand oCmd1 = new SqlCommand("Insert into tbl1(col1) select col1 from
tbl2", m_Conn);
oCmd1.Transaction = trans;
oCmd1.ExecuteNonQuery();


Thanks,
Sharon

 

[Miha Markic [MVP C#]]

Hi Sharon,

The transaction will rollback if it is disposed or Rollback method is
invoked.

 

[Oenone]

If I have convertion error for the insertion of the data, The
transaction is no longer valid, I'ts rolled back.
Is there a property to leave the transaction open ?

That's not the experience I'm having (I just tested this after reading your
email).

I've written some code that starts a transaction, then inserts (using a
SqlCommand object) a record that is invalid (in my case I'm adding a value
that's too large for the field but I doubt that this will behave differently
to your own situation). I handle this exception and carry on regardless. I
then insert another record that is valid. Finally I roll back the
transaction.

This completes without any errors, and when I look in the database I find
that neither of the rows were added, so the second insert must have still
been within the transaction.

Could you try reducing your code to a simple test along these lines (i.e.,
take it out of the complexity of the rest of your application) and see if it
still fails?

 

[Mary Chipman]

If you wrap two separate operations within a single explicit
transaction, and one of the operations fails, both operations will be
rolled back. You wouldn't want the transaction to be left open in this
situation as it would cause serious problems with locking and blocking
on the server.

Whether you see any errors or not depends on how you handle things on
the SQL Server side. You have to check both @@error and @@rowcount
immediately after an INSERT to see if it succeeded. Unlike client code
which breaks on unhandled errors, SQL Server will simply move on and
execute the next statement in the batch (or end the transaction as the
case may be).

IMO you're much better off coding explicit transactions inside of a
stored procedure rather than in client code. You have strong type
checking for input parameters, can implement error handling in T-SQL,
and can return success/failure information in output parameters,
reducing round trips and head-scratching wondering what's going on in
the server. Plus, you encapsulate DML code so that it can be called by
multiple clients and have the security benefits attendant on stored
procedure driven apps.

--Mary

 

[Guest]

I wrote this sample code:

try
{
SqlConnection conn = new SqlConnection("Application
Name=UpgradeDLL;workstation id=Sharon"
+ ";packet size=4096;user id="
+ "sa"
+ ";Password="
+ ""
+ ";data source=" + '"'
+ "192.168.110.30"
+ '"' + ";persist security info=False;initial catalog="
+ "aa");
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand("alter table table1 add a3 int", conn,
tran);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("insert into table1 (a3) values('txt')", conn, tran);
cmd.ExecuteNonQuery();
}
catch(Exception exp)
{
System.Diagnostics.Debug.WriteLine(exp.Message);
}
finally
{
tran.Commit();
conn.Close();
}
}
catch(Exception exp1)
{
System.Diagnostics.Debug.WriteLine(exp1.Message);
}

I want to commit the transaction even if there was an error.
But I get this exception at tran.Commit()
exp1:
"The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION"