Edit a Saved Order - Part 2

Introduction

In this post, having edited an order, we will finally save changes to the SQL database.

Background

The page used to edit an order is split into two sections, the order header and order lines.  The user can make changes directly to the order header by simply selecting a field, where permitted, and changing the data.  For order lines the user needs to select a row from the grid and then an action from the toolbar above the order lines, either to add new lines, edit existing lines or delete lines.

The crucial part here is that all changes are temporary until the user clicks the 'Save' button; if the user clicks the 'Cancel' button the page will close and all changes will be lost.  Similarly all changes will be lost if the user closes the browser page or clicks the browser back button.

Order Lines

The order header doesn't really cause a problem, but order lines present a number of scenarios, all of which need to be considered and handled.

When editing an order the user can:

  • Add a new line
  • Edit an existing line
  • Edit a new line
  • Delete an existing line
  • Delete a new line

It is worth recalling how we deal with the ID of an order line when adding a new order.  When a new order line is added (but not yet saved to the database), it is assigned a temporary negative number.  (For each order the temporary negative number starts at -1 and decreases for each additional order line.)  When the order is eventually saved it is given a positive ID automatically by the SQL database (POLineID is an identity column).

When a saved order is later opened for editing, the order line records are retrieved from the database with their positive POLineID and used to populate the orderLines list.  If we now add new order lines those new lines will have a temporary negative ID. We can therefore distinguish between existing order lines and new ones.

When the overall order is saved (by the user clicking the 'Save' button on the order page) any existing order lines that have been edited will be used to update the SQL database, whereas new order lines (identified by their negative number) will be inserted to the SQL database.

As an aside, a new order line can be edited before the order is saved, but as it will retain the negative ID assigned to it when originally saved, it will still be treated as a new order line and inserted into the database.

If an order line, added during the edit process, is deleted it doesn't cause much of a problem.  It can simply be deleted from the orderLines list.  It won't have ever been added to the database, so no further action is needed.  However, deleting order lines, during the edit process, that have already been saved to the database, does cause a problem.  We want them to be removed from orderLines, so they don't appear in the order lines grid during subsequent editing, but we need to know which lines have been deleted so they can be deleted from the database when the user clicks the 'Save' button.

To get round this problem I have decided to add a new collection (list), called 'OrderLinesToBeDeleted' and if the user selects an order line with a positive POLineID, that ID should be added to the new list.  When the user saves the order, part of the process will be to cycle through the OrderLinesToBeDeleted and delete them from the database.

Code

Currently the 'Save' button does nothing other than return the user to the Index page.

Order Header

As the Order Header is probably the easiest part of this process we will start with this.  We can make use of the POHeaderUpdate method created by the original code generator.  Insert the following immediately above the line arrowed above.

            //Order is being edited
            //POHeader
            bool Success = await POHeaderService.POHeaderUpdate(orderaddedit);

Order Lines

Update and Insert

The orderLines list will possibly contain a mixture of records with positive and negative IDs.  The positive IDs will need to be updated and the negative ones inserted.  We can use the existing POLineUpdate and POLineInsert methods to achieve this.  Insert the following code below the code we have just inserted for the POHeader (with a line gap).

            //POLines
            foreach (var individualPOLine in orderLines)
            {
                //If POLineHeaderID is positive it means it has been edited during the edit of this order                
                if (individualPOLine.POLineID > 0)
                {
                    Success = await POLineService.POLineUpdate(individualPOLine);
                }
                else
                //If POLineHeaderID is negative it means it has been added during the edit of this order
                {
                    individualPOLine.POLineHeaderID = POHeaderID;
                    Success = await POLineService.POLineInsert(individualPOLine);
                }
            }

Delete

To declare the new list to record order lines to be deleted from the database insert the following somewhere near the top of the code section.  It is just a simple list that is going to record the ID of the order lines to be deleted.

private List<int> OrderLinesToBeDeleted = new List<int>();

To populate this list we need to modify OrderLineDelete so that if the user choses to delete an existing order line, identified by its positive ID, the ID is added to the list.  Insert the following at the top of the 'OrderLineDelete' method.

        if (selectedPOLineID > 0)
        {
            //Order line has already been saved to the database, and was present at start of this order edit
            //Add to list of orders to be deleted when order is saved
            OrderLinesToBeDeleted.Add(selectedPOLineID);
        }

That gets us a list of the records to be deleted, but we have no means of deletion!  We'll start by adding a SQL stored procedure, then add a method to use it to the POLineService, and finally add that to the IPOLineService.

SQL

To create the SQL stored procedure open SQL Management Studio, select the PurchaseOrders database, open a new query window and paste in the following and execute.

USE [PurchaseOrders]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE [dbo].[spPOLine_DeleteOne]
-- Needs one parameter for primary key
@POLineID int
AS 
BEGIN
-- SQL Select for one table row
DELETE FROM POLine WHERE POLineID= @POLineID
END

GO
POLineService.cs

To add the method to delete a single POLine add the following to POLineService.

        public async Task<bool> POLineDeleteOne(int @POLineID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@POLineID", POLineID, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spPOLine_DeleteOne", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
IPOLineService

Add the service to the IPOLineService.

 Task<bool> POLineDeleteOne(int @POLineID);

With these elements in place we can now return to PurchaseOrderPage.razor and complete the code there to delete order lines from the database as part of the OrderSave method.  Place the following code immediately below the previous 'foreach' loop.  This will call the POLineDeleteOne method, and finally clear OrderLinesToBeDeleted.

            foreach (var individualPOLine in OrderLinesToBeDeleted)
            {
                Success = await POLineService.POLineDeleteOne(individualPOLine);
            }
            //Clear the list of POLines to be deleted
            OrderLinesToBeDeleted.Clear();

The part of the OrderSave method for editing an order should now look like this:

Save the project and run to test it.

Project Code

The code for all the files changed in this post are shown here.

YouTube Video

Blazor Purchase Orders - Part 18

Powered by Publii