Posted By

stewshack on 05/19/09


Tagged

database c


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

umang_nine


SqlTransaction


 / Published in: C#
 

Here is how I wrap database updates into a SQL transaction.

  1. SqlConnectionStringBuilder DarthServerConnection = new SqlConnectionStringBuilder();
  2.  
  3. DarthServerConnection.DataSource = "DarthServer";
  4. DarthServerConnection.InitialCatalog = "DeathStarDB1";
  5. DarthServerConnection.UserID = "tk421";
  6. DarthServerConnection.Password = "tr00p3r";
  7.  
  8. using ( SqlConnection DeathStarDatabase = new SqlConnection( DarthServerConnection.ToString() ) )
  9. {
  10. // The using statment will call dispose and close the connection.
  11. DeathStarDatabase.Open();
  12.  
  13. SqlTransaction SaveHeroes = DeathStarDatabase.BeginTransaction();
  14.  
  15. string ShutDownSql = "UPDATE GarbageDisposal SET Status = 'Shut Down'";
  16. string OpenDoorSql = "UPDATE GarbageDisposal SET Door = 'Open' WHERE GarbageDisposalID = 3263827";
  17.  
  18. try
  19. {
  20. // Shut down the garbage disposal units on the detention level! No! Shut them all down!
  21. SqlCommand ShutDown = new SqlCommand( ShutDownSql, DeathStarDatabase, SaveHeroes );
  22. ShutDown.ExecuteNonQuery();
  23.  
  24. // We're alive. Open the door to 3263827.
  25. SqlCommand OpenDoor = new SqlCommand( OpenDoorSql, DeathStarDatabase, SaveHeroes );
  26. OpenDoor.ExecuteNonQuery();
  27.  
  28. SaveHeroes.Commit();
  29. }
  30. catch ( Exception error )
  31. {
  32. SaveHeroes.Rollback();
  33.  
  34. MailMessage RescueEmail = new MailMessage( "[email protected]", "[email protected]" );
  35.  
  36. RescueEmail.Subject = "Error while Saving Heroes";
  37. RescueEmail.Body = "An error has occured while attempting to save the following heroes:\n\r"
  38. + "Luke, Leia, Han, and Chewbacca.\n\r"
  39. + "The SQL statements that attempted to execute were:\n\r"
  40. + ShutDownSql + "\n\r"
  41. + OpenDoorSql + "\n\r"
  42. + "The error:\n\r"
  43. + error.ToString() + "\n\r"
  44. + "Result:\n\r"
  45. + "The heroes were not saved.\n\r"
  46. + "Action Item:\n\r"
  47. + "Please rescue R2D2 and C3PO from the Death Star.";
  48.  
  49. SmtpClient YavinIVMail = new SmtpClient();
  50.  
  51. YavinIVMail.Host = "mail.yaviniv.org";
  52. YavinIVMail.Credentials = CredentialCache.DefaultNetworkCredentials;
  53. YavinIVMail.Send( RescueEmail );
  54. }
  55. }

Report this snippet  

You need to login to post a comment.