WindowsFormsTest/Sqlite-EF-Test/新建文本文档.txt

我来为您提供一个在 .NET Framework 4.8 环境下使用 Entity Framework 和 Microsoft.Data.Sqlite 管理 SQLite 数据库的实现方案。
1. 项目配置
首先需要安装必要的 NuGet 包：
 <packages>
  <package id="Microsoft.Data.Sqlite" version="5.0.17" targetFramework="net48" />
  <package id="Microsoft.EntityFrameworkCore" version="3.1.32" targetFramework="net48" />
  <package id="Microsoft.EntityFrameworkCore.Sqlite" version="3.1.32" targetFramework="net48" />
  <package id="System.Memory" version="4.5.4" targetFramework="net48" />
  <package id="System.Buffers" version="4.5.1" targetFramework="net48" />
  <package id="System.Numerics.Vectors" version="4.5.0" targetFramework="net48" />
  <package id="System.Runtime.CompilerServices.Unsafe" version="4.7.1" targetFramework="net48" />
</packages>
2. 数据模型定义
 using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Users")]
public class User
{
    [Key]
    public int Id { get; set; }
    
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }
    
    [MaxLength(255)]
    public string Email { get; set; }
    
    public DateTime CreatedAt { get; set; }
}

[Table("Products")]
public class Product
{
    [Key]
    public int Id { get; set; }
    
    [Required]
    [MaxLength(200)]
    public string Name { get; set; }
    
    public decimal Price { get; set; }
    
    public int Stock { get; set; }
}
3. DbContext 实现
 using Microsoft.EntityFrameworkCore;
using System.IO;

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Product> Products { get; set; }
    
    private readonly string _connectionString;
    
    public AppDbContext()
    {
        var dbPath = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "app.db");
        _connectionString = $"Data Source={dbPath}";
    }
    
    public AppDbContext(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlite(_connectionString);
        }
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.CreatedAt)
                   .HasDefaultValueSql("CURRENT_TIMESTAMP");
        });
        
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.Name);
        });
        
        base.OnModelCreating(modelBuilder);
    }
}
4. 数据库服务封装
 using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class DatabaseService
{
    private readonly string _connectionString;
    
    public DatabaseService(string connectionString = null)
    {
        if (string.IsNullOrEmpty(connectionString))
        {
            var dbPath = System.IO.Path.Combine(
                System.AppDomain.CurrentDomain.BaseDirectory, "app.db");
            _connectionString = $"Data Source={dbPath}";
        }
        else
        {
            _connectionString = connectionString;
        }
    }
    
    // 初始化数据库
    public void InitializeDatabase()
    {
        using var context = new AppDbContext(_connectionString);
        context.Database.EnsureCreated();
    }
    
    // 用户相关操作
    public async Task<User> CreateUserAsync(string name, string email)
    {
        using var context = new AppDbContext(_connectionString);
        var user = new User
        {
            Name = name,
            Email = email,
            CreatedAt = DateTime.Now
        };
        
        context.Users.Add(user);
        await context.SaveChangesAsync();
        return user;
    }
    
    public async Task<List<User>> GetAllUsersAsync()
    {
        using var context = new AppDbContext(_connectionString);
        return await context.Users.ToListAsync();
    }
    
    public async Task<User> GetUserByIdAsync(int id)
    {
        using var context = new AppDbContext(_connectionString);
        return await context.Users.FindAsync(id);
    }
    
    public async Task<User> UpdateUserAsync(int id, string name, string email)
    {
        using var context = new AppDbContext(_connectionString);
        var user = await context.Users.FindAsync(id);
        if (user != null)
        {
            user.Name = name;
            user.Email = email;
            await context.SaveChangesAsync();
        }
        return user;
    }
    
    public async Task<bool> DeleteUserAsync(int id)
    {
        using var context = new AppDbContext(_connectionString);
        var user = await context.Users.FindAsync(id);
        if (user != null)
        {
            context.Users.Remove(user);
            await context.SaveChangesAsync();
            return true;
        }
        return false;
    }
    
    // 产品相关操作
    public async Task<Product> CreateProductAsync(string name, decimal price, int stock)
    {
        using var context = new AppDbContext(_connectionString);
        var product = new Product
        {
            Name = name,
            Price = price,
            Stock = stock
        };
        
        context.Products.Add(product);
        await context.SaveChangesAsync();
        return product;
    }
    
    public async Task<List<Product>> GetAllProductsAsync()
    {
        using var context = new AppDbContext(_connectionString);
        return await context.Products.ToListAsync();
    }
    
    public async Task<Product> GetProductByIdAsync(int id)
    {
        using var context = new AppDbContext(_connectionString);
        return await context.Products.FindAsync(id);
    }
    
    public async Task<Product> UpdateProductAsync(int id, string name, decimal price, int stock)
    {
        using var context = new AppDbContext(_connectionString);
        var product = await context.Products.FindAsync(id);
        if (product != null)
        {
            product.Name = name;
            product.Price = price;
            product.Stock = stock;
            await context.SaveChangesAsync();
        }
        return product;
    }
}
5. 使用示例
 using System;
using System.Threading.Tasks;

class Program
{
    static async Task Main(string[] args)
    {
        try
        {
            // 初始化数据库服务
            var dbService = new DatabaseService();
            dbService.InitializeDatabase();
            
            // 创建用户
            var user = await dbService.CreateUserAsync("张三", "zhangsan@example.com");
            Console.WriteLine($"创建用户: {user.Name}, ID: {user.Id}");
            
            // 创建产品
            var product = await dbService.CreateProductAsync("笔记本电脑", 5999.99m, 10);
            Console.WriteLine($"创建产品: {product.Name}, 价格: {product.Price}");
            
            // 查询所有用户
            var users = await dbService.GetAllUsersAsync();
            Console.WriteLine("所有用户:");
            foreach (var u in users)
            {
                Console.WriteLine($"  {u.Id}: {u.Name} ({u.Email})");
            }
            
            // 查询所有产品
            var products = await dbService.GetAllProductsAsync();
            Console.WriteLine("所有产品:");
            foreach (var p in products)
            {
                Console.WriteLine($"  {p.Id}: {p.Name} - ￥{p.Price} (库存: {p.Stock})");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"发生错误: {ex.Message}");
        }
        
        Console.ReadKey();
    }
}
6. 配置文件 (App.config)
 <?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
  </startup>
  
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqliteConnectionFactory, EntityFramework.Sqlite">
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="Microsoft.Data.Sqlite" 
                type="Microsoft.Data.Sqlite.SqliteProviderServices, Microsoft.Data.Sqlite" />
    </providers>
  </entityFramework>
  
  <system.data>
    <DbProviderFactories>
      <remove invariant="Microsoft.Data.Sqlite" />
      <add name="SQLite Data Provider" 
           invariant="Microsoft.Data.Sqlite" 
           description=".NET Data Provider for SQLite" 
           type="Microsoft.Data.Sqlite.SqliteFactory, Microsoft.Data.Sqlite" />
    </DbProviderFactories>
  </system.data>
</configuration>
关键要点
1.	版本兼容性: 使用 Entity Framework Core 3.1，这是支持 .NET Framework 4.8 的最后一个版本
2.	SQLite 驱动: 使用 Microsoft.Data.Sqlite 而不是传统的 System.Data.SQLite
3.	异步支持: 所有数据库操作都提供异步版本以提高性能
4.	连接字符串: 支持自定义数据库路径，默认在应用程序目录下创建 app.db
5.	数据库初始化: 使用 EnsureCreated() 方法自动创建数据库和表结构
这个方案提供了完整的 SQLite 数据库管理功能，包括增删改查操作，并且完全兼容 .NET Framework 4.8 环境。