---
title: "DAO Interfaces"
description: "Define data access methods using interfaces and attributes"
canonical: "https://roomsharp.dev/docs/v0.4.7/dao-interfaces"
source: "src/content/v0.4.7/dao-interfaces.mdx"
---

# 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

```csharp
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

```csharp
[Insert]
long Insert(User user);

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

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

// Batch insert (using loop inside transaction)
[Insert]
void InsertAll(List<User> users);
```

### `[BulkInsert]`

Generate a high-performance BULK INSERT operation. This is much faster than `[Insert]` for large collections as it uses single-command multi-row insert statements.

```csharp
[BulkInsert]
void InsertBulk(IEnumerable<User> users);

[BulkInsert]
Task InsertBulkAsync(IEnumerable<User> users);

// Streaming input with IAsyncEnumerable
[BulkInsert]
Task InsertStreamAsync(IAsyncEnumerable<User> users);
```

### `[Update]`

Generate an UPDATE operation.

**Return Types:**
- `void` - No return value
- `int` - Returns number of rows updated
- `Task`, `Task<int>` - Async variants

```csharp
[Update]
int Update(User user);

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

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

### `[Delete]`

Generate a DELETE operation.

```csharp
[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.

```csharp
// 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();

// Streaming results (Memory efficient for large datasets)
[Query("SELECT * FROM logs ORDER BY CreatedAt DESC")]
IAsyncEnumerable<Log> StreamLogsAsync();
```

### `[Transaction]`

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

```csharp
[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).

```csharp
[Upsert]
long Upsert(User user);

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

## Complete DAO Example

```csharp
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);

    // 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:

```csharp
// 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 |
| `IAsyncEnumerable<T>` | Streaming results | Large exports, memory-constrained processing |

## Best Practices

- Use async methods for all database operations
- Prefer `[Query]` for better type safety and compile-time validation
- 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](/docs/v0.4.7/generated-code) - See what RoomSharp creates for you
- [Query API](/docs/v0.4.7/query-api) - Learn more about SQL queries
- [Transactions](/docs/v0.4.7/transactions) - Deep dive into transaction handling