C# Sqlite读写方案

一个简单的C#实现Sqlite数据库读写的方法嘞

优点：

• 简单实用，静态实现，随调随用
• 单例实现，合理的资源释放
• Entity First，不用管表，ORM的实现
• 支持异步

AppDbContext.cs

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    /// <summary>
    /// 用户实体类
    /// </summary>
    public DbSet<UserEntity> Users{ get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlite("Data Source=database.db"); // 默认存储在程序根目录
        }
    }
}

SqliteSchemaUpdater.cs 这个主要是实现ORM模式，根据实体类自动CURD数据表

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Data.Common;
using System.Text;

public static class SqliteSchemaUpdater
{
    public static void EnsureSchemaSynced(DbContext context, bool log = false)
    {
        var connection = context.Database.GetDbConnection();
        connection.Open();

        var entityTypes = context.Model.GetEntityTypes();
        var existingTables = GetExistingTableNames(connection);

        // Step 1: DROP tables that are no longer in model
        foreach (var oldTable in existingTables)
        {
            if (!entityTypes.Any(e => e.GetTableName() == oldTable))
            {
                Log($"Dropping table: {oldTable}", log);
                ExecuteNonQuery(connection, $"DROP TABLE IF EXISTS [{oldTable}];");
            }
        }

        // Step 2: CREATE or ALTER
        foreach (var entityType in entityTypes)
        {
            var tableName = entityType.GetTableName()!;
            var props = entityType.GetProperties();

            if (!existingTables.Contains(tableName))
            {
                var createSql = BuildCreateTableSql(tableName, props);
                Log($"Creating table: {tableName}", log);
                ExecuteNonQuery(connection, createSql);
            }
            else
            {
                var existingColumns = GetExistingColumnNames(connection, tableName);
                var modelColumns = props.Select(p => p.GetColumnName()).ToList();

                // Add missing columns
                foreach (var p in props)
                {
                    var columnName = p.GetColumnName();
                    if (!existingColumns.Contains(columnName))
                    {
                        var columnSql = BuildAddColumnSql(p);
                        Log($"Adding column: {columnName} to {tableName}", log);
                        ExecuteNonQuery(connection, $"ALTER TABLE [{tableName}] ADD COLUMN {columnSql};");
                    }
                }

                // Drop obsolete columns
                foreach (var oldCol in existingColumns)
                {
                    if (!modelColumns.Contains(oldCol))
                    {
                        Log($"Dropping column: {oldCol} from {tableName}", log);
                        RecreateTableWithoutColumn(connection, entityType, oldCol, log);
                    }
                }
            }
        }
    }

    private static HashSet<string> GetExistingTableNames(DbConnection conn)
    {
        var tables = new HashSet<string>();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';";
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
            tables.Add(reader.GetString(0));
        return tables;
    }

    private static HashSet<string> GetExistingColumnNames(DbConnection conn, string tableName)
    {
        var columns = new HashSet<string>();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"PRAGMA table_info([{tableName}]);";
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
            columns.Add(reader.GetString(1));
        return columns;
    }

    private static string BuildCreateTableSql(string tableName, IEnumerable<IProperty> props)
    {
        var cols = props.Select(BuildColumnDefinition);
        return $"CREATE TABLE IF NOT EXISTS [{tableName}] ({string.Join(", ", cols)});";
    }

    private static string BuildColumnDefinition(IProperty prop)
    {
        var name = prop.GetColumnName();
        var type = prop.ClrType;
        var sqlType = GetSqliteType(type);
        var sb = new StringBuilder($"[{name}] {sqlType}");

        if (prop.IsPrimaryKey()) sb.Append(" PRIMARY KEY");
        if (prop.IsPrimaryKey() && (type == typeof(int) || type == typeof(long))) sb.Append(" AUTOINCREMENT");

        return sb.ToString();
    }

    private static string BuildAddColumnSql(IProperty prop) => BuildColumnDefinition(prop);

    private static string GetSqliteType(Type type) =>
        type == typeof(int) || type == typeof(long) ? "INTEGER" :
        type == typeof(double) || type == typeof(float) ? "REAL" :
        type == typeof(string) ? "TEXT" :
        type == typeof(bool) ? "INTEGER" :
        type == typeof(DateTime) ? "TEXT" : "TEXT";

    private static void ExecuteNonQuery(DbConnection conn, string sql)
    {
        using var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
    }

    private static void RecreateTableWithoutColumn(DbConnection conn, IEntityType entityType, string dropColumn, bool log)
    {
        var tableName = entityType.GetTableName()!;
        var tempTable = tableName + "_temp";
        var props = entityType.GetProperties().Where(p => p.GetColumnName() != dropColumn).ToList();

        Log($"Recreating table {tableName} without column {dropColumn}", log);

        // 1. Create temp table
        var createTemp = BuildCreateTableSql(tempTable, props);
        ExecuteNonQuery(conn, createTemp);

        // 2. Copy data
        var columns = string.Join(", ", props.Select(p => p.GetColumnName()));
        ExecuteNonQuery(conn, $"INSERT INTO [{tempTable}] ({columns}) SELECT {columns} FROM [{tableName}];");

        // 3. Drop original table
        ExecuteNonQuery(conn, $"DROP TABLE [{tableName}];");

        // 4. Rename temp
        ExecuteNonQuery(conn, $"ALTER TABLE [{tempTable}] RENAME TO [{tableName}];");
    }

    private static void Log(string message, bool enableLog)
    {
        if (enableLog)
            Logs.Info($"[SchemaUpdater] {message}"); // Logs类可以看我之前的日志方案文章：https://wc.sb/23
    }
}

使用示例：

首先要在App.xaml.cs中处理初始化

// App.xaml.cs

/// <summary>
/// 程序启动后事件
/// </summary>
/// <param name="e"></param>
protected override void OnStartup(StartupEventArgs e)
{
    base.OnStartup(e);

    using var db = new AppDbContext();

    // 自动同步数据库结构，log: true 表示输出日志
    SqliteSchemaUpdater.EnsureSchemaSynced(db, log: true);

    // 下面可以写一些数据库初始化操作...

}

使用：

using var db = new AppDbContext(); // 单例实现，啥时候用啥时候创建，不用定义成全局什么的
var user = db.Users.FirstOrDefault(x=>x.Id == "xxx"); // 查数据库，ORM形式，Linq直接查
user.UserName = "李四";
db.Update(user); // 更新数据

UserEntity addUser = new UserEntity();
addUser.UserName = "张三";
db.Add(addUser); // 插入数据

db.Remove(user); // 删除数据

db.SaveChanges(); // 每次操作数据库后，都要执行这个保存操作