May 22, 2019
Hot Topics:

Calling Stored Procedures with ADO.NET

  • November 19, 2004
  • By Paul Kimmel
  • Send Email »
  • More Articles »

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)
    IF((SELECT COUNT(@RegionID) FROM Region
        WHERE RegionDescription = @RegionDescription) = 0)
        SELECT @RegionID = MAX(RegionID) + 1
        FROM Region NOLOCK

        INSERT INTO Region

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

        ' 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: " +

            Dim insertRegion As SqlCommand = _
                New SqlCommand("InsertRegion", _ connection)

            insertRegion.Parameters.Add("@RegionID", Nothing)
            insertRegion.Parameters(0).Direction = _
            insertRegion.Parameters.Add("@RegionDescription", "CA")

            Console.WriteLine("Rows inserted: " +


    Catch ex As Exception
        Console.WriteLine("Rolled back transaction, " + ex.Message)
    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.


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.

Page 4 of 4

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date