/// <summary> /// Get Product list based on order id /// </summary> /// <param name="_orderId"></param> /// <returns></returns> public List <ProductEntity> GetProductDetails(int _orderId) { List <ProductEntity> _productEntity = new List <ProductEntity>(); try { using (var dao = DbActivity.Open()) { CSqlDbCommand cmd = new CSqlDbCommand(DataHelper.DBCommands.USP_GET_PRODUCTLIST); cmd.Parameters.AddWithValue("OrderId", _orderId); dao.ExecReader(cmd); while (dao.DataReader.Read()) { _productEntity.Add(new ProductEntity() { OrderId = dao.DataReader["OrderId"].ToInt(), SKUID = dao.DataReader["SKUID"].ToInt(), Name = dao.DataReader["Name"].ToStr(), Weight = dao.DataReader["Weight"].ToDecimal(), Height = dao.DataReader["Height"].ToDecimal(), Quantity = dao.DataReader["Quantity"].ToInt(), Amount = dao.DataReader["Amount"].ToDecimal(), Image = dao.DataReader["Image"].ToStr(), Barcode = dao.DataReader["Barcode"] == DBNull.Value ? new byte() :Convert.ToByte(dao.DataReader["Barcode"]), }); } } } catch (Exception ex) { LoggerMgr.Web.Error(LoggerMgr.GetErrorMessageRootText(DataHelper.Layer.DataAccessLayer, DataHelper.DataAccess.OrdersContext, MethodInfo.GetCurrentMethod().Name), ex); throw; } return(_productEntity); }
/// <summary> /// Validate User login details /// </summary> /// <param name="UserName"></param> /// <param name="Password"></param> /// <returns></returns> public UserInfoEntity ValidateUser(string UserName, string Password) { UserInfoEntity _userInfo = new UserInfoEntity(); try { using (var dao = DbActivity.Open()) { CSqlDbCommand cmd = new CSqlDbCommand(DataHelper.DBCommands.VALIDATE_USERINFO); cmd.Parameters.AddWithValue("UserName", UserName); cmd.Parameters.AddWithValue("Password", Password); dao.ExecReader(cmd); while (dao.DataReader.Read()) { _userInfo = new UserInfoEntity(); _userInfo.UserId = dao.DataReader["UserId"].ToInt(); _userInfo.UserName = dao.DataReader["UserName"].ToStr(); _userInfo.Password = dao.DataReader["Password"].ToStr(); _userInfo.Role = dao.DataReader["Role"].ToStr(); _userInfo.UpdatedOn = dao.DataReader["UpdatedOn"].ToDateTime(); _userInfo.ExpiredDate = dao.DataReader["ExpiredDate"].ToDateTime(); } } } catch (Exception ex) { LoggerMgr.Web.Error(LoggerMgr.GetErrorMessageRootText(DataHelper.Layer.DataAccessLayer, DataHelper.DataAccess.OrdersContext, MethodInfo.GetCurrentMethod().Name), ex); throw; } return(_userInfo); }
/// <summary> /// Save order information /// </summary> /// <param name="_ordersInfo"></param> public int SaveOrderlist(OrdersEntity _ordersInfo) { int ResultsId = 0; using (var dao = DbActivity.Open()) { try { dao.BeginTrans(); CSqlDbCommand cmd = new CSqlDbCommand(DataHelper.DBCommands.USP_SAVE_ORDERINFO); cmd.Parameters.AddWithValue("OrderId", _ordersInfo.OrderId); cmd.Parameters.AddWithValue("BuyerId", _ordersInfo.BuyerId); cmd.Parameters.AddWithValue("OrderStatus", _ordersInfo.OrderStatus); cmd.Parameters.AddWithValue("LastName", _ordersInfo.BuyerInfoDetail.LastName); cmd.Parameters.AddWithValue("FirstName", _ordersInfo.BuyerInfoDetail.FirstName); cmd.Parameters.AddWithValue("Mobile", _ordersInfo.BuyerInfoDetail.PhoneNumber); cmd.Parameters.AddWithValue("Email", _ordersInfo.BuyerInfoDetail.Email); cmd.Parameters.AddWithValue("Address", _ordersInfo.BuyerInfoDetail.Address); cmd.Parameters.Add("BuyerResultId", _ordersInfo.BuyerInfoDetail.BuyerId, ParameterDirection.InputOutput, DbType.Int32); dao.ExecCommand(cmd); ResultsId = dao.Parameters["BuyerResultId"].Value.ToInt(); SaveProductsInfo(_ordersInfo.ProductInfoDetail, ResultsId, dao); dao.Commit(); } catch (Exception ex) { dao.Rollback(); LoggerMgr.Web.Error(LoggerMgr.GetErrorMessageRootText(DataHelper.Layer.DataAccessLayer, DataHelper.DataAccess.OrdersContext, MethodInfo.GetCurrentMethod().Name), ex); throw; } } return(ResultsId); }
/// <summary> /// Save Products Information /// </summary> /// <param name="_buyerEntity"></param> /// <returns></returns> public void SaveProductsInfo(List <ProductEntity> _product, int _orderId, IDataAccess dao) { try { CSqlDbCommand cmd = new CSqlDbCommand(DataHelper.DBCommands.USP_Save_PRODUCTINFO); foreach (ProductEntity _pr in _product) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("OrderId", _orderId); cmd.Parameters.AddWithValue("SKUID", _pr.SKUID); cmd.Parameters.AddWithValue("Name", _pr.Name); cmd.Parameters.AddWithValue("Weight", _pr.Weight); cmd.Parameters.AddWithValue("Height", _pr.Height); cmd.Parameters.AddWithValue("Quantity", _pr.Quantity); cmd.Parameters.AddWithValue("Amount", _pr.Weight); //cmd.Parameters.AddWithValue("Barcode", _pr.Barcode); cmd.Parameters.AddWithValue("Image", _pr.Image); dao.ExecCommand(cmd); } } catch (Exception ex) { LoggerMgr.Web.Error(LoggerMgr.GetErrorMessageRootText(DataHelper.Layer.DataAccessLayer, DataHelper.DataAccess.OrdersContext, MethodInfo.GetCurrentMethod().Name), ex); throw; } }
/// <summary> /// Execute Scalar /// </summary> /// <param name="commandName">Stored Procedure Name</param> /// <param name="paramters">SQL parameter collection</param> /// <returns></returns> public override object ExecScalar(CSqlDbCommand command) { OpenConnection(); SqlCommand sqlCmd; try { sqlCmd = _sqlCon.CreateCommand(); sqlCmd.CommandText = command.CommandText; //set transaction object if (_sqlTransaction != null) { sqlCmd.Transaction = _sqlTransaction; } //set command timeout if (command.CommandTimeout != 0) { sqlCmd.CommandTimeout = command.CommandTimeout; } else if (this.CommandTimeout != 0) { sqlCmd.CommandTimeout = this.CommandTimeout; } //set Command Type if (command.CommandType != 0) { sqlCmd.CommandType = command.CommandType; } else { sqlCmd.CommandType = this.CommandType; } //set parameters if (command.Parameters != null && command.Parameters.Count > 0) { sqlCmd.Parameters.AddRange(GetParametersWithNativeType(command.Parameters)); } return(sqlCmd.ExecuteScalar()); } catch (Exception ex) { LoggerMgr.Web.Error("Error occurred at SqlDataAccess - method ExecScalar" + ex.Message); throw; } finally { if (_sqlTransaction == null) { CloseConnection(); } } }
/// <summary> /// Create Sql Command /// </summary> /// <param name="commandText"></param> /// <returns></returns> public CSqlDbCommand CreateCommand(string commandText) { var cmd = new CSqlDbCommand(commandText); Parameters = cmd.Parameters; return(cmd); }
/// <summary> /// /Create Sql Command /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <param name="collection"></param> /// <returns></returns> public CSqlDbCommand CreateCommand(string commandText, CommandType cmdType, CSqlDbParameterCollection collection) { var cmd = new CSqlDbCommand(commandText, cmdType); cmd.Parameters = collection; Parameters = cmd.Parameters; return(cmd); }
/// <summary> /// Extension method for IDataAccess /// </summary> /// <param name="dao"></param> /// <param name="cmd"></param> /// <param name="action"></param> public static void ExecReader(IDataAccess dao, CSqlDbCommand cmd, Action <System.Data.IDataReader> action) { dao.ExecReader(cmd); var reader = dao.DataReader; while (reader.Read()) { action(reader); } dao.CloseDataReader(); }
/// <summary> /// Execute Reader /// </summary> /// <param name="procName">Procedure Name</param> /// <param name="sqlParams">SqlParameterCollection /// use the GetInitCollection() method to init the SqlparameterCollection. /// Eg: SqlParameterCollection sqlParams = GetInitCollection(); /// </param> /// <returns></returns> public override void ExecReader(CSqlDbCommand command) { OpenConnection(); SqlCommand cmd = _sqlCon.CreateCommand(); try { cmd.CommandText = command.CommandText; //Command Type if (command.CommandType != 0) { cmd.CommandType = command.CommandType; } else { cmd.CommandType = this.CommandType; } if (command.CommandTimeout != 0) { cmd.CommandTimeout = command.CommandTimeout; } else if (this.CommandTimeout != 0) { cmd.CommandTimeout = this.CommandTimeout; } if (_sqlTransaction != null) { cmd.Transaction = _sqlTransaction; } if (command.Parameters != null && command.Parameters.Count > 0) { cmd.Parameters.AddRange(GetParametersWithNativeType(command.Parameters)); } this._dataReader = cmd.ExecuteReader(); GetOutputParamterValuesIncludeInput((DbParameterCollection)cmd.Parameters, "@"); } catch (Exception ex) { LoggerMgr.Web.Error("Error occurred at SqlDataAccess - method ExecReader" + ex.Message); throw; } }
/// <summary> /// Fill command result set into dataSet variable /// </summary> /// <param name="dataSet">DataSet object</param> /// <param name="commandName">Stored Procedure Name</param> /// <param name="paramters">SQL parameter colelction</param> public override void FillData(DataSet dataSet, CSqlDbCommand command) { SqlCommand cmd = _sqlCon.CreateCommand(); if (command.CommandTimeout != 0) { cmd.CommandTimeout = command.CommandTimeout; } else if (this.CommandTimeout != 0) { cmd.CommandTimeout = this.CommandTimeout; } cmd.CommandText = command.CommandText; //Command Type if (command.CommandType != 0) { cmd.CommandType = command.CommandType; } else { cmd.CommandType = this.CommandType; } if (command.Parameters != null && command.Parameters.Count > 0) { cmd.Parameters.AddRange(GetParametersWithNativeType(command.Parameters)); } SqlDataAdapter adp = new SqlDataAdapter(cmd); try { adp.Fill(dataSet); } catch (Exception ex) { LoggerMgr.Web.Error("Error occurred at SqlDataAccess - method FillData" + ex.Message); throw; } }
/// <summary> /// Get Orderlist /// </summary> /// <returns></returns> public List <OrdersEntity> GetAllOrders(int?_orderId) { List <OrdersEntity> _ordersEntity = new List <OrdersEntity>(); try { using (var dao = DbActivity.Open()) { CSqlDbCommand cmd = new CSqlDbCommand(DataHelper.DBCommands.USP_GET_ORDERLIST); cmd.Parameters.AddWithValue("OrderId", _orderId); dao.ExecReader(cmd); while (dao.DataReader.Read()) { _ordersEntity.Add(new OrdersEntity() { OrderId = dao.DataReader["OrderId"].ToInt(), BuyerId = dao.DataReader["BuyerId"].ToInt(), OrderStatus = dao.DataReader["Status_Code"].ToStr(), BuyerInfoDetail = new BuyerEntity() { BuyerId = dao.DataReader["BuyerId"].ToInt(), LastName = dao.DataReader["LastName"].ToStr(), FirstName = dao.DataReader["FirstName"].ToStr(), PhoneNumber = dao.DataReader["Mobile"].ToStr(), Email = dao.DataReader["Email"].ToStr(), Address = dao.DataReader["Address"].ToStr() }, ProductInfoDetail = GetProductDetails(dao.DataReader["OrderId"].ToInt()) }); } } } catch (Exception ex) { LoggerMgr.Web.Error(LoggerMgr.GetErrorMessageRootText(DataHelper.Layer.DataAccessLayer, DataHelper.DataAccess.OrdersContext, MethodInfo.GetCurrentMethod().Name), ex); throw; } return(_ordersEntity); }
/// <summary> /// Delete Order id /// </summary> /// <param name="_orderId"></param> /// <returns></returns> public int DeleteOrderId(int _orderId) { int result = 0; using (var dao = DbActivity.Open()) { try { CSqlDbCommand cmd = new CSqlDbCommand(DataHelper.DBCommands.USP_DELETE_ORDER); cmd.Parameters.AddWithValue("OrderId", _orderId); result = dao.ExecCommand(cmd); dao.Commit(); } catch (Exception ex) { dao.Rollback(); LoggerMgr.Web.Error(LoggerMgr.GetErrorMessageRootText(DataHelper.Layer.DataAccessLayer, DataHelper.DataAccess.OrdersContext, MethodInfo.GetCurrentMethod().Name), ex); throw; } return(result); } }
/// <summary> /// SQL data access specific method /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <returns></returns> public IList <object[]> Query(string commandText, CommandType cmdType) { CSqlDbCommand cmd = new CSqlDbCommand(commandText, cmdType); ExecReader(cmd); List <object[]> list = new List <object[]>(); while (DataReader.Read()) { object[] data = new object[DataReader.FieldCount]; for (int index = 0; index < DataReader.FieldCount; index++) { data[index] = DataReader[index]; } list.Add(data); } CloseDataReader(); return(list); }
/// <summary> /// Fill Data into dataset /// </summary> /// <param name="dataSet"></param> /// <param name="command"></param> public abstract void FillData(System.Data.DataSet dataSet, CSqlDbCommand command);
/// <summary> /// SQL Execute Command /// </summary> /// <param name="command"></param> /// <returns></returns> public abstract int ExecCommand(CSqlDbCommand command);
/// <summary> /// Execute SQL Stored Procedure /// </summary> /// <param name="commandName">Stored Procedure Name</param> /// <param name="paramters">SQL parameter colelction</param> /// <returns></returns> public override int ExecCommand(CSqlDbCommand command) { OpenConnection(); SqlCommand sqlCmd; try { sqlCmd = _sqlCon.CreateCommand(); sqlCmd.CommandText = command.CommandText; //Command Type if (command.CommandType != 0) { sqlCmd.CommandType = command.CommandType; } else { sqlCmd.CommandType = this.CommandType; } if (command.CommandTimeout != 0) { sqlCmd.CommandTimeout = command.CommandTimeout; } else if (this.CommandTimeout != 0) { sqlCmd.CommandTimeout = this.CommandTimeout; } if (_sqlTransaction != null) { sqlCmd.Transaction = _sqlTransaction; } if (sqlCmd.CommandTimeout != 0) { sqlCmd.CommandTimeout = CommandTimeout; } if (command.Parameters != null && command.Parameters.Count > 0) { sqlCmd.Parameters.AddRange(GetParametersWithNativeType(command.Parameters)); } int i = sqlCmd.ExecuteNonQuery(); GetOutputParamterValuesIncludeInput((DbParameterCollection)sqlCmd.Parameters, "@"); return(i); } catch (Exception ex) { LoggerMgr.Web.Error("Error occurred at SqlDataAccess - method ExecCommand " + ex.Message); throw; } finally { if (_sqlTransaction == null) { CloseConnection(); } } }
/// <summary> /// This call the underlying ExecuteReader and also it close the data reader. /// Since 3.5.1.7 /// </summary> /// <param name="command"></param> public abstract void ExecReader(CSqlDbCommand command);
/// <summary> /// This call the underlying ExecuteReader and also it close the data reader. /// Since 3.5.1.7 /// </summary> /// <param name="dbCommand"></param> /// <param name="reader"></param> public void ExecReader(CSqlDbCommand dbCommand, Action <IDataReader> reader) { this.ExecReader(dbCommand); reader(this.DataReader); this.CloseDataReader(); }
/// <summary> /// SQL ExecuteScalar /// </summary> /// <param name="command"></param> /// <returns></returns> public abstract object ExecScalar(CSqlDbCommand command);