WindowsFormsTest/Sqlite-EF-Test/Program.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.IO;
using System.Threading.Tasks;

namespace Sqlite_EF_Test
{
    internal class Program
    {
        static void Main(string[] args)
        {
            test();
            Console.ReadKey();
        }
        public static async void test()
        {
            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}");
            }
        }
    }

    [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; }
    }

    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);
        }
    }

    public class DatabaseService
    {
        private readonly string _connectionString;

        public DatabaseService(string connectionString = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                var dbPath = Path.Combine(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)
        {
            var user = new User
            {
                Name = name,
                Email = email,
                CreatedAt = DateTime.Now
            };
            using (var context = new AppDbContext(_connectionString))
            {
                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;
            }
        }
    }
}