public List <ProductModel> GetAll(string searchPattern = "")
{
using (IDbConnection cnn = new SqlConnection(_sqlDataAccess.GetConnectionString()))
{
string sql = "SELECT P.Id, P.CategoryId, P.ItemId, P.Name, P.PriceNet, P.PriceGross, P.Tax, " +
"C.Id, C.Code, C.Name " +
"FROM Products P LEFT JOIN Categories C ON C.Id = P.CategoryId " +
$"WHERE P.ItemId LIKE '%{searchPattern}%' OR P.Name LIKE '%{searchPattern}%' " +
"ORDER BY C.[Name], P.ItemId ";
var result = cnn.Query <ProductModel, CategoryModel, ProductModel>(sql,
(product, category) => { product.PrimaryCategory = category; return(product); });
return(result.ToList());
}
}
public async Task <ComponentBugOptionsModel> GetComponentBugOptions()
{
ComponentBugOptionsModel optionsList = new ComponentBugOptionsModel();
try
{
string connString = _sqlDataAccess.GetConnectionString(DataAccessConstant.HandiBugTrackerConn);
using (IDbConnection conn = new SqlConnection(connString))
{
var result = await conn.QueryMultipleAsync(DataAccessConstant.SP_ComponentBug_GetAllOptions);
optionsList.BugPriorityList = result.Read <BugPriorityModel>().ToList();
optionsList.BugSeverityList = result.Read <BugSeverityModel>().ToList();
optionsList.BugStatusList = result.Read <BugStatusModel>().ToList();
optionsList.BugStatusSubStateList = result.Read <BugStatusSubStateModel>().ToList();
optionsList.BugTypeList = result.Read <BugTypeModel>().ToList();
optionsList.ProductList = result.Read <ProductModel>().ToList();
optionsList.ProductHardwareList = result.Read <ProductHardwareModel>().ToList();
optionsList.ProductOSList = result.Read <ProductOSModel>().ToList();
optionsList.ComponentList = result.Read <ComponentModel>().ToList();
optionsList.ProductVersionList = result.Read <ProductVersionModel>().ToList();
}
}
catch (TimeoutException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
}
catch (SqlException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
}
catch (InvalidOperationException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL Invalid operation exception", GetType().FullName), ex);
}
return(optionsList);
}
public void SaveOrder(string buyer, string buyerNotification, double summaryOrderValue, List <OrderLineModelDto> list)
{
using (IDbConnection cnn = new SqlConnection(_sqlDataAccess.GetConnectionString()))
{
cnn.Open();
using (var transaction = cnn.BeginTransaction())
{
string sqlHeader = $"INSERT INTO OrderHeader (Number, BuyerId, Status, BuyerNotification, SummaryValue) " +
$"VALUES ((select dbo.GetOrderNumber()), @BuyerId, @Status, @BuyerNotification, @SummaryValue);" +
$"SELECT CAST(SCOPE_IDENTITY() as int)";
var result = cnn.ExecuteScalar(sqlHeader,
new
{
BuyerId = buyer
,
Status = Const.StatusesList.Where(x => x.Status == StatusEnum.Submitted).FirstOrDefault().StatusId
,
BuyerNotification = buyerNotification
,
SummaryValue = summaryOrderValue
},
transaction: transaction);
int OrderHeaderId = Convert.ToInt32(result);
foreach (var line in list)
{
string sqlLine = $"INSERT INTO OrderLine ([OrderHeaderId], [ItemId], [ItemName], [PriceNet], [PriceGross], [Tax], [SubmittedQty]) " +
$"VALUES (@OrderHeaderId, @ItemId, @ItemName, @PriceNet, @PriceGross, @Tax, @SubmittedQty)";
cnn.Execute(sqlLine,
new { OrderHeaderId, line.ItemId, line.ItemName, line.PriceNet, line.PriceGross, line.Tax, line.SubmittedQty },
transaction: transaction);
}
transaction.Commit();
}
}
}
public CommentController(ISqlDataAccess sqlDataAccess)
{
_sqlDA = sqlDataAccess;
_connString = _sqlDA.GetConnectionString("MyConnection");
}
public AdministratorController(ISqlDataAccess sqlDataAccess)
{
_sqlDA = sqlDataAccess;
_connString = _sqlDA.GetConnectionString("MyConnection");
}