public async Task <RxPriceDTO> FindAll(string name) { var parameters = new { Name = $"%{name}%" }; var sql = $@"SELECT rp.Id, rp.Name, rp.Quantity, rp.Dose, rp.Price, rp.Location, v.Id AS VendorId, v.Name AS Vendor, v.Url, u.Id AS UserId, u.Name AS Username FROM RxPrices AS rp INNER JOIN Vendors AS v ON rp.VendorId = v.Id LEFT JOIN RxPriceUsers AS ru ON rp.Id = ru.RxPriceId LEFT JOIN Users AS u ON ru.UserId = u.Id WHERE LOWER(rp.Name) LIKE LOWER(@Name) ORDER BY rp.Price"; using (var connection = new SqlConnection(_config.GetConnectionString("Default"))) { var rxPrices = await connection.QueryAsync(sql, parameters); var rxPriceDTO = new RxPriceDTO { Method = $"Find All RxPrices: {name}", Count = rxPrices.Count(), RxPrices = rxPrices }; return(rxPriceDTO); } }
public async Task <RxPriceDTO> FindBy(string name, string column, string value) { if (column.Length > 8) { throw new ArgumentException($"Invalid Column Argument: {column}!"); } var parameters = new { Name = $"%{name}%", Value = value }; var sql = $@"SELECT rp.Id, rp.Name, rp.Quantity, rp.Dose, rp.Price, rp.Location, v.Id AS VendorId, v.Name AS Vendor, v.Url FROM RxPrices AS rp INNER JOIN Vendors AS v ON rp.VendorId = v.Id WHERE LOWER(rp.Name) LIKE LOWER(@Name) AND rp.{column} = @Value ORDER BY rp.Price"; using (var connection = new SqlConnection(_config.GetConnectionString("Default"))) { var rxPrices = await connection.QueryAsync(sql, parameters); var rxPriceDTO = new RxPriceDTO { Method = $"Find {name} By {column}: {value}", Count = rxPrices.Count(), RxPrices = rxPrices }; return(rxPriceDTO); } }
public async Task <RxPriceDTO> FindMedication(string name, string location, float price) { var parameters = new { Name = $"%{name}%", Location = $"%{location}%", Price = price }; var sql = @"SELECT rp.Id, rp.Name, rp.Quantity, rp.Dose, rp.Price, rp.Location, v.Id AS VendorId, v.Name AS Vendor, v.Url FROM RxPrices AS rp INNER JOIN Vendors AS v ON rp.VendorId = v.Id WHERE LOWER(rp.Name) LIKE LOWER(@Name) AND LOWER(rp.Location) LIKE LOWER(@Location) AND rp.Price <= @Price ORDER BY rp.Price"; using (var connection = new SqlConnection(_config.GetConnectionString("Default"))) { var rxPrices = await connection.QueryAsync(sql, parameters); var rxPriceDTO = new RxPriceDTO { Method = $"Get Medication: {name}", Count = rxPrices.Count(), RxPrices = rxPrices }; return(rxPriceDTO); } }