Class DbObject<TEntity>
OptimaJet.Workflow.SQLite.DbObject<TEntity>
Base class for SQLite database entity operations providing CRUD, query execution, and data mapping.
Namespace: OptimaJet.Workflow.SQLite
public class DbObject<TEntity> where TEntity : IEntity, new()Inheritance
- Object
Constructors
public DbObject(string schemaName, string dbTableName, int commandTimeout) #Initializes a new instance of DbObject<TEntity>.
Parameters
Name
Type
Description
schemaNameStringThe database schema name.
dbTableNameStringThe database table name.
commandTimeoutInt32The command timeout in seconds.
Properties
Name
Type
CommandTimeout #Int32Gets the command timeout in seconds.
SchemaName #StringGets the database schema name.
DbTableName #StringGets the database table name.
ObjectName #StringGets the fully qualified object name in the format "SchemaName.TableName".
Methods
public Task<int> InsertAsync(SqliteConnection connection, TEntity entity, SqliteTransaction transaction = null) #Inserts an entity into the table.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
entity{TEntity}The entity to insert.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The number of rows affected.
public virtual Task<int> UpsertAsync(SqliteConnection connection, TEntity entity, SqliteTransaction transaction = null) #Inserts or updates an entity using SQLite's INSERT ... ON CONFLICT ... DO UPDATE syntax.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
entity{TEntity}The entity to upsert.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The number of rows affected.
public virtual Task<int> UpdateAsync(SqliteConnection connection, TEntity entity, SqliteTransaction transaction = null) #Updates an existing entity in the table by its key columns.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
entity{TEntity}The entity with updated values.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The number of rows affected.
public Task<TEntity[]> SelectAllAsync(SqliteConnection connection) #Selects all entities from the table.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
Returns
Type
Description
Task<TEntity[]>An array of all entities.
public Task<TEntity[]> SelectAllWithPagingAsync(SqliteConnection connection, List<(string parameterName, SortDirection sortDirection)> orderParameters, Paging paging) #Selects all entities from the table with optional sorting and paging.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
orderParametersList<String, SortDirection>The sorting parameters.
pagingPagingThe paging settings, or
null to return all rows.Returns
Type
Description
Task<TEntity[]>An array of entities.
public Task<TEntity> SelectByKeyAsync(SqliteConnection connection, object id) #Selects an entity by its primary key value.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
idObjectThe primary key value.
Returns
Type
Description
Task<TEntity>The entity if found; otherwise,
null.public Task<TEntity[]> SelectByAsync<TSelect>(SqliteConnection connection, Expression<Func<TEntity, TSelect>> selectorLambda, TSelect value) #Selects entities where a specified property equals the given value.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
selectorLambdaExpression<FuncTEntity, TSelect>An expression selecting the property to filter on.
value{TSelect}The value to match.
Returns
Type
Description
Task<TEntity[]>An array of matching entities.
public Task<int> DeleteByAsync<TSelect>(SqliteConnection connection, Expression<Func<TEntity, TSelect>> selectorLambda, TSelect value, SqliteTransaction transaction = null) #Deletes entities where a specified property equals the given value.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
selectorLambdaExpression<FuncTEntity, TSelect>An expression selecting the property to filter on.
value{TSelect}The value to match.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The number of rows affected.
public Task<TEntity[]> SelectByWithPagingAsync<TSelect, TSort>(SqliteConnection connection, Expression<Func<TEntity, TSelect>> selectorLambda, TSelect value, Expression<Func<TEntity, TSort>> sortLambda, SortDirection sortDirection, Paging paging) #Selects entities filtered by a property value with sorting and paging.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
selectorLambdaExpression<FuncTEntity, TSelect>An expression selecting the property to filter on.
value{TSelect}The value to match.
sortLambdaExpression<FuncTEntity, TSort>An expression selecting the property to sort by.
sortDirectionSortDirectionThe sort direction.
pagingPagingThe paging settings, or
null to return all matching rows.Returns
Type
Description
Task<TEntity[]>An array of matching entities.
public Task<int> DeleteAsync(SqliteConnection connection, object id, SqliteTransaction transaction = null) #Deletes an entity by its primary key value.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
idObjectThe primary key value.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The number of rows affected.
public Task<int> GetCountAsync(SqliteConnection connection) #Returns the total number of entities in the table.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
Returns
Type
Description
Task<Int32>The row count.
public Task<int> GetCountByAsync<TSelect>(SqliteConnection connection, Expression<Func<TEntity, TSelect>> selectorLambda, TSelect value, SqliteTransaction transaction = null) #Returns the number of entities where a specified property equals the given value.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
selectorLambdaExpression<FuncTEntity, TSelect>An expression selecting the property to filter on.
value{TSelect}The value to match.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The count of matching rows.
public Task<int> ExecuteCommandNonQueryAsync(SqliteConnection connection, string commandText, params SqliteParameter[] parameters) #Executes a non-query command without a transaction.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<Int32>The number of rows affected.
public Task<int> ExecuteCommandNonQueryAsync(SqliteConnection connection, string commandText, SqliteTransaction transaction = null, params SqliteParameter[] parameters) #Executes a non-query command with an optional transaction.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
transactionSqliteTransactionAn optional transaction.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<Int32>The number of rows affected.
public Task<object> ExecuteCommandScalarAsync(SqliteConnection connection, string commandText, params SqliteParameter[] parameters) #Executes a scalar command without a transaction and returns the result.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<Object>The first column of the first row in the result set.
public Task<object> ExecuteCommandScalarAsync(SqliteConnection connection, string commandText, SqliteTransaction transaction = null, params SqliteParameter[] parameters) #Executes a scalar command with an optional transaction and returns the result.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
transactionSqliteTransactionAn optional transaction.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<Object>The first column of the first row in the result set.
public Task<TEntity[]> SelectAsync(SqliteConnection connection, string commandText, params SqliteParameter[] parameters) #Executes a SELECT query and maps the results to entities.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<TEntity[]>An array of mapped entities.
public Task<TEntity[]> SelectAsync(SqliteConnection connection, string commandText, SqliteTransaction transaction, params SqliteParameter[] parameters) #Executes a SELECT query within a transaction and maps the results to entities.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
transactionSqliteTransactionThe transaction to execute within.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<TEntity[]>An array of mapped entities.
public Task<List<Dictionary<string, object>>> SelectAsDictionaryAsync(SqliteConnection connection, string commandText, params SqliteParameter[] parameters) #Executes a SELECT query and returns the results as a list of dictionaries.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
commandTextStringThe SQL command text.
parametersSqliteParameter[]The command parameters.
Returns
Type
Description
Task<String, Object>A list of dictionaries where each dictionary represents a row with column names as keys.
public Task<int> InsertAllAsync(SqliteConnection connection, TEntity[] values, SqliteTransaction transaction = null) #Inserts multiple entities in a single batch INSERT statement.
Parameters
Name
Type
Description
connectionSqliteConnectionThe SQLite connection.
values{TEntity}[]The entities to insert.
transactionSqliteTransactionAn optional transaction.
Returns
Type
Description
Task<Int32>The number of rows affected.
public SqliteParameter CreateParameter(TEntity entity, ColumnInfo column, string namePrefix = "") #Creates a SqliteParameter for the specified entity column, converting the value to the appropriate database type.
Parameters
Name
Type
Description
entity{TEntity}The entity to read the column value from.
columnColumnInfoThe column definition.
namePrefixStringAn optional prefix for the parameter name.
Returns
Type
Description
SqliteParameterA configured SqliteParameter.
protected static object ToDbValue<TValue>(TValue value, DbType type) #Converts a CLR value to its database representation for the specified DbType.
Parameters
Name
Type
Description
value{TValue}The value to convert.
typeDbTypeThe target database type.
Returns
Type
Description
ObjectThe database-compatible value.
protected static object FromDbValue<TValue>(TValue dbValue, DbType type) #Converts a database value to its CLR representation for the specified DbType.
Parameters
Name
Type
Description
dbValue{TValue}The database value to convert.
typeDbTypeThe source database type.
Returns
Type
Description
ObjectThe CLR-compatible value.
public string GetKeyOrFirstColumn() #Returns the name of the primary key column, or the first column if no key is defined.
Returns
Type
Description
StringThe column name.