C# .NET Core6.0 使用加密SQLite数据库

开发测试环境：

VS2022+Windows10+.NET Core6.0

话不多说，直接上步骤

1、NuGet安装如下包

PS：如果需要使用Migration来构建，则需要安装如下包，否则不需要安装。

 

 2、Program.cs内容

using Microsoft.EntityFrameworkCore;
using SQLite.Test.Database;
using SQLite.Test.Models;

namespace SQLite.Test
{
    internal class Program
    {
        private static readonly string _dbFileName="newtest.db";
        private static readonly string _password = "iqwbnyrtv9mgsfjbpnuwgrve";

        static void Main(string[] args)
        {
            Console.WriteLine("默认数据库文件名：{0}", AppDbContext.DEFAULTDBFILE);
            Console.WriteLine("新的数据库文件名：{0}", _dbFileName);
            Console.WriteLine("以下示例以默认文件名为例：");

            using (var db = new AppDbContext(AppDbContext.DEFAULTDBFILE, _password))
            {
                //创建一个新的数据库
                if (!File.Exists(AppDbContext.DEFAULTDBFILE))
                {
                    Console.WriteLine("创建新数据库文件");
                    db.Database.EnsureCreated();
                }

                //添加新记录
                Student[] students = new Student[]
                {
                    new()
                    {
                        Id = Guid.NewGuid(),
                        Name = "张三",
                        Age = 15,
                        Sex = "男",
                        Description = "小胖子"
                    },
                    new()
                    {
                        Id = Guid.NewGuid(),
                        Name = "李四",
                        Age = 13,
                        Sex = "女",
                        Description = "小萝莉"
                    }
                };


                Console.WriteLine("添加学生" );
                db.Students.AddRange(students);
                Console.WriteLine("{0} 条记录保存到数据库", db.SaveChanges());

                //显示数据库里所有记录
                Console.WriteLine("数据库中共有{0}条数据", db.Students.Count());
                foreach (var item in db.Students)
                {
                    Console.WriteLine("主键：{0}，姓名：{1}", item.Id, item.Name);
                }

                //删除表中所有数据
                Console.WriteLine("删除表中所有数据");
                db.Database.ExecuteSqlRaw("DELETE FROM Students");

                //显示删除手表里的记录数量
                Console.WriteLine("数据库中共有{0}条数据", db.Students.Count());
            }
        }
    }
}

3、添加模型类Student.cs

我这里习惯新建一个Models目录后，将所有模型文件放到该目录下。

using System.ComponentModel.DataAnnotations;

namespace SQLite.Test.Models
{
    public class Student
    {
        [Key]//主键标识
        [MaxLength(15)]
        public Guid Id { get; set; }                //主键
        [MaxLength(10)]
        public string Name { get; set; }            //姓名
        [MaxLength(4)]
        public int Age { get; set; }             //年龄
        [MaxLength(4)]
        public string Sex { get; set; }             //性别
        [MaxLength]
        public string Description { get; set; }     //描述
    }
}

4、新建目录Database，在目录下新建类文件AppDbContext.cs

using System.Reflection;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using SQLite.Test.Models;

namespace SQLite.Test.Database
{
    public class AppDbContext : DbContext
    {
        //默认数据库名称
        public const string DEFAULTDBFILE = "Data.db";
        //默认密码
        private const string DEFAULTPASSWORD = @"k2G&LqW/WX0F%6::*%PY";


        public DbSet<Student> Students { get; set; }


        private readonly string _dbFileName = DEFAULTDBFILE;
        private readonly string _password = DEFAULTPASSWORD;
        private SqliteConnection _connection;

        public AppDbContext() { }

        public AppDbContext(string dbFileName, string password)
        {
            if (!string.IsNullOrEmpty(dbFileName)) _dbFileName = dbFileName;
            if (!string.IsNullOrEmpty(password)) _password = password;
        }

        public AppDbContext(SqliteConnection sqliteConnection, string password)
        {
            if (!string.IsNullOrEmpty(sqliteConnection?.DataSource)) _dbFileName = sqliteConnection.DataSource;
            _connection = sqliteConnection;
            if (!string.IsNullOrEmpty(password)) _password = password;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            _connection ??= InitializeSQLiteConnection(_dbFileName, _password);
            optionsBuilder.UseSqlite(_connection);
        }


        private static SqliteConnection InitializeSQLiteConnection(string databaseFile, string password)
        {
            var connectionString = new SqliteConnectionStringBuilder
            {
                DataSource = databaseFile,
                Password = password
            };
            return new SqliteConnection(connectionString.ToString());
        }


    }
}

5、测试结果上图：