Transactions in net

Question

What are the best practices to do transactions in C   Net 2 0  What are the classes that should be used  What are the pitfalls to look out for etc  All that commit and rollback stuff  I m just starting a project where I might need to do some transactions while inserting data into the DB  Any responses or links for even basic stuff about transactions are welcome

User · Answer

if you just need it for db-related stuff  some OR Mappers  e g  NHibernate  support transactinos out of the box per default

User · Answer

if you just need it for db-related stuff  some OR Mappers  e g  NHibernate  support transactinos out of the box per default

User · Answer

protected void Button1 Click object sender  EventArgs e                using  SqlConnection connection1   new SqlConnection  Data Source    SQLEXPRESS AttachDbFilename  DataDirectory   Database mdf Integrated Security True User Instance True                        connection1 Open                   Start a local transaction             SqlTransaction sqlTran   connection1 BeginTransaction                   Enlist a command in the current transaction             SqlCommand command   connection1 CreateCommand               command Transaction   sqlTran              try                                Execute two separate commands                 command CommandText                    insert into  doctor  drname drspecialization drday  values   a   b   c                    command ExecuteNonQuery                   command CommandText                    insert into  doctor  drname drspecialization drday  values   x   y   z                    command ExecuteNonQuery                       Commit the transaction                 sqlTran Commit                   Label3 Text    Both records were written to database                            catch  Exception ex                                 Handle the exception if the transaction fails to commit                 Label4 Text   ex Message                   try                                        Attempt to roll back the transaction                     sqlTran Rollback                                    catch  Exception exRollback                                         Throws an InvalidOperationException if the connection                        is closed or the transaction has already been rolled                        back on the server                     Label5 Text   exRollback Message

User · Answer

You could also wrap the transaction up into it s own stored procedure and handle it that way instead of doing transactions in C  itself

User · Answer

There are 2 main kinds of transactions  connection transactions and ambient transactions  A connection transaction  such as SqlTransaction  is tied directly to the db connection  such as SqlConnection   which means that you have to keep passing the connection around - OK in some cases  but doesn t allow  create use release  usage  and doesn t allow cross-db work  An example  formatted for space    using  IDbTransaction tran   conn BeginTransaction          try              your code         tran Commit           catch           tran Rollback            throw            Not too messy  but limited to our connection  conn   If we want to call out to different methods  we now need to pass  conn  around   The alternative is an ambient transaction  new in  NET 2 0  the TransactionScope object  System Transactions dll  allows use over a range of operations  suitable providers will automatically enlist in the ambient transaction   This makes it easy to retro-fit into existing  non-transactional  code  and to talk to multiple providers  although DTC will get involved if you talk to more than one    For example   using TransactionScope tran   new TransactionScope          CallAMethodThatDoesSomeWork        CallAMethodThatDoesSomeMoreWork        tran Complete        Note here that the two methods can handle their own connections  open use close dispose   yet they will silently become part of the ambient transaction without us having to pass anything in   If your code errors  Dispose   will be called without Complete    so it will be rolled back  The expected nesting etc is supported  although you can t roll-back an inner transaction yet complete the outer transaction  if anybody is unhappy  the transaction is aborted   The other advantage of TransactionScope is that it isn t tied just to databases  any transaction-aware provider can use it  WCF  for example  Or there are even some TransactionScope-compatible object models around  i e   NET classes with rollback capability - perhaps easier than a memento  although I ve never used this approach myself    All in all  a very  very useful object   Some caveats    On SQL Server 2000  a TransactionScope will go to DTC immediately  this is fixed in SQL Server 2005 and above  it can use the LTM  much less overhead  until you talk to 2 sources etc  when it is elevated to DTC  There is a glitch that means you might need to tweak your connection string

User · Answer

if you just need it for db-related stuff  some OR Mappers  e g  NHibernate  support transactinos out of the box per default

User · Answer

if you just need it for db-related stuff  some OR Mappers  e g  NHibernate  support transactinos out of the box per default

User · Answer

You could also wrap the transaction up into it s own stored procedure and handle it that way instead of doing transactions in C  itself

User · Answer

It also depends on what you need   For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code   That is the easiest way but it does have complexity and you have to be careful to commit properly  and rollback    I would use something like   SQLTransaction trans   null  using trans   new SqlTransaction                Do SQL stuff here passing my trans into my various SQL executers             trans Commit     May not be quite right     Any failure will pop you right out of the using and the transaction will always commit or rollback  depending on what you tell it to do    The biggest problem we faced was making sure it always committed   The using ensures the scope of the transaction is limited

User · Answer

protected void Button1 Click object sender  EventArgs e                using  SqlConnection connection1   new SqlConnection  Data Source    SQLEXPRESS AttachDbFilename  DataDirectory   Database mdf Integrated Security True User Instance True                        connection1 Open                   Start a local transaction             SqlTransaction sqlTran   connection1 BeginTransaction                   Enlist a command in the current transaction             SqlCommand command   connection1 CreateCommand               command Transaction   sqlTran              try                                Execute two separate commands                 command CommandText                    insert into  doctor  drname drspecialization drday  values   a   b   c                    command ExecuteNonQuery                   command CommandText                    insert into  doctor  drname drspecialization drday  values   x   y   z                    command ExecuteNonQuery                       Commit the transaction                 sqlTran Commit                   Label3 Text    Both records were written to database                            catch  Exception ex                                 Handle the exception if the transaction fails to commit                 Label4 Text   ex Message                   try                                        Attempt to roll back the transaction                     sqlTran Rollback                                    catch  Exception exRollback                                         Throws an InvalidOperationException if the connection                        is closed or the transaction has already been rolled                        back on the server                     Label5 Text   exRollback Message

User · Answer

You could also wrap the transaction up into it s own stored procedure and handle it that way instead of doing transactions in C  itself

User · Answer

It also depends on what you need   For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code   That is the easiest way but it does have complexity and you have to be careful to commit properly  and rollback    I would use something like   SQLTransaction trans   null  using trans   new SqlTransaction                Do SQL stuff here passing my trans into my various SQL executers             trans Commit     May not be quite right     Any failure will pop you right out of the using and the transaction will always commit or rollback  depending on what you tell it to do    The biggest problem we faced was making sure it always committed   The using ensures the scope of the transaction is limited

User · Answer

There are 2 main kinds of transactions  connection transactions and ambient transactions  A connection transaction  such as SqlTransaction  is tied directly to the db connection  such as SqlConnection   which means that you have to keep passing the connection around - OK in some cases  but doesn t allow  create use release  usage  and doesn t allow cross-db work  An example  formatted for space    using  IDbTransaction tran   conn BeginTransaction          try              your code         tran Commit           catch           tran Rollback            throw            Not too messy  but limited to our connection  conn   If we want to call out to different methods  we now need to pass  conn  around   The alternative is an ambient transaction  new in  NET 2 0  the TransactionScope object  System Transactions dll  allows use over a range of operations  suitable providers will automatically enlist in the ambient transaction   This makes it easy to retro-fit into existing  non-transactional  code  and to talk to multiple providers  although DTC will get involved if you talk to more than one    For example   using TransactionScope tran   new TransactionScope          CallAMethodThatDoesSomeWork        CallAMethodThatDoesSomeMoreWork        tran Complete        Note here that the two methods can handle their own connections  open use close dispose   yet they will silently become part of the ambient transaction without us having to pass anything in   If your code errors  Dispose   will be called without Complete    so it will be rolled back  The expected nesting etc is supported  although you can t roll-back an inner transaction yet complete the outer transaction  if anybody is unhappy  the transaction is aborted   The other advantage of TransactionScope is that it isn t tied just to databases  any transaction-aware provider can use it  WCF  for example  Or there are even some TransactionScope-compatible object models around  i e   NET classes with rollback capability - perhaps easier than a memento  although I ve never used this approach myself    All in all  a very  very useful object   Some caveats    On SQL Server 2000  a TransactionScope will go to DTC immediately  this is fixed in SQL Server 2005 and above  it can use the LTM  much less overhead  until you talk to 2 sources etc  when it is elevated to DTC  There is a glitch that means you might need to tweak your connection string

User · Answer

You could also wrap the transaction up into it s own stored procedure and handle it that way instead of doing transactions in C  itself

User · Answer

There are 2 main kinds of transactions  connection transactions and ambient transactions  A connection transaction  such as SqlTransaction  is tied directly to the db connection  such as SqlConnection   which means that you have to keep passing the connection around - OK in some cases  but doesn t allow  create use release  usage  and doesn t allow cross-db work  An example  formatted for space    using  IDbTransaction tran   conn BeginTransaction          try              your code         tran Commit           catch           tran Rollback            throw            Not too messy  but limited to our connection  conn   If we want to call out to different methods  we now need to pass  conn  around   The alternative is an ambient transaction  new in  NET 2 0  the TransactionScope object  System Transactions dll  allows use over a range of operations  suitable providers will automatically enlist in the ambient transaction   This makes it easy to retro-fit into existing  non-transactional  code  and to talk to multiple providers  although DTC will get involved if you talk to more than one    For example   using TransactionScope tran   new TransactionScope          CallAMethodThatDoesSomeWork        CallAMethodThatDoesSomeMoreWork        tran Complete        Note here that the two methods can handle their own connections  open use close dispose   yet they will silently become part of the ambient transaction without us having to pass anything in   If your code errors  Dispose   will be called without Complete    so it will be rolled back  The expected nesting etc is supported  although you can t roll-back an inner transaction yet complete the outer transaction  if anybody is unhappy  the transaction is aborted   The other advantage of TransactionScope is that it isn t tied just to databases  any transaction-aware provider can use it  WCF  for example  Or there are even some TransactionScope-compatible object models around  i e   NET classes with rollback capability - perhaps easier than a memento  although I ve never used this approach myself    All in all  a very  very useful object   Some caveats    On SQL Server 2000  a TransactionScope will go to DTC immediately  this is fixed in SQL Server 2005 and above  it can use the LTM  much less overhead  until you talk to 2 sources etc  when it is elevated to DTC  There is a glitch that means you might need to tweak your connection string

User · Answer

There are 2 main kinds of transactions  connection transactions and ambient transactions  A connection transaction  such as SqlTransaction  is tied directly to the db connection  such as SqlConnection   which means that you have to keep passing the connection around - OK in some cases  but doesn t allow  create use release  usage  and doesn t allow cross-db work  An example  formatted for space    using  IDbTransaction tran   conn BeginTransaction          try              your code         tran Commit           catch           tran Rollback            throw            Not too messy  but limited to our connection  conn   If we want to call out to different methods  we now need to pass  conn  around   The alternative is an ambient transaction  new in  NET 2 0  the TransactionScope object  System Transactions dll  allows use over a range of operations  suitable providers will automatically enlist in the ambient transaction   This makes it easy to retro-fit into existing  non-transactional  code  and to talk to multiple providers  although DTC will get involved if you talk to more than one    For example   using TransactionScope tran   new TransactionScope          CallAMethodThatDoesSomeWork        CallAMethodThatDoesSomeMoreWork        tran Complete        Note here that the two methods can handle their own connections  open use close dispose   yet they will silently become part of the ambient transaction without us having to pass anything in   If your code errors  Dispose   will be called without Complete    so it will be rolled back  The expected nesting etc is supported  although you can t roll-back an inner transaction yet complete the outer transaction  if anybody is unhappy  the transaction is aborted   The other advantage of TransactionScope is that it isn t tied just to databases  any transaction-aware provider can use it  WCF  for example  Or there are even some TransactionScope-compatible object models around  i e   NET classes with rollback capability - perhaps easier than a memento  although I ve never used this approach myself    All in all  a very  very useful object   Some caveats    On SQL Server 2000  a TransactionScope will go to DTC immediately  this is fixed in SQL Server 2005 and above  it can use the LTM  much less overhead  until you talk to 2 sources etc  when it is elevated to DTC  There is a glitch that means you might need to tweak your connection string

User · Answer

It also depends on what you need   For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code   That is the easiest way but it does have complexity and you have to be careful to commit properly  and rollback    I would use something like   SQLTransaction trans   null  using trans   new SqlTransaction                Do SQL stuff here passing my trans into my various SQL executers             trans Commit     May not be quite right     Any failure will pop you right out of the using and the transaction will always commit or rollback  depending on what you tell it to do    The biggest problem we faced was making sure it always committed   The using ensures the scope of the transaction is limited

User · Answer

It also depends on what you need   For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code   That is the easiest way but it does have complexity and you have to be careful to commit properly  and rollback    I would use something like   SQLTransaction trans   null  using trans   new SqlTransaction                Do SQL stuff here passing my trans into my various SQL executers             trans Commit     May not be quite right     Any failure will pop you right out of the using and the transaction will always commit or rollback  depending on what you tell it to do    The biggest problem we faced was making sure it always committed   The using ensures the scope of the transaction is limited

[c#] Transactions in .net

Examples related to c#

Examples related to .net

Examples related to transactions