安装Nuget Packages

• Dapper
• Dapper.SqlBuilder
• Microsoft.Data.SqlClient
• Oracle.ManagedDataAccess.Core
• AutoMapper

添加连接字符串

在appsettings.json中添加代码：

"ConnectionStrings": {
    "SqlConnection": "server=,; database=DapperASPNetCore; Integrated Security=true;Encrypt=false"
  },

创建上下文

public class DapperContext
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public DapperContext()
    {
        _configuration = new ConfigurationBuilder()
                            .SetBasePath(Directory.GetCurrentDirectory())
                            .AddJsonFile("appsettings.json")
                            .Build();
        _connectionString = _configuration.GetConnectionString("SqlConnection");
    }

    public IDbConnection CreateConnection()
        => new SqlConnection(_connectionString);
}

注册服务

builder.Services.AddSingleton<DapperContext>(); 
builder.Services.AddControllers();

创建实体

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Position { get; set; }
    public int CompanyId { get; set; }
}

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Country { get; set; }

    public List<Employee> Employees { get; set; } = new List<Employee>();
}

创建接口

public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
}

创建仓库

public class CompanyRepository : ICompanyRepository
{
    private readonly DapperContext _context;

    public CompanyRepository(DapperContext context)
    {
        _context = context;
    }
    
    public async Task<IEnumerable<Company>> GetCompanies()

    {

        var query = "SELECT * FROM Companies";

        using (var connection = _context.CreateConnection())

        {

            var companies = await connection.QueryAsync<Company>(query);

            return companies.ToList();

        }

    }
}

依赖注入

builder.Services.AddSingleton<DapperContext>();#前面已有
builder.Services.AddScoped<ICompanyRepository, CompanyRepository>(); 
builder.Services.AddControllers();#前面已有

控制器

[Route("api/companies")]
[ApiController]
public class CompaniesController : ControllerBase
{
    private readonly ICompanyRepository _companyRepo;

    public CompaniesController(ICompanyRepository companyRepo)
    {
        _companyRepo = companyRepo;
    }

    [HttpGet]
    public async Task<IActionResult> GetCompanies()
    {
        try
        {
            var companies = await _companyRepo.GetCompanies();
            return Ok(companies);
        }
        catch (Exception ex)
        {
            //log error
            return StatusCode(500, ex.Message);
        }
    }
}

DTO

public class CompanyForCreationDto
{
    public string Name { get; set; }
    public string Address { get; set; }
    public string Country { get; set; }
}