October 15, 2002 - The SqlParameter Class | WebReference

October 15, 2002 - The SqlParameter Class

Yehuda Shiran October 15, 2002
The SqlParameter Class
Tips: October 2002

Yehuda Shiran, Ph.D.
Doc JavaScript

Sending and getting parameters to and from a stored procedure is not trivial. You need to use an instance object of the SqlParameter class for each input or output parameter. You define all parameter attributes via this class. This class cannot be inherited.

First, you need to construct the instance object. The constructor expects three parameters: the name of the parameter as defined by the target stored procedure, the data type of the parameter, and its length in bytes. Here is an example that constructs the instance object parameterCategoryID for the stored procedure's CategoryID parameter:

  var parameterCategoryID : SqlParameter = 
    new SqlParameter("@CategoryID", SqlDbType.Int, 4);
Notice that you don't need to specify the stored procedure's name yet. Next, you need to set the value of the parameter (if input parameter) or set its direction to output (if output parameter). Here is an example for an input parameter:

  parameterCategoryID.Value = categoryID;
And here is an example for setting an output parameter's direction:

  parameterModelName.Direction = ParameterDirection.Output;
Next, you need to add the instance object as a parameter to the SqlCommand instance object. It's only here that you commit to a specific command. Here is an example:

  myCommand.Parameters.Add(parameterCategoryID);
Now you are done. When you execute the command, the parameter object's attributes are communicated to the stored procedure. An input parameter's value is sent to the stored procedure, while an output parameter's value is sent from the stored procedure to the JScript .NET code. You get the output parameter from the Value parameter of the instance object. Here is an example:

  myProductDetails.ModelNumber = parameterModelNumber.Value;