Desktop applications often start with direct database access inside view models, commands, or small helper classes.<\/p>\n\n\n\n
At first, this looks efficient. A button saves a record. A view model loads a list. A small method opens a SQLite connection, executes SQL, and returns data.<\/p>\n\n\n\n
Then the application grows.<\/p>\n\n\n\n
More entities appear. More screens need the same data. Audit logging becomes necessary. Formatting rules for dates and times must stay consistent. SQL commands repeat connection logic. Parameters are added manually everywhere. Dynamic sorting or filtering introduces SQL-injection risks. Testing becomes harder because persistence logic is scattered across the UI.<\/p>\n\n\n\n
The problem is not SQLite.<\/p>\n\n\n\n
The problem is missing repository architecture.<\/p>\n\n\n\n
Chrona uses a more disciplined approach: repository interfaces define contracts, abstract base classes provide shared SQLite infrastructure, specialized base classes enforce safety rules, and concrete repositories implement real persistence use cases.<\/p>\n\n\n\n
This article analyzes that design as a practical desktop architecture pattern.<\/p>\n\n\n\n
A repository interface should describe what the application needs from persistence without exposing how the data is stored.<\/p>\n\n\n\n
Chrona\u2019s audit repository is a clear example:<\/p>\n\n\n\n
public interface IAuditRepository\n{\n \/\/\/ <summary>\n \/\/\/ Schreibt einen Audit-Eintrag. Fehler werden protokolliert und verschluckt,\n \/\/\/ damit die Hauptoperation nicht fehlschl\u00e4gt.\n \/\/\/ <\/summary>\n Task AppendAsync(\n AuditEntry entry,\n CancellationToken cancellationToken = default);\n\n Task<IReadOnlyList<AuditEntry>> GetRecentAsync(\n int maxCount,\n CancellationToken cancellationToken = default);\n\n \/\/\/ <summary>\n \/\/\/ Entfernt alle Audit-Eintr\u00e4ge. Gibt die Anzahl gel\u00f6schter Zeilen zur\u00fcck.\n \/\/\/ <\/summary>\n Task<int> DeleteAllAsync(\n CancellationToken cancellationToken = default);\n}\n<\/code><\/pre>\n\n\n\nThis interface is not just \u201cclean code decoration\u201d.<\/p>\n\n\n\n
It protects the rest of the application from SQLite details.<\/p>\n\n\n\n
A service or view model that depends on IAuditRepository<\/code> does not need to know about connection strings, SQL commands, parameter binding, table names, or transaction details. It only knows the persistence capability it requires.<\/p>\n\n\n\nThat matters in a desktop app because the UI should not become the database orchestration layer. The UI should ask for data or trigger an operation. The repository should own persistence.<\/p>\n\n\n\n
The interface also creates a test seam. In tests, IAuditRepository<\/code> can be replaced with an in-memory fake or mock. That allows application logic to be tested without creating a real SQLite database.<\/p>\n\n\n\nThe key design point is this:<\/p>\n\n\n\n
The interface expresses the persistence contract. It does not leak the persistence mechanism.<\/p>\n\n\n\n
\n\n\n\nShared SQLite Infrastructure Belongs in an Abstract Base Class<\/h2>\n\n\n\n
Without a base repository class, every concrete repository usually repeats the same technical code:<\/p>\n\n\n\n
\n- read connection string<\/li>\n\n\n\n
- open SQLite connection<\/li>\n\n\n\n
- create command<\/li>\n\n\n\n
- add parameters<\/li>\n\n\n\n
- handle
DBNull.Value<\/code><\/li>\n\n\n\n- log failures<\/li>\n\n\n\n
- format values consistently<\/li>\n<\/ul>\n\n\n\n
Chrona avoids this with RepositoryBase<\/code>.<\/p>\n\n\n\npublic abstract class RepositoryBase\n{\n protected readonly string ConnectionString;\n protected readonly ILogger Logger;\n\n protected RepositoryBase(\n IDatabaseInitializer dbInitializer,\n ILogger logger)\n {\n ConnectionString = dbInitializer.ConnectionString;\n Logger = logger;\n }\n\n protected static void AddParameters(\n SqliteCommand command,\n params (string name, object? value)[] parameters)\n {\n foreach (var (name, value) in parameters)\n {\n command.Parameters.AddWithValue(\n name,\n value ?? DBNull.Value);\n }\n }\n\n protected async Task<SqliteConnection> CreateConnectionAsync(\n CancellationToken cancellationToken = default)\n {\n var connection = new SqliteConnection(ConnectionString);\n\n await connection\n .OpenAsync(cancellationToken)\n .ConfigureAwait(false);\n\n return connection;\n }\n}\n<\/code><\/pre>\n\n\n\nThis base class has a narrow and useful responsibility.<\/p>\n\n\n\n
It does not know about booking codes, projects, time entries, or audit entries. It only knows common SQLite infrastructure.<\/p>\n\n\n\n
That is the correct use of an abstract base class.<\/p>\n\n\n\n
The base class removes duplication without taking over domain behavior. Concrete repositories still own their SQL and mapping logic, but they no longer repeat low-level connection and parameter code.<\/p>\n\n\n\n
This gives the repository layer consistency.<\/p>\n\n\n\n
Every repository opens connections the same way. Every repository uses the same parameter helper. Every repository gets its connection string through the database initializer. Every repository has logging available.<\/p>\n\n\n\n
That consistency matters more as the application grows.<\/p>\n\n\n\n
\n\n\n\nWhy Parameter Helpers Matter<\/h2>\n\n\n\n
This helper looks simple:<\/p>\n\n\n\n
protected static void AddParameters(\n SqliteCommand command,\n params (string name, object? value)[] parameters)\n{\n foreach (var (name, value) in parameters)\n {\n command.Parameters.AddWithValue(\n name,\n value ?? DBNull.Value);\n }\n}\n<\/code><\/pre>\n\n\n\nBut it solves a real persistence problem.<\/p>\n\n\n\n
SQLite commands often require many parameters. Without a helper, each repository writes repetitive code:<\/p>\n\n\n\n
command.Parameters.AddWithValue(\"@Code\", bookingCode.Code);\ncommand.Parameters.AddWithValue(\"@Title\", bookingCode.Title);\ncommand.Parameters.AddWithValue(\"@IsActive\", bookingCode.IsActive ? 1 : 0);\n<\/code><\/pre>\n\n\n\nThe repetition itself is not the main problem. The problem is inconsistency.<\/p>\n\n\n\n
One method handles nulls correctly. Another forgets DBNull.Value<\/code>. Another formats a date differently. Another passes a boolean differently.<\/p>\n\n\n\nThe helper centralizes one small but recurring concern: safe parameter insertion with null handling.<\/p>\n\n\n\n
It also reinforces an important rule:<\/p>\n\n\n\n
Values belong in SQL parameters, not string interpolation.<\/p>\n\n\n\n
\n\n\n\nA Specialized Base Class for Safe Dynamic SQL<\/h2>\n\n\n\n
Parameters protect values.<\/p>\n\n\n\n
They do not protect SQL identifiers.<\/p>\n\n\n\n
This distinction is critical.<\/p>\n\n\n\n
A parameter can safely represent a value:<\/p>\n\n\n\n
WHERE Code = @Code\n<\/code><\/pre>\n\n\n\nBut it cannot safely represent a table name or column name:<\/p>\n\n\n\n
ORDER BY @ColumnName\n<\/code><\/pre>\n\n\n\nDatabase engines treat parameters as values, not identifiers. Therefore, if an application supports dynamic table names, column names, sorting, or filtering, it must validate identifiers explicitly.<\/p>\n\n\n\n
Chrona addresses this with a specialized base class.<\/p>\n\n\n\n
public abstract class ChronaRepositoryBase : RepositoryBase\n{\n protected abstract IReadOnlySet<string> AllowedTableNames { get; }\n\n protected abstract IReadOnlySet<string> AllowedColumnNames { get; }\n\n protected ChronaRepositoryBase(\n IDatabaseInitializer dbInitializer,\n ILogger logger)\n : base(dbInitializer, logger)\n {\n }\n\n protected sealed override void ValidateTableName(string tableName)\n {\n if (!AllowedTableNames.Contains(tableName))\n {\n throw new ArgumentException(\n $\"Ung\u00fcltiger Tabellenname: {tableName}\",\n nameof(tableName));\n }\n }\n\n protected sealed override void ValidateColumnName(string columnName)\n {\n if (!AllowedColumnNames.Contains(columnName))\n {\n throw new ArgumentException(\n $\"Ung\u00fcltiger Spaltenname: {columnName}\",\n nameof(columnName));\n }\n }\n}\n<\/code><\/pre>\n\n\n\nThis is a strong repository-layer design decision.<\/p>\n\n\n\n
The class forces concrete repositories to define allowed table and column names. Dynamic identifiers must pass through a whitelist. Invalid identifiers fail immediately.<\/p>\n\n\n\n
That matters because desktop apps often assume local databases are automatically safe. That is a mistake. Even local apps can process imported files, user-entered filters, configurable sort orders, or plugin-like data sources. Dynamic SQL must still be treated carefully.<\/p>\n\n\n\n
The design separates two safety mechanisms:<\/p>\n\n\n\n
SQL values \u2192 parameters\nSQL identifiers \u2192 whitelist validation\n<\/code><\/pre>\n\n\n\nThat is the correct model.<\/p>\n\n\n\n
\n\n\n\nConcrete Repository: Combining Contract, Infrastructure, and SQL<\/h2>\n\n\n\n
A concrete repository in Chrona combines three things:<\/p>\n\n\n\n
IBookingCodeRepository\n fachlicher Vertrag\n\nChronaRepositoryBase\n gemeinsame SQLite-Infrastruktur und Identifier-Schutz\n\nBookingCodeRepository\n konkrete SQL-Implementierung\n<\/code><\/pre>\n\n\n\nThe class shape shows this clearly:<\/p>\n\n\n\n
public class BookingCodeRepository\n : ChronaRepositoryBase, IBookingCodeRepository\n{\n private static readonly HashSet<string> AllowedTables =\n new(StringComparer.Ordinal)\n {\n \"BookingCodes\",\n \"TimeEntries\"\n };\n\n public BookingCodeRepository(\n IDatabaseInitializer dbInitializer,\n IAuditRepository auditRepository,\n ILogger<BookingCodeRepository> logger)\n : base(dbInitializer, logger)\n {\n _auditRepository = auditRepository;\n }\n}\n<\/code><\/pre>\n\n\n\nThis is the correct kind of inheritance.<\/p>\n\n\n\n
The repository does not inherit business behavior from a base class. It inherits infrastructure behavior: connection handling, logging support, identifier validation.<\/p>\n\n\n\n
The interface defines what booking-code persistence must provide. The concrete class defines how that persistence works in SQLite.<\/p>\n\n\n\n
The injected IAuditRepository<\/code> also shows that persistence operations are not isolated database writes only. They can participate in application-level concerns such as audit logging.<\/p>\n\n\n\nThat is realistic desktop architecture.<\/p>\n\n\n\n
\n\n\n\nCreate Operation with SQLite and Audit Logging<\/h2>\n\n\n\n
The CreateAsync<\/code> method demonstrates the repository pattern in a real use case.<\/p>\n\n\n\npublic async Task<BookingCode> CreateAsync(\n BookingCode bookingCode,\n CancellationToken cancellationToken = default)\n{\n using var connection = await CreateConnectionAsync(\n cancellationToken).ConfigureAwait(false);\n\n using var command = new SqliteCommand(\n \"INSERT INTO BookingCodes \" +\n \"(Code, Title, FreeTextAllowed, IsActive, CreatedAt, UpdatedAt) \" +\n \"VALUES (@Code, @Title, @FreeTextAllowed, @IsActive, @CreatedAt, @UpdatedAt); \" +\n \"SELECT last_insert_rowid();\",\n connection);\n\n AddParameters(\n command,\n (\"@Code\", bookingCode.Code),\n (\"@Title\", bookingCode.Title),\n (\"@FreeTextAllowed\", bookingCode.FreeTextAllowed ? 1 : 0),\n (\"@IsActive\", bookingCode.IsActive ? 1 : 0),\n (\"@CreatedAt\", SqliteFormat.SqlRoundTripUtc(bookingCode.CreatedAt)),\n (\"@UpdatedAt\",\n SqliteFormat.SqlRoundTripUtcNullable(bookingCode.UpdatedAt)\n ?? (object)DBNull.Value));\n\n var id = Convert.ToInt32(\n await command\n .ExecuteScalarAsync(cancellationToken)\n .ConfigureAwait(false));\n\n bookingCode.Id = id;\n\n await _auditRepository.AppendAsync(\n new AuditEntry\n {\n Entity = AuditEntityNames.BookingCode,\n EntityId = bookingCode.Id.ToString(),\n ChangeType = ChangeType.Created,\n NewValue = AuditSnapshotSerializer.BookingCodeSnapshot(bookingCode),\n CreatedAt = DateTime.UtcNow\n },\n cancellationToken);\n\n return bookingCode;\n}\n<\/code><\/pre>\n\n\n\nThis method shows several important design qualities.<\/p>\n\n\n\n
First, the database connection is created through the shared base class. The repository does not manually duplicate connection-string handling.<\/p>\n\n\n\n
Second, the SQL command uses parameters for all values. This protects against SQL injection for values and avoids fragile string concatenation.<\/p>\n\n\n\n
Third, boolean values are normalized explicitly as 1<\/code> or 0<\/code>. That is practical for SQLite because SQLite does not have a separate strict boolean storage type in the same way as some other relational databases.<\/p>\n\n\n\nFourth, timestamps are formatted through a dedicated helper. The repository does not rely on current UI culture or machine locale.<\/p>\n\n\n\n
Fifth, the generated ID is assigned back to the entity after last_insert_rowid()<\/code>.<\/p>\n\n\n\nSixth, the audit record is appended immediately after persistence. This makes the creation operation explainable later.<\/p>\n\n\n\n
This is what a repository should do in a desktop business application: persist state consistently and keep the persistence boundary auditable.<\/p>\n\n\n\n
\n\n\n\nWhy Audit Belongs Near the Mutation Boundary<\/h2>\n\n\n\n
Audit logging is strongest when it happens close to the actual mutation.<\/p>\n\n\n\n
For a create operation, the repository has the final persisted entity and generated ID. That is the right moment to create an audit snapshot.<\/p>\n\n\n\n
If audit logging happens later in the UI, the record may be incomplete, inconsistent, or forgotten entirely.<\/p>\n\n\n\n
By injecting IAuditRepository<\/code> into BookingCodeRepository<\/code>, Chrona makes audit logging part of the persistence workflow.<\/p>\n\n\n\nThat does create a design decision: the repository is not purely a table gateway anymore. It also participates in audit side effects.<\/p>\n\n\n\n
This is acceptable in a desktop business app if the rule is consistent and intentional.<\/p>\n\n\n\n
The important part is that audit writing is still abstracted behind IAuditRepository<\/code>. The booking-code repository does not know how audit records are stored internally.<\/p>\n\n\n\n
\n\n\n\nSQLite Formatting Must Be Explicit<\/h2>\n\n\n\n
Local persistence bugs often come from culture and timezone assumptions.<\/p>\n\n\n\n
Chrona uses a dedicated formatting helper:<\/p>\n\n\n\n
internal static class SqliteFormat\n{\n public static string SqlDate(DateTime date)\n {\n return date.ToString(\n \"yyyy-MM-dd\",\n CultureInfo.InvariantCulture);\n }\n\n public static string SqlTime(TimeSpan time)\n {\n return time.ToString(\n @\"hh\\:mm\\:ss\",\n CultureInfo.InvariantCulture);\n }\n\n public static string SqlRoundTripUtc(DateTime utc)\n {\n return utc.ToString(\n \"O\",\n CultureInfo.InvariantCulture);\n }\n}\n<\/code><\/pre>\n\n\n\nThis is small but important.<\/p>\n\n\n\n
A desktop app runs on user machines. Those machines may have different regional settings. If date and numeric values are formatted implicitly, the same application may persist different text representations depending on the user environment.<\/p>\n\n\n\n
That creates bugs that are hard to reproduce.<\/p>\n\n\n\n
Using CultureInfo.InvariantCulture<\/code> prevents this.<\/p>\n\n\n\nUsing \"yyyy-MM-dd\"<\/code> for dates produces stable sortable date strings.<\/p>\n\n\n\nUsing \"O\"<\/code> for UTC timestamps preserves round-trip precision and avoids ambiguous local-time formatting.<\/p>\n\n\n\nThis helper keeps SQLite values predictable.<\/p>\n\n\n\n
\n\n\n\nWhy This Repository Design Works<\/h2>\n\n\n\n
Chrona\u2019s repository design works because it separates concerns without fragmenting the codebase unnecessarily.<\/p>\n\n\n\n
The interface defines what the application expects.<\/p>\n\n\n\n
The abstract base class handles repeated SQLite infrastructure.<\/p>\n\n\n\n
The specialized base class protects dynamic SQL identifiers.<\/p>\n\n\n\n
The concrete repository implements actual persistence behavior.<\/p>\n\n\n\n
The formatter centralizes SQLite-compatible value representation.<\/p>\n\n\n\n
The audit repository records explainable changes.<\/p>\n\n\n\n
These pieces work together as one persistence architecture.<\/p>\n\n\n\n
A simplified dependency view looks like this:<\/p>\n\n\n\n
ViewModel \/ Service\n \u2193\nIBookingCodeRepository\n \u2193\nBookingCodeRepository\n \u2193\nChronaRepositoryBase\n \u2193\nRepositoryBase\n \u2193\nSQLite\n<\/code><\/pre>\n\n\n\nAnd for auditing:<\/p>\n\n\n\n
BookingCodeRepository\n \u2193\nIAuditRepository\n \u2193\nAuditRepository\n \u2193\nSQLite audit table\n<\/code><\/pre>\n\n\n\nThis keeps the UI away from SQL while still giving repositories enough control over efficient database operations.<\/p>\n\n\n\n
\n\n\n\nWhat This Design Avoids<\/h2>\n\n\n\n
This architecture avoids several common desktop persistence mistakes.<\/p>\n\n\n\n
It avoids SQL inside view models.<\/p>\n\n\n\n
It avoids duplicated connection-opening logic.<\/p>\n\n\n\n
It avoids inconsistent null handling.<\/p>\n\n\n\n
It avoids culture-dependent date persistence.<\/p>\n\n\n\n
It avoids raw dynamic SQL identifiers without validation.<\/p>\n\n\n\n
It avoids audit logic being scattered across UI workflows.<\/p>\n\n\n\n
It avoids tight coupling to concrete persistence implementations in higher layers.<\/p>\n\n\n\n
These are practical wins.<\/p>\n\n\n\n
\n\n\n\nTrade-Offs<\/h2>\n\n\n\n
This design is not free.<\/p>\n\n\n\n
Interfaces add structure, but too many interfaces can become noise if they do not protect real boundaries.<\/p>\n\n\n\n
Abstract base classes reduce duplication, but they must stay focused. If a base repository starts containing entity-specific logic, inheritance becomes a liability.<\/p>\n\n\n\n
Audit logging inside repositories creates a side effect. That side effect should be documented clearly, especially whether audit failure blocks the main operation or is treated as best effort.<\/p>\n\n\n\n
SQLite-specific formatting helpers are useful, but they also make the repository layer explicitly tied to SQLite. That is acceptable if SQLite is the chosen persistence engine. Do not pretend the implementation is database-agnostic if it is not.<\/p>\n\n\n\n
The strength of this design is not theoretical purity. The strength is disciplined pragmatism.<\/p>\n\n\n\n
\n\n\n\nTesting Strategy<\/h2>\n\n\n\n
This repository architecture supports several useful test levels.<\/p>\n\n\n\n
Interface-based tests can mock repositories when testing services or view models.<\/p>\n\n\n\n
Formatter tests can verify stable date, time, and UTC formatting.<\/p>\n\n\n\n
Repository integration tests can run against a temporary SQLite database and verify actual SQL behavior.<\/p>\n\n\n\n
Audit tests can verify that create, update, and delete operations append the expected audit entries.<\/p>\n\n\n\n
Identifier-validation tests can verify that only whitelisted tables and columns are accepted.<\/p>\n\n\n\n
The most valuable tests are usually integration tests around concrete repositories because SQL errors often appear only when commands hit a real database.<\/p>\n\n\n\n
Good test cases include:<\/p>\n\n\n\n
Create booking code persists all fields correctly.\nCreate booking code returns generated ID.\nNull UpdatedAt is stored as DBNull.\nCreatedAt uses round-trip UTC format.\nCreate operation appends audit entry.\nInvalid dynamic column name throws exception.\n<\/code><\/pre>\n\n\n\nThese tests protect the persistence boundary.<\/p>\n\n\n\n
\n\n\n\nFinal Thoughts<\/h2>\n\n\n\n
SQLite is a strong choice for desktop applications, but it still needs architecture.<\/p>\n\n\n\n
Chrona\u2019s repository design shows a practical model:<\/p>\n\n\n\n
\n- interfaces for persistence contracts<\/li>\n\n\n\n
- abstract base classes for shared SQLite infrastructure<\/li>\n\n\n\n
- whitelist validation for dynamic SQL identifiers<\/li>\n\n\n\n
- concrete repositories for entity-specific SQL<\/li>\n\n\n\n
- explicit formatting for dates, times, and UTC values<\/li>\n\n\n\n
- audit integration at the mutation boundary<\/li>\n<\/ul>\n\n\n\n
This is the right level of structure for a serious desktop application.<\/p>\n\n\n\n
Not over-engineered.<\/p>\n\n\n\n
Not ad hoc.<\/p>\n\n\n\n
A repository layer should make persistence boring, predictable, testable, and safe. Chrona\u2019s design moves in exactly that direction.<\/p>\n","protected":false},"excerpt":{"rendered":"
Interfaces, Abstract Base Classes, Safe SQL, and Audit-Aware Persistence in Chrona Desktop applications often start with direct database access inside view models, commands, or small helper classes. At first, this looks efficient. A button saves a record. A view model loads a list. A small method opens a SQLite connection, executes SQL, and returns data. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,33,27,8,25,32,34],"tags":[],"class_list":["post-212","post","type-post","status-publish","format-standard","hentry","category-csharp","category-methods","category-programming-principles","category-software-architecture","category-software-engineering","category-winforms","category-wpf"],"_links":{"self":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts\/212","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=212"}],"version-history":[{"count":1,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts\/212\/revisions"}],"predecessor-version":[{"id":213,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=\/wp\/v2\/posts\/212\/revisions\/213"}],"wp:attachment":[{"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=212"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=212"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fabricioruch.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=212"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}