How to: Set and Get Parameters for Command Objects
If you are using command objects 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 = @EmployeeID)
When this statement is executed, you must provide values for all the parameters (@LastName, @FirstName, @BirthDate, and @EmployeeID). To do so, you use parameter objects. Command objects support a parameters collection (for example, Parameters) that contains a set of objects of type SqlParameter, OleDbParameter, OdbcParameter, or OracleParameter. 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.
If you are using a dataset, you typically do not execute commands directly. Instead, you pass parameters to TableAdapter queries, for more information, see How to: Fill a Dataset with Data.
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.
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.
oleDbCommand1.CommandText = "UpdateAuthor"; oleDbCommand1.CommandType = System.Data.CommandType.StoredProcedure; oleDbCommand1.Parameters["au_id"].Value = "172-32-1176"; oleDbCommand1.Parameters["au_lname"].Value = "White"; oleDbCommand1.Parameters["au_fname"].Value = "Johnson"; oleDbConnection1.Open(); oleDbCommand1.ExecuteNonQuery(); oleDbConnection1.Close();
Stored procedures often pass values back to the application that called them.
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 parameters whose Direction property is set to ReturnValue.
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.
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 Modifying Data with Stored Procedures.
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.
int returnValue; oleDbCommand1.CommandText = "CountAuthors"; oleDbCommand1.CommandType = CommandType.StoredProcedure; oleDbConnection1.Open(); oleDbCommand1.ExecuteNonQuery(); oleDbConnection1.Close(); returnValue = (int)(oleDbCommand1.Parameters["retvalue"].Value); MessageBox.Show("Return Value = " + returnValue.ToString());