예제 #1
0
        // Create a new order from the shopping cart
        public static string CreateOrder()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CreateOrder";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@CartID";
            param.Value         = shoppingCartId;
            param.DbType        = DbType.String;
            param.Size          = 36;
            comm.Parameters.Add(param);
            // return the result table
            return(GenericDataAccess.ExecuteScalar(comm));
        }
예제 #2
0
        // Create a new Category
        public static bool CreateCategory(string departmentId,
                                          string name, string description)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogCreateCategory";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@DepartmentID";
            param.Value         = departmentId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@CategoryName";
            param.Value         = name;
            param.DbType        = DbType.String;
            param.Size          = 50;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@CategoryDescription";
            param.Value         = description;
            param.DbType        = DbType.String;
            param.Size          = 1000;
            comm.Parameters.Add(param);
            // result will represent the number of changed rows
            int result = -1;

            try
            {
                // execute the stored procedure
                result = GenericDataAccess.ExecuteNonQuery(comm);
            }
            catch
            {
                // any errors are logged in GenericDataAccess, we ignore them here
            }
            // result will be 1 in case of success
            return(result != -1);
        }
예제 #3
0
        // retrieve the list of products in a category
        public static DataTable GetAllProductsInCategory(string categoryId)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogGetAllProductsInCategory";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@CategoryID";
            param.Value         = categoryId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // execute the stored procedure and save the results in a DataTable
            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);

            return(table);
        }
예제 #4
0
        public static DataTable GetProductsOnFrontPromo(string pageNumber, out int howManyPages)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogGetProductsOnFrontPromo";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@DescriptionLength";
            param.Value         = VacationReservationsConfiguration.ProductDescriptionLength;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@PageNumber";
            param.Value         = pageNumber;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ProductsPerPage";
            param.Value         = VacationReservationsConfiguration.ProductsPerPage;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@HowManyProducts";
            param.Direction     = ParameterDirection.Output;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            // calculate how many pages of products and set the out parameter
            int howManyProducts = Int32.Parse(comm.Parameters
                                              ["@HowManyProducts"].Value.ToString());

            howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)VacationReservationsConfiguration.ProductsPerPage);
            // return the page of products
            return(table);
        }
예제 #5
0
        // move product to a new category
        public static bool MoveProductToCategory(string productId, string oldCategoryId,
                                                 string newCategoryId)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogMoveProductToCategory";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@ProductID";
            param.Value         = productId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@OldCategoryID";
            param.Value         = oldCategoryId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@NewCategoryID";
            param.Value         = newCategoryId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // result will represent the number of changed rows
            int result = -1;

            try
            {
                // execute the stored procedure
                result = GenericDataAccess.ExecuteNonQuery(comm);
            }
            catch
            {
                // any errors are logged in GenericDataAccess, we ignore them here
            }
            // result will be 1 in case of success
            return(result != -1);
        }
예제 #6
0
        // Update the quantity of a shopping cart item
        public static bool UpdateItem(string productId, int quantity)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "ShoppingCartUpdateItem";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@CartID";
            param.Value         = shoppingCartId;
            param.DbType        = DbType.String;
            param.Size          = 36;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ProductID";
            param.Value         = productId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@Quantity";
            param.Value         = quantity;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // returns true in case of success or false in case of an error
            try
            {
                // execute the stored procedure and return true if it executes
                // successfully, or false otherwise
                return(GenericDataAccess.ExecuteNonQuery(comm) != -1);
            }
            catch
            {
                // prevent the exception from propagating, but return false to
                // signal the error
                return(false);
            }
        }
예제 #7
0
        // Add a new shopping cart item
        public static bool AddReview(string customerId, string productId, string review)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogAddProductReview ";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@CustomerID";
            param.Value         = customerId;
            param.DbType        = DbType.String;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ProductID";
            param.Value         = productId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@Review";
            param.Value         = review;
            param.DbType        = DbType.String;
            comm.Parameters.Add(param);
            // returns true in case of success or false in case of an error
            try
            {
                // execute the stored procedure and return true if it executes
                // successfully, or false otherwise
                return(GenericDataAccess.ExecuteNonQuery(comm) != -1);
            }
            catch
            {
                // prevent the exception from propagating, but return false to
                // signal the error
                return(false);
            }
        }
예제 #8
0
        public static string CreateCommerceLibOrder(int shippingId, int taxId)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CreateCustomerOrder";
            // create parameters
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@CartID";
            param.Value         = shoppingCartId;
            param.DbType        = DbType.String;
            param.Size          = 36;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@CustomerId";
            param.Value         =
                Membership.GetUser(
                    HttpContext.Current.User.Identity.Name)
                .ProviderUserKey;
            param.DbType = DbType.Guid;
            param.Size   = 16;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ShippingId";
            param.Value         = shippingId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@TaxId";
            param.Value         = taxId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // return the result table
            return(GenericDataAccess.ExecuteScalar(comm));
        }
예제 #9
0
        // Get product details
        public static ProductDetails GetProductDetails(string productId)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogGetProductDetails";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@ProductID";
            param.Value         = productId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure
            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            // wrap retrieved data into a ProductDetails object
            ProductDetails details = new ProductDetails();

            if (table.Rows.Count > 0)
            {
                // get the first table row
                DataRow dr = table.Rows[0];
                // get product details
                details.ProductID   = int.Parse(productId);
                details.Name        = dr["Name"].ToString();
                details.Description = dr["Description"].ToString();
                details.Price       = Decimal.Parse(dr["Price"].ToString());
                details.Thumbnail   = dr["Thumbnail"].ToString();
                details.Image       = dr["Image"].ToString();
                details.PromoFront  = bool.Parse(dr["PromoFront"].ToString());
                details.PromoDept   =
                    bool.Parse(dr["PromoDept"].ToString());
            }
            // return department details
            return(details);
        }
예제 #10
0
        // gets product recommendations
        public static DataTable GetRecommendations(string productId)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogGetProductRecommendations";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@ProductID";
            param.Value         = productId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@DescriptionLength";
            param.Value         = VacationReservationsConfiguration.ProductDescriptionLength;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // execute the stored procedure
            return(GenericDataAccess.ExecuteSelectCommand(comm));
        }
예제 #11
0
        public static CommerceLibOrderInfo GetOrder(string orderID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CommerceLibOrderGetInfo";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@OrderID";
            param.Value         = orderID;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // obtain the results
            DataTable table    = GenericDataAccess.ExecuteSelectCommand(comm);
            DataRow   orderRow = table.Rows[0];
            // save the results into an CommerceLibOrderInfo object
            CommerceLibOrderInfo orderInfo =
                new CommerceLibOrderInfo(orderRow);

            return(orderInfo);
        }
예제 #12
0
        // Update an existing product
        public static bool UpdateProduct(string productId, string name, string description, string price, string Thumbnail, string Image, string PromoDept, string PromoFront)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "CatalogUpdateProduct";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@ProductID";
            param.Value         = productId;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ProductName";
            param.Value         = name;
            param.DbType        = DbType.String;
            param.Size          = 50;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ProductDescription";
            param.Value         = description;
            param.DbType        = DbType.String;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@Price";
            param.Value         = price;
            param.DbType        = DbType.Decimal;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@Thumbnail";
            param.Value         = Thumbnail;
            param.DbType        = DbType.String;
            param.Size          = 50;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@Image";
            param.Value         = Image;
            param.DbType        = DbType.String;
            param.Size          = 50;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@PromoDept";
            param.Value         = PromoDept;
            param.DbType        = DbType.Boolean;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@PromoFront";
            param.Value         = PromoFront;
            param.DbType        = DbType.Boolean;
            comm.Parameters.Add(param);
            // result will represent the number of changed rows
            int result = -1;

            try
            {
                // execute the stored procedure
                result = GenericDataAccess.ExecuteNonQuery(comm);
            }
            catch
            {
                // any errors are logged in GenericDataAccess, we ignore them here
            }
            // result will be 1 in case of success
            return(result != -1);
        }
예제 #13
0
        public static DataTable Search(string searchString, string allWords,
                                       string pageNumber, out int howManyPages)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            // set the stored procedure name
            comm.CommandText = "SearchCatalog";
            // create a new parameter
            DbParameter param = comm.CreateParameter();

            param.ParameterName = "@DescriptionLength";
            param.Value         = VacationReservationsConfiguration.ProductDescriptionLength;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@AllWords";
            param.Value         = allWords.ToUpper() == "TRUE" ? "1" : "0";
            param.DbType        = DbType.Byte;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@PageNumber";
            param.Value         = pageNumber;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@ProductsPerPage";
            param.Value         = VacationReservationsConfiguration.ProductsPerPage;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);
            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@HowManyResults";
            param.Direction     = ParameterDirection.Output;
            param.DbType        = DbType.Int32;
            comm.Parameters.Add(param);

            // define the maximum number of words
            int howManyWords = 5;

            // transform search string into array of words
            string[] words = Regex.Split(searchString, "[^a-zA-Zа-яА-Я0-9]+");

            // add the words as stored procedure parameters
            int index = 1;

            for (int i = 0; i <= words.GetUpperBound(0) && index <= howManyWords; i++)
            {
                // ignore short words
                if (words[i].Length > 2)
                {
                    // create the @Word parameters
                    param = comm.CreateParameter();
                    param.ParameterName = "@Word" + index.ToString();
                    param.Value         = words[i];
                    param.DbType        = DbType.String;
                    comm.Parameters.Add(param);
                    index++;
                }
            }

            // execute the stored procedure and save the results in a DataTable
            DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
            // calculate how many pages of products and set the out parameter
            int howManyProducts =
                Int32.Parse(comm.Parameters["@HowManyResults"].Value.ToString());

            howManyPages = (int)Math.Ceiling((double)howManyProducts /
                                             (double)VacationReservationsConfiguration.ProductsPerPage);
            // return the page of products
            return(table);
        }