Suppliers

by Christopher Bell

Introduction

The page to maintain supplier records is going to follow the pattern established to maintain tax rates.  This involves:

Altering the SQL stored procedures so the Insert and Update handle the prevention of duplicates, and the List includes only non-archived records and returns them in supplier name sequence.

  • SupplierInsert
  • SupplierUpdate
  • SupplierList

C# code for:

  • SupplierService
  • ISupplierService
  • SupplierPage
  • Change the menu to include SupplierPage

SupplierService and ISupplierService will be amended to handle the revised SQL stored procedures, whilst SupplierPage is a new razor page that will mimic the existing TaxPage.

Stored Procedures

SupplierInsert and SupplierUpdate use exactly the same technique used for Tax Rates to check that duplicates are not going to be created before insert or updating records.  SupplierList is amended to include only records that have not been archived.

SupplierInsert

Open SQL Server Management Studio, select the PurchaseOrders database and paste the following code into a New Query window and Execute to replace SupplierInsert.  Before carrying out the insert it checks that the supplier name doesn't already exist for a live record.

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spSupplier_Insert]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-------------- Stored Proc for INSERT
CREATE PROCEDURE [dbo].[spSupplier_Insert]
--Parameters for Insert stored procedure
@SupplierName nvarchar(50),
@SupplierAddress1 nvarchar(50),
@SupplierAddress2 nvarchar(50),
@SupplierAddress3 nvarchar(50),
@SupplierPostCode nvarchar(10),
@SupplierEmail nvarchar(256)
AS
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Supplier  
          WHERE SupplierName = @SupplierName AND SupplierIsArchived = 0
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE  
      BEGIN  
           INSERT INTO Supplier(SupplierName, SupplierAddress1, SupplierAddress2, SupplierAddress3, SupplierPostCode, SupplierEmail) 
		   VALUES (@SupplierName, @SupplierAddress1, @SupplierAddress2, @SupplierAddress3, @SupplierPostCode, @SupplierEmail)
           set @ResultValue = @@ERROR 
     END  
IF @ResultValue <> 0  
     BEGIN  
            ROLLBACK TRAN  
      END  
ELSE  
      BEGIN  
            COMMIT TRAN  
      END  
RETURN @ResultValue  

GO

SupplierUpdate

Repeat for SupplierUpdate.  Basically the same, but also passes in the SupplierID (to identify the record to be updated) and SupplierIsArchived (to allow the record to be updated to SupplierIsArchived = true.)

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spSupplier_Update]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spSupplier_Update]
-- Parameters for Update stored procedure.
@SupplierID int,
@SupplierName nvarchar(50),
@SupplierAddress1 nvarchar(50),
@SupplierAddress2 nvarchar(50),
@SupplierAddress3 nvarchar(50),
@SupplierPostCode nvarchar(10),
@SupplierEmail nvarchar(256),
@SupplierIsArchived bit
AS
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Supplier  
          WHERE (SupplierName = @SupplierName) AND SupplierID <> @SupplierID AND SupplierIsArchived = 0
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE 
	BEGIN
			UPDATE Supplier SET SupplierName = @SupplierName, 
			SupplierAddress1 = @SupplierAddress1,
			SupplierAddress2 = @SupplierAddress2,
			SupplierAddress3 = @SupplierAddress3,
			SupplierPostCode = @SupplierPostCode,
			SupplierEmail = @SupplierEmail,
			SupplierIsArchived = @SupplierIsArchived
			WHERE SupplierId = @SupplierID
			set @ResultValue = @@ERROR
	END

IF @ResultValue <> 0  
     BEGIN  
            ROLLBACK TRAN  
      END  
ELSE  
      BEGIN  
            COMMIT TRAN  
      END  
RETURN @ResultValue  
GO

SupplierList

Modified to include only non-archived records and ordered by supplier name.

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spSupplier_List]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spSupplier_List]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT SupplierID, SupplierName, SupplierAddress1, SupplierAddress2, SupplierAddress3, 
SupplierPostCode, SupplierEmail, SupplierIsArchived
FROM Supplier 
WHERE SupplierIsArchived = 0
ORDER BY SupplierName ASC
END

GO

C# Code

SupplierService.cs

Replace the SupplierInsert method with the following: 

        public async Task<int> SupplierInsert(string SupplierName, string SupplierAddress1,
            string SupplierAddress2, string SupplierAddress3, string SupplierPostCode,
            string SupplierEmail)
        {
            int Success = 0;
            var parameters = new DynamicParameters();
            parameters.Add("SupplierName", SupplierName, DbType.String);
            parameters.Add("SupplierAddress1", SupplierAddress1, DbType.String);
            parameters.Add("SupplierAddress2", SupplierAddress2, DbType.String);
            parameters.Add("SupplierAddress3", SupplierAddress3, DbType.String);
            parameters.Add("SupplierPostCode", SupplierPostCode, DbType.String);
            parameters.Add("SupplierEmail", SupplierEmail, DbType.String);
            parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                // Stored procedure method
                await conn.ExecuteAsync("spSupplier_Insert", parameters, commandType: CommandType.StoredProcedure);

                Success = parameters.Get<int>("@ReturnValue");
            }
            return Success;
        }

Replace the SupplierUpdate method with the following:

        public async Task<int> SupplierUpdate(int SupplierID, string SupplierName, string SupplierAddress1,
        string SupplierAddress2, string SupplierAddress3, string SupplierPostCode,
        string SupplierEmail, bool SupplierIsArchived)
        {
            int Success = 0;
            var parameters = new DynamicParameters();
            parameters.Add("SupplierID", SupplierID, DbType.Int32);
            parameters.Add("SupplierName", SupplierName, DbType.String);            
            parameters.Add("SupplierAddress1", SupplierAddress1, DbType.String);
            parameters.Add("SupplierAddress2", SupplierAddress2, DbType.String);
            parameters.Add("SupplierAddress3", SupplierAddress3, DbType.String);
            parameters.Add("SupplierPostCode", SupplierPostCode, DbType.String);
            parameters.Add("SupplierEmail", SupplierEmail, DbType.String);
            parameters.Add("SupplierIsArchived", SupplierIsArchived, DbType.Boolean);
            parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spSupplier_Update", parameters, commandType: CommandType.StoredProcedure);

                Success = parameters.Get<int>
                ("@ReturnValue");
            }
            return Success;
        }

ISupplierService.cs

Copy the code from below and replace all code in ISupplierService.

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

namespace BlazorPurchaseOrders.Data
{
    // Each item below provides an interface to a method in SupplierServices.cs
    public interface ISupplierService
    {
        Task<int> SupplierInsert(string SupplierName, string SupplierAddress1,
            string SupplierAddress2, string SupplierAddress3, string SupplierPostCode,
            string SupplierEmail);
        Task<IEnumerable<Supplier>> SupplierList();
        Task<Supplier> Supplier_GetOne(int SupplierID);
        Task<int> SupplierUpdate(int SupplierID, string SupplierName, string SupplierAddress1,
            string SupplierAddress2, string SupplierAddress3, string SupplierPostCode,
            string SupplierEmail, bool SupplierIsArchived);
    }
}

SupplierPage.razor

The SupplierPage is going to be copy of TaxPage, but with changes to handle the different columns in the supplier model.  Start by selecting the Pages folder, right-clicking and adding a new Razor component; call it SupplierPage.

Open TaxPage, copy everything and paste over the auto-generated code in SupplierPage.

We will replace all occurrences of 'Tax' with 'Supplier' and 'tax' with 'supplier'. This won't make all the changes we need, but will be a start!

Select Edit > Find and Replace > Quick Replace.  Enter 'Tax' in the find box and 'Supplier' in the replace box.  Make sure you have 'Match Case' highlighted and 'Current Document' selected.

A cautious approach is probably best, so click the 'Replace Next' button (Alt-R) and work you way through the document...  You will notice that items will be replaced where further changes will be needed, e.g. in model column names as well as descriptions.

Repeat, this time replacing 'tax' with 'supplier', again making sure that 'Match Case' is selected.

This will have done most of the hard work, but column names, etc. will need changing.  In the DataGrid change as shown below.  Note that I have added a further grid column, and that all columns are left-aligned and the Format attribute has been removed. 

In the DialogAddEditSupplier we have more fields than for the TaxPage, so additional TextBoxes must be added (and the NumericTextBox removed).  I have also played around with the 'FloatLabelType' so that no labels are shown for a couple of the address lines - I think it makes the dialog look a little less cluttered.

In the DialogDeleteSupplier we will also want the dialog to mirror the Add/Edit dialog, but with 'Enabled' set to false.  The easiest way to do this is to copy SfTextBox section from DialogAddEditSupplier and change Enabled from "true" to "false", as shown below:

The 'SupplierSave', 'SupplierUpdate' and 'ConfirmDeleteYes' methods need updating for the Supplier model fields, replacing the Tax fields.  (Don't forget to add the 'SupplierIsArchived = 'true' for the delete method.)

Having made these changes the application should run, but some of the text items, such as the toolbar tooltips, header text for the Warning and possibly other items will need checking and changing to reflect that this is now for suppliers.  Searching for 'Description' and 'Rate' should find most mis-matches.

Edit NavMenu.razor, copy and paste back the last <li> section and then update so that href="supplier" and the menu item is 'Suppliers'.

Run the application, add a few records, edit some and delete a couple to test that all the functions work as expected.

Improvements

Post Code to uppercase

Something that always annoys me is the situation where a data entry field that should have uppercase letters allows the user to enter lowercase.  Currently the supplier post code field suffers from this fault.

We will fix this problem at two levels; the first place to fix is at the SQL level.  We can alter the Insert and Update stored procedures so that anything entered in the Post Code field is always converted to uppercase as it is written to the database.  This is simply achieved by modifying the stored procedures by enclosing the @SupplierPostCode parameter with UPPER(...) as shown below:

The above will make sure the database has the post code in uppercase, but as the user enters the post code it will still show as lowercase (if that is how the user entered it).  A minor improvement would be to convert any lowercase letter into uppercase immediately as it was typed.  This can be achieved by adding some CSS styling to the post code field.  To do this, add 'CssClass="ToUpperCase"' within the SfTextbox tag for post code in the DialogAddEditSupplier, and then add the following before the @code line.  (There is no significance to my choice of CssClass name of "ToUpperCase".)

<style>
    .e-control-wrapper.ToUpperCase .e-textbox {
        text-transform: uppercase;
    }
</style>

Concatenated Address

There is one other thing that we can improve upon.  At present the DataGrid shows the first line of the address.  This gives a hint of where the supplier is located, but doesn't tell the whole story.  We could add additional columns to the grid for address line 2, etc., but we would soon run out of space.  It would, in my opinion, look a lot better if we could present the whole address, with the different lines separated by commas in the Address column.

I don't claim any responsibility for what follows!  It demonstrates the power of the web more than anything, and I found it without too much difficulty on StackOverflow.  (I once heard a speaker say that 90% of all code originates, one way or another, from StackOverflow - so I am following a well beaten path!) 

SQL Stored Procedure

The stored procedure (SupplierList) that returns a list of suppliers can be modified to include a new column, which I have called 'CombinedAddress', by adding a comma after 'SupplierIsArchived' in the Select statement and then adding the following:

CombinedAddress = STUFF(
      COALESCE(', ' + NULLIF(RTRIM(SupplierAddress1), ''), '') 
    + COALESCE(', ' + NULLIF(RTRIM(SupplierAddress2), ''), '') 
    + COALESCE(', ' + NULLIF(RTRIM(SupplierAddress3), ''), '')
	+ COALESCE(', ' + NULLIF(RTRIM(SupplierPostCode), ''), '')
    , 1, 2, '')

I'm on shaky ground here, but this is how I think it works, working outwards.

  • RTRIM(....) removes any trailing spaces, should there be any at the end of an address line
  • NULLIF(...) compares two expressions and returns a Null if they are the same, otherwise it returns the first expression.  In this case it compares 'SupplierAddress1' (for example) with an empty string ('').  If SupplierAddress1 happens to be an empty string it returns Null, or if not it returns SupplierAddress1.
  • The ', ' + RTRIM(....) uses the fact that "anything + null = null"; thus if, say, SupplierAddress1 is null then ', '+ SupplierAddress1 will be null.
  • COALESCE(....) returns the first non-null value in a list.  Therefore if SupplierAddress1 is not null it will return ', address line 1' and similarly if SupplierAddress2 is not null it will return ', address line 2'.  If, however, SupplierAddress1 is null it will return '' (i.e. empty string).
  • The + signs between the COALESCE statements concatenate the outputs from the COALESCE statements into a single string.
  • STUFF is a bit specialist!  It comprises 4 elements,
    • An Input string
    • Start position
    • Length
    • A New string
  • STUFF takes the Input string, and at the Start position deletes a number of characters defined be Length and inserts the New string at that position.

In our case STUFF takes the 'coalesced' string (which will be all the non-null address lines stuck together with a leading comma and space), starts at position 1, deletes 2 characters and replaces them with '' (nothing).  I.e. just deletes the first two characters.

The overall effect is just what we need, all address lines concatenated together with comma separators benween the 

The SQL script for the revised SupplierList stored procedure is:

 USE [PurchaseOrders]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
ALTER PROCEDURE [dbo].[spSupplier_List]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT SupplierID, SupplierName, SupplierAddress1, SupplierAddress2, SupplierAddress3, 
SupplierPostCode, SupplierEmail, SupplierIsArchived,
CombinedAddress = STUFF(
      COALESCE(', ' + RTRIM(SupplierAddress1), '') 
    + COALESCE(', ' + RTRIM(SupplierAddress2), '') 
    + COALESCE(', ' + RTRIM(SupplierAddress3), '')
	+ COALESCE(', ' + RTRIM(SupplierPostCode), '')
    , 1, 2, '')
FROM Supplier 
WHERE SupplierIsArchived = 0
ORDER BY SupplierName ASC
END

Supplier.cs

For this change to the stored procedure to work we also need to change the Supplier model to include the new item.  Add the following to the end of Supplier.cs.  (Note we only need 'get';)

public string CombinedAddress { get; }

SupplierPage.razor

To change the Supplier page, amend the second <GridColumn> so that the field now references 'CombinedAddress'.  I have also included 'AllowResizing' in the <SfGrid> tag to allow the user to re-size columns if they require, and have also changed the relative widths of the columns.

The finished page should now look similar to this:

Project Code

The code for the stored procedures and C# for Suppliers can be found here.

YouTube Video

Blazor Project - Purchase Orders - Part 7 Adding the Supplier list and maintenance form, including ensuring post codes are displayed, and stored to the database in uppercase, together with the concatenation of separate address lines into a single string.

Blazor Project - Purchase Orders - Part 7 Update  Corrects SQL to handle empty strings in address lines when creating a combined address.