EF Core 访问加密Sqlite异常解决方法

时间 2021-01-22 标签 sqlite c#

最近打算升级到EF Core ,数据库是Sqlite,找了一个案例，按步就班:

public class SampleDBContext : DbContext

    {
        private SqliteConnection m_conn = null;
        public SqliteConnection Conn
        {
            get
            {
                if (m_conn == null)
                {
                    SQLitePCL.Batteries.Init();
                    string dbName=Path.Combine(Environment.CurrentDirectory, "SampleDB.db");
                    string connStr=new SqliteConnectionStringBuilder(){
                        DataSource= dbName,
                        Mode=SqliteOpenMode.ReadWriteCreate
                        ,Password ="admin"
                    }.ToString();
                    m_conn = new SqliteConnection(connStr);
                    m_conn.Open();
                }
                return m_conn;
            }
        }
        private static bool _created = false;
        public SampleDBContext()
        {
            if (!_created)
            {
                _created = true;
                Database.EnsureDeleted();
                Database.EnsureCreated();
            }
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
        {
            optionbuilder.UseSqlite(Conn);
        }
    }

运行后出现如下错误:

Method not found: 'Int32 SQLitePCL.ISQLite3Provider.sqlite3_win32_set_directory(Int32, System.String)"

注释掉SQLitePCL.Batteries.Init();问题依旧

着重参考了下列两篇文章

https://www.bricelam.net/2016/06/13/sqlite-encryption.html

https://github.com/ericsink/SQLitePCL.raw/issues/290

折腾了好久，结果问题依旧，放弃吧......

不甘心，过了两天，重拾旧业，仔仔细细，反复查看，发现SQLitePCLRaw.bundle_sqlcipher(1.1.14)默认引用的SQLitePCLRaw.core是2.02版本

而最新的SQLitePCLRaw.core是2.03版本，因此试着对包SQLitePCLRaw.provider.sqlcipher(2.03)及SQLitePCLRaw.core(2.03)进行显式引用，，结果...

是新问题，然后，注释掉Database.EnsureDeleted()，再次编译，运行，终于...

大功告成!

最后把对SQLitePCLRaw.core的显示引用去除，项目文件内容如下:

<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup>

解决新问题：

最后一个问题的出现，分析了一下原因，提示写着，数据库被其他进程占用了，通过跟踪发现其实并不是被其他进程占用了，而是调用Database.EnsureDeleted()时，连接已经打开了，optionbuilder.UseSqlite使用了SqliteConnection，把optionbuilder.UseSqlite的参数改为连接字符串，问题就消失了。

最终程序如下:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.IO;
using System.Linq;

namespace EFCoreTest
{
    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class SampleDBContext : DbContext
    {
        private SqliteConnection m_conn = null;
        public SqliteConnection Conn
        {
            get
            {
                if (m_conn == null)
                {
                    //SQLitePCL.Batteries.Init();
                    //string dbName=Path.Combine(Environment.CurrentDirectory, "SampleDB.db");
                    //string connStr=new SqliteConnectionStringBuilder(){
                    //    DataSource= dbName,
                    //    Mode=SqliteOpenMode.ReadWriteCreate
                    //    ,Password ="admin"
                    //}.ToString();
                    m_conn = new SqliteConnection(ConnStr);
                    m_conn.Open();
                }
                return m_conn;
            }
        }
        private string ConnStr
        {
            get
            {
                string dbName = Path.Combine(Environment.CurrentDirectory, "SampleDB.db");
                return new SqliteConnectionStringBuilder()
                {
                    DataSource = dbName,
                    Mode = SqliteOpenMode.ReadWriteCreate,
                    Password = "admin"
                }.ToString();
            }
        }
        private static bool _created = false;
        public SampleDBContext()
        {
            if (!_created)
            {
                _created = true;
                Database.EnsureDeleted();
                Database.EnsureCreated();
            }
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Category>().ToTable("tb_Category");
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
        {
            optionbuilder.UseSqlite(ConnStr);
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            //Console.WriteLine("Hello World!");

            using (var dbContext = new SampleDBContext())
            {
                dbContext.Set<Category>().Add(new Category { Name = "Wigs" });
                dbContext.Set<Category>().Add(new Category { Name = "Shoes" });
                dbContext.Set<Category>().Add(new Category { Name = "Dresses" });
                dbContext.SaveChanges();

foreach (var cat in dbContext.Set<Category>().ToList()) { Console.WriteLine($"CategoryId= {cat.Id}, CategoryName = {cat.Name}"); } } Console.ReadKey(); } } }