Command Parameters

Many data operations require criteria-based filtering.

This often occurs through user input converted to an SQL query; for example, sales staff may pull all orders in the last week. Command objects only contain strings. Though the possibility of building a dynamic string exists, it does not offer a good solution because it leaves an application wide open to an attacker's SQL injection.

Parameters offer a better solution for filtering. All parameter content becomes field data, not a part of an SQL statement. The use of parameterized queries consists of three steps:

  1. Create the command string with its required parameters.
  2. Declare the associated SqlParameter object, and assign appropriate values.
  3. Assign the SqlParameter object to the command object's parameter properties.

CREATE THE STRING

The command string contains parameter placeholders. Command execution populates placeholders. Correct parameter syntax requires the use of the “@” symbol prefix on parameter names. Review an example of a parameter string below: 

SqlCommand cmd = new SqlCommand(“select * from Clients where service
= @Service”, conn);

The example above uses a single parameter, however, statements permit multiple parameters, and require an associated and assigned (to the command object) parameter object.

DECLARE THE PARAMETER OBJECT

Each parameter requires a definition, a task managed by the SqlParameter type. Code defines an SqlParameter instance for each parameter within a command of a command object. The following example adds a parameter definition to the “@Service” parameter from the previous example: 

SqlParameter pRam = new SqlParameter();
pRam.ParameterName = “@Service”;
pRam.Value = inputService;

ASSIGN THE OBJECT

Each defined SqlParameter must be explicitly tied to the command object. This informs the command object of the parameter. Assign the parameter through the command object's parameter properties. Review an example below: 

cmd.Parameters.Add(pRam);
« Previous
Next »