SQL Stored Procedures
Tax_Insert
USE [PurchaseOrders]
GO
DROP PROCEDURE [dbo].[spTax_Insert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTax_Insert]
(
@TaxDescription nvarchar(50),
@TaxRate decimal(6, 4)
)
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Tax
WHERE TaxDescription = @TaxDescription AND TaxIsArchived = 0
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
INSERT INTO Tax(TaxDescription, TaxRate) VALUES (@TaxDescription, @TaxRate)
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
Tax_Update
USE [PurchaseOrders]
GO
DROP PROCEDURE [dbo].[spTax_Update]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for UPDATE
CREATE PROCEDURE [dbo].[spTax_Update]
-- Parameters for Update stored procedure.
@TaxID int,
@TaxDescription nvarchar(50),
@TaxRate decimal(6, 4),
@TaxIsArchived bit
AS
DECLARE @ResultValue int
BEGIN TRAN
IF EXISTS
(
SELECT * FROM Tax
WHERE (TaxDescription = @TaxDescription) AND TaxID <> @TaxID AND TaxIsArchived = 0
)
BEGIN
SET @ResultValue = 99
END
ELSE
BEGIN
UPDATE Tax SET TaxDescription = @TaxDescription, TaxRate = @TaxRate, TaxIsArchived = @TaxIsArchived WHERE TaxId = @TaxID
set @ResultValue = @@ERROR
END
IF @ResultValue <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
RETURN @ResultValue
GO
Tax_List
USE [PurchaseOrders]
GO
DROP PROCEDURE [dbo].[spTax_List]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTax_List]
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT TaxID, TaxDescription, TaxRate, TaxIsArchived FROM Tax
WHERE TaxIsArchived = 0
ORDER BY TaxRate DESC, TaxDescription ASC
END
GO
C# Code
TaxService.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 TaxService : ITaxService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public TaxService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a Tax table row (SQL Insert)
// This only works if you're already created the stored procedure.
public async Task<int> TaxInsert(string TaxDescription, Decimal TaxRate)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("TaxDescription", TaxDescription, DbType.String);
parameters.Add("TaxRate", TaxRate, DbType.Decimal);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
// Stored procedure method
await conn.ExecuteAsync("spTax_Insert", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>("@ReturnValue");
}
return Success;
}
// Get a list of tax rows (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<IEnumerable<Tax>> TaxList()
{
IEnumerable<Tax> taxes;
using (var conn = new SqlConnection(_configuration.Value))
{
taxes = await conn.QueryAsync<Tax>("spTax_List", commandType: CommandType.StoredProcedure);
}
return taxes;
}
// Get one tax based on its TaxID (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<Tax> Tax_GetOne(int @TaxID)
{
Tax tax = new Tax();
var parameters = new DynamicParameters();
parameters.Add("@TaxID", TaxID, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
tax = await conn.QueryFirstOrDefaultAsync<Tax>("spTax_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return tax;
}
// Update one Tax row based on its TaxID (SQL Update)
// This only works if you're already created the stored procedure.
public async Task<int> TaxUpdate(string TaxDescription, decimal TaxRate, int TaxID, bool TaxIsArchived)
{
int Success = 0;
var parameters = new DynamicParameters();
parameters.Add("TaxDescription", TaxDescription, DbType.String);
parameters.Add("TaxRate", TaxRate, DbType.Decimal);
parameters.Add("TaxId", TaxID, DbType.Int32);
parameters.Add("TaxIsArchived", TaxIsArchived, DbType.Boolean);
parameters.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using (var conn = new SqlConnection(_configuration.Value))
{
await conn.ExecuteAsync("spTax_Update", parameters, commandType: CommandType.StoredProcedure);
Success = parameters.Get<int>
("@ReturnValue");
}
return Success;
}
}
}
ITaxService.cs
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorPurchaseOrders.Data
{
// Each item below provides an interface to a method in TaxServices.cs
public interface ITaxService
{
Task<int> TaxInsert(string TaxDescription, Decimal TaxRate);
Task<IEnumerable<Tax>> TaxList();
Task<Tax> Tax_GetOne(int TaxID);
Task<int> TaxUpdate(string TaxDescription, Decimal TaxRate, int TaxID, bool TaxIsArchived);
}
}
@page "/tax"
@using BlazorPurchaseOrders.Data
@inject ITaxService TaxService
@using Syncfusion.Blazor.Navigations
<h3>Tax Rates</h3>
<br />
<SfGrid DataSource="@tax"
Toolbar="@Toolbaritems">
<GridColumns>
<GridColumn Field="@nameof(Tax.TaxDescription)"
HeaderText="Description"
TextAlign="TextAlign.Left"
Width="60">
<GridColumn Field="@nameof(Tax.TaxRate)"
HeaderText="Rate %"
TextAlign="TextAlign.Right"
Format="p2"
Width="40">
</GridColumn>
</GridColumn>
</GridColumns>
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="Tax"></GridEvents>
</SfGrid>
<SfDialog @ref="DialogAddEditTax" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> @HeaderText </Header>
</DialogTemplates>
<EditForm Model="@addeditTax" OnValidSubmit="@TaxSave">
<div>
<SfTextBox Enabled="true" Placeholder="Description"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="addeditTax.TaxDescription"></SfTextBox>
<SfNumericTextBox Enabled="true" Placeholder="Tax Rate" Width="50"
Format="p2"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="addeditTax.TaxRate"></SfNumericTextBox>
</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="DialogDeleteTax" IsModal="true" Width="500px" ShowCloseIcon="true" Visible="false">
<DialogTemplates>
<Header> Confirm Delete </Header>
<Content>
<SfTextBox Enabled="false" Placeholder="Description"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="addeditTax.TaxDescription"></SfTextBox>
<SfNumericTextBox Enabled="false" Placeholder="Tax Rate" Width="50"
Format="p2"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="addeditTax.TaxRate"></SfNumericTextBox>
<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" />
@code {
IEnumerable<Tax> tax;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
SfDialog DialogAddEditTax;
Tax addeditTax = new Tax();
string HeaderText = "";
WarningPage Warning;
string WarningHeaderMessage = "";
string WarningContentMessage = "";
public int SelectedTaxId { get; set; } = 0;
SfDialog DialogDeleteTax;
protected override async Task OnInitializedAsync()
{
//Populate the list of VAT objects from the VAT table.
tax = await TaxService.TaxList();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new Tax Rate", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected Tax Rate", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected Tax Rate", PrefixIcon = "e-delete" });
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
addeditTax = new Tax(); // Ensures a blank form when adding
HeaderText = "Add Tax Rate";
await this.DialogAddEditTax.Show();
}
if (args.Item.Text == "Edit")
{
//Code for editing goes here
//Check that a Tax Rate has been selected
if (SelectedTaxId == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Tax Rate from the grid.";
Warning.OpenDialog();
}
else
{
//populate addeditTax (temporary data set used for the editing process)
HeaderText = "Edit Tax Rate";
addeditTax = await TaxService.Tax_GetOne(SelectedTaxId);
await this.DialogAddEditTax.Show();
}
}
if (args.Item.Text == "Delete")
{
//code for deleting goes here
if (SelectedTaxId == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Tax Rate from the grid.";
Warning.OpenDialog();
}
else
{
//populate addeditTax (temporary data set used for the editing process)
HeaderText = "Delete Tax Rate";
addeditTax = await TaxService.Tax_GetOne(SelectedTaxId);
await this.DialogDeleteTax.Show();
}
}
}
protected async Task TaxSave()
{
if (addeditTax.TaxID == 0)
{
int Success = await TaxService.TaxInsert(addeditTax.TaxDescription, addeditTax.TaxRate);
if (Success != 0)
{
//Tax Rate already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Tax 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
addeditTax = new Tax();
}
}
else
{
// Item is being edited
int Success = await TaxService.TaxUpdate(addeditTax.TaxDescription, addeditTax.TaxRate, SelectedTaxId, addeditTax.TaxIsArchived);
if (Success != 0)
{
//Tax Rate already exists
WarningHeaderMessage = "Warning!";
WarningContentMessage = "This Tax Description already exists; it cannot be added again.";
Warning.OpenDialog();
}
else
{
await this.DialogAddEditTax.Hide();
this.StateHasChanged();
addeditTax = new Tax();
SelectedTaxId = 0;
}
}
//Always refresh datagrid
tax = await TaxService.TaxList();
StateHasChanged();
}
private async Task CloseDialog()
{
await this.DialogAddEditTax.Hide();
}
public void RowSelectHandler(RowSelectEventArgs<Tax> args)
{
//{args.Data} returns the current selected records.
SelectedTaxId = args.Data.TaxID;
}
public async void ConfirmDeleteNo()
{
await DialogDeleteTax.Hide();
SelectedTaxId = 0;
}
public async void ConfirmDeleteYes()
{
int Success = await TaxService.TaxUpdate(addeditTax.TaxDescription, addeditTax.TaxRate, SelectedTaxId, addeditTax.TaxIsArchived = 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.DialogDeleteTax.Hide();
tax = await TaxService.TaxList();
this.StateHasChanged();
addeditTax = new Tax();
SelectedTaxId = 0;
}
}
}
@using Syncfusion.Blazor.Popups;
<SfDialog @ref="DialogWarning" @bind-Visible="@IsVisible" IsModal="true" Width="300px" ShowCloseIcon="true">
<DialogTemplates>
<Header> @WarningHeaderMessage </Header>
<Content>@WarningContentMessage</Content>
</DialogTemplates>
<DialogButtons>
<DialogButton Content="OK" IsPrimary="true" OnClick="@CloseDialog" />
</DialogButtons>
</SfDialog>
@code {
SfDialog DialogWarning;
public bool IsVisible { get; set; } = false;
[Parameter] public string WarningHeaderMessage { get; set; }
[Parameter] public string WarningContentMessage { get; set; }
public void OpenDialog()
{
this.IsVisible = true;
this.StateHasChanged();
}
public void CloseDialog()
{
this.IsVisible = false;
this.StateHasChanged();
}
}
<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>
</ul>
</div>
@code {
private bool collapseNavMenu = true;
private string NavMenuCssClass => collapseNavMenu ? "collapse" : null;
private void ToggleNavMenu()
{
collapseNavMenu = !collapseNavMenu;
}
}