這邊以 TODO List 來完成一個 Server 端服務

工具使用 Dapper、SQLite 做底層的資料庫串接

完整的 Server 端 nuget 需要安裝的如下

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.1.66" />
    <PackageReference Include="Grpc.AspNetCore" Version="2.71.0" />
    <PackageReference Include="Grpc.AspNetCore.HealthChecks" Version="2.71.0" />
    <PackageReference Include="Grpc.AspNetCore.Server.Reflection" Version="2.71.0" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="6.1.1" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="9.0.9" />
    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="3.0.2" />
  </ItemGroup>

資料庫存取部分

考量未來的資料庫存取擴充，建立一個介面 IDbConnectionFactory

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public class SqliteConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public SqliteConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }
    // 未來要取得連線的地方可以實作 IDbConnectionFactory 抽換
    // 如: MS-SQL、Oracle
    public IDbConnection CreateConnection()
    {
        var connection = new SqliteConnection(_connectionString);
        connection.Open();
        return connection;
    }
}

定義資料存取層，TodoRepository

public class TodoRepository
{
    private readonly IDbConnectionFactory _connectionFactory;

    public TodoRepository(IDbConnectionFactory connection)
    {
        _connectionFactory = connection;
        
        // 為了 Demo 才在這邊檢核，實務上請千萬不要在這邊做
        EnsureDatabaseCreated();
    }
    
    // 確保有 Todos Table
    public void EnsureDatabaseCreated()
    {
        using var connection = _connectionFactory.CreateConnection();
        // 建立表格（如果不存在）
        connection.Execute(@"
        CREATE TABLE IF NOT EXISTS Todos (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Title TEXT NOT NULL,
            Description TEXT,
            IsCompleted INTEGER NOT NULL DEFAULT 0,
            CreatedAt TEXT NOT NULL DEFAULT (datetime('now')),
            UserId TEXT
        )");
    }
    // -- 以下就實作 CRUD --
    public async Task<TodoItem> CreateAsync(TodoItem todo)
    {
        using var connection = _connectionFactory.CreateConnection();
        var sql = @"
        INSERT INTO Todos (Title, Description, IsCompleted, CreatedAt)
        VALUES (@Title, @Description, @IsCompleted, @CreatedAt);
        SELECT last_insert_rowid();";

        var id = await connection.ExecuteScalarAsync<int>(sql, todo);
        todo.Id = id;
        return todo;
    }

    public async Task<TodoItem?> GetByIdAsync(int id)
    {
        using var connection = _connectionFactory.CreateConnection();
        var sql = "SELECT * FROM Todos WHERE Id = @Id";
        return await connection.QuerySingleOrDefaultAsync<TodoItem>(sql, new { Id = id });
    }

    public async Task<IEnumerable<TodoItem>> ListAsync(bool? filterCompleted = null)
    {
        using var connection = _connectionFactory.CreateConnection();
        var sql = "SELECT * FROM Todos";
        var parameters = new DynamicParameters();

        if (filterCompleted.HasValue)
        {
            sql += " WHERE IsCompleted = @IsCompleted";
            parameters.Add("@IsCompleted", filterCompleted.Value ? 1 : 0);
        }

        return await connection.QueryAsync<TodoItem>(sql, parameters);
    }

    public async Task<bool> UpdateAsync(TodoItem todo)
    {
        using var connection = _connectionFactory.CreateConnection();
        var sql = @"
        UPDATE Todos
        SET Title = @Title, Description = @Description, IsCompleted = @IsCompleted
        WHERE Id = @Id";

        var rowsAffected = await connection.ExecuteAsync(sql, todo);
        return rowsAffected > 0;
    }

    public async Task<bool> DeleteAsync(int id)
    {
        using var connection = _connectionFactory.CreateConnection();
        var sql = "DELETE FROM Todos WHERE Id = @Id";
        var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
        return rowsAffected > 0;
    }
}

對外 gRPC 接口部份

TodoService .proto 定義

syntax = "proto3";

option csharp_namespace = "MyGrpc.Shares";

import "google/protobuf/timestamp.proto";
import "google/protobuf/wrappers.proto"; 
package todo;

// gRPC 服務定義
service TodoService {
  rpc CreateTodo (CreateTodoReq) returns (CreateTodoRes);
  rpc GetTodo (GetTodoReq) returns (GetTodoRes);
  rpc ListTodos (ListTodosReq) returns (ListTodosRes);
  rpc UpdateTodo (UpdateTodoReq) returns (UpdateTodoRes);
  rpc DeleteTodo (DeleteTodoReq) returns (DeleteTodoRes);
}


// 待辦事項資料結構
message TodoItem {
  int32 id = 1;
  string title = 2;
  string description = 3;
  bool is_completed = 4;
  google.protobuf.Timestamp created_time = 5;
}

// 建立待辦事項請求
message CreateTodoReq {
  string title = 1;
  string description = 2;
}

message CreateTodoRes {
  TodoItem todo = 1;
}

// 取得單一待辦事項請求
message GetTodoReq {
  int32 id = 1;
}

message GetTodoRes {
  TodoItem todo = 1;
}

// 列出所有待辦事項請求
message ListTodosReq {
  google.protobuf.BoolValue filter_completed = 1; // null 表示不限制
}

message ListTodosRes {
  repeated TodoItem todos = 1;
}

// 更新待辦事項請求
message UpdateTodoReq {
  int32 id = 1;
  string title = 2;
  string description = 3;
  bool is_completed = 4;
}

message UpdateTodoRes {
  TodoItem todo = 1;
}

// 刪除待辦事項請求
message DeleteTodoReq {
  int32 id = 1;
}

message DeleteTodoRes {
  bool success = 1;
}

繼承並實作 TodoService

public class TodoService(TodoRepository repository) : MyGrpc.Shares.TodoService.TodoServiceBase
{
    private readonly TodoRepository _repository = repository;

    // Helper: 將 DB Model 轉成 gRPC Message 回傳給 Client
    private static TodoItem MapToGrpcTodo(Models.TodoItem todo)
    {
        return new TodoItem
        {
            Id = todo.Id,
            Title = todo.Title,
            Description = todo.Description ?? string.Empty,
            IsCompleted = todo.IsCompleted,
            CreatedTime = todo.CreatedAt.ToTimestamp()
        };
    }

    public override async Task<CreateTodoRes> CreateTodo(CreateTodoReq request, ServerCallContext context)
    {
        var todo = new Models.TodoItem
        {
            Title = request.Title,
            Description = request.Description,
            IsCompleted = false,
            UserId = "" // 來自 JWT Token 參數
        };

        var created = await _repository.CreateAsync(todo);

        return new CreateTodoRes
        {
            Todo = MapToGrpcTodo(created)
        };
    }

    public override async Task<UpdateTodoRes> UpdateTodo(UpdateTodoReq request, ServerCallContext context)
    {
        var existing = await _repository.GetByIdAsync(request.Id);
        if (existing == null)
            throw new RpcException(new Grpc.Core.Status(StatusCode.NotFound, "Todo not found"));

        existing.Title = request.Title;
        existing.Description = request.Description;
        existing.IsCompleted = request.IsCompleted;

        var success = await _repository.UpdateAsync(existing);
        if (!success)
            throw new RpcException(new Grpc.Core.Status(StatusCode.Internal, "Failed to update todo"));

        return new UpdateTodoRes
        {
            Todo = MapToGrpcTodo(existing)
        };
    }

    public override async Task<DeleteTodoRes> DeleteTodo(DeleteTodoReq request, ServerCallContext context)
    {
        var success = await _repository.DeleteAsync(request.Id);
        if (!success)
            throw new RpcException(new Grpc.Core.Status(StatusCode.NotFound, "Todo not found"));

        return new DeleteTodoRes { Success = true };
    }

    public override async Task<GetTodoRes> GetTodo(GetTodoReq request, ServerCallContext context)
    {
        var todo = await _repository.GetByIdAsync(request.Id);
        if (todo == null)
            throw new RpcException(new Grpc.Core.Status(StatusCode.NotFound, "Todo not found"));

        return new GetTodoRes
        {
            Todo = MapToGrpcTodo(todo)
        };
    }

    public override async Task<ListTodosRes> ListTodos(ListTodosReq request, ServerCallContext context)
    {
        var todos = await _repository.ListAsync(request.FilterCompleted);

        var response = new ListTodosRes();
        foreach (var todo in todos)
        {
            response.Todos.Add(MapToGrpcTodo(todo));
        }

        return response;
    }
}

依賴注入

Program.cs 對以上 DI 注入

//...
var connectionString = "Data Source=todo.db";
builder.Services.AddSingleton<IDbConnectionFactory>(new SqliteConnectionFactory(connectionString));
builder.Services.AddSingleton<TodoRepository>();

//...
app.MapGrpcService<TodoService>();