// 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)); }
// 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); }
// 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); }
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); }
// 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); }
// 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); } }
// 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); } }
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)); }
// 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); }
// 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)); }
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); }
// 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); }
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); }