/// <summary>
/// insert user wish list
/// </summary>
/// <param name="userWishList">WishListDAO</param>
/// <returns></returns>
public int AddToWishList(WishListDAO userWishList)
{
Database database = null;
DbCommand insertCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
insertCommand = database.GetStoredProcCommand("uspAddToWishList");
database.AddInParameter(insertCommand, "userId", DbType.String, userWishList.UserId);
database.AddInParameter(insertCommand, "productId", DbType.String, userWishList.ProductId);
return(database.ExecuteNonQuery(insertCommand));
}
catch (SqlException ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->AddToWishList()", ex);
throw new BaseException("DBInsert");
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->AddToWishList()", ex);
throw new BaseException("DBInsert");
}
finally
{
if (insertCommand.Connection.State == System.Data.ConnectionState.Open)
{
insertCommand.Connection.Close();
}
database = null;
}
}
/// <summary>
/// retreives all category information
/// </summary>
/// <returns>table</returns>
public byte[] RetreiveCategoryImage(string categoryId)
{
Database database = null;
DbCommand selectCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
selectCommand = database.GetStoredProcCommand("uspRetreiveCategoryImage");
database.AddInParameter(selectCommand, "categoryId", DbType.String, categoryId);
return(database.ExecuteScalar(selectCommand) as byte[]);
}
catch (Exception ex)
{
SpencerLogger.Error("CategoryDBLL->RetreiveCategoryImage()", ex);
throw new BaseException("DBSelect");
}
finally
{
if (selectCommand.Connection.State == ConnectionState.Open)
{
selectCommand.Connection.Close();
}
database = null;
selectCommand = null;
}
}
/// <summary>
/// retreives user's wish list
/// </summary>
/// <param name="userId">string</param>
/// <returns></returns>
public DataTable MyWishList(string userId)
{
Database database = null;
DbCommand selectCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
selectCommand = database.GetStoredProcCommand("uspMyWishList");
database.AddInParameter(selectCommand, "userId", DbType.String, userId);
return(database.ExecuteDataSet(selectCommand).Tables[0]);
}
catch (SqlException ex)
{
SpencerLogger.Error("UserDetailsDBLL->MyWishList()", ex);
throw new BaseException("DBSelect");
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDBLL->MyWishList()", ex);
throw new BaseException("DBSelect");
}
finally
{
if (selectCommand.Connection.State == ConnectionState.Open)
{
selectCommand.Connection.Close();
}
selectCommand = null;
}
}
/// <summary>
/// Validates the userId in the DB
/// </summary>
/// <param name="userId">string</param>
/// <returns></returns>
public bool ValidateUserId(string userId)
{
Database database = null;
DbCommand insertCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
insertCommand = database.GetStoredProcCommand("uspValidateUser");
database.AddInParameter(insertCommand, "userId", DbType.String, userId);
return((int)database.ExecuteScalar(insertCommand) > 0);
}
catch (SqlException ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->ValidateUser()", ex);
throw new BaseException("DBSelect");
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->ValidateUser()", ex);
throw new BaseException("DBSelect");
}
finally
{
if (insertCommand.Connection.State == System.Data.ConnectionState.Open)
{
insertCommand.Connection.Close();
}
database = null;
}
}
/// <summary>
/// retreives single category image from DB
/// </summary>
/// <param name="categoryId">string</param>
/// <returns></returns>
public DataTable RetreiveAllCategory()
{
Database database = null;
DbCommand selectCommand = null;
try
{
database = DatabaseFactory.CreateDatabase("SpencerConnectionString");
selectCommand = database.GetStoredProcCommand("uspRetreiveAllCategory");
return(database.ExecuteDataSet(selectCommand).Tables[0]);
}
catch (Exception ex)
{
SpencerLogger.Error("CategoryDBLL->SearchCategory()", ex);
throw new BaseException("DBSelect");
}
finally
{
if (selectCommand.Connection.State == ConnectionState.Open)
{
selectCommand.Connection.Close();
}
database = null;
selectCommand = null;
}
}
/// <summary>
/// retreives users comments of a product
/// </summary>
/// <param name="productId">string</param>
/// <returns></returns>
public DataTable RetreiveProductComments(string productId)
{
Database database = null;
DbCommand selectCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
selectCommand = database.GetStoredProcCommand("uspRetreiveProductComments");
database.AddInParameter(selectCommand, "productId", DbType.String, productId);
return(database.ExecuteDataSet(selectCommand).Tables[0]);
}
catch (Exception ex)
{
SpencerLogger.Error("ProductDBLL->RetreiveProductComments()", ex);
throw new BaseException("DBSelect");
}
finally
{
if (selectCommand.Connection.State == ConnectionState.Open)
{
selectCommand.Connection.Close();
}
selectCommand = null;
database = null;
}
}
/// <summary>
/// insertes user comments
/// </summary>
/// <param name="userFeedback">FeedbackDAO</param>
/// <returns></returns>
public int InsertProductComment(FeedbackDAO userFeedback)
{
Database database = null;
DbCommand insertCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
insertCommand = database.GetStoredProcCommand("uspInsertProductComment");
database.AddInParameter(insertCommand, "userId", DbType.String, userFeedback.UserID);
database.AddInParameter(insertCommand, "productId", DbType.String, userFeedback.ProductId);
database.AddInParameter(insertCommand, "description", DbType.String, userFeedback.Description);
return(database.ExecuteNonQuery(insertCommand));
}
catch (Exception ex)
{
SpencerLogger.Error("ProductDBLL->InsertProductComment()", ex);
throw new BaseException("DBInsert");
}
finally
{
if (insertCommand.Connection.State == ConnectionState.Open)
{
insertCommand.Connection.Close();
}
insertCommand = null;
database = null;
}
}
/// <summary>
/// Inserts new user info into DB
/// </summary>
/// <param name="userDetailsDAO">UserDetailDAO</param>
/// <returns></returns>
public int CreateUser(UserDetailDAO userDetailsDAO)
{
Database database = null;
DbCommand insertCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
insertCommand = database.GetStoredProcCommand("uspCreateUser");
database.AddInParameter(insertCommand, "@userId", DbType.String, userDetailsDAO.UserId);
database.AddInParameter(insertCommand, "@password", DbType.String, userDetailsDAO.Password);
database.AddInParameter(insertCommand, "@securityQuestion", DbType.String, userDetailsDAO.SecurityQuestion);
database.AddInParameter(insertCommand, "@answer", DbType.String, userDetailsDAO.Answer);
database.AddInParameter(insertCommand, "@firstName", DbType.String, userDetailsDAO.FirstName);
database.AddInParameter(insertCommand, "@lastName", DbType.String, userDetailsDAO.LastName);
database.AddInParameter(insertCommand, "@address", DbType.String, userDetailsDAO.Address);
database.AddInParameter(insertCommand, "@mobileNo", DbType.String, userDetailsDAO.MobileNo);
database.AddInParameter(insertCommand, "@email", DbType.String, userDetailsDAO.Email);
return(database.ExecuteNonQuery(insertCommand));
}
catch (SqlException ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->CreateUser()", ex);
throw new BaseException("DBInsert");
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->CreateUser()", ex);
throw new BaseException("DBInsert");
}
finally
{
if (insertCommand.Connection.State == System.Data.ConnectionState.Open)
{
insertCommand.Connection.Close();
}
database = null;
}
}
/// <summary>
/// retreives product based on word matches
/// </summary>
/// <param name="productName">string</param>
/// <param name="count">int</param>
/// <returns></returns>
public string[] RetreiveAllProduct(string productName, int count)
{
Database database = null;
DbCommand selectCommand = null;
DataSet dataset = null;
List <string> list = null;
try
{
database = DatabaseFactory.CreateDatabase();
selectCommand = database.GetStoredProcCommand("uspRetreiveAllProduct");
database.AddInParameter(selectCommand, "productName", DbType.String, productName);
database.AddInParameter(selectCommand, "count", DbType.Int16, count);
dataset = database.ExecuteDataSet(selectCommand);
list = new List <string>();
foreach (DataRow row in dataset.Tables[0].Rows)
{
list.Add(row["productName"].ToString());
}
return(list.ToArray());
}
catch (Exception ex)
{
SpencerLogger.Error("ProductDBLL->RetreiveProducts()", ex);
throw new BaseException("DBSelect");
}
finally
{
if (selectCommand.Connection.State == ConnectionState.Open)
{
selectCommand.Connection.Close();
}
selectCommand = null;
database = null;
list = null;
dataset = null;
}
}
/// <summary>
/// inserts user details & retreives orderID
/// </summary>
/// <param name="userTransaction">TransactionDAO</param>
/// <returns></returns>
public string GetOrderId(TransactionDAO userTransaction)
{
using (TransactionScope tranScope = new TransactionScope())
{
Database database = null;
DbCommand insertCommand = null;
DataSet dataset = null;
try
{
database = DatabaseFactory.CreateDatabase();
insertCommand = database.GetStoredProcCommand("uspGetOrderId");
database.AddInParameter(insertCommand, "userId", DbType.String, userTransaction.UserID);
database.AddInParameter(insertCommand, "address", DbType.String, userTransaction.DeliveryAddress);
dataset = database.ExecuteDataSet(insertCommand);
tranScope.Complete();
return(dataset.Tables[0].Rows[0][0].ToString());
}
catch (SqlException ex)
{
SpencerLogger.Error("UserDetailsDBLL->GetOrderId()", ex);
throw new BaseException("DBSelect");
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->GetOrderId()", ex);
throw new BaseException("DBInsert");
}
finally
{
if (insertCommand.Connection.State == ConnectionState.Open)
{
insertCommand.Connection.Close();
}
insertCommand = null;
database = null;
}
}
}
/// <summary>
/// updates userInformation into database
/// </summary>
/// <param name="userDetailsDAO"></param>
/// <returns></returns>
public int UpdatePersonalDetails(UserDetailDAO userDetailsDAO)
{
Database database = null;
DbCommand insertCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
insertCommand = database.GetStoredProcCommand("uspCreateUser");
database.AddInParameter(insertCommand, "userId", DbType.String, userDetailsDAO.UserId);
database.AddInParameter(insertCommand, "firstName", DbType.String, userDetailsDAO.FirstName);
database.AddInParameter(insertCommand, "lastName", DbType.String, userDetailsDAO.LastName);
database.AddInParameter(insertCommand, "address", DbType.String, userDetailsDAO.Address);
database.AddInParameter(insertCommand, "mobileNo", DbType.String, userDetailsDAO.MobileNo);
database.AddInParameter(insertCommand, "email", DbType.String, userDetailsDAO.Email);
database.AddInParameter(insertCommand, "imageType", DbType.String, userDetailsDAO.ImageType);
database.AddInParameter(insertCommand, "image", DbType.Binary, userDetailsDAO.Image);
return(database.ExecuteNonQuery(insertCommand));
}
catch (SqlException ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->UpdatePersonalDetails()", ex);
throw new BaseException("DBInsert");
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->UpdatePersonalDetails()", ex);
throw new BaseException("DBInsert");
}
finally
{
if (insertCommand.Connection.State == System.Data.ConnectionState.Open)
{
insertCommand.Connection.Close();
}
database = null;
}
}
/// <summary>
/// inserts user ordered products in DB
/// </summary>
/// <param name="userOrderList">List<TransactionDetailDAO></param>
/// <returns></returns>
public int InsertOrders(ref List <TransactionDetailDAO> userOrderList)
{
using (TransactionScope tranScope = new TransactionScope())
{
Database database = null;
DbCommand insertCommand = null;
try
{
database = DatabaseFactory.CreateDatabase();
int count = userOrderList.Count;
DataTable orderDetailsTable = new DataTable();
foreach (TransactionDetailDAO orderList in userOrderList)
{
insertCommand = database.GetStoredProcCommand("uspInserBulktOrders");
database.AddInParameter(insertCommand, "orderId", DbType.String, orderList.OrderId);
database.AddInParameter(insertCommand, "orderXmlDoc", DbType.String, orderList.ProductId);
int result = database.ExecuteNonQuery(insertCommand);
}
tranScope.Complete();
return(1);
}
catch (Exception ex)
{
SpencerLogger.Error("UserDetailsDatabaseService->InsertOrder()", ex);
throw new BaseException("DBInsert");
}
finally
{
if (insertCommand.Connection.State == ConnectionState.Open)
{
insertCommand.Connection.Close();
}
insertCommand = null;
database = null;
}
}
}