Calling Stored Procedures with ADO.NET

Filling a DataTable

An ADO.NET DataTable represents an in-memory resultset. The DataTable contains the results of one SELECT statement, whether the resultset draws that data from a view or multiple tables.

To invoke SelectCustomer and fill a DataTable, you need to incorporate a SqlDataAdapter into your code. The SqlDataAdapter uses the SqlCommand object, representing the command when you call SqlDatAdapter.Fill. Listing 7 shows an example running against your result set.

Listing 7: Filling a DataTable Using SelectCustomer and a SqlDataAdapter

Public Sub SelectCustomer()
    Dim connection As SqlConnection = _
        New SqlConnection(connectionString)
    connection.Open()
    Try

        Dim command As SqlCommand = _
            New SqlCommand("SelectCustomer", _ connection)
        command.Parameters.Add("@CustomerID", "PAULK")
        command.CommandType = CommandType.StoredProcedure

        Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)

        Dim table As DataTable = New DataTable
        adapter.Fill(table)

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Throw
    Finally
        connection.Close()
    End Try
End Sub

Because the SelectCustomer command requires only one parameter, the CustomerID, you reduced the number of parameters, created a SqlDataAdapter by initializing it with the SqlCommand object, created a DataTable, and called SqlDataAdapter.Fill to fill the DataTable. If an external consumer wanted to use the DataTable, you would need only change your code to a function and return the DataTable right after the adapter.Fill(table) statement. The rest of the code would remain unchanged.

Invoking DeleteCustomer

You can delete a customer by copying Listing 6 almost intact. All you need to do is exchange the InsertCustomer argument with DeleteCustomer and remove all of the parameters except for the first one, CustomerID.

To convert the code to a general utility function, define a method and pass in the CustomerID. I used this technique to create a general cleanup function to facilitate removing test customers. Listing 8 shows the delete capability as a general utility function.

Listing 8: Delete-a-customer Method

Public Sub DeleteCustomer(Optional ByVal commandID As String = _
                          "PAULK")
    Dim connection As SqlConnection = _
        New SqlConnection(connectionString)
    connection.Open()
    Try
        Dim command As SqlCommand = _
            New SqlCommand("DeleteCustomer", connection)
        command.CommandType = CommandType.StoredProcedure

        command.Parameters.Add("@CustomerID", commandID)

        Console.WriteLine("Rows deleted: " + _
                          command.ExecuteNonQuery().ToString())

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Throw
    Finally
        connection.Close()
    End Try
End Sub

The Optional modifier in Visual Basic .NET supports a funny kind of polymorphism. You could write two DeleteCustomer methods. The first DeleteCustomer might take no parameters and call a second DeleteCustomer passing what is the default optional value in the method in Listing 8. The second DeleteCustomer would then take the string parameter. Behaviorally, this would feel just like a polymorphic method call. With Visual Basic .NET, you can use variations of methods with the Optional parameter and polymorphism.

Fetching Output Parameters

Suppose you elected to generate the CustomerID from the company name rather than let the user or code dictate the CustomerID. In this instance, the stored procedure would generate, as well as return, the CustomerID. To do this, you need to modify the CustomerID parameter of the InsertCustomer stored procedure to be an OUTPUT parameter and add an algorithm to generate the CustomerID. Listing 9 shows a modified stored procedure with the aforementioned adjustments. (The algorithm for CustomerID is not robust enough for practical use but could easily be replaced at a later time.)

Listing 9: Having the Stored Procedure Define the Primary Key and Return It as an Output Parameter.

ALTER PROCEDURE dbo.InsertCustomerCreateKey
    (
        @CustomerID      nchar(5) = '' OUTPUT,
        @CompanyName     nvarchar(40),
        @ContactName     nvarchar(30),
        @ContactTitle    nvarchar(30),
        @Address         nvarchar(60),
        @City            nvarchar(15),
        @Region          nvarchar(15),
        @PostalCode      nvarchar(10),
        @Country         nvarchar(15),
        @Phone           nvarchar(24),
        @Fax             nvarchar(24)
    )
AS
    /* Generate unique key if possible */
    SET @CustomerID = LEFT(@CompanyName, 5)
        
    INSERT INTO Customers
    (
        CustomerID,
        CompanyName,
        ContactName,
        ContactTitle,
        Address,
        City,
        Region,
        PostalCode,
        Country,
        Phone,
        Fax
    )
    VALUES
    (
        @CustomerID,
        @CompanyName,
        @ContactName,
        @ContactTitle,
        @Address,
        @City,
        @Region,
        @PostalCode,
        @Country,
        @Phone,
        @Fax
    )

RETURN @@ROWCOUNT

Obtaining and using the output parameter requires a minor adjustment to the code that calls InsertCustomer. In short, after you create the CustomerID parameter, you need to specify the type, size, and direction. The function FetchOutputParameter (in Listing 10) demonstrates these changes. The additional or revised lines of code are in boldface.

Listing 10: FetchOutputParameter Demonstrates How to Use Output Parameters.

Public Function FetchOutputParameter() As String
    Dim connection As SqlConnection = _
        New SqlConnection(connectionString)
    connection.Open()
    Try
        Dim command As SqlCommand = _
            New SqlCommand("InsertCustomerCreateKey", connection)
        command.CommandType = CommandType.StoredProcedure

        command.Parameters.Add("@CustomerID", "")
        command.Parameters(0).SqlDbType = SqlDbType.NChar
        command.Parameters(0).Size = 5
        command.Parameters(0).Direction = ParameterDirection.Output

        command.Parameters.Add("@CompanyName", "Pauly's Bar")
        command.Parameters.Add("@ContactName", "Paul Kimmel")
        command.Parameters.Add("@ContactTitle", "The Fat Man")
        command.Parameters.Add("@Address", "31025 La Jolla")
        command.Parameters.Add("@City", "Inglewood")
        command.Parameters.Add("@Region", "CA")
        command.Parameters.Add("@Country", "USA")
        command.Parameters.Add("@PostalCode", "90425")
        command.Parameters.Add("@Phone", "(415) 555-1234")
        command.Parameters.Add("@Fax", "(415) 555-1235")

        Console.WriteLine("Rows inserted: " + _
            command.ExecuteNonQuery().ToString())
        Console.WriteLine("Generated key is: " + _
            command.Parameters(0).Value)

        Return command.Parameters(0).Value

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Throw
    Finally
        connection.Close()
    End Try
End Function

Page 3 of 4