Backing Out Of A SQL Transaction

When writing MS SQL statements, sometimes it’s necessary to have several queries that work together in unison.  Whether it’s updating, deleting or inserting multiple rows into multiple, different tables, it’s nice to be able to run a series of SQL commands as one single step.  This is where running a batch transaction comes in handy, as the following set of sample code demonstrates.

BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('This is a sample value.');
INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL');
INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.');
COMMIT TRANSACTION BigGroupInsert;

As you can see, this transaction will perform all of the Insert statements specified.  If there is an error processing one of the statements, like one of the values being to large for the target column, an error will occur for just that statement and all of the rest of the SQL commands will process.  That includes commands that were intended to occur after the failed step, not just the ones before.

But it’s possible this is not the correct course of action to take.  What if the entire batch needs to either perform all of the changes correctly or not make any changes at all?  It could be bad for data integrity if some tables have the updated rows, but related data is missing from other tables because of a SQL error.  Well fear not, there is a solution.  Add the command SET XACT_ABORT ON before the BEGIN TRANSACTION command to have SQL back out all changes in the Transaction if any error occurs.  This way the transaction works as a single unit and you can be guaranteed that that the entire transaction either worked or it didn’t, and there is nothing in between.

SET XACT_ABORT ON;
BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('If one of these values was too long.');
INSERT INTO TestTable2 (Field1, Field2) values ('Then this whole transaction', 'should fail');
INSERT INTO TestTable3 (Field1) values ('And none of these values should be inserted.');
COMMIT TRANSACTION BigGroupInsert;

For more information about SET XACT_ABORT, please refer to the MSDN article.

Serialization and Deserialization

Serialization is a process of converting an object into a stream of data. So it easily transfers over the network or can save to disk.   So, using this concept of serialization, we can serialize any object to XML string.
Here is a person class that we can serialize.

Here is the code that takes the person object into string of xml format.

Using Serialization in .NET is provided by the System.Runtim.Serialization name space.

Once you have the XML string, now you can save it to disk, store it into database, or transfer over the network.
Here is the deserialization method that takes serialized string of XML into the person object.

 

Kyoungsu Do
Software Quality Engineer
ImageSource, Inc.