Stored Procedures
Product_Insert
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
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
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, 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
GO
C# Code
Product.cs
using System;
using System.ComponentModel.DataAnnotations;
// This is the model for one row in the database table. You may need to make some adjustments.
namespace BlazorPurchaseOrders.Data
{
public class Product
{
[Required]
public int ProductID { get; set; }
[Required]
[StringLength(25)]
public string ProductCode { get; set; }
[Required]
[StringLength(50)]
public string ProductDescription { get; set; }
[Required]
public decimal ProductUnitPrice { get; set; }
public int ProductSupplierID { get; set; }
[Required]
public bool ProductIsArchived { get; set; }
public string SupplierName { get; }
}
}
ProductService.cs
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace BlazorPurchaseOrders.Data
{
public class ProductService : IProductService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public ProductService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a Product table row (SQL Insert)
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;
}
// Get a list of product rows (SQL Select)
public async Task<IEnumerable<Product>> ProductList()
{
IEnumerable<Product> products;
using (var conn = new SqlConnection(_configuration.Value))
{
products = await conn.QueryAsync<Product>("spProduct_List", commandType: CommandType.StoredProcedure);
}
return products;
}
// Get one product based on its ProductID (SQL Select)
public async Task<Product> Product_GetOne(int @ProductID)
{
Product product = new Product();
var parameters = new DynamicParameters();
parameters.Add("@ProductID", ProductID, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
product = await conn.QueryFirstOrDefaultAsync<Product>("spProduct_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return product;
}
// Update one Product row based on its ProductID (SQL Update)
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
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<Product> Product_GetOne(int ProductID);
Task<int> ProductUpdate(
int ProductID,
string ProductCode,
string ProductDescription,
decimal ProductUnitPrice,
Int32 ProductSupplierID,
bool ProductIsArchived
);
}
}
<div class="top-row pl-4 navbar navbar-dark">
<a class="navbar-brand" href="">BlazorPurchaseOrders</a>
<button class="navbar-toggler" @onclick="ToggleNavMenu">
<span class="navbar-toggler-icon"></span>
</button>
</div>
<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
<ul class="nav flex-column">
<li class="nav-item px-3">
<NavLink class="nav-link" href="" Match="NavLinkMatch.All">
<span class="oi oi-home" aria-hidden="true"></span> Home
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="counter">
<span class="oi oi-plus" aria-hidden="true"></span> Counter
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="tax">
<span class="oi oi-list-rich" aria-hidden="true"></span> Tax Rates
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="supplier">
<span class="oi oi-list-rich" aria-hidden="true"></span> Suppliers
</NavLink>
</li>
<li class="nav-item px-3">
<NavLink class="nav-link" href="product">
<span class="oi oi-list-rich" aria-hidden="true"></span> Products
</NavLink>
</li>
</ul>
</div>
@code {
private bool collapseNavMenu = true;
private string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
private void ToggleNavMenu()
{
collapseNavMenu = !collapseNavMenu;
}
}
@page "/product"
@using BlazorPurchaseOrders.Data
@inject IProductService ProductService
@inject ISupplierService SupplierService
<h3>Products</h3>
<br />
<SfGrid DataSource="@product"
Toolbar="@Toolbaritems"
AllowResizing="true">
<GridColumns>
<GridColumn Field="@nameof(Product.ProductCode)"
HeaderText="Code"
TextAlign="TextAlign.Left"
Width="40">
</GridColumn>
<GridColumn Field="@nameof(Product.ProductDescription)"
HeaderText="Description"
TextAlign="TextAlign.Left"
Width="60">
</GridColumn>
<GridColumn Field="@nameof(Product.ProductUnitPrice)"
HeaderText="Unit Price"
TextAlign="TextAlign.Right"
Format="N2"
Width="40">
</GridColumn>
<GridColumn Field="@nameof(Product.SupplierName)"
HeaderText="Supplier"
TextAlign="TextAlign.Left"
Width="60">
</GridColumn>
</GridColumns>
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="Product"></GridEvents>
</SfGrid>
<SfDialog @ref="DialogAddEditProduct" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> @HeaderText </Header>
</DialogTemplates>
<EditForm Model="@addeditProduct" OnValidSubmit="@ProductSave">
<div>
<SfTextBox Enabled="true" Placeholder="Product Code"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditProduct.ProductCode"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="Description"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditProduct.ProductDescription"></SfTextBox>
<SfNumericTextBox Enabled="true" Placeholder="Unit Price"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditProduct.ProductUnitPrice"></SfNumericTextBox>
<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>
</div>
<br /><br />
<div class="e-footer-content">
<div class="button-container">
<button type="button" class="e-btn e-normal" @onclick="@CloseDialog">Cancel</button>
<button type="submit" class="e-btn e-normal e-primary">Save</button>
</div>
</div>
</EditForm>
</SfDialog>
<SfDialog @ref="DialogDeleteProduct" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content>
<SfTextBox Enabled="false" Placeholder="Product Code"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditProduct.ProductCode"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="Description"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditProduct.ProductDescription"></SfTextBox>
<SfNumericTextBox Enabled="false" Placeholder="Unit Price"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditProduct.ProductUnitPrice"></SfNumericTextBox>
<SfDropDownList DataSource="@supplier"
TItem="Supplier"
TValue="int"
Text="SupplierID"
@bind-Value="addeditProduct.ProductSupplierID"
FloatLabelType="@FloatLabelType.Auto"
Placeholder="Select a Supplier"
Enabled="false">
<DropDownListFieldSettings Text="SupplierName" Value="SupplierID"></DropDownListFieldSettings>
</SfDropDownList>
<br />
<br />
<span class="text-danger">Please confirm that you want to delete this record</span>
</Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="Delete" IsPrimary="true" OnClick="@ConfirmDeleteYes" />
<DialogButton Content="Cancel" IsPrimary="false" OnClick="@ConfirmDeleteNo" />
</DialogButtons>
</SfDialog>
<WarningPage @ref="Warning" WarningHeaderMessage="@WarningHeaderMessage" WarningContentMessage="@WarningContentMessage" />
<style>
.e-control-wrapper.ToUpperCase .e-textbox {
text-transform: uppercase;
}
</style>
@code {
IEnumerable<Product> product;
IEnumerable<Supplier> supplier;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
SfDialog DialogAddEditProduct;
Product addeditProduct = new Product();
string HeaderText = "";
WarningPage Warning;
string WarningHeaderMessage = "";
string WarningContentMessage = "";
public int SelectedProductId { get; set; } = 0;
SfDialog DialogDeleteProduct;
protected override async Task OnInitializedAsync()
{
//Populate the list of Product objects from the Product table.
product = await ProductService.ProductList();
supplier = await SupplierService.SupplierList();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new Product", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected Product", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected Product", PrefixIcon = "e-delete" });
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
addeditProduct = new Product(); // Ensures a blank form when adding
HeaderText = "Add Product";
await this.DialogAddEditProduct.Show();
}
if (args.Item.Text == "Edit")
{
//Code for editing goes here
//Check that a Product Rate has been selected
if (SelectedProductId == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Product from the grid.";
Warning.OpenDialog();
}
else
{
//populate addeditProduct (temporary data set used for the editing process)
HeaderText = "Edit Product";
addeditProduct = await ProductService.Product_GetOne(SelectedProductId);
await this.DialogAddEditProduct.Show();
}
}
if (args.Item.Text == "Delete")
{
//code for deleting goes here
if (SelectedProductId == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Product from the grid.";
Warning.OpenDialog();
}
else
{
//populate addeditProduct (temporary data set used for the editing process)
HeaderText = "Delete Product";
addeditProduct = await ProductService.Product_GetOne(SelectedProductId);
await this.DialogDeleteProduct.Show();
}
}
}
protected async Task ProductSave()
{
if (addeditProduct.ProductID == 0)
{
int Success = await ProductService.ProductInsert(
addeditProduct.ProductCode,
addeditProduct.ProductDescription,
addeditProduct.ProductUnitPrice,
addeditProduct.ProductSupplierID
);
if (Success != 0)
{
//Product Rate already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Product Description already exists; it cannot be added again.";
Warning.OpenDialog();
// Data is left in the dialog so the user can see the problem.
}
else
{
// Clears the dialog and is ready for another entry
// User must specifically close or cancel the dialog
addeditProduct = new Product();
}
}
else
{
// Item is being edited
int Success = await ProductService.ProductUpdate(
SelectedProductId,
addeditProduct.ProductCode,
addeditProduct.ProductDescription,
addeditProduct.ProductUnitPrice,
addeditProduct.ProductSupplierID,
addeditProduct.ProductIsArchived);
if (Success != 0)
{
//Product Rate already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Product already exists; it cannot be added again.";
Warning.OpenDialog();
}
else
{
await this.DialogAddEditProduct.Hide();
this.StateHasChanged();
addeditProduct = new Product();
SelectedProductId = 0;
}
}
//Always refresh datagrid
product = await ProductService.ProductList();
StateHasChanged();
}
private async Task CloseDialog()
{
await this.DialogAddEditProduct.Hide();
}
public void RowSelectHandler(RowSelectEventArgs<Product> args)
{
//{args.Data} returns the current selected records.
SelectedProductId = args.Data.ProductID;
}
public async void ConfirmDeleteNo()
{
await DialogDeleteProduct.Hide();
SelectedProductId = 0;
}
public async void ConfirmDeleteYes()
{
int Success = await ProductService.ProductUpdate(
SelectedProductId,
addeditProduct.ProductCode,
addeditProduct.ProductDescription,
addeditProduct.ProductUnitPrice,
addeditProduct.ProductSupplierID,
addeditProduct.ProductIsArchived = true);
if (Success != 0)
{
//Product Rate already exists - THis should never happen when marking a record 'IsArchived'.
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Unknown error has occurred - the record has not been deleted!";
Warning.OpenDialog();
}
else
{
await this.DialogDeleteProduct.Hide();
product = await ProductService.ProductList();
this.StateHasChanged();
addeditProduct = new Product();
SelectedProductId = 0;
}
}
}