How to: Set and Get Parameters for Command Objects

Article
02/04/2013

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.

Note

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.

Setting Parameter Values

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.

With oleDbCommand1
    .CommandText = "UpdateAuthor"
    .CommandType = System.Data.CommandType.StoredProcedure
    .Parameters("au_id").Value = "172-32-1176"
    .Parameters("au_lname").Value = "White"
    .Parameters("au_fname").Value = "Johnson"
End With

OleDbConnection1.Open()
oleDbCommand1.ExecuteNonQuery()
OleDbConnection1.Close()

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();

Getting Return Values

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.

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 Modifying Data with Stored Procedures (ADO.NET).

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.

Dim returnValue As Integer

oleDbCommand1.CommandText = "CountAuthors"
oleDbCommand1.CommandType = CommandType.StoredProcedure

oleDbConnection1.Open()
oleDbCommand1.ExecuteNonQuery()
oleDbConnection1.Close()

returnValue = CType(oleDbCommand1.Parameters("retvalue").Value, Integer)
MessageBox.Show("Return Value = " & returnValue.ToString())

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());

Share via

How to: Set and Get Parameters for Command Objects

Setting Parameter Values

To set a parameter value

Getting Return Values

To get values returned by procedures

To get a procedure's return value

See Also

Tasks

Concepts

Additional resources