Learning Resources
 

The Command attributes in the markup


Parameter Specification
When you use the SqlDataSource control, you can set the control's command properties to parameterized SQL statements or to the name of a stored procedure. If you specify a stored procedure for a command, you must specify that the command type of the command is StoredProcedure.

Parameter Names
The SqlDataSource control adds the value of the ParameterPrefix property to the beginning of all parameter names. (The default prefix is "@".)

If a data-bound control such as a GridView control is bound to the SqlDataSource control, during an update or delete operation the data-bound control passes both current and original record values to the SqlDataSource control. The current values are passed in the Values dictionary. The original values are passed in the Keys or OldValues dictionaries. The contents of these dictionaries are appended to the underlying DbCommand object's Parameters collection for a given data operation.

In the SqlDataSource control's SQL commands, you use a naming convention to match parameter placeholders to the old values passed into the command. You establish the format of the placeholder name by setting the SqlDataSource control's OldValuesParameterFormatString property. Set the OldValuesParameterFormatString property to a string that includes "{0}" as a placeholder for the name of the field. For example, if you set the OldValuesParameterFormatString property to "old_{0}", the names of the original-value parameters will resolve to the field name prefixed with "@old_". Consider an update operation that involves a field named LastModifiedDate. The current value for the field is passed in the Values dictionary and the original value for the field is passed in the OldValues dictionary. A parameter named @LastModifiedDate is created to pass the current value and a parameter named @old_LastModifiedDate is created to pass the original value. You can then include both parameters in an SQL statement to differentiate between the current and original values for the field, as shown in the following example:

UPDATE Table1 SET LastModifiedDate = @LastModifiedDate
  WHERE Key = @Key AND LastModifiedDate = @old_LastModifiedDate

The ability to separate current and original values in a command is essential when performing optimistic concurrency checks or working with a data source where the primary key can be modified.

For more information on the dictionaries passed by a data-bound control, see How a Data Source Control Creates Parameters for Data-bound Fields.

Using Parameters with the SqlClient Provider
By default, the SqlDataSource control uses the System.Data.SqlClient data provider to work with SQL Server as the data source. The System.Data.SqlClient provider supports named parameters as placeholders, as shown in the following example:

SELECT * FROM Employees WHERE LastName = @LastName
  AND FirstName = @FirstName

With named parameters, the order in which the parameters is specified in the command's parameters collection is not important. However, you must ensure that the parameter names that you use in your SQL command correspond to the names of the parameters in the associated collection.

--MSDN