October 10, 2002 - Retrieving Selected Products from the IBuySpy Database | WebReference

October 10, 2002 - Retrieving Selected Products from the IBuySpy Database

Yehuda Shiran October 10, 2002
Retrieving Selected Products from the IBuySpy Database
Tips: October 2002

Yehuda Shiran, Ph.D.
Doc JavaScript

A common interaction with an MSSQL database is to retrieve all records with a given attribute. A classic example is to retrieve all products with a given category ID. Usually, you'll use a stored procedure that expects a single parameter, the category ID. To accomplish this task, you need to follow the following recipe:

  1. Create an instance of connection to the database, a
SqlConnection object.
  • Create an instance of an SQL command, a
    • SqlCommand object.
  • Mark the command as a stored procedure.
  • Create a parameter instance object.
  • Set the parameter instance object's
    • Value parameter.
  • Add the parameter instance object to the SQL command object, created above.
  • Execute the command.
  • Return the
    • SqlDataReader result to the caller.

    Let's take an example. The ProductsDB.js file includes the method GetProducts(categoryID: int). As its name implies, this method retrieves all products belonging to categoryID. It follows the recipe above, calling the stored procedure ProductsByCategory. This stored procedure expects a single parameter, CategoryID. It returns a table of all products with the given CategoryID. Here is the code:

      public function GetProducts(categoryID: int) : SqlDataReader {
    var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    var myCommand : SqlCommand = new SqlCommand("ProductsByCategory", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;
    var parameterCategoryID : SqlParameter = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
    parameterCategoryID.Value = categoryID;
    myCommand.Parameters.Add(parameterCategoryID);
    myConnection.Open();
    var result : SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
    return result;
  }