Using ADO.NET Transactions
Let's wrap up with transactions. You can use a transaction for every interaction that changes data, but you don't have to. However, if you are making multiple dependent changes, transactions are a must.
ADO.NET makes using transactions simple. All you need to do is declare a transaction, call SqlConnection.BeginTransaction to start the transaction, and have every command use that connection and transaction to participate in the transaction. In the try block, call commit after all commands have executed and in the except block call SqlTransaction.Rollback. The finally block still closes the connection.
By using this technique, you can make sure that multiple dependent modifications to a database all either succeed or fail. Maintaining consistency in your database is an imperative in database programming.
In this example, the code calls InsertCustomer and InsertRegion. If the RegionDescription is not found in the Region table, a row is added to the Region table as part of the transaction. Listing 11 shows the new InsertRegion method with a conditional test, and Listing 12 shows the function UseTransaction that invokes both stored procedures, showing the placement of the new transaction code.
Listing 11: The InsertRegion Stored Procedure with an IF-conditional Test.
ALTER PROCEDURE dbo.InsertRegion
(
@RegionID int = NULL OUTPUT,
@RegionDescription nchar(50)
)
AS
IF((SELECT COUNT(@RegionID) FROM Region
WHERE RegionDescription = @RegionDescription) = 0)
BEGIN
SELECT @RegionID = MAX(RegionID) + 1
FROM Region NOLOCK
INSERT INTO Region
(
RegionID,
RegionDescription
)
VALUES
(
@RegionID,
@RegionDescription
)
END
RETURN @@ROWCOUNT
Listing 12: Using the SqlTransaction Object to Treat Two Insert-Stored Procedures as a Homogeneous Operation.
Public Sub UseTransaction()
Dim connection As SqlConnection = _
New SqlConnection(connectionString)
Dim transaction As SqlTransaction = connection.BeginTransaction
connection.Open()
Try
' Insert Customer
Dim insertCustomer As SqlCommand = _
New SqlCommand("InsertCustomer", connection)
insertCustomer.CommandType = CommandType.StoredProcedure
insertCustomer.Transaction = transaction
insertCustomer.Parameters.Add("@CustomerID", "PAULK")
insertCustomer.Parameters.Add("@CompanyName", "Pauly's Bar")
insertCustomer.Parameters.Add("@ContactName", "Paul Kimmel")
insertCustomer.Parameters.Add("@ContactTitle", "The Fat Man")
insertCustomer.Parameters.Add("@Address", "31025 La Jolla")
insertCustomer.Parameters.Add("@City", "Inglewood")
insertCustomer.Parameters.Add("@Region", "CA")
insertCustomer.Parameters.Add("@Country", "USA")
insertCustomer.Parameters.Add("@PostalCode", "90425")
insertCustomer.Parameters.Add("@Phone", "(415) 555-1234")
insertCustomer.Parameters.Add("@Fax", "(415) 555-1235")
Console.WriteLine("Rows inserted: " +
insertCustomer.ExecuteNonQuery().ToString)
Dim insertRegion As SqlCommand = _
New SqlCommand("InsertRegion", _ connection)
insertRegion.Parameters.Add("@RegionID", Nothing)
insertRegion.Parameters(0).Direction = _
ParameterDirection.Output
insertRegion.Parameters.Add("@RegionDescription", "CA")
Console.WriteLine("Rows inserted: " +
insertRegion.ExecuteNonQuery().ToString)
transaction.Commit()
Catch ex As Exception
transaction.Rollback()
Console.WriteLine("Rolled back transaction, " + ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
An Optimal Approach
Software developers have to make many choices. Should the constraints move with the database or the code, or should constraints be repeated in both places? Should SQL be inline in your VB.NET code or in stored procedures? There are many schools of thought on both sides of the fence.
A thorough, maximal implementation means that the database is complete, stands alone, and is ready for any code to be layered on top of it. A thorough implementation also means that the code is complete, stands alone, and won't send bad data to the database—whether the database would accept the bad data or not. This maximal approach also takes a maximal amount of time. An optimal approach means that you use the rules and techniques necessary to cover the use cases thoroughly; unfortunately, programmers are often asked to implement an optimal solution on a minimal budget and schedule.
After reading this article, you should be better prepared for the pragmatics of the world in which you live.
Biography
Paul Kimmel is the VB Today columnist, has written several books on .NET programming, and is a software architect. Look for his upcoming book UML DeMystified from McGraw-Hill/Osborne, Spring 2005. You may contact him at pkimmel@softconcepts.com if you need assistance developing software or are interested in joining the Lansing Area .NET Users Group (glugnet.org).
Copyright © 2004 by Paul Kimmel. All Rights Reserved.
IE 7
Firefox 2.0
