在同一個事務中執行讀取和寫入

我正在嘗試在我的應用程序中實現事務。我只是想像 BeginTransaction() 文件中顯示的範例那樣實現它。

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("@recipient_id", reader("Recipient_UserID"))
                   cmd.Parameters.AddWithValue("@ownership_id", reader("Ownership_OwnershipID"))
                   cmd.ExecuteNonQuery()
               End If
               trans.Commit()
           Catch ex As Exception
               Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
               Console.WriteLine("  Message: {0}", ex.Message)
               Try
                   trans.Rollback()
               Catch ex2 As Exception
                   Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                   Console.WriteLine("  Message: {0}", ex2.Message)
               End Try
           End Try

       End Using
   End If

End Sub

問題是，每當它遇到 Commit() 時，我都會收到錯誤消息：

送出異常類型：System.Data.SqlClient.SqlException

消息：無法執行事務操作，因為有處理此事務的待處理請求。

我預計問題在於我試圖從 中讀取SELECT以填充 second 中的值UPDATE，但我不知道它還能如何完成。我嘗試將數據庫IsolationLevel設置為其他一些值，但它並沒有改變任何東西。

我還找到了TransactionScope類，並為此程式碼實現了它的範例。但是，當我嘗試將該技術應用於一組更複雜的操作時，它抱怨說它無法與我本地電腦的分佈式事務伺服器通信。我打開它，DTS 取消了另一個更複雜的交易，原因不明，我不想掉進那個兔子洞。

誰能指出我在程式碼中做錯了什麼？

此外，在 .NET 程序中包裝一組 SQL 操作的正確方法是什麼？也許 DTS 是更好的選擇，但這個應用程序最終將存在於 Azure 數據庫中，而且 Azure 似乎也不支持 DTS，儘管有一些關於“自動升級”隔離級別的說法，我很困惑.

為了速度，我在這個應用程序中簡單地使用了儲存過程，但發現它並沒有更快，並決定我不想嘗試在數據庫中維護任何程式碼。相反，我想將我的數據庫訪問庫與我的 GUI 前端應用程序（在 VB 中）一起保存在 Visual Studio 中，以便（大部分）在一個地方處理它。

首先，當Aaron Bertrand在評論中建議您將 SQL 移動到儲存過程中時，我完全同意他的觀點。順便說一句，就“在 .NET 程序中包裝一組 SQL 操作的正確方法”而言，這也是明顯的贏家。

由於您擔心在兩個地方管理程式碼，您應該在 Visual Studio 中查看數據庫項目。我已經使用了一段時間了，效果非常好。當你走這條路時，你甚至會得到一些方便但有限的重構工具。

我發現您的 Visual Basic 程式碼存在幾個問題：

• 您沒有正確處理 SqlCommand 對象。
• 您沒有正確處理 SqlDataReader 對象。
• 您實際上並沒有對從閱讀器返回的數據做任何事情，所以它是多餘的。
• 您正在重用變數cmdwhen disposition = True，而不是使用另一個變數。我不確定，但我相信，這將阻止事務回滾初始更新。無論如何，這不是我認為的最佳實踐。
• 您可能應該停止使用該.AddWithValue()方法。如本博文中所述，由於隱式轉換，您可能會遇到問題。

此外，按照Magoo 先生在評論中的建議並在您的評論中確認關閉SqlDataReader工作的原因是因為讀者正在阻止連接上發生任何其他事情，直到它被關閉。這是文件中的引用：

在使用 SqlDataReader 時，關聯的 SqlConnection 正忙於為 SqlDataReader 提供服務，除了關閉 SqlConnection 之外，無法對 SqlConnection 執行其他操作。在呼叫 SqlDataReader 的 Close 方法之前就是這種情況。例如，在呼叫 Close 之前，您無法檢索輸出參數。

有關可能由您使用/濫用讀者的方式引起的更多/相關惡作劇，請查看Stack Overflow 上的這個答案。

---

根據您的評論，還有一些想法：

填充在數據庫已經存在之後創建的數據庫項目的一種簡單方法是使用“模式比較”功能。在我的 Visual Studio 版本中，此功能位於以下菜單欄中：工具 –> SQL Server –> 新架構比較。您可能需要為您的 Visual Studio 版本下載最新版本的 SSDT（免費）才能獲得此功能。

我同意您可能不應該在接下來的幾週內將您的內聯 SQL 轉移到儲存過程中。正如我在過去提到的那樣，在重構應用程序的核心元素時阻止更新很少是最好的計劃。您可以選擇將所有新數據訪問移動到儲存過程中，並在必須觸及內聯 SQL 時決定將 SQL 移動到儲存過程中。這將是微創的，隨著時間的推移分散工作，並允許您系統地改進您的程式碼庫（VB.NET和SQL）。

引用自：https://dba.stackexchange.com/questions/95390