    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;

    namespace ASPNET.StarterKit.Commerce {

        //*******************************************************
        //
        // ProductDetails Class
        //
        // A simple data class that encapsulates details about
        // a particular product inside the Commerce Starter Kit Product
        // database.
        //
        //*******************************************************

        public class ProductDetails {

            public String  ModelNumber;
            public String  ModelName;
            public String  ProductImage;
            public decimal UnitCost;
            public String  Description;
        }

        //*******************************************************
        //
        // ProductsDB Class
        //
        // Business/Data Logic Class that encapsulates all data
        // logic necessary to query products within
        // the Commerce Starter Kit Products database.
        //
        //*******************************************************

        public class ProductsDB {

            //*******************************************************
            //
            // ProductsDB.GetProductCategories() Method <a name="GetProductCategories"></a>
            //
            // The GetProductCategories method returns a DataReader that exposes all
            // product categories (and their CategoryIDs) within the Commerce Starter Kit Products
            // database.  The SQLDataReaderResult struct also returns the
            // SQL connection, which must be explicitly closed after the
            // data from the DataReader is bound into the controls.
            //
            // Other relevant sources:
            //     + <a href="ProductCategoryList.htm" style="color:green">ProductCategoryList Stored Procedure</a>
            //
            //*******************************************************

            public SqlDataReader GetProductCategories()
            {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand myCommand = new SqlCommand("CMRC_ProductCategoryList", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Execute the command
                myConnection.Open();
                SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // Return the datareader result
                return result;
            }

            //*******************************************************
            //
            // ProductsDB.GetProducts() Method <a name="GetProducts"></a>
            //
            // The GetProducts method returns a struct containing a forward-only,
            // read-only DataReader. This displays all products within a specified
            // product category.  The SQLDataReaderResult struct also returns the
            // SQL connection, which must be explicitly closed after the
            // data from the DataReader is bound into the controls.
            //
            // Other relevant sources:
            //     + <a href="ProductsByCategory.htm" style="color:green">ProductsByCategory Stored Procedure</a>
            //
            //*******************************************************

            public SqlDataReader GetProducts(int categoryID) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand myCommand = new SqlCommand("CMRC_ProductsByCategory", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);
                parameterCategoryID.Value = categoryID;
                myCommand.Parameters.Add(parameterCategoryID);

                // Execute the command
                myConnection.Open();
                SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // Return the datareader result
                return result;
            }

            //*******************************************************
            //
            // ProductsDB.GetProductDetails() Method <a name="GetProductDetails"></a>
            //
            // The GetProductDetails method returns a ProductDetails
            // struct containing specific details about a specified
            // product within the Commerce Starter Kit Products Database.
            //
            // Other relevant sources:
            //     + <a href="ProductDetail.htm" style="color:green">ProductDetail Stored Procedure</a>
            //
            //*******************************************************

            public ProductDetails GetProductDetails(int productID) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand myCommand = new SqlCommand("CMRC_ProductDetail", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
                parameterProductID.Value = productID;
                myCommand.Parameters.Add(parameterProductID);

                SqlParameter parameterUnitCost = new SqlParameter("@UnitCost", SqlDbType.Money, 8);
                parameterUnitCost.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(parameterUnitCost);

                SqlParameter parameterModelNumber = new SqlParameter("@ModelNumber", SqlDbType.NVarChar, 50);
                parameterModelNumber.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(parameterModelNumber);

                SqlParameter parameterModelName = new SqlParameter("@ModelName", SqlDbType.NVarChar, 50);
                parameterModelName.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(parameterModelName);

                SqlParameter parameterProductImage = new SqlParameter("@ProductImage", SqlDbType.NVarChar, 50);
                parameterProductImage.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(parameterProductImage);

                SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 3800);
                parameterDescription.Direction = ParameterDirection.Output;
                myCommand.Parameters.Add(parameterDescription);

                // Open the connection and execute the Command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                // Create and Populate ProductDetails Struct using
                // Output Params from the SPROC
                ProductDetails myProductDetails = new ProductDetails();

                myProductDetails.ModelNumber = (string)parameterModelNumber.Value;
                myProductDetails.ModelName = (string)parameterModelName.Value;
                myProductDetails.ProductImage = ((string)parameterProductImage.Value).Trim();
                myProductDetails.UnitCost = (decimal)parameterUnitCost.Value;
                myProductDetails.Description = ((string)parameterDescription.Value).Trim();

                return myProductDetails;
            }

            //*******************************************************
            //
            // ProductsDB.GetProductsAlsoPurchased() Method <a name="GetProductsAlsoPurchased"></a>
            //
            // The GetPGetProductsAlsoPurchasedroducts method returns a struct containing
            // a forward-only, read-only DataReader.  This displays a list of other products
            // also purchased with a specified product  The SQLDataReaderResult struct also
            // returns the SQL connection, which must be explicitly closed after the
            // data from the DataReader is bound into the controls.
            //
            // Other relevant sources:
            //     + <a href="CustomerAlsoBought.htm" style="color:green">CustomerAlsoBought Stored Procedure</a>
            //
            //*******************************************************

            public SqlDataReader GetProductsAlsoPurchased(int productID) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand myCommand = new SqlCommand("CMRC_CustomerAlsoBought", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
                parameterProductID.Value = productID;
                myCommand.Parameters.Add(parameterProductID);

                // Execute the command
                myConnection.Open();
                SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // Return the datareader result
                return result;
            }

            //*******************************************************
            //
            // ProductsDB.GetMostPopularProductsOfWeek() Method <a name="GetMostPopularProductsOfWeek"></a>
            //
            // The GetMostPopularProductsOfWeek method returns a struct containing a
            // forward-only, read-only DataReader containing the most popular products
            // of the week within the Commerce Starter Kit Products database.
            // The SQLDataReaderResult struct also returns the
            // SQL connection, which must be explicitly closed after the
            // data from the DataReader is bound into the controls.
            //
            // Other relevant sources:
            //     + <a href="ProductsMostPopular.htm" style="color:green">ProductsMostPopular Stored Procedure</a>
            //
            //*******************************************************

            public SqlDataReader GetMostPopularProductsOfWeek() {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand myCommand = new SqlCommand("CMRC_ProductsMostPopular", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Execute the command
                myConnection.Open();
                SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // Return the datareader result
                return result;
            }

            //*******************************************************
            //
            // ProductsDB.SearchProductDescriptions() Method <a name="SearchProductDescriptions"></a>
            //
            // The SearchProductDescriptions method returns a struct containing
            // a forward-only, read-only DataReader.  This displays a list of all
            // products whose name and/or description contains the specified search
            // string. The SQLDataReaderResult struct also returns the SQL connection,
            // which must be explicitly closed after the data from the DataReader
            // is bound into the controls.
            //
            // Other relevant sources:
            //     + <a href="ProductSearch.htm" style="color:green">ProductSearch Stored Procedure</a>
            //
            //*******************************************************

            public SqlDataReader SearchProductDescriptions(string searchString) {

                // Create Instance of Connection and Command Object
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand myCommand = new SqlCommand("CMRC_ProductSearch", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                SqlParameter parameterSearch = new SqlParameter("@Search", SqlDbType.NVarChar, 255);
                parameterSearch.Value = searchString;
                myCommand.Parameters.Add(parameterSearch);

                // Execute the command
                myConnection.Open();
                SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // Return the datareader result
                return result;
            }
        }
    }