Stored Procedures
Supplier_Insert
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, UPPER(@SupplierPostCode), @SupplierEmail)
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
Supplier_Update
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 = UPPER(@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
Supplier_List
USE [PurchaseOrders]
GO
DROP PROCEDURE [dbo].[spSupplier_List]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT
CREATE 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(', ' + NULLIF(RTRIM(SupplierAddress1), ''), '')
+ COALESCE(', ' + NULLIF(RTRIM(SupplierAddress2), ''), '')
+ COALESCE(', ' + NULLIF(RTRIM(SupplierAddress3), ''), '')
+ COALESCE(', ' + NULLIF(RTRIM(SupplierPostCode), ''), '')
, 1, 2, '')
FROM Supplier
WHERE SupplierIsArchived = 0
ORDER BY SupplierName ASC
END
GO
C# Code
SupplierService.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 SupplierService : ISupplierService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public SupplierService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a Supplier table row (SQL Insert)
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;
}
// Get a list of supplier rows (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<IEnumerable<Supplier>> SupplierList()
{
IEnumerable<Supplier> suppliers;
using (var conn = new SqlConnection(_configuration.Value))
{
suppliers = await conn.QueryAsync<Supplier>("spSupplier_List", commandType: CommandType.StoredProcedure);
}
return suppliers;
}
// Get one supplier based on its SupplierID (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<Supplier> Supplier_GetOne(int @SupplierID)
{
Supplier supplier = new Supplier();
var parameters = new DynamicParameters();
parameters.Add("@SupplierID", SupplierID, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
supplier = await conn.QueryFirstOrDefaultAsync<Supplier>("spSupplier_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return supplier;
}
// Update one Supplier row based on its SupplierID (SQL Update)
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
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);
}
}
@page "/supplier"
@using BlazorPurchaseOrders.Data
@inject ISupplierService SupplierService
@using Syncfusion.Blazor.Navigations
<h3>Suppliers</h3>
<br />
<SfGrid DataSource="@supplier"
Toolbar="@Toolbaritems"
AllowResizing="true">
<GridColumns>
<GridColumn Field="@nameof(Supplier.SupplierName)"
HeaderText="Supplier Name"
TextAlign="TextAlign.Left"
Width="30">
</GridColumn>
<GridColumn Field="@nameof(Supplier.CombinedAddress)"
HeaderText="Address"
TextAlign="TextAlign.Left"
Width="60">
</GridColumn>
<GridColumn Field="@nameof(Supplier.SupplierEmail)"
HeaderText="Email"
TextAlign="TextAlign.Left"
Width="60">
</GridColumn>
</GridColumns>
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="Supplier"></GridEvents>
</SfGrid>
<SfDialog @ref="DialogAddEditSupplier" IsModal="true" Width="550px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> @HeaderText </Header>
</DialogTemplates>
<EditForm Model="@addeditSupplier" OnValidSubmit="@SupplierSave">
<div>
<SfTextBox Enabled="true" Placeholder="Name"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierName"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="Address" Width="50"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierAddress1"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="" Width="50"
FloatLabelType="@FloatLabelType.Never"
@bind-Value="addeditSupplier.SupplierAddress2"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="" Width="50"
FloatLabelType="@FloatLabelType.Never"
@bind-Value="addeditSupplier.SupplierAddress3"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="Post Code" Width="50"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierPostCode"
CssClass="ToUpperCase"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="Email" Width="50"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierEmail"></SfTextBox>
</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="DialogDeleteSupplier" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content>
<div>
<SfTextBox Enabled="false" Placeholder="Name"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierName"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="Address" Width="50"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierAddress1"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="" Width="50"
FloatLabelType="@FloatLabelType.Never"
@bind-Value="addeditSupplier.SupplierAddress2"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="" Width="50"
FloatLabelType="@FloatLabelType.Never"
@bind-Value="addeditSupplier.SupplierAddress3"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="Post Code" Width="50"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierPostCode"></SfTextBox>
<SfTextBox Enabled="false" Placeholder="Email" Width="50"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="addeditSupplier.SupplierEmail"></SfTextBox>
<br />
<br />
<span class="text-danger">Please confirm that you want to delete this record</span>
</div>
</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<Supplier> supplier;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
SfDialog DialogAddEditSupplier;
SfDialog DialogDeleteSupplier;
Supplier addeditSupplier = new Supplier();
int SelectedSupplierId = 0;
string HeaderText = "";
string WarningHeaderMessage = "";
string WarningContentMessage = "";
WarningPage Warning;
protected override async Task OnInitializedAsync()
{
//Populate the list of Supplier objects from the Supplier table.
supplier = await SupplierService.SupplierList();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new Supplier", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected Supplier", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected Supplier", PrefixIcon = "e-delete" });
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
addeditSupplier = new Supplier(); // Ensures a blank form when adding
HeaderText = "Add Supplier";
await this.DialogAddEditSupplier.Show();
}
if (args.Item.Text == "Edit")
{
//Code for editing goes here
//Check that a Supplier has been selected
if (SelectedSupplierId == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Supplier from the grid.";
Warning.OpenDialog();
}
else
{
//populate addeditSupplier (temporary data set used for the editing process)
HeaderText = "Edit Supplier";
addeditSupplier = await SupplierService.Supplier_GetOne(SelectedSupplierId);
await this.DialogAddEditSupplier.Show();
}
}
if (args.Item.Text == "Delete")
{
//code for deleting goes here
if (SelectedSupplierId == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Tax Rate from the grid.";
Warning.OpenDialog();
}
// else
{
//populate addeditSupplier (temporary data set used for the editing process)
HeaderText = "Delete Supplier";
addeditSupplier = await SupplierService.Supplier_GetOne(SelectedSupplierId);
await this.DialogDeleteSupplier.Show();
}
}
}
public void RowSelectHandler(RowSelectEventArgs<Supplier> args)
{
//{args.Data} returns the current selected records.
SelectedSupplierId = args.Data.SupplierID;
}
protected async Task SupplierSave()
{
if (addeditSupplier.SupplierID == 0)
{
int Success = await SupplierService.SupplierInsert(addeditSupplier.SupplierName,
addeditSupplier.SupplierAddress1,
addeditSupplier.SupplierAddress2,
addeditSupplier.SupplierAddress3,
addeditSupplier.SupplierPostCode,
addeditSupplier.SupplierEmail);
if (Success != 0)
{
//Tax Rate already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Supplier 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
addeditSupplier = new Supplier();
}
}
else
{
// Item is being edited
int Success = await SupplierService.SupplierUpdate(
addeditSupplier.SupplierID,
addeditSupplier.SupplierName,
addeditSupplier.SupplierAddress1,
addeditSupplier.SupplierAddress2,
addeditSupplier.SupplierAddress3,
addeditSupplier.SupplierPostCode,
addeditSupplier.SupplierEmail,
addeditSupplier.SupplierIsArchived);
if (Success != 0)
{
//Supplier already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Supplier already exists; it cannot be added again.";
Warning.OpenDialog();
}
else
{
await this.DialogAddEditSupplier.Hide();
this.StateHasChanged();
addeditSupplier = new Supplier();
SelectedSupplierId = 0;
}
}
//Always refresh datagrid
supplier = await SupplierService.SupplierList();
StateHasChanged();
}
private async Task CloseDialog()
{
await this.DialogAddEditSupplier.Hide();
}
public async void ConfirmDeleteNo()
{
await DialogDeleteSupplier.Hide();
SelectedSupplierId = 0;
}
public async void ConfirmDeleteYes()
{
int Success = await SupplierService.SupplierUpdate(
addeditSupplier.SupplierID,
addeditSupplier.SupplierName,
addeditSupplier.SupplierAddress1,
addeditSupplier.SupplierAddress2,
addeditSupplier.SupplierAddress3,
addeditSupplier.SupplierPostCode,
addeditSupplier.SupplierEmail,
addeditSupplier.SupplierIsArchived = true);
if (Success != 0)
{
//Tax 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.DialogDeleteSupplier.Hide();
supplier = await SupplierService.SupplierList();
this.StateHasChanged();
addeditSupplier = new Supplier();
SelectedSupplierId = 0;
}
}
}
<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>
</ul>
</div>
@code {
private bool collapseNavMenu = true;
private string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
private void ToggleNavMenu()
{
collapseNavMenu = !collapseNavMenu;
}
}