20.2 Properties Reference

DbType dataType = Parameter.DbType;
Parameter.DbType = dataType;

Specifies the data type of the parameter, using the DbType enumeration in the System.Data namespace. This enumeration includes values for common data types, such as strings and numbers of various sizes. If this property is set, the value of the Parameter is converted to this type before it is passed to the data source. If the type isn't specified, ADO.NET attempts to infer the data source type by looking at the .NET type.

Example

The following code snippet sets the Parameter.DbType property:

param.DbType = DbType.Int32;

Note

Every Parameter class includes a DbType property and a provider-specific data type property such as SqlDbType and OleDbType. These two properties are linked. When you set the provider-specific type, the DbType is adjusted to use a compatible type and vice versa. Usually, you will set this provider-specific type, but you may want to use the DbType when writing generic ADO.NET code.

ParameterDirection pd = Parameter.Direction;
Parameter.Direction = pd;

Specifies whether the parameter is input-only, output-only, bidirectional, or a return value. You set the Direction property using a value from the ParameterDirection enumeration, as shown in Table 20-2.

Table 20-2. ParameterDirection values

Value	Description
Input	The parameter value is submitted to the parameterized command or stored procedure.
Output	The parameter value is returned from the parameterized command or stored procedure.
InputOutput	The parameter can send information to a stored procedure and parameterized command and retrieve the new value if it is modified.
ReturnValue	The parameter represents a return value from a stored procedure or built-in function.

Note

If a value isn't returned for an output parameter or return value, the Value of the corresponding Parameter is null. With the OLE DB provider, make sure you add a parameter for the return value (if needed) before other parameters.

Boolean isNullable = Parameter.IsNulllable;
Parameter.IsNullable = isNullable;

This Boolean parameter indicates whether null values are accepted for the Parameter.Value property. The default is false.

Note

If your parameter accepts null values, you can test for them using the System.DBNull class. In this case, DBNull.Value equals Parameter.Value.

Int32 offset = Parameter.Offset;
Parameter.Offset = offset;

Sets an offset to use for the Value property, provided the Value property contains a binary or string type. For example, if you set the Offset to 5, the Parameter submits the content from the Value property, starting with the sixth character (in the case of a string) or sixth byte (in the case of binary data). The default is 0 (or no offset).

Note

The Offset property is rarely used, but it lets you submit a portion of a large byte array (for example, when dealing with image processing).

OleDbType oleDbType = Parameter.OleDbType
Parameter.OleDbType = OleDbType

Specifies the OLE DB data type of the parameter, using the OleDbType enumeration in the System.Data.OleDb namespace. The value of the Parameter is converted into this type before it is passed to the data source. The default is a DbType.VarWChar, a variable length string of Unicode characters.

Example

The following code snippet sets the Parameter.OleDbType property and then examines the linked DbType. In this case, the linked DbType value is DbType.Int32.

param.OleDbType = OleDbType.Integer;
Console.WriteLine(param.DbType.ToString());

Notes

Every OleDbParameter class includes a DbType property and a provider-specific OleDbType property. These two properties are linked. When you set the OleDbType property, the DbType is adjusted to use a compatible type and vice versa.

For more information about valid OLE DB data types and their mappings to .NET framework types, see Appendix A.

string parameterName = Parameter.ParameterName;
Parameter.ParameterName = parameterName;

Names the parameter with a string. When calling a stored procedure, you must make sure that the ParameterName property exactly matches the parameter name defined in the data source. The ParameterName is also used as an indexer for the ParameterCollection (as is the ordinal position in the collection) and can retrieve Parameter objects, check if they exist in the collection, or remove them.

Example

The following code snippet creates a parameter named @Description, and then retrieves it from the collection by name.

cmd.Parameters.Add("@Description", SqlDbType.VarChar, 88);
SqlParameter param = cmd.Parameters["@Description"];

Notes

The ParameterName can also insert values into parameterized commands used with the SQL Server provider. However, the name doesn't play an important role when creating parameterized commands with the OLE DB provider. With the OLE DB provider, all parameters are identified with question-mark placeholders, and the order alone determines which parameters to substitute. For a full example of both SQL Server and OLE DB parameterized commands, refer to Chapter 4.

Parameter names typically begin with the @ character (as in @CustomerID). This is a common convention, but it isn't necessary.

byte precision = Parameter.Precision;
Parameter.Precision = precision;

Determines the maximum number of digits that represents the Value property, assuming it is a numeric type. For example, the number 1234.56 has a precision of 6. The default precision is 0, which indicates no maximum.

Note

You don't need to set the Precision property. The primary reason to set Precision is to ensure you don't accidentally submit a number that has a higher precision than allowed by the data source. In SQL Server, you can set a precision for all decimal data types when creating a table.

byte scale = Parameter.Scale;
Parameter.Scale = scale;

Determines the maximum number of digits to the right of the decimal in the Value property, assuming it is a numeric type. For example, the number 1234.56 has a scale of 2. The default scale is 0, which indicates no maximum.

Note

You don't need to set the Scale property. The primary reason to set Scale is to ensure you don't accidentally submit a number that has a higher scale than allowed by the data source. In SQL Server, you can set the scale for all decimal data types when creating a table.

int size = Parameter.Size;
Parameter.Size = size;

Determines the maximum size of the Value property for a binary or string parameter. For fixed-width data types, the Size property is ignored. If not set, the actual size of the specified parameter value is used

In the case of a Unicode string, the Size corresponds to a number of characters, not including the final null termination. In the case of binary data or an ANSI string, the Size refers to a number of bytes. If you set the Size property to a value smaller than the parameter data, the Value is truncated.

string columnName = Parameter.SourceColumn;
Parameter.SourceColumn = columnName;

Sets the name of the linked column from the DataSet. This property is important when using the DataAdapter to apply updates. In the case of an input parameter, the value from the source column in the DataSet is copied to the parameter value before the command is executed. In the case of an output parameter, the parameter value is copied to the DataSet column after the command is executed, assuming the Command.UpdateRowSource property allows it.

Example

The following code defines a linked @CategoryID output parameter. After the command is executed, the Value from the parameter is copied into the CategoryID column in the DataSet.

param = cmdInsert.Parameters.Add("@CategoryID", SqlDbType.Int);
param.SourceColumn = "CategoryID";
param.Direction = ParameterDirection.Output;

Note

When using the SourceColumn property with an input parameter, the SourceVersion property must indicate the version of the column data you want to use. Its default is DataRowVersion.Current.

DataRowVersion version = Parameter.SourceVersion;
Parameter.SourceVersion = version;

Sets the DataRowVersion of the data that is used from the linked column in the DataSet. This property is used when the DataAdapter applies an update. In the case of an input parameter, the value from the source column in the DataSet is copied to the parameter value before the command is executed. The SourceVersion property doesn't apply to output or return-value parameters.

The list of valid DataRowVersion values is shown in Table 20-3. Typically, you use the Current value to apply a new change (for example, in the SET statement of a SQL UPDATE command), and the Original value to find a specific row (for example, in the WHERE clause of a SQL command).

Table 20-3. DataRowVersion values

Value	Description
Current	Represents the current value. This may differ from the original value if any changes have been made to the row.
Original	Represents the value retrieved from the data source or the value that was committed the last time changes were applied.
Proposed	Represents an edited value that hasn't yet been committed. Rarely used with a parameter.

Example

The following code defines a linked @CategoryName input parameter. Before the command is executed, the current value from the CategoryName field is copied to the Parameter.Value property.

param = cmdInsert.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
param.SourceColumn = "CategoryName";

// The following line could be omitted, as this is the default.
param.SourceVersion = DataRowVersion.Current;

SqlDbType dataType = Parameter.SqlDbType;
Parameter.SqlDbType = dataType;

Specifies the SQL Server data type of the parameter, using the SqlDbType enumeration in the System.Data namespace. If this property is set, the value of the Parameter is converted to this type before it's passed to the data source. The default is a SqlDbType.NVarChar, a variable-length string of Unicode characters.

Example

The following code snippet sets the Parameter.SqlDbType property and then examines the linked DbType. In this case, the linked DbType value is DbType.Int32.

param.SqlDbType = SqlDbType.Int;
Console.WriteLine(param.DbType.ToString());

Notes

Every SqlParameter class includes a DbType property and a provider-specific SqlDbType property. These two properties are linked. When you set the SqlDbType property, the DbType uses a compatible type and vice versa.

For more information about valid SQL Server data types, and their mappings to .NET framework types, see Appendix A.

Object value = Parameter.Value;
Parameter.Value = value;

This is the actual content stored in the parameter. For output parameters, this is the information returned after the command is complete. For input parameters, this is the value that is sent to the data source when the command is executed.

The Value is stored as a loosely typed object, so it can accommodate any data type. When the command is executed, all input parameter values are converted to the appropriate type by examining the provider-specific type property (such as SqlDbType or OleDbType). This conversion can result in an error, particularly if the type doesn't support the IConvertible interface.

Example

The following code snippet creates a new parameter (as a variable-length Unicode string) and then sets the Value property using the .NET string type:

SqlParameter param;
param = new SqlParameter("@Description", SqlDbType.VarChar, 88);
param.Value = "This is the description";

Note

If you wish to specify a null value for a parameter, you must set the Parameter.Value property using the DBNull.Value property, which represents a null value. You can't set it directly to a null reference, or an error will occur.