# 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](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/)

```csharp
public class DbObject<TEntity> where TEntity : IEntity, new()
```

## Type Hierarchy

-   DbObject<TEntity> Current type
    -   [ProcessInstanceTree](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/ProcessInstanceTree/)
    -   [WorkflowApprovalHistory](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowApprovalHistory/)
    -   [WorkflowForm](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite.Models/WorkflowForm/)
    -   [WorkflowGlobalParameter](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowGlobalParameter/)
    -   [WorkflowInbox](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowInbox/)
    -   [WorkflowProcessInstance](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowProcessInstance/)
    -   [WorkflowProcessInstancePersistence](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowProcessInstancePersistence/)
    -   [WorkflowProcessInstanceStatus](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowProcessInstanceStatus/)
    -   [WorkflowProcessScheme](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowProcessScheme/)
    -   [WorkflowProcessTimer](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowProcessTimer/)
    -   [WorkflowProcessTransitionHistory](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowProcessTransitionHistory/)
    -   [WorkflowRuntime](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite.Models/WorkflowRuntime/)
    -   [WorkflowScheme](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/WorkflowScheme/)
    -   [WorkflowSync](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite.Models/WorkflowSync/)

## Constructors

`public DbObject(string schemaName, string dbTableName, int commandTimeout)` 

Initializes a new instance of [DbObject<TEntity>](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/DbObject-TEntity/).

### Parameters

| Name | Type | Description |
| --- | --- | --- |
| `schemaName` | `String` | The database schema name. |
| `dbTableName` | `String` | The database table name. |
| `commandTimeout` | `Int32` | The command timeout in seconds. |

## Properties

`public int CommandTimeout { get; }` 

Gets the command timeout in seconds.

`public string SchemaName { get; }` 

Gets the database schema name.

`public string DbTableName { get; }` 

Gets the database table name.

`public string ObjectName { get; }` 

Gets 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `entity` | `{TEntity}` | The entity to insert. |
| `transaction` | `SqliteTransaction` | An 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `entity` | `{TEntity}` | The entity to upsert. |
| `transaction` | `SqliteTransaction` | An 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `entity` | `{TEntity}` | The entity with updated values. |
| `transaction` | `SqliteTransaction` | An 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `orderParameters` | `List<String, SortDirection>` | The sorting parameters. |
| `paging` | `Paging` | The 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `id` | `Object` | The 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `selectorLambda` | `Expression<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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `selectorLambda` | `Expression<FuncTEntity, TSelect>` | An expression selecting the property to filter on. |
| `value` | `{TSelect}` | The value to match. |
| `transaction` | `SqliteTransaction` | An 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `selectorLambda` | `Expression<FuncTEntity, TSelect>` | An expression selecting the property to filter on. |
| `value` | `{TSelect}` | The value to match. |
| `sortLambda` | `Expression<FuncTEntity, TSort>` | An expression selecting the property to sort by. |
| `sortDirection` | `SortDirection` | The sort direction. |
| `paging` | `Paging` | The 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `id` | `Object` | The primary key value. |
| `transaction` | `SqliteTransaction` | An 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `selectorLambda` | `Expression<FuncTEntity, TSelect>` | An expression selecting the property to filter on. |
| `value` | `{TSelect}` | The value to match. |
| `transaction` | `SqliteTransaction` | An 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `transaction` | `SqliteTransaction` | An optional transaction. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `transaction` | `SqliteTransaction` | An optional transaction. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `transaction` | `SqliteTransaction` | The transaction to execute within. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `commandText` | `String` | The SQL command text. |
| `parameters` | `SqliteParameter[]` | 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 |
| --- | --- | --- |
| `connection` | `SqliteConnection` | The SQLite connection. |
| `values` | `{TEntity}[]` | The entities to insert. |
| `transaction` | `SqliteTransaction` | An optional transaction. |

### Returns

| Type | Description |
| --- | --- |
| `Task<Int32>` | The number of rows affected. |

`public SqliteParameter CreateParameter(TEntity entity, ColumnInfo column, string namePrefix = "")` 

Creates a [SqliteParameter](https://learn.microsoft.com/dotnet/api/microsoft.data.sqlite.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. |
| `column` | `ColumnInfo` | The column definition. |
| `namePrefix` | `String` | An optional prefix for the parameter name. |

### Returns

| Type | Description |
| --- | --- |
| `SqliteParameter` | A configured [SqliteParameter](https://learn.microsoft.com/dotnet/api/microsoft.data.sqlite.sqliteparameter). |

`protected static object ToDbValue<TValue>(TValue value, DbType type)` 

Converts a CLR value to its database representation for the specified [DbType](https://learn.microsoft.com/dotnet/api/system.data.dbtype).

### Parameters

| Name | Type | Description |
| --- | --- | --- |
| `value` | `{TValue}` | The value to convert. |
| `type` | `DbType` | The target database type. |

### Returns

| Type | Description |
| --- | --- |
| `Object` | The database-compatible value. |

`protected static object FromDbValue<TValue>(TValue dbValue, DbType type)` 

Converts a database value to its CLR representation for the specified [DbType](https://learn.microsoft.com/dotnet/api/system.data.dbtype).

### Parameters

| Name | Type | Description |
| --- | --- | --- |
| `dbValue` | `{TValue}` | The database value to convert. |
| `type` | `DbType` | The source database type. |

### Returns

| Type | Description |
| --- | --- |
| `Object` | The 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 |
| --- | --- |
| `String` | The column name. |

## Fields

`public static List<ColumnInfo> DBColumnsStatic` 

Static list of column definitions shared across all instances of the same generic type.

`public List<ColumnInfo> DBColumns` 

List of column definitions for this database object.

[Previous ColumnInfo](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/ColumnInfo/) [Next Extensions](https://api.workflowengine.io/sqlite/OptimaJet.Workflow.SQLite/Extensions/)