Automatic rollback inside using statement?

If I start a transaction using the following approach ...

using (SqlTransaction trans = destConn.BeginTransaction())
{

...do some transfers using SqlBulkCopy
}

...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?

No.If an error occurs,Rollback the transaction in acatch statement to void out any changes and thenrethrow the error so that the application can deal with it accordingly.

http://davidhayden.com/blog/dave/archive/2005/10/14/2515.aspx

|||Is it really necessary to close the connection inside ausing (SqlConnection connection1 =new SqlConnection(connectString1)) block like David Hayden does?|||

They don't do it here:

http://msdn2.microsoft.com/en-us/library/tchktcdk.aspx

Also look at TransactionScopes they're pretty interesting:

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

|||

Let me tell you the internals of transaction it is mathmatically defined to be one operation at a time by the people who created it but SQL Server langauge T-SQL let you create more than one at a time through what we call transaction savepoints you may have seen them in David's blog entry. That is called nested transaction but you must use savepoint or the whole thing will rollback to the first if there is an error. Those savepoints can be rolled back as of service pack 3 of SQL Server 2000 if needed. And you must use the second Using statement before you use them so the system can call dispose for you automatically, that is good practice so resources like transaction is not running when you are through with them. And if you are not hosting the application in your local intranet you cannot use transaction scope because transaction scope performs illegal none atomic transactions and SQL Server does the right thing and promotes the operation to distributed transaction, if you use transaction scope you must call a T-SQL stored procedure doing the correct atomic transaction to finish your transaction block. There is an auto commit mode that is the default unless you change the definition in SQL Server and automatic roll back is if you don't use the savepoint and there is an error in your code. Post again if you still have questions. Try the thread below and read the code and the info in the links in it and you will understand what I have explained here. Hope this helps.

http://forums.asp.net/thread/1284424.aspx

|||

Thanx!

I'll read the thread