October 11, 2002 - Retrieving Product Details from the IBuySpy Database | WebReference

October 11, 2002 - Retrieving Product Details from the IBuySpy Database

Yehuda Shiran October 11, 2002
Retrieving Product Details from the IBuySpy Database
Tips: October 2002

Yehuda Shiran, Ph.D.
Doc JavaScript

A common interaction with an MSSQL database is to retrieve records from the MSSQL database and return them in an sqlDataReader instance object. The JScript .NET application will then use this object to iterate over the records and display them in a tabular format. Sometimes, though, you may want to retrieve single attributes from the database, as opposed to whole records. Since you do all your interactions with the database through stored procedures, you need to write a stored procedure with output parameters. In your JScript .NET code, you need to create an sqlParameter instance object for each output parameter. We have shown yesterday how to define an input parameter. The difference between defining an input parameter and an output parameter is that, while the direction of an input parameter is the default set during the creation of the SqlParameter object, you need to assign the Direction property of the SqlParameter object to the constant ParameterDirection.Output for output parameters.

Here is the full recipe to retrieve single attributes from the MSSQL database:

  1. Create an instance of a connection to the database, an
SqlConnection object.
  • Create an instance of an SQL command, an
    • SqlCommand object.
  • Mark the command as a stored procedure.
  • For each input parameter:
    1. Create a parameter instance object.
    2. Set the parameter instance object's
    Value parameter.
  • Add the parameter instance object to the SQL command object, created above.
  • For each output parameter:
    1. Create a parameter instance object.
    2. Set the parameter instance object's
    Direction parameter to the constant ParameterDirection.Output
  • Add the parameter instance object to the SQL command object, created above.
  • Execute the command.

    • Let's look at an example. The ProductsDB.js file includes the method GetProductDetail(productID: int). As its name implies, this method retrieves the details of a product with ID productID. It follows the recipe above, calling the stored procedure ProductDetail. This stored procedure expects a single input parameter, ProductID. It returns five output parameters: UnitCost, ModelNumber, ModelName, ProductImage, and Description. Here is the code:

      public function GetProductDetails(productID: int) : ProductDetails {
    var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    var myCommand : SqlCommand = new SqlCommand("ProductDetail", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;
    var parameterProductID : SqlParameter = new SqlParameter("@ProductID", SqlDbType.Int, 4);
    parameterProductID.Value = productID;
    myCommand.Parameters.Add(parameterProductID);
    var parameterUnitCost : SqlParameter = new SqlParameter("@UnitCost", SqlDbType.Money, 8);
    parameterUnitCost.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterUnitCost);
    var parameterModelNumber : SqlParameter = new SqlParameter("@ModelNumber", SqlDbType.NVarChar, 50);
    parameterModelNumber.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterModelNumber);
    var parameterModelName : SqlParameter = new SqlParameter("@ModelName", SqlDbType.NVarChar, 50);
    parameterModelName.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterModelName);
    var parameterProductImage : SqlParameter  = new SqlParameter("@ProductImage", SqlDbType.NVarChar, 50);
    parameterProductImage.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterProductImage);
    var parameterDescription : SqlParameter  = new SqlParameter("@Description", SqlDbType.NVarChar, 3800);
    parameterDescription.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterDescription);
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myConnection.Close();
    var myProductDetails : ProductDetails  = new ProductDetails();
    myProductDetails.ModelNumber = parameterModelNumber.Value;
    myProductDetails.ModelName = parameterModelName.Value;
    myProductDetails.ProductImage = (parameterProductImage.Value).Trim();
    myProductDetails.UnitCost = parameterUnitCost.Value;
    myProductDetails.Description = (parameterDescription.Value).Trim();
    return myProductDetails;
  }