Пример #1
0
        /// <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;
            }
        }
Пример #2
0
        /// <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;
            }
        }
Пример #3
0
        /// <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;
            }
        }
Пример #4
0
        /// <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;
            }
        }
Пример #5
0
        /// <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;
            }
        }
Пример #6
0
        /// <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;
            }
        }
Пример #7
0
        /// <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;
            }
        }
Пример #8
0
        /// <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;
            }
        }
Пример #9
0
        /// <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;
            }
        }
Пример #10
0
 /// <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;
         }
     }
 }
Пример #11
0
        /// <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;
            }
        }
Пример #12
0
 /// <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;
         }
     }
 }