Database Processing

Summary

In the Visual Studio project for each table we are going to need:

• Model file
• Service file
  
• Interface file
  

This is going to be repetitive a tedious, but here goes...

Contact

Contact Model

Right-click the Data folder and select Add > Class. Name it 'Contact.cs' and click 'Add'.

Replace the existing code with the following:

using System.ComponentModel.DataAnnotations;

namespace BlazorPhotinoGreetings.Data
{
    public class Contact
    {
        [Required]
        public int ContactID { get; set; }

        [Required]
        [StringLength(75, ErrorMessage = "'Contact' has a maximum length of 75 characters.")]
        public required string ContactName { get; set; }

        [Required]
        [EmailAddress(ErrorMessage = "Invalid Email Address format.")]
        [StringLength(255, ErrorMessage = "'Email' has a maximum length of 255 characters.")]
        public required string ContactEmailAddress { get; set; }

        [Required]
        [StringLength(75, ErrorMessage = "'Salutation' has a maximum length of 75 characters.")]
        public required string Salutation { get; set; }

        [Required]
        public int OccasionID { get; set; }

        [Required]
        public int MessageID { get; set; }

        [Required]
        public int SignatureID { get; set; }

        [Required]
        public int SenderID { get; set; }

        [Required]
        public int SignOffMessageID { get; set; }
        
        public string? ExtraMessage { get; set; }

        [Required]
        public int IsSelected { get; set; }
    }
}

Contact Service

Right-click the Data folder and select Add > Class. Name it 'ContactService.cs' and click 'Add'.

Replace the existing code with the following:

using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SQLite;

namespace BlazorPhotinoGreetings.Data
{
    public class ContactService : IContactService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public ContactService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";

        // Get a list of contact rows by UserID    
        public async Task<IEnumerable<Contact>> ContactList()
        {
            IEnumerable<Contact> contacts;          

            sqlCommand = "SELECT * FROM Contact";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                contacts = await conn.QueryAsync<Contact>(sqlCommand);
            }
            return contacts;
        }

        // Add (create) a Contact table row (SQL Insert)
        public async Task<bool> ContactInsert(Contact contact)
        {
            var parameters = new DynamicParameters();
           
            parameters.Add("@ContactName", contact.ContactName, DbType.String);
            parameters.Add("@ContactEmailAddress", contact.ContactEmailAddress, DbType.String);            
            parameters.Add("@Salutation", contact.Salutation, DbType.String);
            parameters.Add("@OccasionID", contact.OccasionID, DbType.Int32);
            parameters.Add("@MessageID", contact.MessageID, DbType.Int32);
            parameters.Add("@SignatureID", contact.SignatureID, DbType.Int32);            
            parameters.Add("@SenderID", contact.SenderID, DbType.Int32);
            parameters.Add("@ExtraMessage", contact.ExtraMessage, DbType.String);
            parameters.Add("@IsSelected", contact.IsSelected, DbType.Int32);

            sqlCommand = "INSERT INTO Contact (ContactName, ContactEmailAddress, Salutation, OccasionID, MessageID, SignatureID, SenderID, ExtraMessage, IsSelected) ";
            sqlCommand += "VALUES(@ContactName, @ContactEmailAddress, @Salutation, @OccasionID, @MessageID, @SignatureID, @SenderID, @ExtraMessage, @IsSelected)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<bool> ContactUpdate(Contact contact)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@ContactID", contact.ContactID, DbType.Int32);            
            parameters.Add("@ContactName", contact.ContactName, DbType.String);
            parameters.Add("@ContactEmailAddress", contact.ContactEmailAddress, DbType.String);
            parameters.Add("@Salutation", contact.Salutation, DbType.String);
            parameters.Add("@OccasionID", contact.OccasionID, DbType.Int32);
            parameters.Add("@MessageID", contact.MessageID, DbType.Int32);
            parameters.Add("@SignatureID", contact.SignatureID, DbType.Int32);
            parameters.Add("@SenderID", contact.SenderID, DbType.Int32);
            parameters.Add("@ExtraMessage", contact.ExtraMessage, DbType.String);
            parameters.Add("@IsSelected", contact.IsSelected, DbType.Int32);
            

            sqlCommand = "UPDATE Contact ";
            sqlCommand += "SET ContactName = @ContactName, ";            
            sqlCommand += "ContactEmailAddress = @ContactEmailAddress, ";
            sqlCommand += "Salutation = @Salutation, ";
            sqlCommand += "OccasionID = @OccasionID, ";
            sqlCommand += "MessageID = @MessageID, ";
            sqlCommand += "SignatureID = @SignatureID, ";
            sqlCommand += "SenderID = @SenderID, ";
            sqlCommand += "ExtraMessage = @ExtraMessage, ";
            sqlCommand += "IsSelected = @IsSelected ";
            sqlCommand += "WHERE ContactID  = @ContactID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<Contact> ContactGetOneByContactID(int ContactID)
        {
            Contact? contact = null;

            var parameters = new DynamicParameters();
            parameters.Add("@ContactID", ContactID, DbType.Int32);

            sqlCommand = "SELECT * FROM Contact WHERE ContactID = @ContactID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                contact = await conn.QueryFirstOrDefaultAsync<Contact>(sqlCommand, parameters);
            }
            return contact ?? throw new InvalidOperationException("Contact not found");
        }

        // Physically delete one Contact row based on its ContactID (SQL Delete)
        public async Task<bool> ContactDeleteByContactID(int ContactID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@ContactID", ContactID, DbType.Int32);

            sqlCommand = "DELETE FROM Contact ";
            sqlCommand += "WHERE ContactID= @ContactID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }
    }
}

Contact Interface

Right-click the Data folder and select Add > Class. Name it 'IContactService.cs' and click 'Add'.

namespace BlazorPhotinoGreetings.Data
{
    public interface IContactService
    {
        Task<IEnumerable<Contact>> ContactList();
        Task<bool> ContactInsert(Contact contact);
        Task<bool> ContactUpdate(Contact contact);
        Task<Contact> ContactGetOneByContactID(int ContactID);
        Task<bool> ContactDeleteByContactID(int ContactID);
    }

}

Occasion

Occasion Model

Right-click the Data folder and select Add > Class. Name it 'Occasion.cs' and click 'Add'.

Replace the existing code with the following:

using Dapper;
using Microsoft.Extensions.Configuration;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SQLite;

namespace BlazorPhotinoGreetings.Data
{
    public class Occasion
    {
        [Required]
        public int OccasionID { get; set; }

        [Required]
        [StringLength(25, ErrorMessage = "'Occasion' has a maximum length of 25 characters.")]
        public required string OccasionName { get; set; }

    }
}

Occasion Service

Right-click the Data folder and select Add > Class. Name it 'OccasionService.cs' and click 'Add'.

Replace the existing code with the following: (Note that I have had to use the variable 'newEvent' because 'event' is a reserved word in C#)

using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SQLite;

namespace BlazorPhotinoGreetings.Data
{
    public class OccasionService : IOccasionService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public OccasionService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";

        // Get a list of Event rows by UserID    
        public async Task<IEnumerable<Occasion>> OccasionList()
        {
            IEnumerable<Occasion> occasions;

            sqlCommand = "SELECT * FROM Occasion";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                occasions = await conn.QueryAsync<Occasion>(sqlCommand);
            }
            return occasions;
        }

        public async Task<bool> OccasionInsert(Occasion occasion)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@OccasionName", occasion.OccasionName, DbType.String);

            sqlCommand = "INSERT INTO Occasion (OccasionName) ";
            sqlCommand += "VALUES(@OccasionName)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<bool> OccasionUpdate(Occasion occasion)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@OccasionID", occasion.OccasionID, DbType.Int32);
            parameters.Add("@EventName", occasion.OccasionName, DbType.String);            

            sqlCommand = "UPDATE Occasion ";
            sqlCommand += "SET OccasionName= @OccasionName";
            sqlCommand += "WHERE OccasionID= @OccasionID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<Occasion> OccasionGetOneByOccasionID(int OccasionID)
        {
            Occasion? occasion = null;

            var parameters = new DynamicParameters();
            parameters.Add("@OccasionID", OccasionID, DbType.Int32);

            sqlCommand = "SELECT * FROM Event WHERE OccasionID= @OccasionID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                occasion= await conn.QueryFirstOrDefaultAsync<Occcasion>(sqlCommand, parameters);
            }
            return occasion?? throw new InvalidOperationException("Occasion not found");
        }

        // Physically delete one Event row based on its EventID (SQL Delete)
        public async Task<bool> OccasionDeleteByOccasionID(int OccasionID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@OccasionID", OccasionID, DbType.Int32);

            sqlCommand = "DELETE FROM Event ";
            sqlCommand += "WHERE OccasionID= @OccasionID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }
    }
}

Occasion Interface

Right-click the Data folder and select Add > Class. Name it 'IOccasionService.cs' and click 'Add'.

Replace the existing code with the following: 

namespace BlazorPhotinoGreetings.Data
{
    internal interface IOccasionService
    {
        Task<IEnumerable<Occasion>> OccasionList();
        Task<bool> OccasionInsert(Occasion occasion);
        Task<bool> OccasionUpdate(Occasion occasion);
        Task<Message> OccasionGetOneByOccasionID(int OccasionID);
        Task<bool> OccasionDeleteByOccasionID(int OccasionID);
    }
}

Message

Message Model

Right-click the Data folder and select Add > Class. Name it 'Message.cs' and click 'Add'.

Replace the existing code with the following:

using System.ComponentModel.DataAnnotations;

namespace BlazorPhotinoGreetings.Data
{
    public class Message
    {
        [Required]
        public int MessageID { get; set; }

        [Required]
        [StringLength(30, ErrorMessage = "'Description' has a maximum length of 30 characters.")]
        public string MessageDescription { get; set; } = string.Empty;

        [Required]
        [StringLength(250, ErrorMessage = "'Text' has a maximum length of 250 characters.")]
        public string MessageText { get; set; } = string.Empty;
    }
}

Message Service

Right-click the Data folder and select Add > Class. Name it 'MessageService.cs' and click 'Add'.

Replace the existing code with the following:

using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SQLite;

namespace BlazorPhotinoGreetings.Data
{
    public class MessageService : IMessageService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public MessageService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";

        // Get a list of message rows by UserID    
        public async Task<IEnumerable<Message>> MessageList()
        {
            IEnumerable<Message> messages;

            sqlCommand = "SELECT * FROM Message";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                messages = await conn.QueryAsync<Message>(sqlCommand);
            }
            return messages;
        }

        // Add (create) a Message table row (SQL Insert)
        public async Task<bool> MessageInsert(Message message)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@MessageDescription", message.MessageDescription, DbType.String);
            parameters.Add("@MessageText", message.MessageText, DbType.String);

            sqlCommand = "INSERT INTO Message (MessageDescription, MessageText) ";
            sqlCommand += "VALUES(@MessageDescription, @MessageText)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<bool> MessageUpdate(Message message)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@MessageID", message.MessageID, DbType.Int32);
            parameters.Add("@MessageDescription", message.MessageDescription, DbType.String);
            parameters.Add("@MessageText", message.MessageText, DbType.String);

            sqlCommand = "UPDATE Message ";
            sqlCommand += "SET MessageDescription = @MessageDescription, ";
            sqlCommand += "MessageText = @MessageText ";
            sqlCommand += "WHERE MessageID  = @MessageID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<Message> MessageGetOneByMessageID(int MessageID)
        {
            Message? message = null;

            var parameters = new DynamicParameters();
            parameters.Add("@MessageID", MessageID, DbType.Int32);

            sqlCommand = "SELECT * FROM Message WHERE MessageID = @MessageID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                message = await conn.QueryFirstOrDefaultAsync<Message>(sqlCommand, parameters);
            }
            return message ?? throw new InvalidOperationException("Message not found");
        }

        // Physically delete one Message row based on its MessageID (SQL Delete)
        public async Task<bool> MessageDeleteByMessageID(int MessageID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@MessageID", MessageID, DbType.Int32);

            sqlCommand = "DELETE FROM Message ";
            sqlCommand += "WHERE MessageID= @MessageID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }
    }
}

Message Interface

Right-click the Data folder and select Add > Class. Name it 'IMessageService.cs' and click 'Add'.

Replace the existing code with the following:

namespace BlazorPhotinoGreetings.Data
{
    internal interface IMessageService
    {
        Task<IEnumerable<Message>> MessageList();
        Task<bool> MessageInsert(Message message);
        Task<bool> MessageUpdate(Message message);
        Task<Message> MessageGetOneByMessageID(int MessageID);
        Task<bool> MessageDeleteByMessageID(int MessageID);
    }
}

Signature

Signature model

Right-click the Data folder and select Add > Class. Name it 'Signature.cs' and click 'Add'.

Replace the existing code with the following:

using System.ComponentModel.DataAnnotations;

namespace BlazorPhotinoGreetings.Data
{
    public class Signature
    {
        [Required]
        public int SignatureID { get; set; }

        [Required(ErrorMessage = "'From Signature' is compulsory (e.g. Anne & Andrew)")]
        [StringLength(50, ErrorMessage = "'From Signature' has a maximum length of 50 characters.")]
        public string FromSignature { get; set; } = string.Empty;
    }
}

Signature Service

Right-click the Data folder and select Add > Class. Name it 'SignatureService.cs' and click 'Add'.

Replace the existing code with the following:

using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SQLite;

namespace BlazorPhotinoGreetings.Data
{
    public class SignatureService : ISignatureService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public SignatureService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";

        // Get a list of Signature rows by UserID    
        public async Task<IEnumerable<Signature>> SignatureList()
        {
            IEnumerable<Signature> signatures;

            sqlCommand = "SELECT * FROM Signature";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                signatures = await conn.QueryAsync<Signature>(sqlCommand);
            }
            return signatures;
        }

        // Add (create) a Signature table row (SQL Insert)
        public async Task<bool> SignatureInsert(Signature signature)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@FromSignature", signature.FromSignature, DbType.String);

            sqlCommand = "INSERT INTO Signature (FromSignature) ";
            sqlCommand += "VALUES(@FromSignature)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<bool> SignatureUpdate(Signature signature)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@SignatureID", signature.SignatureID, DbType.Int32);
            parameters.Add("@FromSignature", signature.FromSignature, DbType.String);

            sqlCommand = "UPDATE Signature ";
            sqlCommand += "SET FromSignature = @FromSignature ";
            sqlCommand += "WHERE SignatureID  = @SignatureID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<Signature?> SignatureGetOneBySignatureID(int SignatureID)
        {
            Signature? signatures = null;

            var parameters = new DynamicParameters();
            parameters.Add("@SignatureID", SignatureID, DbType.Int32);

            sqlCommand = "SELECT * FROM Signature WHERE SignatureID = @SignatureID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                signatures = await conn.QueryFirstOrDefaultAsync<Signature>(sqlCommand, parameters);
            }
            return signatures;
        }

        // Physically delete one Signature row based on its SignatureID (SQL Delete)
        public async Task<bool> SignatureDeleteBySignatureID(int SignatureID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@SignatureID", SignatureID, DbType.Int32);

            sqlCommand = "DELETE FROM Signature ";
            sqlCommand += "WHERE SignatureID= @SignatureID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }
    }
}

Signature Interface

Right-click the Data folder and select Add > Class. Name it 'SignatureService.cs' and click 'Add'.

Replace the existing code with the following:

namespace BlazorPhotinoGreetings.Data
{
    public interface ISignatureService
        {
        Task<IEnumerable<Signature>> SignatureList();
        Task<bool> SignatureInsert(Signature signature);
        Task<bool> SignatureUpdate(Signature signature);
        Task<Signature?> SignatureGetOneBySignatureID(int SignatureID);
        Task<bool> SignatureDeleteBySignatureID(int SignatureID);
    }
}

Sender

Sender Model

Right-click the Data folder and select Add > Class. Name it 'Sender.cs' and click 'Add'.

Replace the existing code with the following:

using System.ComponentModel.DataAnnotations;

namespace BlazorPhotinoGreetings.Data
{
    public class Sender
    {
        [Required]
        public int SenderID { get; set; }

        [Required(ErrorMessage = "'Friendly Name' is compulsory (e.g. John Smith)")]
        [StringLength(50, ErrorMessage = "'Smpt Friendly Name' has a maximum length of 50 characters.")]
        public string SmtpFriendlyName { get; set; } = string.Empty;

        [Required(ErrorMessage = "'Email Address' is compulsory (e.g. jsmith@gmail.com)")]
        [EmailAddress(ErrorMessage = "Invalid Email Address format.")]
        public string SmtpEmailAddress { get; set; } = string.Empty;

        [Required(ErrorMessage = "'Password' is compulsory")]
        [StringLength(30, ErrorMessage = "'Password' has a maximum length of 30 characters.")]
        public string SmtpPassword { get; set; } = string.Empty;

        [Required(ErrorMessage = "'Smpt Host Name' is compulsory")]
        public string SmtpHost { get; set; } = string.Empty;
        [Required(ErrorMessage = "'Port Number' is compulsory (e.g. 587)")]
        public int SmtpPort { get; set; }
        
    }
}

Sender Service

Right-click the Data folder and select Add > Class. Name it 'SenderService.cs' and click 'Add'.

Replace the existing code with the following:

using BlazorPhotinoGreetings.Data;
using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SQLite;

namespace BlazorPhotinoGreetings.Data
{
    public class SenderService : ISenderService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public SenderService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public string connectionId = "Default";
        public string sqlCommand = "";

        // Get a list of sender rows
        public async Task<IEnumerable<Sender>> SenderList()
        {
            IEnumerable<Sender> senders;

            sqlCommand = "SELECT * FROM Sender";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                senders = await conn.QueryAsync<Sender>(sqlCommand);
            }
            return senders;
        }

        // Add (create) a Sender table row (SQL Insert)
        public async Task<bool> SenderInsert(Sender sender)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@SmtpFriendlyName", sender.SmtpFriendlyName, DbType.String);
            parameters.Add("@SmtpEmailAddress", sender.SmtpEmailAddress, DbType.String);
            parameters.Add("@SmtpPassword", sender.SmtpPassword, DbType.String);
            parameters.Add("@SmtpHost", sender.SmtpHost, DbType.String);
            parameters.Add("@SmtpPort", sender.SmtpPort, DbType.Int32);


            sqlCommand = "INSERT INTO Sender (SmtpFriendlyName, SmtpEmailAddress, SmtpPassword, SmtpHost, SmtpPort) ";
            sqlCommand += "VALUES(@SmtpFriendlyName, @SmtpEmailAddress, @SmtpPassword, @SmtpHost, @SmtpPort)";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<bool> SenderUpdate(Sender sender)
        {
            var parameters = new DynamicParameters();

            parameters.Add("@SenderID", sender.SenderID, DbType.Int32);
            parameters.Add("@SmtpFriendlyName", sender.SmtpFriendlyName, DbType.String);
            parameters.Add("@SmtpEmailAddress", sender.SmtpEmailAddress, DbType.String);
            parameters.Add("@SmtpPassword", sender.SmtpPassword, DbType.String);
            parameters.Add("@SmtpHost", sender.SmtpHost, DbType.String);
            parameters.Add("@SmtpPort", sender.SmtpPort, DbType.Int32);


            sqlCommand = "UPDATE Sender ";
            sqlCommand += "SET SmtpFriendlyName = @SmtpFriendlyName, ";
            sqlCommand += "SmtpEmailAddress = @SmtpEmailAddress, ";
            sqlCommand += "SmtpPassword = @SmtpPassword, ";
            sqlCommand += "SmtpHost = @SmtpHost, ";
            sqlCommand += "SmtpPort = @SmtpPort ";
            sqlCommand += "WHERE SenderID  = @SenderID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }

        public async Task<Sender> SenderGetOneBySenderID(int SenderID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@SenderID", SenderID, DbType.Int32);

            sqlCommand = "SELECT * FROM Sender WHERE SenderID = @SenderID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                var sender = await conn.QueryFirstOrDefaultAsync<Sender>(sqlCommand, parameters);
                if (sender == null)
                {
                    throw new InvalidOperationException($"Sender with ID {SenderID} not found.");
                }
                return sender;
            }
        }

        // Physically delete one Sender row based on its SenderID (SQL Delete)
        public async Task<bool> SenderDeleteBySenderID(int SenderID)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@SenderID", SenderID, DbType.Int32);

            sqlCommand = "DELETE FROM Sender ";
            sqlCommand += "WHERE SenderID= @SenderID";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                await conn.ExecuteAsync(sqlCommand, parameters);
            }
            return true;
        }
    }
}

Sender Interface

Right-click the Data folder and select Add > Class. Name it 'ISenderService.cs' and click 'Add'.

Replace the existing code with the following:

namespace BlazorPhotinoGreetings.Data
{
    internal interface ISenderService
    {
        Task<IEnumerable<Sender>> SenderList();
        Task<bool> SenderInsert(Sender sender);
        Task<bool> SenderUpdate(Sender sender);
        Task<Sender> SenderGetOneBySenderID(int SenderID);
        Task<bool> SenderDeleteBySenderID(int SenderID);
    }
}

Register in Program.cs

Having added the services and interfaces we need to register them in Program.cs.

Insert the following at the top of the file:

using BlazorPhotinoGreetings.Data;
using Microsoft.Extensions.Configuration;

And in the 'builder' section, add the following: (Note the first line. This was found to be necessary after a lot of frustration! - Care of Copilot!)

// Discovered that this line is required to use the IConfiguration interface in the services
appBuilder.Services.AddSingleton<IConfiguration>(provider => new ConfigurationBuilder().AddJsonFile("appsettings.json").Build());

appBuilder.Services.AddScoped<IContactService, ContactService>();
appBuilder.Services.AddScoped<IOccasionService, OccasionService>();
appBuilder.Services.AddScoped<IMessageService, MessageService>();
appBuilder.Services.AddScoped<ISignatureService, SignatureService>();
appBuilder.Services.AddScoped<ISenderService, SenderService>();  

Finally

It's worth saving all files and running the project at this point to check there no typographical errors have crept in!