public static List <ShippingInfo> GetShippingInfo(int shippingRegionId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CommerceLibShippingGetInfo";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ShippingRegionId";
param.Value = shippingRegionId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// obtain the results
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
List <ShippingInfo> result = new List <ShippingInfo>();
foreach (DataRow row in table.Rows)
{
ShippingInfo rowData = new ShippingInfo();
rowData.ShippingID = int.Parse(row["ShippingId"].ToString());
rowData.ShippingType = row["ShippingType"].ToString();
rowData.ShippingCost =
double.Parse(row["ShippingCost"].ToString());
rowData.ShippingRegionId = shippingRegionId;
result.Add(rowData);
}
return(result);
}
// Get category details
public static CategoryDetails GetCategoryDetails(string categoryId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogGetCategoryDetails";
// 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
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
// wrap retrieved data into a CategoryDetails object
CategoryDetails details = new CategoryDetails();
if (table.Rows.Count > 0)
{
details.DepartmentId = Int32.Parse(table.Rows[0]["DepartmentID"].ToString());
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
// return department details
return(details);
}
public static DataTable GetDepartments()
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetDepartments";
// execute the stored procedure and return the results
return(GenericDataAccess.ExecuteSelectCommand(comm));
}
// Retrieve orders that need to be shipped/completed
public static DataTable GetVerifiedUncompleted()
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "OrdersGetVerifiedUncompleted";
// return the result table
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
// retrieve the list of products in a category
public static DataTable GetProductsInCategory
(string categoryId, string pageNumber, out int howManyPages)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogGetProductsInCategory";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CategoryID";
param.Value = categoryId;
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);
// 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);
}
// get categories that do not contain a specified product
public static DataTable GetCategoriesWithoutProduct(string productId)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "CatalogGetCategoriesWithoutProduct";
// 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
return(GenericDataAccess.ExecuteSelectCommand(comm));
}
// Retrieve the recent orders
public static DataTable GetByRecent(int count)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "OrdersGetByRecent";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Count";
param.Value = count;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the result table
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
public static DataTable GetDetails(string orderID)
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "OrderGetDetails";
// create a new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@OrderID";
param.Value = orderID;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
// return the results
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
// 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);
}
// Retrieve shopping cart items
public static DataTable GetItems()
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "ShoppingCartGetItems";
// 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
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
return(table);
}
// 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);
}
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);
}
