Members' Newsletter

Introduction

I want to extend the Photino and MudBlazor project into something that I can use to demonstrate some MudBlazor techniques and might end up being useful.

The application I have alighted on is one that can be used by clubs and societies to send out the occasional newsletter. There are, of course plenty of commercial applications that can do this (such as Mailchimp) and this is not intended to compete with those. But it will have the advantage that, being built on Photino, your membership data does not leave your control - but nevertheless should still be kept under GDPR (data protection) guidelines.

I also intend to use AI (ChatGPT5) to write parts of the code where this might otherwise be tedious or repetitive  

We will need a database to store membership details and for this I will use SQLite.  I have never got to grips with Entity Framework, preferring Dapper as the Object-Relational Mapper.

I have also discovered a website that can be used to create html for newsletters (amongst other uses), called EmailBuilder.js.

To email the newsletters I intend to use MailKit. This is fairly mainstream and is well regarded for this function, and free! (What's more, I have also used it before successfully!)

The topics that we will cover are therefore:

  • MudBlazor, particularly
    • DataGrid
    • Dialogs
  • SQLite, including
    • DbBrowser for SQLite
  • EmailBuilder.js
  • MailKit
  • ChatGPT5

Database

We'll start with the database. Luckily it's going to be very simple. I think we will have one table for members' details and another to hold the information needed to email the newsletters.

The design of the members table should be:

  • First Name
  • Last Name
  • Email Address
  • An include/exclude flag

The Include/Exclude flag is so that we can either include or exclude certain members from a particular newsletter or mailing.

The information needed to send emails is:

  • From Email Address
  • Email Address Password
  • From 'Friendly Address'
  • SMTP Host Name
  • SMTP Port Number

As an aside I am putting the email sender data in the database, as opposed to, say, appsettings.json, is that I want it to be easily maintained without the need for a user to dive into the guts of the application.

It's easy enough to open DB Browser and add a database and create these tables, but let's give AI the task of creating a script to add the tables. We will add the database manually and call it 'Membership'.

To lay the groundwork for this, open the project in Visual Studio and

  • add a new folder called 'Data'
  • Right-click the new folder and select 'Copy full path'.

Open DB Browser for SQLite

  • Click the 'New Database' tab
  • Paste the path to the data folder into the 'address bar' at the top of the form. You will need to click the -> to the right to confirm the address
  • Enter a name for the database in the 'File name' box and save
  • A form will open prompting you to define a table; close this dialog.

We will now use ChatGPT5 to write the scripts to add the tables.

Open a web browser and go to: https://chatgpt.com/?openaicom_referred=true. From my experience so far I have found that it's good to give ChatGPT as much information as possible and to be specific about what you want. It might mean a few iterations adding details, but it will be worth it.

I am using SQLite and would like a script to create a table called 'Members'. The data I need to store is listed below:
First Name
Last Name
Email Address
An include/exclude flag

I'm being deliberately vague here; I could have specified the column types, but I want ChatGPT to save me work, not create it!

This is the result, and it looks pretty good to me. And it comes with an explanation.

  • Copy the script from ChatGPT.
  • Return to DB Browser for SQLite
  • Click the 'Excute SQL' tab.
  • Paste in the script from ChatGPT

That's created the Members table. Repeat for the 'Senders' table, using this instruction for ChatGPT:

I need another table, called 'Senders' with the following data
From Email Address
Email Address Password
From 'Friendly Address'
SMTP Host Name
SMTP Port Number
Could you write another script that will create this table in SQLite?

Copy the script into DB Browser and execute the SQL again.

Close DB Browser, making sure you save the database, (There is no need to save the tab layout.)

Important

Before going any further, make sure the database is handled correctly both when testing and when publishing.  To do this, in Visual Studio:

  • Right-click the database file
  • Select Properties
    • Change 'Build Action' to 'Content'
    • Change 'Copy to Output' to 'Copy if newer'

The last property might need to be changed once published to production, depending on the circumstances. (Beware: you wouldn't want the production data overwritten with test data for example!)

Model Classes

We can now use the SQL we used to create the tables to ask ChatGPT to create the model classes for the Visual Studio project.

Here is the prompt I used:

Using the SQL scripts you gave to create the Members and Senders tables, please give me the C# code needed to create model classes.

This is the code I got back:

Models/Member.cs

namespace PhotinoDemo.Models
{
    public class Member
    {
        public int MemberID { get; set; }

        public string FirstName { get; set; } = string.Empty;

        public string LastName { get; set; } = string.Empty;

        public string EmailAddress { get; set; } = string.Empty;

        /// <summary>
        /// Include flag: 1 = include, 0 = exclude
        /// </summary>
        public int IncludeFlag { get; set; }
    }
}

Models/Sender.cs

namespace PhotinoDemo.Models
{
    public class Sender
    {
        public int SenderID { get; set; }

        public string FromEmailAddress { get; set; } = string.Empty;

        public string EmailPassword { get; set; } = string.Empty;

        public string? FriendlyAddress { get; set; }

        public string SmtpHostName { get; set; } = string.Empty;

        public int SmtpPortNumber { get; set; }
    }
}

And the actual response shown below:

There is one lesson learnt from this. I omitted to say which folder I intended to save the model files in; I had intended them to be saved in the Data folder. In the absence of this information ChatGPT assumed I wanted to save the files in a folder called 'Models'. This probably follows standard practice, but wasn't what I intended. I also failed to state whether I wanted data validation using DataAnnotations, and therefore those [Required] statements are missing.

I have decided that I'm not going to fight, and will therefore add a 'Models' folder, add two new items for Member.cs and Sender.cs and paste the code into those files.

Services and Interfaces

I expect this to be more challenging. I want C# code for CRUD operations, for Insert, Update and Delete (all for a single record), plus read all records and return as a list/IEnumerable, and lastly read a specific record identified by its ID. I will be using Dapper, so I will need to state that in the ChatGPT question, and want the service to be saved in a folder called 'Services' and the interfaces in a folder called 'Interfaces', both of which should be sub-folders of 'Data'.  This is the question I composed for ChatGPT:

Using the models you have suggested for Member and Sender, could you provide me C# code for corresponding service and interface files, called MemberService.cs and SenderService.cs, and IMemberService.cs and ISenderService.cs. I would like the service files to be saved in a subfolder of ‘Data’ called ‘Services’ and the interface files in a subfolder of ‘Data’ called ‘Interfaces’. I will be using Dapper. The database is a SQLite database called ‘Membership.db’ and is stored in the ‘Data’ folder. The path to the database will be saved in appsettings.json. The services required are:
• Insert a single record
• Update a single record
• Delete a single record
• Read a single record, identified by its ID
• Read and return a list of all records, preferably as an IEnumerable
If there is any additional information you need please ask. 

These are screenshots of the ChatGPT response (I have not captured all the code, but show that separately below the screenshots).

IMemberService.cs

using PhotinoDemo.Models;

namespace PhotinoDemo.Data.Interfaces
{
    public interface IMemberService
    {
        Task<int> InsertAsync(Member member);
        Task<int> UpdateAsync(Member member);
        Task<int> DeleteAsync(int memberId);
        Task<Member?> GetByIdAsync(int memberId);
        Task<IEnumerable<Member>> GetAllAsync();
    }
}

ISenderService.cs

using PhotinoDemo.Models;

namespace PhotinoDemo.Data.Interfaces
{
    public interface ISenderService
    {
        Task<int> InsertAsync(Sender sender);
        Task<int> UpdateAsync(Sender sender);
        Task<int> DeleteAsync(int senderId);
        Task<Sender?> GetByIdAsync(int senderId);
        Task<IEnumerable<Sender>> GetAllAsync();
    }
}

MemberService.cs

using System.Data;
using System.Data.SQLite;
using Dapper;
using Microsoft.Extensions.Configuration;
using PhotinoDemo.Data.Interfaces;
using PhotinoDemo.Models;

namespace PhotinoDemo.Data.Services
{
    public class MemberService : IMemberService
    {
        private readonly string _connectionString;

        public MemberService(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("MembershipDb")
                ?? throw new InvalidOperationException("Database connection string not found.");
        }

        private IDbConnection CreateConnection()
            => new SQLiteConnection(_connectionString);

        public async Task<int> InsertAsync(Member member)
        {
            const string sql = @"
                INSERT INTO Members (FirstName, LastName, EmailAddress, IncludeFlag)
                VALUES (@FirstName, @LastName, @EmailAddress, @IncludeFlag);
                SELECT last_insert_rowid();";

            using var conn = CreateConnection();
            return await conn.ExecuteScalarAsync<int>(sql, member);
        }

        public async Task<int> UpdateAsync(Member member)
        {
            const string sql = @"
                UPDATE Members
                SET FirstName = @FirstName,
                    LastName = @LastName,
                    EmailAddress = @EmailAddress,
                    IncludeFlag = @IncludeFlag
                WHERE MemberID = @MemberID;";

            using var conn = CreateConnection();
            return await conn.ExecuteAsync(sql, member);
        }

        public async Task<int> DeleteAsync(int memberId)
        {
            const string sql = "DELETE FROM Members WHERE MemberID = @MemberID;";
            using var conn = CreateConnection();
            return await conn.ExecuteAsync(sql, new { MemberID = memberId });
        }

        public async Task<Member?> GetByIdAsync(int memberId)
        {
            const string sql = "SELECT * FROM Members WHERE MemberID = @MemberID;";
            using var conn = CreateConnection();
            return await conn.QuerySingleOrDefaultAsync<Member>(sql, new { MemberID = memberId });
        }

        public async Task<IEnumerable<Member>> GetAllAsync()
        {
            const string sql = "SELECT * FROM Members;";
            using var conn = CreateConnection();
            return await conn.QueryAsync<Member>(sql);
        }
    }
}

SenderService.cs

using System.Data;
using System.Data.SQLite;
using Dapper;
using Microsoft.Extensions.Configuration;
using PhotinoDemo.Data.Interfaces;
using PhotinoDemo.Models;

namespace PhotinoDemo.Data.Services
{
    public class SenderService : ISenderService
    {
        private readonly string _connectionString;

        public SenderService(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("MembershipDb")
                ?? throw new InvalidOperationException("Database connection string not found.");
        }

        private IDbConnection CreateConnection()
            => new SQLiteConnection(_connectionString);

        public async Task<int> InsertAsync(Sender sender)
        {
            const string sql = @"
                INSERT INTO Senders (FromEmailAddress, EmailPassword, FriendlyAddress, SmtpHostName, SmtpPortNumber)
                VALUES (@FromEmailAddress, @EmailPassword, @FriendlyAddress, @SmtpHostName, @SmtpPortNumber);
                SELECT last_insert_rowid();";

            using var conn = CreateConnection();
            return await conn.ExecuteScalarAsync<int>(sql, sender);
        }

        public async Task<int> UpdateAsync(Sender sender)
        {
            const string sql = @"
                UPDATE Senders
                SET FromEmailAddress = @FromEmailAddress,
                    EmailPassword = @EmailPassword,
                    FriendlyAddress = @FriendlyAddress,
                    SmtpHostName = @SmtpHostName,
                    SmtpPortNumber = @SmtpPortNumber
                WHERE SenderID = @SenderID;";

            using var conn = CreateConnection();
            return await conn.ExecuteAsync(sql, sender);
        }

        public async Task<int> DeleteAsync(int senderId)
        {
            const string sql = "DELETE FROM Senders WHERE SenderID = @SenderID;";
            using var conn = CreateConnection();
            return await conn.ExecuteAsync(sql, new { SenderID = senderId });
        }

        public async Task<Sender?> GetByIdAsync(int senderId)
        {
            const string sql = "SELECT * FROM Senders WHERE SenderID = @SenderID;";
            using var conn = CreateConnection();
            return await conn.QuerySingleOrDefaultAsync<Sender>(sql, new { SenderID = senderId });
        }

        public async Task<IEnumerable<Sender>> GetAllAsync()
        {
            const string sql = "SELECT * FROM Senders;";
            using var conn = CreateConnection();
            return await conn.QueryAsync<Sender>(sql);
        }
    }
}

appsettings.json

{
  "ConnectionStrings": {
    "MembershipDb": "Data Source=Data/Membership.db"
  }
}

At first sight that looks pretty good. It will save a lot of typing and should eliminate the possibility of mistyping and/or missing something if copying and pasting from similar code. The only thing I can spot is that in the 'services' files it has misinterpreted my question where I state the database is called Membership.db into the connection string being "MembershipDB".

  • Manually add Interfaces and Services subfolders
  • In the relevant folders manually add the following and paste in the suggested code, (correcting 'MembershipDB' to 'Membership').
    • IMemberService.cs
    • ISenderService.cs
    • MemberService.cs
    • SenderService.cs
  • Update appsettings.json to include the suggested code

Dapper and  System.Data.SQLite

We now need to add Dapper and System.Data.SQLite NuGet packages. Use Tools > Nuget Package Manager to add these. Once these are installed the red squiggles in the C# code should disappear.

Program.cs

The last thing we need to do is update Program.cs. ChatGPT has offered to provide the code, so we may as well accept the offer.

Yes please. Please provide the Dependency Injection registration snippet for both IMemberService and ISenderService.

A screenshot of the ChatGPT response is shown below, with the code shown below the screenshot.

using PhotinoDemo.Data.Interfaces;
using PhotinoDemo.Data.Services;

var builder = WebApplication.CreateBuilder(args);

// Add configuration from appsettings.json
builder.Configuration.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);

// Register services for Dapper-based data access
builder.Services.AddScoped<IMemberService, MemberService>();
builder.Services.AddScoped<ISenderService, SenderService>();

var app = builder.Build();

// ... your existing app setup code

app.Run();

The Program.cs we have already has 'appbuilder' in place of 'builder'.

  • Insert the two 'using; statements at the top of the file
  • Substitute 'appbuilder' for 'builder' and insert the two 'builder.Services.' lines

I found that the '.AddJsonFile' line caused an error. I have previously found that with Photino the configuration from appsettings can be acquired using this line of code:

// 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());

(It might be redundant - but we can change it later if needed.)

Conclusion

We have now laid the groundwork for using MudBlazor controls for CRUD operations. This will be coming next. Providing I find that it all works, the code provided by ChatGPT was an easier, more reliable, route than copying and pasting from previous projects. It needs handling with care and it helps if you know what you want and can specify any restrictions and other packages you know you will be using. We have used it for just two tables without any complication of foreign keys, but for multiple tables I think it would save a lot of time.

Powered by Publii