Transactions with Stored Procedures
Database transactions are by no means limited to ADO.NET. In fact, transactions are powered by the data source. That is to say, when you initiate a transaction inside ADO.NET, the data source (in this case Microsoft SQL Server) actually performs all the work.
To illustrate this point, the example in Listing 18.4 demonstrates how to perform a database transaction inside a stored procedure. It's very similar to an example used in a previous hour that adds an employee to the Employees table of the Northwind SQL Server database. This procedure also assigns a territory to the newly added employee as well, all rolled into a transaction.
To start a transaction inside a stored procedure in Microsoft SQL Server, you use the BEGIN TRAN keywords. To roll back a transaction, you use ROLLBACK TRAN and as you might have guessed, to commit a transaction, you use COMMIT TRAN.
Listing 18.4 Rolling a Transaction Back to a Saved Point
CREATE PROCEDURE Employee_Add ( @LastName nvarchar(20), @FirstName nvarchar(10), @Title nvarchar(30), @BirthDate datetime, @HireDate datetime, @Address nvarchar(60), @City nvarchar(15), @PostalCode nvarchar(10), @TerritoryID nvarchar(20) ) AS DECLARE @iCommunityProductAuditID int BEGIN TRAN -- add the main record INSERT INTO Employees ( LastName, FirstName, Title, BirthDate, HireDate, Address, City, PostalCode ) VALUES ( @LastName, @FirstName, @Title, @BirthDate, @HireDate, @Address, @City, @PostalCode ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN @@ERROR END -- get EmployeeID declare @EmployeeID int SET @EmployeeID = @@IDENTITY -- add employee to a territory INSERT INTO EmployeeTerritories ( EmployeeID, TerritoryID ) VALUES ( @EmployeeID, @TerritoryID ) IF @@ERROR <> 0 BEGIN ROLLBACK TRAN RETURN @@ERROR END COMMIT TRAN RETURN @@ERROR GO
The Employee_Add stored procedure in Listing 18.4 accepts a relatively large list of parameters in lines 311. As you'll recall from Hour 15, "Working with Stored Procedures," these are the same as function arguments. Line 18 begins a transaction within the stored procedure. Lines 2142 add a record into the Employees table using the values supplied in the parameters.
If any errors were encountered while performing the INSERT query, the transaction is rolled back using the ROLLBACK TRAN SQL statement in lines 4448. In line 52, the automatically incremented identity number created for the newly added employee is assigned to the @EmployeeID variable. The @EmployeeID is then used in lines 5564 to add a territory for that new employee to the EmployeeTerritories table. Again, lines 6670 ensure that if any errors were encountered, the entire transaction is rolled back; this means that not only will the EmployeeTerritories entry be removed, but also the entry made for the new employee in the Employees table. On line 72, the transaction is commited.