6.4. Using ADO.NET and SQL Server DBMS Transactions Together
Problem
You need to use a DBMS transaction within a SQL Server stored procedure from an ADO.NET transaction with the SQL Server .NET data provider.
Solution
Use error-checking within a catch
block
as shown in Example 6-5.
The sample uses a single stored procedure:
InsertCategories_Transacted
Used to insert a single record into the Categories table in the Northwind database within a DBMS transaction. If the record insert fails, the transaction is rolled back; otherwise, the transaction is committed.
The sample code contains two event handlers:
Form.Load
Sets up the sample by filling a
DataTable
with the Categories table from the Northwind sample database. The default view of the table is bound to a data grid on the form.- Insert
Button.Click
Inserts user-entered data for the Categories records into the Northwind database within a manual transaction using a DBMS transacted stored procedure. The transaction is rolled back in the stored procedure if either the Force DBMS Rollback checkbox is checked or if no value is entered for the Category Name field. Otherwise, the ADO.NET manual transaction is committed.
Example 6-5. Stored procedure: InsertCategories_Transacted
CREATE PROCEDURE InsertCategories_Transacted @CategoryId int output, @CategoryName nvarchar(15), @Description ntext, @Rollback bit = 0 AS SET NOCOUNT ON begin tran insert Categories( CategoryName, Description) values ( @CategoryName, @Description) if @@error<>0 or @@rowcount=0 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.