October 9, 2002 - Retrieving Product Categories from the IBuySpy Database | WebReference

October 9, 2002 - Retrieving Product Categories from the IBuySpy Database

Yehuda Shiran October 9, 2002
Retrieving Categories from the IBuySpy Database
Tips: October 2002

Yehuda Shiran, Ph.D.
Doc JavaScript

The simplest interaction with a MSSQL database is to retrieve a table from it with a stored procedure. To accomplish this task using JScript .NET, follow the recipe below:

  1. Create an instance of connection to the database, a
SqlConnection object.
  • Create an instance of a SQL command, a
    • SqlCommand object.
  • Mark the command as a stored procedure.
  • Execute the command.
  • Return the
    • SqlDataReader result to the caller.

    Here's an example. The ProductsDB.js file includes the method GetProductCategories(). As the name implies, this method retrieves the product categories from the database. It follows the recipe above, calling the stored procedure ProductCategoryList. This stored procedure is the simplest one, as it doesn't expect any parameters. It returns a table of all product categories in the databases. Here's 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;
  }