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表
