Calling Stored Procedures with ADO.NET

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.

An Optimal Approach

Related Articles

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).