Abstract Base Database Connection (BaseDatabaseConnection) Class

Namespace: Cxt.Account.Settings.API.DatabaseImplements: IDatabaseConnectionAbstract: Cannot be instantiated directly; base for concrete DB connection implementations.

Provides a foundational, abstract IDatabaseConnection implementation, encapsulating common ADO.NET DbConnection / DbCommand logic provider-agnostically.

Purpose

Reduces boilerplate in concrete DB connection classes (e.g., SqliteDatabaseConnection) by:

• Managing the underlying DbConnection.
• Implementing IDatabaseConnection methods (OpenAsync, CloseAsync, ExecuteNonQueryAsync, ExecuteReaderAsync, DisposeAsync).
• Providing parameter addition mechanism for DbCommand.
• Delegating provider-specific DbCommand creation to derived classes (via abstract CreateCommand). Facilitates multi-provider DB support (e.g., SQLite, SQL Server) by abstracting common ADO.NET operations.

Key Components

• protected readonly DbConnection _connection;
  • Stores the provider-specific DbConnection (e.g., SqliteConnection) supplied by a derived class.

Constructor: BaseDatabaseConnection

protected BaseDatabaseConnection(System.Data.Common.DbConnection connection)

• Description: Initializes the _connection field.
• Parameters:

  Parameter	Type	Description
  connection	System.Data.Common.DbConnection	The provider-specific database connection instance.

• Throws: ArgumentNullException if connection is null.

Implemented Methods (from IDatabaseConnection)

Open Async

public async Task OpenAsync(CancellationToken cancellationToken = default)

• Description: Delegates to the underlying _connection.OpenAsync(cancellationToken).
• Parameters:

  Parameter	Type	Default	Description
  cancellationToken	CancellationToken	default	For operation cancellation.

Close Async

public async Task CloseAsync()

• Description: Delegates to the underlying _connection.CloseAsync().

Execute Non Query Async

public async Task<int> ExecuteNonQueryAsync(string sql, Dictionary<string, object> parameters, CancellationToken cancellationToken = default)

• Description: Creates a command, adds parameters, executes it as a non-query, and disposes the command.
  • Calls abstract CreateCommand(sql) (implemented by derived class).
  • Adds parameters via AddParameters.
  • Executes via command.ExecuteNonQueryAsync(cancellationToken).
  • Disposes DbCommand via using.
• Parameters:

  Parameter	Type	Default	Description
  sql	string		SQL command string.
  parameters	Dictionary<string, object>		SQL parameters dictionary.
  cancellationToken	CancellationToken	default	For operation cancellation.

• Returns: Task<int> resolving to the number of affected rows.

Execute Reader Async

public async Task<System.Data.Common.DbDataReader> ExecuteReaderAsync(string sql, Dictionary<string, object> parameters, CancellationToken cancellationToken = default)

• Description: Creates a command, adds parameters, and executes it to return a DbDataReader.
  • Calls CreateCommand(sql).
  • Adds parameters via AddParameters.
  • Executes via command.ExecuteReaderAsync(cancellationToken).
• Parameters:

  Parameter	Type	Default	Description
  sql	string		SQL query string.
  parameters	Dictionary<string, object>		SQL parameters dictionary.
  cancellationToken	CancellationToken	default	For operation cancellation.

• Returns: Task<System.Data.Common.DbDataReader> resolving to a DbDataReader.

Dispose Async

public async ValueTask DisposeAsync()

• Description: Implements IAsyncDisposable. Delegates to the underlying _connection.DisposeAsync().

Abstract and Virtual Methods

Create Command (Abstract)

protected abstract System.Data.Common.DbCommand CreateCommand(string sql);

• Description: Must be implemented by derived classes to create a provider-specific DbCommand (e.g., SqliteCommand) associated with the current _connection.
• Parameters:

  Parameter	Type	Description
  sql	string	The SQL string to be used for the command.

• Returns: System.Data.Common.DbCommand - The provider-specific command.

Add Parameters (Virtual)

protected virtual void AddParameters(System.Data.Common.DbCommand command, Dictionary<string, object> parameters)

• Description: Default implementation for adding parameters to a DbCommand. Iterates through the parameters dictionary, creates a DbParameter for each, sets its ParameterName and Value, and adds it to the command.Parameters collection.
• Parameters:

  Parameter	Type	Description
  command	System.Data.Common.DbCommand	The command to which parameters will be added.
  parameters	Dictionary<string, object>	A dictionary of parameter names and their values.

• Behavior: This method can be overridden by derived classes for custom parameter handling if needed.

How It Works with Derived Classes

Derived classes (e.g., SqliteDatabaseConnection) extend BaseDatabaseConnection by:

1. Supplying a provider-specific DbConnection (e.g., new SqliteConnection(connectionString)) to the base constructor.
2. Implementing protected abstract DbCommand CreateCommand(string sql) to return a provider-specific DbCommand (e.g., new SqliteCommand(sql, (SqliteConnection)_connection)).

This promotes code reuse and separates common DB logic from provider-specifics.