CslaBasicDataAccess.cs

// Project requires the following packages:
// <PackageReference Include="Csla" Version="8.0.0" /> (or latest)
// <PackageReference Include="System.Data.SQLite" Version="1.0.118" />

using Csla;
using Csla.Configuration;
using Microsoft.Extensions.DependencyInjection;
using MyCode;
using System.Data.SQLite;

var services = new ServiceCollection();
services.AddCsla();
services.AddScoped((p) => 
    {
        var connection = new SQLiteConnection("Data Source=Data.db");
        connection.Open();
        return connection;
    });
services.AddScoped<PersonDal>();
var serviceProvider = services.BuildServiceProvider();

// initialize database
var connection = serviceProvider.GetRequiredService<SQLiteConnection>();
var createTableSql = "CREATE TABLE IF NOT EXISTS Person (Id INTEGER PRIMARY KEY, Name TEXT)";
var createTableCommand = new SQLiteCommand(createTableSql, connection);
createTableCommand.ExecuteNonQuery();
connection.Close();

// test code
var portal = serviceProvider.GetRequiredService<IDataPortal<PersonEdit>>();
var person = await portal.CreateAsync();
person.Name = "Rocky Lhotka";
await person.SaveAndMergeAsync();
Console.WriteLine(person);

namespace MyCode
{
    public class PersonEdit : BusinessBase<PersonEdit>
    {
        public static readonly PropertyInfo<int> IdProperty = RegisterProperty<int>(nameof(Id));
        public int Id
        {
            get => GetProperty(IdProperty);
            set => SetProperty(IdProperty, value);
        }

        public static readonly PropertyInfo<string> NameProperty = RegisterProperty<string>(nameof(Name));
        public string Name
        {
            get => GetProperty(NameProperty);
            set => SetProperty(NameProperty, value);
        }

        public override string ToString()
        {
            return $"Person: Id {Id}, Name '{Name}'";
        }

        [Create, RunLocal]
        private async Task CreateAsync()
        {
            await BusinessRules.CheckRulesAsync();
        }

        [Fetch]
        [Transactional(TransactionalTypes.TransactionScope)]
        private async Task FetchAsync(int id, [Inject] PersonDal dal)
        {
            var personDto = await dal.FetchAsync(id);
            using (BypassPropertyChecks)
            {
                LoadProperty(IdProperty, id);
                LoadProperty(NameProperty, personDto.Name);
            }
            await BusinessRules.CheckRulesAsync();
        }

        [Update, Insert, DeleteSelf]
        [Transactional(TransactionalTypes.TransactionScope)]
        private async Task UpsertAsync([Inject] PersonDal dal)
        {
            var personDto = new PersonDto { Id = Id, Name = Name };
            if (IsNew)
                personDto = await dal.InsertAsync(personDto);
            else if (IsDeleted)
                personDto = await dal.DeleteAsync(personDto);
            else
                personDto = await dal.UpdateAsync(personDto);
            LoadProperty(IdProperty, personDto.Id);
        }

        [Delete]
        [Transactional(TransactionalTypes.TransactionScope)]
        private async Task DeleteAsync([Inject] PersonDal dal)
        {
            var personDto = new PersonDto { Id = Id, Name = Name };
            await dal.DeleteAsync(personDto);
        }
    }

    public class PersonDal(SQLiteConnection connection)
    {
        public async Task<PersonDto> FetchAsync(int id)
        {
            var command = new SQLiteCommand($"SELECT Name FROM Person WHERE Id = {id}", connection);
            using var reader = await command.ExecuteReaderAsync();
            reader.Read();
            return new PersonDto { Id = id, Name = reader.GetString(0) };
        }

        public async Task<PersonDto> UpdateAsync(PersonDto person)
        {
            using var command = new SQLiteCommand($"UPDATE Person SET Name = '{person.Name}' WHERE Id = {person.Id}", connection);
            await command.ExecuteNonQueryAsync();
            return person;
        }

        public async Task<PersonDto> InsertAsync(PersonDto person)
        {
            using var command = new SQLiteCommand($"INSERT INTO Person (Name) VALUES ('{person.Name}') Returning RowId", connection);
            var newId = await command.ExecuteScalarAsync();
            person.Id = Convert.ToInt32(newId);
            return person;
        }

        public async Task<PersonDto> DeleteAsync(PersonDto person)
        {
            using var command = new SQLiteCommand($"DELETE FROM Person WHERE Id = {person.Id}", connection);
            await command.ExecuteNonQueryAsync();
            person.Id = -1;
            return person;
        }
    }

    public class PersonDto
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}