DAO Interfaces

Define data access methods using interfaces and attributes

What are DAOs?

Data Access Objects (DAOs) are interfaces that define how you interact with your database. By marking interfaces with [Dao] and decorating methods with operation attributes, RoomSharp's source generator creates fully type-safe implementations at compile time.

Basic DAO Definition

using RoomSharp.Attributes;

[Dao]
public interface IUserDao
{
    [Insert]
    long Insert(User user);

    [Query("SELECT * FROM users WHERE Email = :email")]
    Task<User?> FindByEmailAsync(string email);

    [Update]
    int Update(User user);

    [Delete]
    int Delete(User user);
}

DAO Attributes

`[Dao]`

Marks an interface for source generation. The generator will create a concrete implementation class.

`[Insert]`

Generate an INSERT operation.

Return Types:

void - No return value
long - Returns the inserted row ID
int - Returns number of rows inserted
Task, Task<long>, Task<int> - Async variants

[Insert]
long Insert(User user);

[Insert]
Task<long> InsertAsync(User user);

// Batch insert
[Insert]
void InsertAll(List<User> users);

[Insert]
Task InsertAllAsync(IEnumerable<User> users);

`[Update]`

Generate an UPDATE operation.

Return Types:

void - No return value
int - Returns number of rows updated
Task, Task<int> - Async variants

[Update]
int Update(User user);

[Update]
Task<int> UpdateAsync(User user);

// Batch update
[Update]
Task UpdateAllAsync(List<User> users);

`[Delete]`

Generate a DELETE operation.

[Delete]
int Delete(User user);

[Delete]
Task<int> DeleteAsync(User user);

// Batch delete
[Delete]
Task DeleteAllAsync(List<User> users);

`[Query]`

Execute custom SQL queries with parameter binding.

Parameter Binding: Use :paramName syntax to bind method parameters.

// Single result
[Query("SELECT * FROM users WHERE Id = :userId")]
Task<User?> GetByIdAsync(long userId);

// Multiple results
[Query("SELECT * FROM users WHERE CreatedAt > :date")]
Task<List<User>> GetUsersAfterAsync(DateTime date);

// Scalar value
[Query("SELECT COUNT(*) FROM users")]
Task<int> GetCountAsync();

// Delete with query
[Query("DELETE FROM users WHERE IsActive = 0")]
Task<int> DeleteInactiveUsersAsync();

`[RawQuery]`

Execute dynamic queries built at runtime.

[RawQuery]
Task<List<User>> RunCustomQueryAsync(ISupportSQLiteQuery query);

// Usage
var query = SimpleSQLiteQuery.Create(
    "SELECT * FROM users WHERE Name LIKE ?", 
    "%john%"
);
var results = await dao.RunCustomQueryAsync(query);

`[Transaction]`

Wrap method execution in a database transaction. Can contain method body with multiple operations.

[Transaction]
async Task<long> UpsertAsync(User user)
{
    var existing = await FindByEmailAsync(user.Email);
    if (existing is null)
    {
        return Insert(user);
    }
    
    existing.Name = user.Name;
    Update(existing);
    return existing.Id;
}

[Transaction]
async Task<int> ReplaceAllAsync(IEnumerable<User> users)
{
    await DeleteAllAsync();
    foreach (var user in users)
    {
        Insert(user);
    }
    return users.Count();
}

`[Upsert]`

Insert or update (INSERT OR REPLACE in SQLite).

[Upsert]
long Upsert(User user);

[Upsert]
Task<long> UpsertAsync(User user);

Complete DAO Example

using RoomSharp.Attributes;

[Dao]
public interface ITodoDao
{
    // Insert operations
    [Insert]
    long Insert(Todo todo);
    
    [Insert]
    Task InsertAllAsync(IEnumerable<Todo> todos);

    // Update operations
    [Update]
    int Update(Todo todo);

    // Delete operations  
    [Delete]
    int Delete(Todo todo);

    // Query operations
    [Query("SELECT * FROM todos WHERE ListId = :listId ORDER BY CreatedAt DESC")]
    Task<List<Todo>> GetTodosByListAsync(long listId);

    [Query("SELECT COUNT(*) FROM todos WHERE IsCompleted = 0")]
    Task<int> GetActiveCountAsync();

    [Query("SELECT * FROM todos WHERE IsCompleted = :completed")]
    Task<List<Todo>> GetByStatusAsync(bool completed);

    // Raw query
    [RawQuery]
    Task<List<Todo>> RunCustomQueryAsync(ISupportSQLiteQuery query);

    // Transaction
    [Transaction]
    async Task<int> ReplaceAllAsync(IEnumerable<Todo> todos)
    {
        await DeleteCompletedAsync();
        foreach (var todo in todos)
        {
            Insert(todo);
        }
        return todos.Count();
    }

    [Query("DELETE FROM todos WHERE IsCompleted = 1")]
    Task<int> DeleteCompletedAsync();
}

Async Support

RoomSharp fully supports asynchronous operations:

Task - Async operation with no return value
Task<T> - Async operation returning a value
ValueTask<T> - High-performance async for hot paths

💡 Best Practice: Use async methods for all I/O operations to avoid blocking threads. RoomSharp generates efficient async code that doesn't allocate unnecessarily.

Parameter Binding

Query parameters are bound using the :paramName syntax:

// Single parameter
[Query("SELECT * FROM users WHERE Id = :userId")]
Task<User?> GetByIdAsync(long userId);

// Multiple parameters
[Query("SELECT * FROM todos WHERE ListId = :listId AND IsCompleted = :completed")]
Task<List<Todo>> GetTodosAsync(long listId, bool completed);

// DateTime parameter
[Query("SELECT * FROM logs WHERE CreatedAt BETWEEN :start AND :end")]
Task<List<Log>> GetLogsInRangeAsync(DateTime start, DateTime end);

Return Type Guidelines

Return Type	Use Case	Example
`void` / `Task`	No return value needed	Batch inserts/updates
`int` / `Task<int>`	Rows affected	Update, Delete operations
`long` / `Task<long>`	Inserted row ID	Insert with auto-generated PK
`T?` / `Task<T?>`	Single optional result	Find by ID, search queries
`List<T>` / `Task<List<T>>`	Multiple results	Get all, filtered lists

Best Practices

Use async methods for all database operations
Prefer [Query] over [RawQuery] when possible for better type safety
Use [Transaction] for operations that must be atomic
Return nullable types (T?) for queries that might return no results
Use descriptive method names that indicate what the query does
Keep DAO interfaces focused on a single entity when possible

Next Steps

Generated Code - See what RoomSharp creates for you
Query API - Learn more about SQL queries
Transactions - Deep dive into transaction handling