// Function returns the In_Store_Order with the specified order_id from the database public async Task <In_Store_Order> GetById(int order_id) { using (NpgsqlConnection sql = new NpgsqlConnection(_connectionString)) // Specifying the database context { using (NpgsqlCommand cmd = new NpgsqlCommand("\"spIn_Store_Order_GetById\"", sql)) // Specifying stored procedure { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new NpgsqlParameter("order_id", NpgsqlDbType.Integer)); cmd.Parameters[0].Value = order_id; In_Store_Order response = null; await sql.OpenAsync(); // Parsing the data retrieved from the database using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { response = MapToValue(reader); } } return(response); } } }
public async Task ModifyById(In_Store_Order in_store_order) { using (NpgsqlConnection sql = new NpgsqlConnection(_connectionString)) { using (NpgsqlCommand cmd = new NpgsqlCommand("\"spIn_Store_Order_ModifyById\"", sql)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new NpgsqlParameter("order_id", NpgsqlDbType.Integer)); cmd.Parameters.Add(new NpgsqlParameter("waiter_id", NpgsqlDbType.Integer)); cmd.Parameters[0].Value = in_store_order.Order_ID; cmd.Parameters[1].Value = in_store_order.waiter_ID; await sql.OpenAsync(); await cmd.ExecuteNonQueryAsync(); return; } } }
// Function inserts an In_Store_Order record in the database public async Task Insert(In_Store_Order in_store_order) { using (NpgsqlConnection sql = new NpgsqlConnection(_connectionString)) // Specifying the database context { using (NpgsqlCommand cmd = new NpgsqlCommand("\"spIn_Store_Order_InsertValue\"", sql)) // Specifying stored procedure { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new NpgsqlParameter("order_id", NpgsqlDbType.Integer)); cmd.Parameters.Add(new NpgsqlParameter("tableno", NpgsqlDbType.Integer)); cmd.Parameters[0].Value = in_store_order.Order_ID; cmd.Parameters[1].Value = in_store_order.TableNo; await sql.OpenAsync(); await cmd.ExecuteNonQueryAsync(); return; } } }
public async Task <ActionResult> Put(int order_id, [FromBody] In_Store_Order in_store_order) { if (order_id != in_store_order.Order_ID) { // If id from body and id from URL don't match return(BadRequest("id in URL has to match the id of the record to be updated\n")); } try { // Searching for record var response = await _repository.GetById(order_id); if (response == null) { // If record does not exist return(NotFound("In_Store_Order record was not found\n")); } else { // Record exists, then modify it await _repository.ModifyById(in_store_order); string format = "In_Store_Order record with key={0} was updated succesfully\n"; return(Ok(String.Format(format, order_id))); } } catch (Npgsql.PostgresException ex) { // Postgres threw some exception return(BadRequest(ex.Message.ToString())); } catch { // Unknown error return(BadRequest("Error: In_Store_Order record could not be updated\n")); } }