C#实战：Dapper操作PostgreSQL笔记

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Npgsql; // 引入PostgreSQL的Npgsql库
 
public class PostgresNotebook
{
    private readonly string _connectionString;
 
    public PostgresNotebook(string connectionString)
    {
        _connectionString = connectionString;
    }
 
    public async Task<IEnumerable<Note>> ListNotesAsync()
    {
        using (var connection = new NpgsqlConnection(_connectionString))
        {
            // 使用Dapper的查询方法直接返回结果集
            return await connection.QueryAsync<Note>("SELECT * FROM notes");
        }
    }
 
    public async Task<Note> GetNoteAsync(Guid id)
    {
        using (var connection = new NpgsqlConnection(_connectionString))
        {
            // 使用参数化查询获取单个Note对象
            return await connection.QueryFirstOrDefaultAsync<Note>(
                "SELECT * FROM notes WHERE id = @id", new { id });
        }
    }
 
    public async Task<Note> AddNoteAsync(Note note)
    {
        using (var connection = new NpgsqlConnection(_connectionString))
        {
            // 使用Dapper的执行方法插入数据，并返回插入的Note对象
            return (await connection.QueryAsync<Note>(
                "INSERT INTO notes (content, important) VALUES (@content, @important) RETURNING *",
                note)).Single();
        }
    }
 
    public async Task<bool> UpdateNoteAsync(Note note)
    {
        using (var connection = new NpgsqlConnection(_connectionString))
        {
            // 使用Dapper的执行方法更新数据，并返回受影响的行数
            return (await connection.ExecuteAsync(
                "UPDATE notes SET content = @content, important = @important WHERE id = @id",
                note)) > 0;
        }
    }
 
    public async Task<bool> DeleteNoteAsync(Guid id)
    {
        using (var connection = new NpgsqlConnection(_connectionString))
        {
            // 使用Dapper的执行方法删除数据，并返回受影响的行数
            return (await connection.ExecuteAsync("DELETE FROM notes WHERE id = @id", new { id })) > 0;
        }
    }
}
 
public class Note
{
    public Guid Id { get; set; }
    public string Content { get; set; }
    public bool Important { get; set; }
}

这个代码实例展示了如何使用Dapper ORM与PostgreSQL数据库进行交互。PostgresNotebook类封装了与notes表