Products

Introduction

The page to maintain product records is going to follow the pattern established to maintain tax rates and suppliers. 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 product code sequence.

• Product_Insert
• Product_Update
• Product_List

I originally put a foreign key constraint on the Product table 'pointing' to the Supplier table.  Having this foreign key in place means that whenever a record is added to the Product table the ProductSupplierID column must be populated with a valid SupplierID from the Supplier table.  In hindsight I have decided this probably too restrictive; allowing products to be created without being linked to a supplier would allow the user more flexibility.

• I have therefore also included a script to delete the foreign key on the Product table.

C# code for:

• ProductService
• IProductService
• ProductPage
• NavMenu

ProductService and IProductService will be amended to handle the revised SQL stored procedures, whilst ProductPage is a new razor page that will mimic the existing SupplierPage.

Stored Procedures

ProductInsert and ProductUpdate use exactly the same technique used for Suppliers to check that duplicates are not going to be created before insert or updating records.  In this case the check for duplicates is based on a combination of product code and supplier to allow for the fact that different suppliers could, conceivably, use the same product code. ProductList is amended to include only records that have not been archived and to return the records in product code sequence.

Product_Insert

Open SQL Server Management Studio, select the PurchaseOrders database and paste the following code into a New Query window and Execute to replace ProductInsert. Before carrying out the insert it checks that the product code / supplier ID combination doesn't already exist for a non-archived record.

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spProduct_Insert]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-------------- Stored Proc for INSERT
CREATE PROCEDURE [dbo].[spProduct_Insert]
--Parameters for Insert stored procedure
@ProductCode nvarchar(25),
@ProductDescription nvarchar(50),
@ProductUnitPrice money,
@ProductSupplierID int
AS
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Product 
          WHERE ProductCode = @ProductCode 
          AND ProductSupplierID = @ProductSupplierID 
          AND ProductIsArchived = 0
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE  
      BEGIN  
           INSERT INTO Product(ProductCode, ProductDescription, ProductUnitPrice, ProductSupplierID) 
		   VALUES (@ProductCode, @ProductDescription, @ProductUnitPrice, @ProductSupplierID)
           set @ResultValue = @@ERROR 
     END  
IF @ResultValue <> 0  
     BEGIN  
            ROLLBACK TRAN  
      END  
ELSE  
      BEGIN  
            COMMIT TRAN  
      END  
RETURN @ResultValue  

GO

Product_Update

Repeat for ProductUpdate. Basically the same, but also passes in the ProductID (to identify the record to be updated) and ProductIsArchived (to allow the record to be updated to ProductIsArchived = true.)

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spProduct_Update]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spProduct_Update]
-- Parameters for Update stored procedure.
@ProductID int,
@ProductCode nvarchar(25),
@ProductDescription nvarchar(50),
@ProductUnitPrice money,
@ProductSupplierID int,
@ProductIsArchived bit
AS
DECLARE @ResultValue int  
BEGIN TRAN  
IF EXISTS  
    (  
          SELECT * FROM Product 
          WHERE ProductCode = @ProductCode 
		  AND ProductSupplierID = @ProductSupplierID
		  AND ProductIsArchived = 0
		  AND ProductID <> @ProductID
        )  
     BEGIN  
         SET  @ResultValue = 99  
     END  
ELSE  
      BEGIN  
           UPDATE Product
		   SET ProductCode = @ProductCode, 
		   ProductDescription = @ProductDescription, 
		   ProductUnitPrice = @ProductUnitPrice, 
		   ProductSupplierID = @ProductSupplierID, 
		   ProductIsArchived = @ProductIsArchived 
		   WHERE ProductID = @ProductID
           set @ResultValue = @@ERROR 
     END  
IF @ResultValue <> 0  
     BEGIN  
            ROLLBACK TRAN  
      END  
ELSE  
      BEGIN  
            COMMIT TRAN  
      END  
RETURN @ResultValue  

GO

Product_List

Modified to include only non-archived records and ordered by product code and then, should two or more records exist with the same product code, product description.

USE [PurchaseOrders]
GO

DROP PROCEDURE [dbo].[spProduct_List]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--------------  Stored Proc for SELECT
CREATE PROCEDURE [dbo].[spProduct_List]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT Product.ProductID, Product.ProductCode, Product.ProductDescription, Product.ProductUnitPrice
FROM   dbo.Product
WHERE (dbo.Product.ProductIsArchived = 0)
ORDER BY ProductCode, ProductDescription
END

GO

Drop Foreign Key

Drops the supplier foreign key on the product table

ALTER TABLE [Product] DROP CONSTRAINT IF EXISTS FK_Product_Supplier;
GO

C# Code

ProductService.cs

Replace the ProductInsert method with the following: 

        public async Task<int> ProductInsert(
            string ProductCode, 
            string ProductDescription,
            decimal ProductUnitPrice, 
            Int32 ProductSupplierID
            )
        {
            int Success = 0;
            var parameters = new DynamicParameters();
            parameters.Add("ProductCode", ProductCode, DbType.String);
            parameters.Add("ProductDescription", ProductDescription, DbType.String);
            parameters.Add("ProductUnitPrice", ProductUnitPrice, DbType.Decimal);
            parameters.Add("ProductSupplierID", ProductSupplierID, DbType.Int32);
            parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                // Stored procedure method
                await conn.ExecuteAsync("spProduct_Insert", parameters, commandType: CommandType.StoredProcedure);

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

Replace the ProductUpdate method with the following:

public async Task<int> ProductUpdate(
            int ProductID, 
            string ProductCode, 
            string ProductDescription, 
            decimal ProductUnitPrice, 
            Int32 ProductSupplierID, 
            bool ProductIsArchived
            )
        {
            int Success = 0;
            var parameters = new DynamicParameters();
            parameters.Add("ProductID", ProductID, DbType.Int32);
            parameters.Add("ProductCode", ProductCode, DbType.String);
            parameters.Add("ProductDescription", ProductDescription, DbType.String);
            parameters.Add("ProductUnitPrice", ProductUnitPrice, DbType.Decimal);
            parameters.Add("ProductSupplierID", ProductSupplierID, DbType.Int32);
            parameters.Add("ProductIsArchived", ProductIsArchived, DbType.Boolean);
            parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                await conn.ExecuteAsync("spProduct_Update", parameters, commandType: CommandType.StoredProcedure);

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

IProductService.cs

Replace the whole of IProductService with the following:

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

namespace BlazorPurchaseOrders.Data
{
    // Each item below provides an interface to a method in ProductServices.cs
    public interface IProductService
    {
        Task<int> ProductInsert(
            string ProductCode,
            string ProductDescription,
            decimal ProductUnitPrice,
            Int32 ProductSupplierID);
        Task<IEnumerable<Product>> ProductList();
        Task<IEnumerable<Product>> ProductListBySupplier(int SupplierID);
        Task<Product> Product_GetOne(int ProductID);
        Task<int> ProductUpdate(
            int ProductID,
            string ProductCode,
            string ProductDescription,
            decimal ProductUnitPrice,
            Int32 ProductSupplierID,
            bool ProductIsArchived
            );
    }
}

ProductPage.razor

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

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

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

Select Edit > Find and Replace > Quick Replace. Enter 'Supplier' in the find box and 'Product' in the replace box. Make sure you have 'Match Case' highlighted and 'Current Document' selected.  I am normally cautious about using 'Replace all', but found this was fine.

The next tasks are to amend the DataGrid and Dialogs, replacing Supplier columns with appropriate Product columns.

Make sure you have run the SQL script to drop the foreign key constraint on the product table.  You should be able to run the project and add, edit and delete products.

However, we haven't added the ability to record a supplier for each product.  To do this we will add a Syncfusion DropDownList and record the SupplierID from the DropDownList in the ProductSupplierID column of the product.

The DropDownList will display the supplier name, and to this it will use a list of suppliers. To do this we need to do the following:

• Inject ISupplierService SupplierService
• Declare IEnumerable<Supplier>supplier
• Populate the list of suppliers when the form is initialised
• Modify the Dialogs with the Syncfusion DropDownList.

Copy and paste the following under the existing @inject statement.

@inject ISupplierService SupplierService

Copy and paste the following under the existing IEnumerable for Product.

IEnumerable<Supplier> supplier;

Copy and paste the following under the existing statement for product in the OnInitializedAsync method.

supplier = await SupplierService.SupplierList();

Copy and paste the following under the <SfNumericTextBox> in both the AddEditProduct and DeleteProduct dialogs.  In the Delete dialog change Enabled to "false".

            <SfDropDownList DataSource="@supplier"
                            TItem="Supplier"
                            TValue="int"
                            Text="SupplierID"
                            @bind-Value="addeditProduct.ProductSupplierID"
                            FloatLabelType="@FloatLabelType.Auto"
                            Placeholder="Select a Supplier"
                            Enabled="true">
                <DropDownListFieldSettings Text="SupplierName" Value="SupplierID"></DropDownListFieldSettings>
            </SfDropDownList>

Notice that the SfDropDownList has a DataSource, which in this case is "@supplier" (because we want a list of suppliers!), TItem = "Supplier", TValue = "int" (it must be int, not int32, string or anything else) and Text = "SupplierID" (the ID column in the supplier table).  The @bind-Value is the column in the addeditProduct object where we are going to record the SupplierID, and lastly the <DropDownListFieldSettings> have the Text = "SupplierName" (what we want displayed and is the column name from the Supplier table) and Value = "SupplierID" (what we want to be bound to the addeditProduct object).

The above took me quite a time to get right - looking at it now it seems obvious, but it was anything but when I was doing it first.)

As an example, the DialogAddEditProduct should now look similar to this:

Improving the DataGrid

I think the Products DataGrid would be improved by including the supplier as one of the columns, more specifically the supplier name.

We can do this by:

• Modifying the SQL for ProductList so that it includes the supplier name
• Adding the supplier name to the Product model class
• Adding the new column to the DataGrid

ProductList Stored Procedure

The SQL script to alter the stored procedure for Product_List is shown below.  The most significant part of the change is the join between the product table and supplier table.  Having made the join we then return the supplier name from the supplier table.  The 'Left Outer Join' ensures that all records are returned from the products table even if the ProductSupplierID is null.  (Apart from records where ProductIsArchived is not 0.)

USE [PurchaseOrders]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--------------  Stored Proc for SELECT
ALTER PROCEDURE [dbo].[spProduct_List]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT Product.ProductID, Product.ProductCode, Product.ProductDescription, Product.ProductUnitPrice, Supplier.SupplierName
FROM   dbo.Product LEFT OUTER JOIN
           dbo.Supplier ON dbo.Product.ProductSupplierID = dbo.Supplier.SupplierID
WHERE (dbo.Product.ProductIsArchived = 0)
ORDER BY ProductCode, ProductDescription
END

Product.cs

Add 'SupplierName' to the Product model class. As we are only ever going to be getting records there is no need for 'set'.

public string SupplierName { get;  }

ProductPage.razor

In the DataGrid add a further column set the Field="@nameof(Product.SupplierName)" and other attributes as shown below:

        <GridColumn Field="@nameof(Product.SupplierName)"
                    HeaderText="Supplier"
                    TextAlign="TextAlign.Left"
                    Width="60">
        </GridColumn>

Project Code

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

YouTube Video

Blazor Project - Purchase Orders - Part 8 Adding the Product list and maintenance form.