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