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