- Hour 18: Working with Transactions
- Transactions and ADO.NET
- Transactions with Stored Procedures
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 3–11. 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 21–42 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 44–48. 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 55–64 to add a territory for that new employee to the EmployeeTerritories table. Again, lines 66–70 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.