Dapper Code Generator Code

POHeader

SQL script for stored procedures

-------------- Stored Proc for INSERT
CREATE PROCEDURE spPOHeader_Insert
--Parameters for Insert stored procedure
@POHeaderOrderNumber int,
@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 (@POHeaderOrderNumber, @POHeaderOrderDate, @POHeaderSupplierID, @POHeaderSupplierAddress1, @POHeaderSupplierAddress2, @POHeaderSupplierAddress3, @POHeaderSupplierPostCode, @POHeaderSupplierEmail, @POHeaderRequestedBy, @POHeaderIsArchived)
END

GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spPOHeader_List
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT POHeaderID, POHeaderOrderNumber, POHeaderOrderDate, POHeaderSupplierID, POHeaderSupplierAddress1, POHeaderSupplierAddress2, POHeaderSupplierAddress3, POHeaderSupplierPostCode, POHeaderSupplierEmail, POHeaderRequestedBy, POHeaderIsArchived FROM POHeader ORDER BY POHeaderID DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spPOHeader_GetOne
-- Needs one parameter for primary key
@POHeaderID int
AS 
BEGIN
-- SQL Select for one table row
SELECT POHeaderID, POHeaderOrderNumber, POHeaderOrderDate, POHeaderSupplierID, POHeaderSupplierAddress1, POHeaderSupplierAddress2, POHeaderSupplierAddress3, POHeaderSupplierPostCode, POHeaderSupplierEmail, POHeaderRequestedBy, POHeaderIsArchived FROM POHeader WHERE POHeaderID= @POHeaderID
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE spPOHeader_Update
-- Parameters for Update stored procedure.
@POHeaderID int,
@POHeaderOrderNumber int,
@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 Update stored procedure
UPDATE POHeader SET POHeaderOrderNumber = @POHeaderOrderNumber, POHeaderOrderDate = @POHeaderOrderDate, POHeaderSupplierID = @POHeaderSupplierID, POHeaderSupplierAddress1 = @POHeaderSupplierAddress1, POHeaderSupplierAddress2 = @POHeaderSupplierAddress2, POHeaderSupplierAddress3 = @POHeaderSupplierAddress3, POHeaderSupplierPostCode = @POHeaderSupplierPostCode, POHeaderSupplierEmail = @POHeaderSupplierEmail, POHeaderRequestedBy = @POHeaderRequestedBy, POHeaderIsArchived = @POHeaderIsArchived WHERE POHeaderID = @POHeaderID
END

GO

POHeader.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 POHeader
    {
        [Required]
        public int POHeaderID { get; set; }
        [Required]
        public int POHeaderOrderNumber { get; set; }
        [Required]
        public DateTime POHeaderOrderDate { get; set; }
        [Required]
        public int POHeaderSupplierID { get; set; }
        [StringLength(50)]
        public string POHeaderSupplierAddress1 { get; set; }
        [StringLength(50)]
        public string POHeaderSupplierAddress2 { get; set; }
        [StringLength(50)]
        public string POHeaderSupplierAddress3 { get; set; }
        [StringLength(10)]
        public string POHeaderSupplierPostCode { get; set; }
        [StringLength(256)]
        public string POHeaderSupplierEmail { get; set; }
        [StringLength(450)]
        public string POHeaderRequestedBy { get; set; }
        [Required]
        public bool POHeaderIsArchived { get; set; }

    }
}

POHeaderService.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 POHeaderService : IPOHeaderService
    {
        // Database connection
        private readonly SqlConnectionConfiguration _configuration;
        public POHeaderService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }
        // Add (create) a POHeader table row (SQL Insert)
        // This only works if you're already created the stored procedure.
        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;
        }

    }
}

IPOHeaderService

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

namespace BlazorPurchaseOrders.Data
{
    // Each item below provides an interface to a method in POHeaderServices.cs
    public interface IPOHeaderService
    {
        Task<bool> POHeaderInsert(POHeader poheader);
        Task<IEnumerable<POHeader>> POHeaderList();
        Task<POHeader> POHeader_GetOne(int POHeaderID);
        Task<bool> POHeaderUpdate(POHeader poheader);
    }
}

POLine

SQL script for stored procedures

-------------- Stored Proc for INSERT
CREATE PROCEDURE spPOLine_Insert
--Parameters for Insert stored procedure
@POLineHeaderID int,
@POLineProductID int,
@POLineProductDescription nvarchar(50),
@POLineProductQuantity decimal(9, 3),
@POLineProductUnitPrice money,
@POLineTaxRate decimal(6, 4)
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO POLine(POLineHeaderID, POLineProductID, POLineProductDescription, POLineProductQuantity, POLineProductUnitPrice, POLineTaxRate) VALUES (@POLineHeaderID, @POLineProductID, @POLineProductDescription, @POLineProductQuantity, @POLineProductUnitPrice, @POLineTaxRate)
END

GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spPOLine_List
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT POLineID, POLineHeaderID, POLineProductID, POLineProductDescription, POLineProductQuantity, POLineProductUnitPrice, POLineTaxRate FROM POLine ORDER BY POLineID DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spPOLine_GetOne
-- Needs one parameter for primary key
@POLineID int
AS 
BEGIN
-- SQL Select for one table row
SELECT POLineID, POLineHeaderID, POLineProductID, POLineProductDescription, POLineProductQuantity, POLineProductUnitPrice, POLineTaxRate FROM POLine WHERE POLineID= @POLineID
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE spPOLine_Update
-- Parameters for Update stored procedure.
@POLineID int,
@POLineHeaderID int,
@POLineProductID int,
@POLineProductDescription nvarchar(50),
@POLineProductQuantity decimal(9, 3),
@POLineProductUnitPrice money,
@POLineTaxRate decimal(6, 4)
AS
BEGIN
-- SQL for Update stored procedure
UPDATE POLine SET POLineHeaderID = @POLineHeaderID, POLineProductID = @POLineProductID, POLineProductDescription = @POLineProductDescription, POLineProductQuantity = @POLineProductQuantity, POLineProductUnitPrice = @POLineProductUnitPrice, POLineTaxRate = @POLineTaxRate WHERE POLineID = @POLineID
END

GO

POLine.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 POLine
    {
        [Required]
        public int POLineID { get; set; }
        [Required]
        public int POLineHeaderID { get; set; }
        [Required]
        public int POLineProductID { get; set; }
        [Required]
        [StringLength(50)]
        public string POLineProductDescription { get; set; }
        [Required]
        public decimal POLineProductQuantity { get; set; }
        [Required]
        public decimal POLineProductUnitPrice { get; set; }
        [Required]
        public decimal POLineTaxRate { get; set; }

    }
}

POLineService.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 POLineService : IPOLineService
    {
        // Database connection
        private readonly SqlConnectionConfiguration _configuration;
        public POLineService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }
        // Add (create) a POLine table row (SQL Insert)
        // This only works if you're already created the stored procedure.
        public async Task<bool> POLineInsert(POLine poline)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("POLineHeaderID", poline.POLineHeaderID, DbType.Int32);
                parameters.Add("POLineProductID", poline.POLineProductID, DbType.Int32);
                parameters.Add("POLineProductDescription", poline.POLineProductDescription, DbType.String);
                parameters.Add("POLineProductQuantity", poline.POLineProductQuantity, DbType.Decimal);
                parameters.Add("POLineProductUnitPrice", poline.POLineProductUnitPrice, DbType.Decimal);
                parameters.Add("POLineTaxRate", poline.POLineTaxRate, DbType.Decimal);

                // Stored procedure method
                await conn.ExecuteAsync("spPOLine_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
        // Get a list of poline rows (SQL Select)
        // This only works if you're already created the stored procedure.
        public async Task<IEnumerable<POLine>> POLineList()
        {
            IEnumerable<POLine> polines;
            using (var conn = new SqlConnection(_configuration.Value))
            {
                polines = await conn.QueryAsync<POLine>("spPOLine_List", commandType: CommandType.StoredProcedure);
            }
            return polines;
        }
       
        // Get one poline based on its POLineID (SQL Select)
        // This only works if you're already created the stored procedure.
        public async Task<POLine> POLine_GetOne(int @POLineID)
        {
            POLine poline = new POLine();
            var parameters = new DynamicParameters();
            parameters.Add("@POLineID", POLineID, DbType.Int32);
            using (var conn = new SqlConnection(_configuration.Value))
            {
                poline = await conn.QueryFirstOrDefaultAsync<POLine>("spPOLine_GetOne", parameters, commandType: CommandType.StoredProcedure);
            }
            return poline;
        }
        // Update one POLine row based on its POLineID (SQL Update)
        // This only works if you're already created the stored procedure.
        public async Task<bool> POLineUpdate(POLine poline)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("POLineID", poline.POLineID, DbType.Int32);

                parameters.Add("POLineHeaderID", poline.POLineHeaderID, DbType.Int32);
                parameters.Add("POLineProductID", poline.POLineProductID, DbType.Int32);
                parameters.Add("POLineProductDescription", poline.POLineProductDescription, DbType.String);
                parameters.Add("POLineProductQuantity", poline.POLineProductQuantity, DbType.Decimal);
                parameters.Add("POLineProductUnitPrice", poline.POLineProductUnitPrice, DbType.Decimal);
                parameters.Add("POLineTaxRate", poline.POLineTaxRate, DbType.Decimal);

                await conn.ExecuteAsync("spPOLine_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
    }
}

IPOLineService

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

namespace BlazorPurchaseOrders.Data
{
    // Each item below provides an interface to a method in POLineServices.cs
    public interface IPOLineService
    {
        Task<bool> POLineInsert(POLine poline);
        Task<IEnumerable<POLine>> POLineList();
        Task<POLine> POLine_GetOne(int POLineID);
        Task<bool> POLineUpdate(POLine poline);
    }
}

Product

SQL script for stored procedures

-------------- Stored Proc for INSERT
CREATE PROCEDURE spProduct_Insert
--Parameters for Insert stored procedure
@ProductCode nvarchar(25),
@ProductDescription nvarchar(50),
@ProductUnitPrice money,
@ProductSupplierID int,
@ProductIsArchived bit
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO Product(ProductCode, ProductDescription, ProductUnitPrice, ProductSupplierID, ProductIsArchived) VALUES (@ProductCode, @ProductDescription, @ProductUnitPrice, @ProductSupplierID, @ProductIsArchived)
END

GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spProduct_List
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT ProductID, ProductCode, ProductDescription, ProductUnitPrice, ProductSupplierID, ProductIsArchived FROM Product ORDER BY ProductID DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spProduct_GetOne
-- Needs one parameter for primary key
@ProductID int
AS 
BEGIN
-- SQL Select for one table row
SELECT ProductID, ProductCode, ProductDescription, ProductUnitPrice, ProductSupplierID, ProductIsArchived FROM Product WHERE ProductID= @ProductID
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE spProduct_Update
-- Parameters for Update stored procedure.
@ProductID int,
@ProductCode nvarchar(25),
@ProductDescription nvarchar(50),
@ProductUnitPrice money,
@ProductSupplierID int,
@ProductIsArchived bit
AS
BEGIN
-- SQL for Update stored procedure
UPDATE Product SET ProductCode = @ProductCode, ProductDescription = @ProductDescription, ProductUnitPrice = @ProductUnitPrice, ProductSupplierID = @ProductSupplierID, ProductIsArchived = @ProductIsArchived WHERE ProductID = @ProductID
END

GO

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; }

    }
}

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)
        // This only works if you're already created the stored procedure.
        public async Task<bool> ProductInsert(Product product)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("ProductCode", product.ProductCode, DbType.String);
                parameters.Add("ProductDescription", product.ProductDescription, DbType.String);
                parameters.Add("ProductUnitPrice", product.ProductUnitPrice, DbType.Decimal);
                parameters.Add("ProductSupplierID", product.ProductSupplierID, DbType.Int32);
                parameters.Add("ProductIsArchived", product.ProductIsArchived, DbType.Boolean);

                // Stored procedure method
                await conn.ExecuteAsync("spProduct_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
        // Get a list of product rows (SQL Select)
        // This only works if you're already created the stored procedure.
        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)
        // This only works if you're already created the stored procedure.
        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)
        // This only works if you're already created the stored procedure.
        public async Task<bool> ProductUpdate(Product product)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("ProductID", product.ProductID, DbType.Int32);

                parameters.Add("ProductCode", product.ProductCode, DbType.String);
                parameters.Add("ProductDescription", product.ProductDescription, DbType.String);
                parameters.Add("ProductUnitPrice", product.ProductUnitPrice, DbType.Decimal);
                parameters.Add("ProductSupplierID", product.ProductSupplierID, DbType.Int32);
                parameters.Add("ProductIsArchived", product.ProductIsArchived, DbType.Boolean);

                await conn.ExecuteAsync("spProduct_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
    }
}

IProductService

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<bool> ProductInsert(Product product);
        Task<IEnumerable<Product>> ProductList();
        Task<Product> Product_GetOne(int ProductID);
        Task<bool> ProductUpdate(Product product);
    }
}

Supplier

SQL script for stored procedures

-------------- Stored Proc for INSERT
CREATE PROCEDURE 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),
@SupplierIsArchived bit
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO Supplier(SupplierName, SupplierAddress1, SupplierAddress2, SupplierAddress3, SupplierPostCode, SupplierEmail, SupplierIsArchived) VALUES (@SupplierName, @SupplierAddress1, @SupplierAddress2, @SupplierAddress3, @SupplierPostCode, @SupplierEmail, @SupplierIsArchived)
END

GO

--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spSupplier_List
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT SupplierID, SupplierName, SupplierAddress1, SupplierAddress2, SupplierAddress3, SupplierPostCode, SupplierEmail, SupplierIsArchived FROM Supplier ORDER BY SupplierID DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spSupplier_GetOne
-- Needs one parameter for primary key
@SupplierID int
AS 
BEGIN
-- SQL Select for one table row
SELECT SupplierID, SupplierName, SupplierAddress1, SupplierAddress2, SupplierAddress3, SupplierPostCode, SupplierEmail, SupplierIsArchived FROM Supplier WHERE SupplierID= @SupplierID
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE 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
BEGIN
-- SQL for Update stored procedure
UPDATE Supplier SET SupplierName = @SupplierName, SupplierAddress1 = @SupplierAddress1, SupplierAddress2 = @SupplierAddress2, SupplierAddress3 = @SupplierAddress3, SupplierPostCode = @SupplierPostCode, SupplierEmail = @SupplierEmail, SupplierIsArchived = @SupplierIsArchived WHERE SupplierID = @SupplierID
END

GO

Supplier.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 Supplier
    {
	[Required]
public int SupplierID { get; set; }
[Required]
[StringLength(50)]
public string SupplierName { get; set; }
[StringLength(50)]
public string SupplierAddress1 { get; set; }
[StringLength(50)]
public string SupplierAddress2 { get; set; }
[StringLength(50)]
public string SupplierAddress3 { get; set; }
[StringLength(10)]
public string SupplierPostCode { get; set; }
[StringLength(256)]
public string SupplierEmail { get; set; }
[Required]
public bool SupplierIsArchived { get; set; }

    }
}

SupplierService

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)
        // This only works if you're already created the stored procedure.
        public async Task<bool> SupplierInsert(Supplier supplier)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("SupplierName", supplier.SupplierName, DbType.String);
                parameters.Add("SupplierAddress1", supplier.SupplierAddress1, DbType.String);
                parameters.Add("SupplierAddress2", supplier.SupplierAddress2, DbType.String);
                parameters.Add("SupplierAddress3", supplier.SupplierAddress3, DbType.String);
                parameters.Add("SupplierPostCode", supplier.SupplierPostCode, DbType.String);
                parameters.Add("SupplierEmail", supplier.SupplierEmail, DbType.String);
                parameters.Add("SupplierIsArchived", supplier.SupplierIsArchived, DbType.Boolean);

                // Stored procedure method
                await conn.ExecuteAsync("spSupplier_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
        // 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)
        // This only works if you're already created the stored procedure.
        public async Task<bool> SupplierUpdate(Supplier supplier)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("SupplierID", supplier.SupplierID, DbType.Int32);

                parameters.Add("SupplierName", supplier.SupplierName, DbType.String);
                parameters.Add("SupplierAddress1", supplier.SupplierAddress1, DbType.String);
                parameters.Add("SupplierAddress2", supplier.SupplierAddress2, DbType.String);
                parameters.Add("SupplierAddress3", supplier.SupplierAddress3, DbType.String);
                parameters.Add("SupplierPostCode", supplier.SupplierPostCode, DbType.String);
                parameters.Add("SupplierEmail", supplier.SupplierEmail, DbType.String);
                parameters.Add("SupplierIsArchived", supplier.SupplierIsArchived, DbType.Boolean);

                await conn.ExecuteAsync("spSupplier_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
    }
}

ISupplierService

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<bool> SupplierInsert(Supplier supplier);
        Task<IEnumerable<Supplier>> SupplierList();
        Task<Supplier> Supplier_GetOne(int SupplierID);
        Task<bool> SupplierUpdate(Supplier supplier);
    }
}

Tax

SQL script for stored procedures

-------------- Stored Proc for INSERT
CREATE PROCEDURE spTax_Insert
--Parameters for Insert stored procedure
@TaxDescription nvarchar(50),
@TaxRate decimal(6, 4),
@TaxIsArchived bit
AS
BEGIN
--SQL for Insert stored procedure
INSERT INTO Tax(TaxDescription, TaxRate, TaxIsArchived) VALUES (@TaxDescription, @TaxRate, @TaxIsArchived)
END

GO
--------------  Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
CREATE PROCEDURE spTax_List
--No parameters required.
AS
BEGIN
--SQL for Select stored procedure.
SELECT TaxID, TaxDescription, TaxRate, TaxIsArchived FROM Tax ORDER BY TaxID DESC
END

GO

--------------  Stored Proc for SELECT (one)
CREATE PROCEDURE spTax_GetOne
-- Needs one parameter for primary key
@TaxID int
AS 
BEGIN
-- SQL Select for one table row
SELECT TaxID, TaxDescription, TaxRate, TaxIsArchived FROM Tax WHERE TaxID= @TaxID
END

GO

--------------  Stored Proc for UPDATE
CREATE PROCEDURE spTax_Update
-- Parameters for Update stored procedure.
@TaxID int,
@TaxDescription nvarchar(50),
@TaxRate decimal(6, 4),
@TaxIsArchived bit
AS
BEGIN
-- SQL for Update stored procedure
UPDATE Tax SET TaxDescription = @TaxDescription, TaxRate = @TaxRate, TaxIsArchived = @TaxIsArchived WHERE TaxID = @TaxID
END

GO

Tax.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 Tax
    {
        [Required]
        public int TaxID { get; set; }
        [Required]
        [StringLength(50)]
        public string TaxDescription { get; set; }
        [Required]
        public decimal TaxRate { get; set; }
        [Required]
        public bool TaxIsArchived { get; set; }

    }
}

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<bool> TaxInsert(Tax tax)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("TaxDescription", tax.TaxDescription, DbType.String);
                parameters.Add("TaxRate", tax.TaxRate, DbType.Decimal);
                parameters.Add("TaxIsArchived", tax.TaxIsArchived, DbType.Boolean);

                // Stored procedure method
                await conn.ExecuteAsync("spTax_Insert", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
        // 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<bool> TaxUpdate(Tax tax)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("TaxID", tax.TaxID, DbType.Int32);

                parameters.Add("TaxDescription", tax.TaxDescription, DbType.String);
                parameters.Add("TaxRate", tax.TaxRate, DbType.Decimal);
                parameters.Add("TaxIsArchived", tax.TaxIsArchived, DbType.Boolean);

                await conn.ExecuteAsync("spTax_Update", parameters, commandType: CommandType.StoredProcedure);
            }
            return true;
        }
    }
}

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<bool> TaxInsert(Tax tax);
        Task<IEnumerable<Tax>> TaxList();
        Task<Tax> Tax_GetOne(int TaxID);
        Task<bool> TaxUpdate(Tax tax);
    }
}

Startup.cs

using BlazorPurchaseOrders.Areas.Identity;
using BlazorPurchaseOrders.Data;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Components.Authorization;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.UI;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Syncfusion.Blazor;

namespace BlazorPurchaseOrders
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            //Syncfusion support
            services.AddSyncfusionBlazor();
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString("DefaultConnection")));
            services.AddDefaultIdentity<IdentityUser>(options => options.SignIn.RequireConfirmedAccount = true)
                .AddEntityFrameworkStores<ApplicationDbContext>();
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddScoped<AuthenticationStateProvider, RevalidatingIdentityAuthenticationStateProvider<IdentityUser>>();
            services.AddDatabaseDeveloperPageExceptionFilter();
            services.AddSingleton<WeatherForecastService>();
            var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDBContext"));
            services.AddSingleton(sqlConnectionConfiguration);
            services.AddScoped<IPOHeaderService, POHeaderService>();
            services.AddScoped<IPOLineService, POLineService>();
            services.AddScoped<IProductService, ProductService>();
            services.AddScoped<ISupplierService, SupplierService>();
            services.AddScoped<ITaxService, TaxService>();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            //Register Syncfusion license
            Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("MzgwNDkyQDMxMzgyZTM0MmUzMEZUTU9mRm9mSFJ2V2tRNjhFVmJjVlBJdHBOa1A2TVRpNG5kMXBvWEZlVmM9");
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseMigrationsEndPoint();
            }
            else
            {
                app.UseExceptionHandler("/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthentication();
            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
                endpoints.MapBlazorHub();
                endpoints.MapFallbackToPage("/_Host");
            });
        }
    }
}