/// <summary>
        /// Gets a recently added products list
        /// </summary>
        /// <param name="Number">Number of products to load</param>
        /// <param name="showHidden">A value indicating whether to show hidden records</param>
        /// <returns>Recently added products list</returns>
        public override DBProductCollection GetRecentlyAddedProducts(int Number, bool showHidden)
        {
            DBProductCollection productCollection = new DBProductCollection();
            Database db = NopSqlDataHelper.CreateConnection(_sqlConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductLoadRecentlyAdded");
            db.AddInParameter(dbCommand, "Number", DbType.Int32, Number);
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    DBProduct product = GetProductFromReader(dataReader);
                    productCollection.Add(product);
                }
            }

            return productCollection;
        }
        /// <summary>
        /// Gets all products displayed on the home page
        /// </summary>
        /// <returns>Product collection</returns>
        public override DBProductCollection GetAllProductsDisplayedOnHomePage(bool showHidden)
        {
            DBProductCollection productCollection = new DBProductCollection();
            Database db = NopSqlDataHelper.CreateConnection(_sqlConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductLoadDisplayedOnHomePage");
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    DBProduct product = GetProductFromReader(dataReader);
                    productCollection.Add(product);
                }
            }

            return productCollection;
        }
        /// <summary>
        /// Gets a list of products purchased by other customers who purchased the above
        /// </summary>
        /// <param name="ProductID">Product identifier</param>
        /// <param name="showHidden">A value indicating whether to show hidden records</param>
        /// <param name="PageSize">Page size</param>
        /// <param name="PageIndex">Page index</param>
        /// <param name="TotalRecords">Total records</param>
        /// <returns>Product collection</returns>
        public override DBProductCollection GetProductsAlsoPurchasedByID(int ProductID,
            bool showHidden, int PageSize, int PageIndex, out int TotalRecords)
        {
            TotalRecords = 0;
            DBProductCollection productCollection = new DBProductCollection();
            Database db = NopSqlDataHelper.CreateConnection(_sqlConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductAlsoPurchasedLoadByProductID");
            db.AddInParameter(dbCommand, "ProductID", DbType.Int32, ProductID);
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddOutParameter(dbCommand, "TotalRecords", DbType.Int32, 0);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    DBProduct product = GetProductFromReader(dataReader);
                    productCollection.Add(product);
                }
            }
            TotalRecords = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalRecords"));

            return productCollection;
        }
        /// <summary>
        /// Gets all products
        /// </summary>
        /// <param name="CategoryID">Category identifier</param>
        /// <param name="ManufacturerID">Manufacturer identifier</param>
        /// <param name="FeaturedProducts">A value indicating whether loaded products are marked as featured (relates only to categories and manufacturers). 0 to load featured products only, 1 to load not featured products only, null to load all products</param>
        /// <param name="PriceMin">Minimum price</param>
        /// <param name="PriceMax">Maximum price</param>
        /// <param name="Keywords">Keywords</param>
        /// <param name="SearchDescriptions">A value indicating whether to search in descriptions</param>
        /// <param name="PageSize">Page size</param>
        /// <param name="PageIndex">Page index</param>
        /// <param name="FilteredSpecs">Filtered product specification identifiers</param>
        /// <param name="showHidden">A value indicating whether to show hidden records</param>
        /// <param name="TotalRecords">Total records</param>
        /// <returns>Product collection</returns>
        public override DBProductCollection GetAllProducts(int CategoryID, int ManufacturerID,
            bool? FeaturedProducts, decimal? PriceMin, decimal? PriceMax, string Keywords,
            bool SearchDescriptions, int PageSize, int PageIndex,
            List<int> FilteredSpecs, bool showHidden, int SortBy, bool SortTo,
            int minHeight, int maxHeight, int minWidth, int maxWidth, out int TotalRecords)
        {
            TotalRecords = 0;
            DBProductCollection productCollection = new DBProductCollection();
            Database db = NopSqlDataHelper.CreateConnection(_sqlConnectionString);
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductLoadAllPaged");
            db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, CategoryID);
            db.AddInParameter(dbCommand, "ManufacturerID", DbType.Int32, ManufacturerID);
            if (FeaturedProducts.HasValue)
                db.AddInParameter(dbCommand, "FeaturedProducts", DbType.Boolean, FeaturedProducts.Value);
            else
                db.AddInParameter(dbCommand, "FeaturedProducts", DbType.Boolean, null);
            if (PriceMin.HasValue)
                db.AddInParameter(dbCommand, "PriceMin", DbType.Decimal, PriceMin.Value);
            else
                db.AddInParameter(dbCommand, "PriceMin", DbType.Decimal, null);
            if (PriceMax.HasValue)
                db.AddInParameter(dbCommand, "PriceMax", DbType.Decimal, PriceMax.Value);
            else
                db.AddInParameter(dbCommand, "PriceMax", DbType.Decimal, null);
            db.AddInParameter(dbCommand, "Keywords", DbType.String, Keywords);
            db.AddInParameter(dbCommand, "SearchDescriptions", DbType.Boolean, SearchDescriptions);
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(dbCommand, "SortBy", DbType.Int32, SortBy);
            db.AddInParameter(dbCommand, "SortTo", DbType.Boolean, SortTo);
            db.AddInParameter(dbCommand, "HeightMin", DbType.Decimal, minHeight);
            db.AddInParameter(dbCommand, "WidthMin", DbType.Decimal, minWidth);
            db.AddInParameter(dbCommand, "HeightMax", DbType.Decimal, maxHeight);
            db.AddInParameter(dbCommand, "WidthMax", DbType.Decimal, maxWidth);

            string commaSeparatedSpecIDs = string.Empty;
            if (FilteredSpecs != null)
            {
                FilteredSpecs.Sort();
                for (int i = 0; i < FilteredSpecs.Count; i++)
                {
                    commaSeparatedSpecIDs += FilteredSpecs[i].ToString();
                    if (i != FilteredSpecs.Count - 1)
                    {
                        commaSeparatedSpecIDs += ",";
                    }
                }
            }
            db.AddInParameter(dbCommand, "FilteredSpecs", DbType.String, commaSeparatedSpecIDs);
            db.AddOutParameter(dbCommand, "TotalRecords", DbType.Int32, 0);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    DBProduct product = GetProductFromReader(dataReader);
                    productCollection.Add(product);
                }
            }
            TotalRecords = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalRecords"));

            return productCollection;
        }