Setting and Getting Data Command Parameters
If you are using data commands directly to execute commands against a database, the SQL statements or stored procedures you are executing often require parameters. For example, a simple Update statement that requires parameters might look like the following:
UPDATE Employees SET LastName = @LastName, FirstName = @FirstName, BirthDate = @BirthDate WHERE (EmployeeID = @Emp_id)
When this statement is executed, you must provide values for all the parameters (@LastName, @FirstName, @BirthDate, and @Emp_id). To do so, you use parameter objects. Data commands support a Parameters collection that contains a set of objects of type OleDbParameter , SqlParameter, OdbcParameter or OracleParameter class. There is one parameter object in the collection for each parameter you need to pass. In addition, if you are calling a stored procedure, you might need an additional parameter to accept the procedure's return value.
Note For Oracle, when using named parameters in an SQL statement or stored procedure, you must precede the parameter name with a colon (:). However, when referring to a named parameter elsewhere in your code (for example, when calling the Add method), do not precede the named parameter with a colon (:). The data provider supplies the colon automatically.
Note If you are using a dataset, you typically do not execute data commands directly. Instead, you use adapter-specific ways to set parameters. For more information, see Using Parameters with a DataAdapter.
You can configure a command's Parameters collection using the Properties window or in code. For more information about using the Properties window, see Configuring Parameters for Data Adapters. For an example of how to create and configure parameters programmatically, see Using Stored Procedures with a Command.
Tip It is a good idea to give names to your parameter objects so that you can reference them in code easily. Although you can reference a parameter object using its index value in the Parameters collection, it is clearer and less error-prone to refer to it by name.
Before you execute a command, you must set a value for every parameter in the command.
To set a parameter value
- For each parameter in the command's Parameters collection, set its Value property to the value to pass.
The following example shows how to set parameters before executing a command that references a stored procedure. The sample assumes that you have already configured the Parameters collection with three parameters named au_id, au_lname, and au_fname. The individual parameters are set by name to make it clear which parameter is being set.
' Visual Basic ' The CommandText and CommandType properties can be set ' in the Properties window but are shown here for completeness. With OleDbCommand1 .CommandText = "UpdateAuthor" .CommandType = System.Data.CommandType.StoredProcedure .Parameters("au_id").Value = listAuthorID.Text .Parameters("au_lname").Value = txtAuthorLName.Text .Parameters("au_fname").Value = txtAuthorFName.Text End With OleDbConnection1.Open() OleDbCommand1.ExecuteNonQuery() OleDbConnection1.Close() // C# // The CommandText and CommandType properties can be set // in the Properties window but are shown here for completeness. OleDbCommand1.CommandText = "UpdateAuthor"; OleDbCommand1.CommandType = System.Data.CommandType.StoredProcedure; OleDbCommand1.Parameters["au_id"].Value = listAuthorID.Text; OleDbCommand1.Parameters["au_lname"].Value = txtAuthorLName.Text; OleDbCommand1.Parameters["au_fname"].Value = txtAuthorFName.Text; OleDbConnection1.Open(); OleDbCommand1.ExecuteNonQuery(); OleDbConnection1.Close();
Getting Return Values
Stored procedures often pass values back to the application that called them. They can do so by passing the value using a parameter or by defining and passing a return value.
To get values returned by procedures
- Create parameters whose Direction property is set to Output or InputOutput (if the parameter is used in the procedure to both receive and send values). Make sure the data type of the parameter matches the expected return value.
- After executing the procedure, read the Value property of the parameter being passed back.
To get a procedure's return value
- Create a parameter and set its Direction property is set to ReturnValue.
Note The parameter object for the return value must be the first item in the Parameters collection.
- Make sure the parameter's data type matches the expected return value.
Note Update, Insert, and Delete SQL statements return an integer value indicating the number of records affected by the statement. You can get this value as the return value of the ExecuteNonQuery method. For more information, see Executing Updates or Database Commands using a Data Command.
The following example shows how to get the return value of a stored procedure called CountAuthors. In this case, it is assumed that the first parameter in the command's Parameters collection is named "retvalue" and that is configured with a direction of ReturnValue.
' Visual Basic Dim cntAffectedRecords As Integer ' The CommandText and CommandType properties can be set ' in the Properties window but are shown here for completeness. OleDbcommand1.CommandText = "CountAuthors" OleDbCommand1.CommandType = CommandType.StoredProcedure OleDbConnection1.Open() OleDbCommand1.ExecuteNonQuery() OleDbConnection1.Close() cntAffectedRecords = CType(OleDbCommand1.Parameters("retvalue").Value, Integer) MessageBox.Show("Affected records = " & cntAffectedRecords.ToString) // C# int cntAffectedRecords; // The CommandText and CommandType properties can be set // in the Properties window but are shown here for completeness. oleDbcommand1.CommandText = "CountAuthors"; oleDbCommand1.CommandType = CommandType.StoredProcedure; oleDbConnection1.Open(); oleDbCommand1.ExecuteNonQuery(); oleDbConnection1.Close(); cntAffectedRecords = (int)(OleDbCommand1.Parameters["retvalue"].Value); MessageBox.Show("Affected records = " + cntAffectedRecords.ToString());
Introduction to DataCommand Objects in Visual Studio | Working with Data Commands: High-Level Process | Parameters in Data-Adapter Commands | Configuring Parameters for Data Adapters | Executing a Data Command that Returns a Result Set | Executing Updates or Database Commands using a Data Command | Executing a Data Command that Returns a Single Value