{"id":220,"date":"2007-06-28T15:53:46","date_gmt":"2007-06-28T20:53:46","guid":{"rendered":"\/cgi\/wp\/?p=220"},"modified":"2019-12-11T08:39:28","modified_gmt":"2019-12-11T13:39:28","slug":"transaction-savepoints","status":"publish","type":"post","link":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/?p=220","title":{"rendered":"Transaction Savepoints"},"content":{"rendered":"<div class=\"entry\">\n<p>Today I learned how to roll back multiple database queries using transaction savepoints. This is useful when you have several related record inserts, updates, or deletes that should all succeed or all fail in order to maintain your data integrity. You can associate a transaction with a database connection, create a transaction savepoint, and then rollback all your queries if there is an error or commit the queries. Since I am mostly programming in C# nowadays I will provide sample code in C#:<\/p>\n<pre>string strSQL;\r\nSqlCommand objCmd = new SqlCommand();\r\nSqlConnection objCn = new SqlConnection(strCn);\r\nobjCn.Open();\r\n<span style=\"background-color: #ffff00;\">SqlTransaction transaction = objCn.BeginTransaction();<\/span>\r\n<span style=\"color: #008000;\">\/\/ create a savepoint to rollback all database changes if necessary<\/span>\r\n<span style=\"background-color: #ccffcc;\">transaction.Save(\"BeforeInserts\");<\/span>\r\ntry {\r\nstrSQL = \"INSERT INTO table_name VALUES(column_name, column_name)\";\r\nobjCmd.Connection = objCn;\r\nobjCmd.CommandText = strSQL;\r\n<span style=\"background-color: #ffff00;\">objCmd.Transaction = transaction;<\/span>\r\nobjCmd.ExecuteNonQuery();\r\nstrSQL = \"INSERT INTO table_name VALUES(column_name, column_name)\";\r\nobjCmd.Connection = objCn;\r\nobjCmd.CommandText = strSQL;\r\n<span style=\"background-color: #ffff00;\">objCmd.Transaction = transaction;<\/span>\r\nobjCmd.ExecuteNonQuery();\r\n}\r\ncatch (SqlException ex)\r\n{\r\nlblMessage.Text = \"Error: \" + ex.Message + \"rn\" + ex.StackTrace + \"rn\";\r\n<span style=\"color: #008000;\">\/\/ rollback all database changes<\/span>\r\n<span style=\"background-color: #ccffcc;\">transaction.Rollback(\"BeforeInserts\");<\/span>\r\n}\r\nfinally\r\n{\r\n<span style=\"background-color: #ffff00;\">transaction.Commit();<\/span>\r\n<span style=\"color: #008000;\">\/\/ additional database changes cannot use the transaction after it has been committed<\/span>\r\nstrSQL = \"UPDATE table_name SET column_name = '\"\"' + value + \"'\";\r\nobjCmd.CommandText = strSQL;\r\nobjCmd.ExecuteNonQuery();\r\nobjCn.Close();\r\n}<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Today I learned how to roll back multiple database queries using transaction savepoints. This is useful when you have several related record inserts, updates, or deletes that should all succeed or all fail in order to maintain your data integrity. &hellip; <a href=\"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/?p=220\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"_links":{"self":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts\/220"}],"collection":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=220"}],"version-history":[{"count":3,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts\/220\/revisions"}],"predecessor-version":[{"id":3430,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=\/wp\/v2\/posts\/220\/revisions\/3430"}],"wp:attachment":[{"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/williamsportwebdeveloper.com\/cgi\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}