ADO.NET 3.5 Cookbook, 2nd Edition

7.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 try-catch block as shown in Example 7-5.

The solution uses a table named SimultaneousTransaction in the database AdoDotNet35Cookbook. Execute the following T-SQL to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE SimultaneousTransaction(
	    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The solution uses a single stored procedure named InsertSimultaneousTransaction that inserts a single record into the SimultaneousTransaction table within a DBMS transaction. If the record insert fails, the transaction is rolled back; otherwise, the transaction is committed. The solution takes an argument named @RollbackFlag that causes the DBMS transaction to be rolled back simulating a failure. Create the stored procedure by executing the T-SQL code in Example 7-6.

Example 7-6. Stored procedure: InsertSimultaneousTransaction

USE AdoDotNet35Cookbook
GO

CREATE PROCEDURE InsertSimultaneousTransaction
    @Id int output,
    @Field1 nvarchar(50),
    @Field2 nvarchar(50),
    @RollbackFlag bit = 0
AS
    SET NOCOUNT ON

    BEGIN TRAN

    INSERT SimultaneousTransaction( Field1, Field2) VALUES ( @Field1, @Field2) IF @@ERROR <> 0 OR @@ROWCOUNT = 0 OR @RollbackFlag = 1 BEGIN ROLLBACK TRAN SET @Id = -1 RETURN 1 END COMMIT TRAN SET @ID = SCOPE_IDENTITY( ...