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.

1. When working with datareaders and stored procedures, and your stored procedure returns a paramter value, you must close the datareader before you can access the parameter value. Microsoft says its not a bug, but missing functionality is just as bad as erroneous funcitonality. So: BUG!
2. You can call .ExecuteScalar on queries that return multiple rows (for some reason, eventhough it makes no sense), however, it will only return the first field of the first row.
3. You can bind datareaders to web objects when in ASP.NET, however, you can only bind datareaders to datasets, data tables, and data views when working in VB.NET.
4. You must ‘prime’ a datareader before accessing its content, like so:

   While reader.Read()[...]End While

5. When retrieving data from datareaders using the various get methods, only the SQL data type get methods allow for NULL values. Using the get methods uses less overhead, since the data is returned as that explicit type and not as an object.
6. Multiple selects can be executed at the same time using a single data reader. This can be usefull for minimizing resource usage when initializing data when opening an application or loading a web app. Consider the following (note reader.NextResult(), which moves to the next SQL statement in the datareader results):

   comm.CommandText = "SELECT * FROM customers;SELECT * FROM products"reader = comm.ExecuteReaderWhile reader.Read()   [...]End WhileIf (reader.NextResult()) Then   While reader.Read()       [...]   End WhileEnd If