C#:实现WEB API 读写DB

下面我将详细说明如何使用C#实现Web API读写数据库，采用分层架构和最佳实践：

技术栈

• Web框架: ASP.NET Core 6+
• 数据库访问: Entity Framework Core 7
• 数据库: SQL Server
• 架构模式: 分层架构（Controller-Service-Repository）

实现步骤

1. 项目结构

WebApiProject/
├── Controllers/          // API端点
├── Models/               // 数据模型
├── Services/             // 业务逻辑
├── Repositories/         // 数据库操作
├── DTOs/                 // 数据传输对象
└── appsettings.json      // 配置

2. 数据模型 (Models/Product.cs)

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
}

3. 数据库上下文 (Data/AppDbContext.cs)

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    
    public DbSet<Product> Products => Set<Product>();
}

4. 仓储模式实现 (Repositories/ProductRepository.cs)

public interface IProductRepository
{
    Task<Product> GetByIdAsync(int id);
    Task<IEnumerable<Product>> GetAllAsync();
    Task AddAsync(Product product);
    Task UpdateAsync(Product product);
    Task DeleteAsync(int id);
}

public class ProductRepository : IProductRepository
{
    private readonly AppDbContext _context;

    public ProductRepository(AppDbContext context) => _context = context;

    public async Task<Product> GetByIdAsync(int id) => 
        await _context.Products.FindAsync(id);

    public async Task<IEnumerable<Product>> GetAllAsync() => 
        await _context.Products.ToListAsync();

    public async Task AddAsync(Product product)
    {
        await _context.Products.AddAsync(product);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateAsync(Product product)
    {
        _context.Products.Update(product);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteAsync(int id)
    {
        var product = await GetByIdAsync(id);
        if (product != null) 
        {
            _context.Products.Remove(product);
            await _context.SaveChangesAsync();
        }
    }
}

5. 服务层 (Services/ProductService.cs)

public interface IProductService
{
    Task<ProductResponse> GetProductAsync(int id);
    Task<IEnumerable<ProductResponse>> GetAllProductsAsync();
    Task CreateProductAsync(CreateProductRequest request);
    Task UpdateProductAsync(UpdateProductRequest request);
    Task DeleteProductAsync(int id);
}

public class ProductService : IProductService
{
    private readonly IProductRepository _repository;
    private readonly IMapper _mapper;

    public ProductService(IProductRepository repository, IMapper mapper)
    {
        _repository = repository;
        _mapper = mapper;
    }

    public async Task<ProductResponse> GetProductAsync(int id)
    {
        var product = await _repository.GetByIdAsync(id);
        return _mapper.Map<ProductResponse>(product);
    }

    // 其他方法实现类似...
}

6. API控制器 (Controllers/ProductsController.cs)

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductService _productService;

    public ProductsController(IProductService productService) 
        => _productService = productService;

    [HttpGet("{id}")]
    public async Task<ActionResult<ProductResponse>> Get(int id)
    {
        var product = await _productService.GetProductAsync(id);
        return product != null ? Ok(product) : NotFound();
    }

    [HttpPost]
    public async Task<IActionResult> Create([FromBody] CreateProductRequest request)
    {
        await _productService.CreateProductAsync(request);
        return CreatedAtAction(nameof(Get), new { id = request.Id }, request);
    }

    // 其他端点: PUT, DELETE...
}

7. 依赖注入配置 (Program.cs)

var builder = WebApplication.CreateBuilder(args);

// 添加数据库上下文
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// 注册分层组件
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IProductService, ProductService>();

// 添加AutoMapper
builder.Services.AddAutoMapper(typeof(Program));

var app = builder.Build();

8. 数据库连接配置 (appsettings.json)

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=ProductDB;Integrated Security=true;TrustServerCertificate=true;"
  }
}

关键特性

1. 分层架构：

   • Controller：处理HTTP请求
   • Service：业务逻辑处理
   • Repository：数据库操作抽象

2. DTO模式：

   • 使用请求/响应DTO隔离内部模型

   public record CreateProductRequest(string Name, decimal Price);
   public record ProductResponse(int Id, string Name, decimal Price);
   

3. 异步编程：

   • 所有数据库操作使用async/await
   • 提高API吞吐量

4. 实体跟踪优化：

   • 在Repository中控制SaveChangesAsync调用
   • 避免DbContext生命周期过长

测试示例

使用Swagger或Postman测试端点：

• GET /api/products/1
• POST /api/products

  { "name": "Laptop", "price": 1299.99 }
  

数据库迁移

1. 安装EF Core工具：

   dotnet tool install --global dotnet-ef
   

2. 创建迁移：

   dotnet ef migrations add InitialCreate
   dotnet ef database update
   

此实现提供了：

• 清晰的职责分离
• 可测试的组件
• 灵活的数据库支持
• 可扩展的架构基础
• 符合RESTful设计原则

实际部署时建议添加：

1. 全局异常处理中间件
2. 请求验证管道
3. 身份认证/授权
4. 日志记录系统
5. 性能监控