static async Task <List <ItemInventoryType> > Decrement(string sku) { List <ItemInventoryType> inventoryInfo = new List <ItemInventoryType>(); try { string updateSql = $"UPDATE Inventory SET Quantity = i.Quantity - 1 FROM Inventory as i where i.sku = '{sku}'"; var selectSql = $"SELECT Sku, Quantity, Modified FROM Inventory WHERE Sku = '{sku}'"; string sqlConnectionString = Environment.GetEnvironmentVariable("sqlConnectionString"); using (SqlConnection conn = new SqlConnection(sqlConnectionString)) { await conn.OpenAsync(); using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { await cmd.ExecuteNonQueryAsync(); cmd.CommandText = selectSql; var reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { if (await reader.ReadAsync()) { var inventoryItem = new ItemInventoryType { Sku = reader.GetString(0), Quantity = reader.GetInt32(1), Modified = reader.GetDateTime(2) }; inventoryInfo.Add(inventoryItem); reader.Close(); } } } } } catch (Exception) { // Do some logging here } return(inventoryInfo); }
public static async Task <IActionResult> RunGetInventoryForSKU( [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "inventory")] HttpRequest req, ILogger log) { try { List <ItemInventoryType> inventoryInfo = new List <ItemInventoryType>(); // going to assume only 1 sku at a time string sku = req.Query["skus"]; string sqlConnectionString = Environment.GetEnvironmentVariable("sqlConnectionString"); using (SqlConnection conn = new SqlConnection(sqlConnectionString)) { await conn.OpenAsync(); var sql = $"SELECT Sku, Quantity, Modified FROM Inventory WHERE Sku = '{sku}'"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { var reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { if (await reader.ReadAsync()) { var inventoryItem = new ItemInventoryType { Sku = reader.GetString(0), Quantity = reader.GetInt32(1), Modified = reader.GetDateTime(2) }; inventoryInfo.Add(inventoryItem); reader.Close(); } } else { reader.Close(); // Just add a dummy one for next time string insertSQL = $"INSERT INTO Inventory (Sku, Quantity, Modified) VALUES ('{sku}', 12, GETDATE())"; cmd.CommandText = insertSQL; await cmd.ExecuteNonQueryAsync(); var inventoryItem = new ItemInventoryType { Sku = sku, Quantity = 12, Modified = DateTime.Now }; inventoryInfo.Add(inventoryItem); } } } return(new OkObjectResult(inventoryInfo)); } catch (Exception ex) { log.LogError(ex, "Inventory Finder"); return(new StatusCodeResult(500)); } }