Perform reads and writes in the same transaction

I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation.

`Public Shared Sub Process(wwid As String, trade_id As Integer, disposition As Boolean)

Dim q As String
Dim cmd, cmd_select As SqlCommand
Dim reader As SqlDataReader
Dim trans As SqlTransaction

Dim user_id As Integer = User.CheckAuthentication(wwid)
If user_id > 0 Then
Using conn As New SqlConnection(CNGDB)
conn.Open()
'1. ReadUncommitted
'2. ReadCommitted
'3. RepeatableRead
'4. Serializable
'5. Snapshot
trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted)
Try
q = "UPDATE Trades SET Disposition = @disposition, FinalizedAt = @finalized_at" & _
" WHERE TradeID = @trade_id"
cmd = New SqlCommand(q, conn)
cmd.Transaction = trans
cmd.Parameters.AddWithValue("@disposition", disposition)
cmd.Parameters.AddWithValue("@finalized_at", DateTime.Now)
cmd.Parameters.AddWithValue("@trade_id", trade_id)
cmd.ExecuteNonQuery()
If disposition = True Then
q = "SELECT Ownership_OwnershipID, Recipient_UserID FROM Trades" & _
" WHERE TradeID = @trade_id"
cmd_select = New SqlCommand(q, conn)
cmd_select.Transaction = trans
cmd_select.Parameters.AddWithValue("@trade_id", trade_id)
reader = cmd_select.ExecuteReader
reader.Read()
q = "UPDATE Ownerships SET User_UserID = @recipient_id" & _
" WHERE OwnershipID = @ownership_id"
cmd = New SqlCommand(q, conn)
cmd.Transaction = trans
cmd.Parameters.AddWithValue("

First of all I agree completely with Aaron Bertrand when he suggested in the comments you move the SQL into a stored procedure. This is also the clear winner as far as "the proper way to wrap a set of SQL operations in a .NET program" by the way.

Since you're worried about managing code in two places you should check out database projects in Visual Studio. I've been using these for a while now, and it works really well. You even get some handy, albeit limited, refactoring tools when you go this route.

I see several problems with your Visual Basic code:

• You aren't disposing your SqlCommand object properly.

• You aren't disposing your SqlDataReader object properly.

• You aren't actually doing anything with the data returned from the reader so it is superfluous.

• You are reusing the variable cmd when disposition = True, instead of using another variable. I'm not sure, but I believe, this will prevent the transaction from rolling back the initial update. Regardless this isn't a best practice in my opinion.

• You should probably move away from using the .AddWithValue() method. As described in this blog post, you can run into problems due to implicit conversions.

Also the reason why closing the SqlDataReader worked as suggested by Mister Magoo in the comments and confirmed in your comment was because the reader is preventing anything else from happening on the connection until it is closed. Here is a quote from the documentation:


While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

For more/related shenanigans that can be caused by the way you're using/abusing the reader check out this answer on Stack Overflow.

A couple more thoughts based on your comment:

One easy way to populate a database project that is created after a database already exists is to use the "Schema Compare" functionality. In my version of Visual Studio this functionality is found in the menu bar under: Tools --> SQL Server --> New Schema Comparison. You may need to download the latest version of the SSDT(free) for your version of Visual Studio to get this functionality.

I agree that you probably shouldn't dedicate the next few weeks just to moving your inline SQL into stored procedures. As I've mentioned in the past blocking updates while you refactor core elements of your application is rarely the best plan. You can choose to move all new data access into stored procedures, and decide to move SQL into stored procedures when you have to touch the inline SQL. This will be minimally invasive, spread the work out over time, and allow you to systematically improve your code base (VB.NET and SQL).

StackExchange Database Administrators Q#95390, answer score: 8