SQL
Delete Foreign Keys and amend POHeader table
ALTER TABLE [Product] DROP CONSTRAINT IF EXISTS FK_Product_Supplier;
GO
ALTER TABLE [POHeader] DROP CONSTRAINT IF EXISTS FK_POHeader_Supplier;
GO
ALTER TABLE [POLine] DROP CONSTRAINT IF EXISTS FK_POLine_POHeader;
GO
ALTER TABLE [POLine] DROP CONSTRAINT IF EXISTS FK_POLine_Product;
GO
DELETE FROM POLine
GO
DELETE FROM POHeader
GO
/****** Object: Table [dbo].[POHeader] ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[POHeader]') AND type in (N'U'))
DROP TABLE [dbo].[POHeader]
GO
/****** Object: Table [dbo].[POHeader] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[POHeader](
[POHeaderID] [int] IDENTITY(1,1) NOT NULL,
[POHeaderOrderNumber] [int] NOT NULL,
[POHeaderOrderDate] [datetime] NOT NULL,
[POHeaderSupplierID] [int] NOT NULL,
[POHeaderSupplierAddress1] [nvarchar](50) NULL,
[POHeaderSupplierAddress2] [nvarchar](50) NULL,
[POHeaderSupplierAddress3] [nvarchar](50) NULL,
[POHeaderSupplierPostCode] [nvarchar](10) NULL,
[POHeaderSupplierEmail] [nvarchar](256) NULL,
[POHeaderRequestedBy] [nvarchar](450) NULL,
[POHeaderIsArchived] [bit] NOT NULL,
CONSTRAINT [PK_POHeader] PRIMARY KEY CLUSTERED
(
[POHeaderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[POHeader] ADD CONSTRAINT [DF_POHeader_POHeaderOrderNumber] DEFAULT ((0)) FOR [POHeaderOrderNumber]
GO
ALTER TABLE [dbo].[POHeader] ADD CONSTRAINT [DF_POHeader_POHeaderIsArchived] DEFAULT ((0)) FOR [POHeaderIsArchived]
GO
NextPurchaseOrderNumber Sequence
USE [PurchaseOrders]
GO
/****** Object: Sequence [dbo].[NextPurchaseOrderNumber] ******/
DROP SEQUENCE [dbo].[NextPurchaseOrderNumber]
GO
USE [PurchaseOrders]
GO
/****** Object: Sequence [dbo].[NextPurchaseOrderNumber] ******/
CREATE SEQUENCE [dbo].[NextPurchaseOrderNumber]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
CACHE
GO
USE [PurchaseOrders]
GO
/****** Object: StoredProcedure [dbo].[spPOHeader_Insert] Script Date: 16/02/2021 18:27:07 ******/
DROP PROCEDURE [dbo].[spPOHeader_Insert]
GO
/****** Object: StoredProcedure [dbo].[spPOHeader_Insert] Script Date: 16/02/2021 18:27:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spPOHeader_Insert]
--Parameters for Insert stored procedure
@POHeaderOrderDate date,
@POHeaderSupplierID int,
@POHeaderSupplierAddress1 nvarchar(50),
@POHeaderSupplierAddress2 nvarchar(50),
@POHeaderSupplierAddress3 nvarchar(50),
@POHeaderSupplierPostCode nvarchar(10),
@POHeaderSupplierEmail nvarchar(256),
@POHeaderRequestedBy nvarchar(450),
@POHeaderIsArchived bit
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO POHeader(POHeaderOrderNumber, POHeaderOrderDate, POHeaderSupplierID, POHeaderSupplierAddress1, POHeaderSupplierAddress2, POHeaderSupplierAddress3, POHeaderSupplierPostCode, POHeaderSupplierEmail, POHeaderRequestedBy, POHeaderIsArchived)
VALUES (NEXT VALUE FOR NextPurchaseOrderNumber, @POHeaderOrderDate, @POHeaderSupplierID, @POHeaderSupplierAddress1, @POHeaderSupplierAddress2, @POHeaderSupplierAddress3, @POHeaderSupplierPostCode, @POHeaderSupplierEmail, @POHeaderRequestedBy, @POHeaderIsArchived)
END
GO
Reset Purchase Order data
USE [PurchaseOrders]
GO
DELETE FROM POHeader
GO
DELETE FROM POLine
GO
ALTER SEQUENCE NextPurchaseOrderNumber RESTART WITH 1
GO
C#
Index.razor
@page "/"
@using BlazorPurchaseOrders.Data
@inject IPOHeaderService POHeaderService
@inject NavigationManager NavigationManager
<div class="col-sm-12">
<h3>Purchase Orders</h3>
<br />
<SfGrid DataSource="@poheader"
Toolbar="Toolbaritems">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="POHeader"></GridEvents>
<GridColumns>
<GridColumn Field="@nameof(POHeader.POHeaderOrderNumber)"
HeaderText="Order No"
TextAlign="@TextAlign.Left"
Width="20">
</GridColumn>
<GridColumn Field="@nameof(POHeader.POHeaderOrderDate)"
HeaderText="Date"
Format="d"
Type="ColumnType.Date"
TextAlign="@TextAlign.Center"
Width="20">
</GridColumn>
</GridColumns>
</SfGrid>
</div>
@code {
// Create an empty list, named poheader, of empty POHeader objects.
IEnumerable<POHeader> poheader;
private List<ItemModel> Toolbaritems = new List<ItemModel>();
int POHeaderID = 0;
protected override async Task OnInitializedAsync()
{
//Populate the list of countries objects from the Countries table.
poheader = await POHeaderService.POHeaderList();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new order", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected order", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected order", PrefixIcon = "e-delete" });
}
public void ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
POHeaderID = 0;
NavigationManager.NavigateTo($"/purchaseorder/{POHeaderID}");
}
if (args.Item.Text == "Edit")
{
//Code for editing
}
if (args.Item.Text == "Edit")
{
//Code for deleting
}
}
}
@page "/purchaseorder/{POHeaderID:int}"
@using BlazorPurchaseOrders.Data
@inject NavigationManager NavigationManager
@inject ISupplierService SupplierService
@inject IPOHeaderService POHeaderService
@using Microsoft.AspNetCore.Components.Authorization
@inject AuthenticationStateProvider AuthenticationStateProvider
@using System
@using System.Collections.Generic
<h3>@pagetitle</h3>
<EditForm Model="@orderaddedit" OnValidSubmit="@OrderSave">
<div class="grid-container">
<div class="grid-child left-column">
<SfDropDownList DataSource="@supplier"
TItem="Supplier"
TValue="int"
Text="SupplierID"
@bind-Value="orderaddedit.POHeaderSupplierID"
FloatLabelType="@FloatLabelType.Auto"
Placeholder="Select a Supplier"
Enabled="true">
<DropDownListFieldSettings Text="SupplierName" Value="SupplierID"></DropDownListFieldSettings>
<DropDownListEvents TItem="Supplier" TValue="int" ValueChange="OnChangeSupplier"></DropDownListEvents>
</SfDropDownList>
<SfTextBox Enabled="true" Placeholder="Address"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="orderaddedit.POHeaderSupplierAddress1"></SfTextBox>
<SfTextBox Enabled="true" Placeholder=""
FloatLabelType="@FloatLabelType.Never"
@bind-Value="orderaddedit.POHeaderSupplierAddress2"></SfTextBox>
<SfTextBox Enabled="true" Placeholder=""
FloatLabelType="@FloatLabelType.Never"
@bind-Value="orderaddedit.POHeaderSupplierAddress3"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="Post Code"
FloatLabelType="@FloatLabelType.Never"
@bind-Value="orderaddedit.POHeaderSupplierPostCode"></SfTextBox>
<SfTextBox Enabled="true" Placeholder="Email"
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="orderaddedit.POHeaderSupplierEmail"></SfTextBox>
</div>
<div class="grid-child right-column">
<SfNumericTextBox Enabled="false" Placeholder="Order No"
FloatLabelType="@FloatLabelType.Always"
ShowSpinButton="false"
@bind-Value="orderaddedit.POHeaderOrderNumber"></SfNumericTextBox>
<SfDatePicker TValue="DateTime"
Placeholder='Order Date'
FloatLabelType="@FloatLabelType.Auto"
@bind-Value="orderaddedit.POHeaderOrderDate"></SfDatePicker>
<SfTextBox Enabled="false" Placeholder="Requested by"
FloatLabelType="@FloatLabelType.Always"
@bind-Value="orderaddedit.POHeaderRequestedBy"></SfTextBox>
</div>
</div>
<br /><br />
<div class="e-footer-content">
<div class="button-container">
<button type="submit" class="e-btn e-normal e-primary">Save</button>
<button type="button" class="e-btn e-normal" @onclick="@Cancel">Cancel</button>
</div>
</div>
</EditForm>
<style>
.grid-container {
display: grid;
max-width: 900px; /* Maximum width of the whole container - in this case both columns */
grid-template-columns: 1fr 1fr; /* Relative width of each column (1fr 1fr is equivalent to, say, 33fr 33fr */
grid-gap: 75px; /* size of the gap between columns */
}
</style>
@code {
POHeader orderaddedit = new POHeader();
IEnumerable<Supplier> supplier;
string pagetitle = "";
private string UserName;
[Parameter]
public int POHeaderID { get; set; }
//Executes on page open, sets headings and gets data in the case of edit
protected override async Task OnInitializedAsync()
{
supplier = await SupplierService.SupplierList();
orderaddedit.POHeaderOrderDate = DateTime.Now;
if (POHeaderID == 0)
{
pagetitle = "Add an Order";
}
else
{
pagetitle = "Edit an Order";
}
//Get user if logged in and populate the 'Requested by' column
var authState = await AuthenticationStateProvider.GetAuthenticationStateAsync();
var user = authState.User;
if (user.Identity.IsAuthenticated)
{
UserName = user.Identity.Name;
}
else
{
UserName = "The user is NOT authenticated.";
}
orderaddedit.POHeaderRequestedBy = UserName;
}
private void OnChangeSupplier(Syncfusion.Blazor.DropDowns.ChangeEventArgs<int, Supplier> args)
{
this.orderaddedit.POHeaderSupplierAddress1 = args.ItemData.SupplierAddress1;
this.orderaddedit.POHeaderSupplierAddress2 = args.ItemData.SupplierAddress2;
this.orderaddedit.POHeaderSupplierAddress3 = args.ItemData.SupplierAddress3;
this.orderaddedit.POHeaderSupplierPostCode = args.ItemData.SupplierPostCode;
this.orderaddedit.POHeaderSupplierEmail = args.ItemData.SupplierEmail;
}
// Executes OnValidSubmit of EditForm above
protected async Task OrderSave()
{
if (POHeaderID == 0)
{
//Save the record
bool Successs = await POHeaderService.POHeaderInsert(orderaddedit);
NavigationManager.NavigateTo("/");
}
else
{
NavigationManager.NavigateTo("/");
}
}
//Executes if user clicks the Cancel button.
void Cancel()
{
NavigationManager.NavigateTo("/");
}
}
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace BlazorPurchaseOrders.Data
{
public class POHeaderService : IPOHeaderService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public POHeaderService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a POHeader table row (SQL Insert)
public async Task<bool> POHeaderInsert(POHeader poheader)
{
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
//parameters.Add("POHeaderOrderNumber", poheader.POHeaderOrderNumber, DbType.Int32);
parameters.Add("POHeaderOrderDate", poheader.POHeaderOrderDate, DbType.Date);
parameters.Add("POHeaderSupplierID", poheader.POHeaderSupplierID, DbType.Int32);
parameters.Add("POHeaderSupplierAddress1", poheader.POHeaderSupplierAddress1, DbType.String);
parameters.Add("POHeaderSupplierAddress2", poheader.POHeaderSupplierAddress2, DbType.String);
parameters.Add("POHeaderSupplierAddress3", poheader.POHeaderSupplierAddress3, DbType.String);
parameters.Add("POHeaderSupplierPostCode", poheader.POHeaderSupplierPostCode, DbType.String);
parameters.Add("POHeaderSupplierEmail", poheader.POHeaderSupplierEmail, DbType.String);
parameters.Add("POHeaderRequestedBy", poheader.POHeaderRequestedBy, DbType.String);
parameters.Add("POHeaderIsArchived", poheader.POHeaderIsArchived, DbType.Boolean);
// Stored procedure method
await conn.ExecuteAsync("spPOHeader_Insert", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
// Get a list of poheader rows (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<IEnumerable<POHeader>> POHeaderList()
{
IEnumerable<POHeader> poheaders;
using (var conn = new SqlConnection(_configuration.Value))
{
poheaders = await conn.QueryAsync<POHeader>("spPOHeader_List", commandType: CommandType.StoredProcedure);
}
return poheaders;
}
// Get one poheader based on its POHeaderID (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<POHeader> POHeader_GetOne(int @POHeaderID)
{
POHeader poheader = new POHeader();
var parameters = new DynamicParameters();
parameters.Add("@POHeaderID", POHeaderID, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
poheader = await conn.QueryFirstOrDefaultAsync<POHeader>("spPOHeader_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return poheader;
}
// Update one POHeader row based on its POHeaderID (SQL Update)
// This only works if you're already created the stored procedure.
public async Task<bool> POHeaderUpdate(POHeader poheader)
{
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
parameters.Add("POHeaderID", poheader.POHeaderID, DbType.Int32);
parameters.Add("POHeaderOrderNumber", poheader.POHeaderOrderNumber, DbType.Int32);
parameters.Add("POHeaderOrderDate", poheader.POHeaderOrderDate, DbType.Date);
parameters.Add("POHeaderSupplierID", poheader.POHeaderSupplierID, DbType.Int32);
parameters.Add("POHeaderSupplierAddress1", poheader.POHeaderSupplierAddress1, DbType.String);
parameters.Add("POHeaderSupplierAddress2", poheader.POHeaderSupplierAddress2, DbType.String);
parameters.Add("POHeaderSupplierAddress3", poheader.POHeaderSupplierAddress3, DbType.String);
parameters.Add("POHeaderSupplierPostCode", poheader.POHeaderSupplierPostCode, DbType.String);
parameters.Add("POHeaderSupplierEmail", poheader.POHeaderSupplierEmail, DbType.String);
parameters.Add("POHeaderRequestedBy", poheader.POHeaderRequestedBy, DbType.String);
parameters.Add("POHeaderIsArchived", poheader.POHeaderIsArchived, DbType.Boolean);
await conn.ExecuteAsync("spPOHeader_Update", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
}
}