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