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.
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
Comment and Contribute