Eureka!

Transaction processing is an absolutely vital part of developing database applications. It allows for commits and rollbacks. So, in the event that something unforseen should occur, you can roll back all your SQL statements and handle the exception, then make the necessary adjustments, and run the scripts again. Here is a short example:

Dim objConn As New SqlConnectionDim objComm As New SqlCommandDim objTran As SqlTransaction

Try   objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"   objConn.Open()   objTran = objConn.BeginTransaction(IsolationLevel.ReadCommitted)   objComm.Connection = objConn   objComm.CommandType = CommandType.Text   objComm.CommandText = "INSERT INTO blog (entry) VALUES ('" + txtEntry.Text + "')"   objComm.Transaction = objTran   objComm.ExecuteNonQuery()   objTran.Commit()   objConn.Close()   LoadBlog()Catch objError As Exception   objTran.Rollback()   MessageBox.Show(objError.Message)End Try

You will immediately notice that I roll back any SQL queries that happened after the transaction object was set if any error should occur. This means that if anything goes wrong, the database does not get updated. You can see where this is going: for maintenance, batch processes, etc., you should always be using transaction processes, so that if one statement should fail, it wont corrupt other dependant data.

Be the first to comment! :)