1.9. 数据访问

.NET 提供多种数据访问技术，从 ADO.NET 到 Entity Framework Core。

1.9.1. ADO.NET

ADO.NET 是 .NET 最底层的数据访问 API，提供直接执行 SQL 的能力。

using System.Data;
using System.Data.SqlClient;

// 连接字符串
string connStr = "Server=.;Database=MyDb;Trusted_Connection=true;TrustServerCertificate=true;";

// 查询
using var conn = new SqlConnection(connStr);
await conn.OpenAsync();

var cmd = new SqlCommand("SELECT Id, Name, Email FROM Users WHERE Id = @id", conn);
cmd.Parameters.AddWithValue("@id", userId);

using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
    string? email = reader.IsDBNull(2) ? null : reader.GetString(2);
}

// 非查询操作（INSERT / UPDATE / DELETE）
var insertCmd = new SqlCommand(@"
    INSERT INTO Users (Name, Email, CreatedAt)
    VALUES (@name, @email, @createdAt)", conn);

insertCmd.Parameters.AddWithValue("@name", "Alice");
insertCmd.Parameters.AddWithValue("@email", "alice@example.com");
insertCmd.Parameters.AddWithValue("@createdAt", DateTime.UtcNow);

int rows = await insertCmd.ExecuteNonQueryAsync();

// 事务
using var tx = await conn.BeginTransactionAsync();
try
{
    cmd.Transaction = (SqlTransaction)tx;
    await cmd.ExecuteNonQueryAsync();
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

警告

始终使用参数化查询，不要拼接 SQL 字符串，防止 SQL 注入。

// 错误！SQL 注入风险
var sql = $"SELECT * FROM Users WHERE Name = '{userInput}'";

// 正确！参数化查询
var cmd = new SqlCommand("SELECT * FROM Users WHERE Name = @name", conn);
cmd.Parameters.AddWithValue("@name", userInput);

1.9.2. Entity Framework Core

EF Core 是 .NET 的首选 ORM，支持多种数据库。

1.9.2.1. DbContext 与实体

// 实体类
public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string? Email { get; set; }
    public DateTime CreatedAt { get; set; }

    // 导航属性
    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

public class Order
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public decimal Amount { get; set; }
    public DateTime OrderDate { get; set; }

    // 导航属性
    public User User { get; set; } = null!;
}

// DbContext
public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(@"Server=.;Database=MyDb;...");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API 配置
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name)
                  .IsRequired()
                  .HasMaxLength(100);
            entity.HasIndex(e => e.Email).IsUnique();
        });
    }
}

1.9.2.2. CRUD 操作

// 创建
using var db = new AppDbContext();
var user = new User { Name = "Alice", Email = "alice@example.com" };
db.Users.Add(user);
await db.SaveChangesAsync();     // Id 自动填充

// 查询
var user = await db.Users
    .Include(u => u.Orders)      // Eager Loading
    .FirstOrDefaultAsync(u => u.Id == id);

var users = await db.Users
    .Where(u => u.Name.Contains("A"))
    .OrderBy(u => u.Name)
    .Skip(page * size)
    .Take(size)
    .ToListAsync();

// 更新
var user = await db.Users.FindAsync(id);
if (user != null)
{
    user.Name = "Updated Name";
    await db.SaveChangesAsync();
}

// 删除
var user = await db.Users.FindAsync(id);
if (user != null)
{
    db.Users.Remove(user);
    await db.SaveChangesAsync();
}

// 原生 SQL
var users = await db.Users
    .FromSql($"SELECT * FROM Users WHERE Name LIKE {search}")
    .ToListAsync();

1.9.2.3. Migrations

dotnet ef migrations add InitialCreate
dotnet ef database update

# 生成 SQL 脚本
dotnet ef migrations script

1.9.3. Dapper（轻量级 ORM）

using Dapper;

// 需要安装 Dapper NuGet 包

using var conn = new SqlConnection(connStr);

// 查询
var user = await conn.QueryFirstOrDefaultAsync<User>(
    "SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });

var users = await conn.QueryAsync<User>(
    "SELECT * FROM Users WHERE Name LIKE @Search",
    new { Search = $"%{keyword}%" });

// 执行
int rows = await conn.ExecuteAsync(
    "UPDATE Users SET Name = @Name WHERE Id = @Id",
    new { Name = "Alice", Id = 1 });

// 事务
using var tx = conn.BeginTransaction();
await conn.ExecuteAsync("DELETE FROM Orders WHERE UserId = @Id", new { id }, tx);
await conn.ExecuteAsync("DELETE FROM Users WHERE Id = @Id", new { id }, tx);
tx.Commit();

小技巧

EF Core vs Dapper 选择：

• EF Core：CRUD 密集、复杂关系映射、快速开发 → 选 EF Core

• Dapper：性能敏感、纯 SQL 掌控、简单数据映射 → 选 Dapper

• 兼顾：EF Core 负责复杂业务，Dapper 负责高并发查询

1.9.4. 文件数据库（SQLite）

// 安装 Microsoft.EntityFrameworkCore.Sqlite

public class LocalDbContext : DbContext
{
    public DbSet<ConfigItem> Config => Set<ConfigItem>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlite("Data Source=app.db");
    }
}

// SQLite 适合单机桌面应用、不需要安装数据库服务