4.3 Execute Parameterized Stored Procedures in ADO.NET
To take advantage of stored procedures to their full power, you need to be able to pass
parameters so that specific criteria can be used. This How-To describes how to create
parameters off the OleDbCommand objects to pass parameters to SQL Server.
You need to execute a parameterized stored procedure in your application. How do you
do this using Visual Basic .NET and ADO.NET?
Technique
In ADO, you have a Command object to execute stored procedures, among other tasks. In
ADO.NET, you also have a Command object that performs basically the same task. In
fact, many of the properties and methods that you use are the same. You can see a list of
those in Table 4.4.
Table 4.4. Objects That Are Used for This Technique, with Properties and Methods
Object Property Description/Method
Connection ConnectionString Contains the connection string that is used.
Connection Open Opens the connection that the Command object uses.
Command cmdText Specifies the SQL statement to use. Can be SQL
statement or names of objects such as tables or stored
procedures.
Command Connection Uses the Connection object.
Command CommandType Specifies the type of command you want to execute.
Can be one of the following command types:
StoredProcedure, DirectTable, or Text.
Command Parameters Parameters to pass to the stored procedure.
Command ExecuteReader Creates a DataReader object with the data that the
command object specifies.
DataReader Read Reads the next record in the DataReader, and also tests
the end of the data returned.
DataReader GetString Returns the current record, getting the column
specified, and returns it as string.

connection and creates the command object. The name of the stored procedure is
passed, and the command type is specified, which is
CommandType.StoredProcedure. Next, parameters and the DataReader are
created. The last task is to iterate through the data and add it to the display text
box.
4. Private Sub btnView_Click(ByVal sender As System.Object, _
5. ByVal e As System.EventArgs) Handles btnView.Click
6.
7. Dim ocnn As New OleDb.OleDbConnection(BuildCnnStr("(local)",
8. "Northwind"))
9. Dim ocmdCustHist As New OleDb.OleDbCommand("CustOrderHist", ocnn)
10. Dim odrCustHist As OleDb.OleDbDataReader
11.
12. Try
13. '-- Specify the name of the stored procedure
14. ocmdCustHist.CommandType = CommandType.StoredProcedure
15.
16. '-- Specify the parameters.
17. ocmdCustHist.Parameters.Add("@CustomerID", Me.txtCustID.Text)
18.
19. '-- Open the connection object.
20. ocnn.Open()
21.
22. '-- Establish the DataRead object.
23. odrCustHist = _
24. ocmdCustHist.ExecuteReader(CommandBehavior.SequentialAccess)
25.
26. '-- Iterate through the data loaded, building the results string.
27. Do While odrCustHist.Read
28. Me.txtResults.Text &= odrCustHist.GetString(0) & _