Orders - Part 4

Introduction

At present we are saving the purchase order header to the SQL database, but are not saving the purchase order lines; in this part we will tackle this topic.

Warning

What follows is NOT best practice, but more of that later.

Background

To link purchase order lines to the relevant purchase order header, the purchase order line table has a column called 'POLineHeaderID'  which records the 'POHeaderID' of the purchase order header.  (POLineHeaderID is the foreign key on the child table POLine, linking to the primary key of the parent table POHeader.)

Saving of the full purchase order is a two-part process, the saving of POHeader and then saving POLine.  The problem is that before we can save POLine we need to know the POHeaderID from POHeader so that we can write this to the POLine records.

We will start by making changes to the saving of the POHeader so that it returns the POHeaderID.  The first part of this is to change the SQL stored procedure. At present the SQL stored procedure does not return any value (other than whether it has succeeded or not).  The stored procedure can be modified so that it returns values and we can use this to return the ID of the newly inserted record.  The steps are, in SQL,

  • declare a parameter for the ID and specify it as 'OUTPUT'. For simplicity I have called this @Output, but it could be called anything.
  • after the code for the insert, set the new output parameter to the new ID.  As this is an identity column we do this using 'SET @Output = Scope_Identity()'.

(I have also removed the parameter POHeaderIsArchived from the parameters, the 'Insert Into' and 'Values' clauses.  These will default to 0 and are therefore not needed.)

Use the following script to modify the POHeader_Insert stored procedure.

USE [PurchaseOrders]
GO
/****** Object:  StoredProcedure [dbo].[spPOHeader_Insert] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPOHeader_Insert]
--Parameters for Insert stored procedure
@POHeaderOrderDate date,
@POHeaderSupplierID int,
@POHeaderSupplierAddress1 nvarchar(50),
@POHeaderSupplierAddress2 nvarchar(50),
@POHeaderSupplierAddress3 nvarchar(50),
@POHeaderSupplierPostCode nvarchar(10),
@POHeaderSupplierEmail nvarchar(256),
@POHeaderRequestedBy nvarchar(450),
@Output int OUTPUT
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO POHeader(POHeaderOrderNumber, POHeaderOrderDate, POHeaderSupplierID, POHeaderSupplierAddress1, POHeaderSupplierAddress2, POHeaderSupplierAddress3, POHeaderSupplierPostCode, POHeaderSupplierEmail, POHeaderRequestedBy) 
VALUES (NEXT VALUE FOR NextPurchaseOrderNumber, @POHeaderOrderDate, @POHeaderSupplierID, @POHeaderSupplierAddress1, @POHeaderSupplierAddress2, @POHeaderSupplierAddress3, @POHeaderSupplierPostCode, @POHeaderSupplierEmail, @POHeaderRequestedBy)
SET @Output = SCOPE_IDENTITY()
END

The next step is to modify POHeaderService so that 'POHeaderInsert' reflects, amongst others, the change of type from bool to int and the passing in of individual fields and the addition of an output parameter.

The code for the POHeaderInsert method is shown below:

// Add (create) a POHeader table row (SQL Insert)
        public async Task<int> POHeaderInsert(
                DateTime POHeaderOrderDate,                
                int POHeaderSupplierID,
                string POHeaderSupplierAddress1,
                string POHeaderSupplierAddress2,
                string POHeaderSupplierAddress3,
                string POHeaderSupplierPostCode,
                string POHeaderSupplierEmail,
                string POHeaderRequestedBy)
        {
            int newHeaderID = 0;
            using (var conn = new SqlConnection(_configuration.Value))
            {                
                var parameters = new DynamicParameters();                
                parameters.Add("POHeaderOrderDate", POHeaderOrderDate, DbType.Date);
                parameters.Add("POHeaderSupplierID", POHeaderSupplierID, DbType.Int32);
                parameters.Add("POHeaderSupplierAddress1", POHeaderSupplierAddress1, DbType.String);
                parameters.Add("POHeaderSupplierAddress2", POHeaderSupplierAddress2, DbType.String);
                parameters.Add("POHeaderSupplierAddress3", POHeaderSupplierAddress3, DbType.String);
                parameters.Add("POHeaderSupplierPostCode", POHeaderSupplierPostCode, DbType.String);
                parameters.Add("POHeaderSupplierEmail", POHeaderSupplierEmail, DbType.String);
                parameters.Add("POHeaderRequestedBy", POHeaderRequestedBy, DbType.String);

                parameters.Add("@Output", DbType.Int32, direction: ParameterDirection.Output);

                // Stored procedure method
                await conn.ExecuteAsync("spPOHeader_Insert", parameters, commandType: CommandType.StoredProcedure);

                newHeaderID = parameters.Get<int>("@Output");
            };
            return newHeaderID;
        }

Because POHeaderService has changed we also need to amend the interface, IPOHeaderService, as shown below:

using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace BlazorPurchaseOrders.Data
{
    // Each item below provides an interface to a method in POHeaderServices.cs
    public interface IPOHeaderService
    {
        Task<int> POHeaderInsert(
            DateTime POHeaderOrderDate,
            int POHeaderSupplierID,
            string POHeaderSupplierAddress1,
            string POHeaderSupplierAddress2,
            string POHeaderSupplierAddress3,
            string POHeaderSupplierPostCode,
            string POHeaderSupplierEmail,
            string POHeaderRequestedBy
            );
        Task<IEnumerable<POHeader>> POHeaderList();
        Task<POHeader> POHeader_GetOne(int POHeaderID);
        Task<bool> POHeaderUpdate(POHeader poheader);
    }
}

We can now change the 'OrderSave' method on the main PurchaseOrderPage, as shown below.  I have declared an integer to hold the value of the new POHeaderID called 'HeaderID', but again it could be called anything.

The effect of this is that the POHeader is saved to the SQL database and POHeaderID is returned.  We can now assign the value of POHeaderID to each POLine record and save those records.  To do this:

  • Inject the POLineService at the top of the file
  • Within the OrderSave method, cycle round the collection of POLine, assign POHeaderID and save the POLine record.

Save and run the project.  At present the Index page with the list of purchase orders doesn't show any useful detail about an order, neither does the 'Edit' button open the PurchaseOrderPage; to check that data is being saved correctly open SQL Management Studio and select data from the POLine table.

Why this isn't a good idea!

The problem with the code as we have it is that a record can be written to POHeader, but if something fails after that we will have incomplete data; perhaps no records written to POLine or perhaps only the first, say 10 out of 20 lines.

In situations like this we would like all records to be written to both tables or none.  This can be achieved by 'wrapping' the whole code in a single transaction that either gets committed or rolled back if an error occurs.  This can be accomplished either in SQL or in C#.  To be honest, I've looked at both approaches and neither seems particularly simple.  Rather than over-complicating 'OrderSave' I have ignored this for the time being - but hope to return to this subject in a later post.

Project Code

The C# and SQL code for the changes made for saving purchase order lines are shown here.

YouTube Video

Blazor Purchase Orders - Part 13 - Saving Order Lines.

Powered by Publii