Person Model, Service & Interface

Introduction

Unlike in the previous Birthday Reminder application we won't be able to use the Dapper Code Generator to create code for the Model, Service and Interface files; this time we will need to add them manually, but we can still make use of some code from that project.

Person Model

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

Replace the existing code with the following (basically copied from the Birthday Reminders project).  The only significant change is that PersonID is now declared as an int instead of a GUID.

using System.ComponentModel.DataAnnotations;

namespace SQLiteBirthdayReminders.Data
{
    public class Person
    {
        [Required]
        public int PersonID { get; set; }
        [Required(ErrorMessage = "'First Name' is required.")]
        [StringLength(50, ErrorMessage = "'First Name' has a maximum length of 50 characters.")]
        public string PersonFirstName { get; set; } = String.Empty;
        [Required(ErrorMessage = "'Last Name' is required.")]
        [StringLength(50, ErrorMessage = "'Last Name' has a maximum length of 50 characters.")]
        public string PersonLastName { get; set; } = String.Empty;
        [Required(ErrorMessage = "Date of Birth is compulsory")]
        public DateTime PersonDateOfBirth { get; set; }
        [Required(ErrorMessage = "'Send Reminder To' is required.")]
        [EmailAddress(ErrorMessage = "Invalid Email Address format.")]
        [StringLength(100, ErrorMessage = "'Email' has a maximum length of 100 characters.")]
        public string PersonSendReminderTo { get; set; } = String.Empty;

        public string? PersonFullName { get; }
        public DateTime NextBirthday { get; }
        public int? DaysToNextBirthday { get; }
        public int? AgeNextBirthday { get; }
    }
}

Person Service

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

Replace the existing code with the following:

using Dapper;
using System.Data.SQLite;
using System.Data;

namespace SQLiteBirthdayReminders.Data
{
    public class PersonService : IPersonService
    {
        // Database connection
        private readonly IConfiguration _configuration;
        public PersonService(IConfiguration configuration)
        {
            _configuration = configuration;
        }

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

        // Get a list of person rows (SQL Select)     
        public async Task<IEnumerable<Person>> PersonList()
        {
            IEnumerable<Person> people;

            sqlCommand = "Select * from Person";

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

This is a very limited version of what we will eventually need, but is the least we need to check our connection to the SQLite database.

There are a number of significant differences between this and the equivalent code for SQL Server:

  • Because we can't use stored procedures it requires the 'raw' sql. This is held in the sqlCommand string variable.
  • The IDbConnection is 'SQLIteConnection'
  • The 'QueryAsync' is just 'sqlCommand' - the sql statement.

Person Interface

Right-click the Data folder and select Add >  New Item > Interface. Name it 'IPersonService.cs' and click 'Add'.

Replace the existing code with the following:

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

namespace SQLiteBirthdayReminders.Data
{
    public interface IPersonService
    {
        Task<IEnumerable<Person>> PersonList();
    }
}

Again, this is the bare minimum that I think we can get away with.

Program.cs

Having created the model, service and interface we need to add the following to Program.cs in the 'Builder' section.

builder.Services.AddScoped<IPersonService, PersonService>();
Powered by Publii