.net 4.6.1 使用System.Data.SQLite或者Microsoft.Data.Sqlite操作sqlite数据库问题记录

参考内容

C#操作SQLite数据库
 Microsoft.Data.Sqlite 概述
 与 System.Data.SQLite 的比较 - Microsoft.Data.Sqlite
自定义 SQLite 版本 - Microsoft.Data.Sqlite
How do I call SQLitePCL.Batteries.Init().?-stackoverflow.com
C# sqlite Unable to load sqlite3.dll-stackoverflow.com

System.Data.SQLite

安装NuGet包

代码

SqliteAdoNetHelper

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Canaan.TestIdea.ProjectA
{
    public class SqliteAdoNetHelper
    {
        public static SqliteAdoNetHelper Instance = new SqliteAdoNetHelper();


        public SqliteAdoNetHelper()
        {
        }

        public int ExecuteNonQueryV2(string dbPath, string dbName
            , string sql)
        {
            
            var builder = CreateConnBuilder(dbPath, dbName);

            using (var connection = new SQLiteConnection(builder.ConnectionString))
            {

                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    return command.ExecuteNonQuery();
                }
            }
        }


        private SqlConnectionStringBuilder CreateConnBuilder(string dbPath, string dbName)
        {

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder["Data Source"] = $"{dbPath}\\{dbName}.db";

            return builder;
        }
    }
}

Program

using Canaan.TestIdea.ProjectA;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Canaan.TestIdea.ProjectD
{
    class Program
    {
        static void Main(string[] args)
        {
            var path = System.IO.Directory.GetCurrentDirectory();
            var dbName = "temp";
            var sql = "create table  if not exists highscores (name varchar(20), score int)";
            SqliteAdoNetHelper.Instance.ExecuteNonQueryV2(path, dbName, sql);
        }
    }
}

异常

System.DllNotFoundException
HResult=0x80131524
Message=无法加载 DLL“SQLite.Interop.dll”: 找不到指定的模块。 (异常来自 HRESULT:0x8007007E)。
Source=<无法计算异常源>
StackTrace:
<无法计算异常堆栈跟踪>

处理

项目A下面有SQLite.Interop.dll,但项目D下面没有

复制x64文件夹到项目D下面

出现同样的异常，将x86文件夹也复制到项目D下面，或者去掉项目D生成设置下的首选32位解决该问题

Microsoft.Data.Sqlite

安装NuGet包

代码

SqliteAdoNetHelper


using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Canaan.TestIdea.ProjectI
{
    public class SqliteAdoNetHelper
    {
        public static SqliteAdoNetHelper Instance = new SqliteAdoNetHelper();
      
        public int ExecuteNonQuery(string dbPath, string dbName
            , string sql)
        {
           
            var builder = CreateConnBuilder(dbPath, dbName);

            using (var connection = new SqliteConnection(builder.ConnectionString))
            {
                
                connection.Open();

                using (SqliteCommand command = new SqliteCommand(sql, connection))
                {
                    return command.ExecuteNonQuery();
                }
            }
        }

       
        private SqlConnectionStringBuilder CreateConnBuilder(string dbPath, string dbName)
        {
            
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder["Data Source"] = $"{dbPath}\\{dbName}.db";

            return builder;
        }
    }
}

Program

using Canaan.TestIdea.ProjectI;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Canaan.TestIdea.ProjectJ
{
    class Program
    {
        static void Main(string[] args)
        {
            var path = System.IO.Directory.GetCurrentDirectory();
            var dbName = "temp";
            var sql = "create table  if not exists highscores (name varchar(20), score int)";
            SqliteAdoNetHelper.Instance.ExecuteNonQuery(path, dbName, sql);
        }
    }
}

异常

System.Exception
HResult=0x80131500
Message=You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init().
Source=SQLitePCLRaw.core
StackTrace:
在 SQLitePCL.raw.get_Provider()
在 SQLitePCL.raw.sqlite3_open_v2(utf8z filename, sqlite3& db, Int32 flags, utf8z vfs)
在 SQLitePCL.raw.sqlite3_open_v2(String filename, sqlite3& db, Int32 flags, String vfs)
在 Microsoft.Data.Sqlite.SqliteConnectionInternal..ctor(SqliteConnectionStringBuilder connectionOptions, SqliteConnectionPool pool)
在 Microsoft.Data.Sqlite.SqliteConnectionPool.GetConnection()
在 Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(SqliteConnection outerConnection)
在 Microsoft.Data.Sqlite.SqliteConnection.Open()
在 Canaan.TestIdea.ProjectI.SqliteAdoNetHelper.ExecuteNonQuery(String dbPath, String dbName, String sql) 在 D:\NetFrameworkProject\Canaan\Canaan.TestIdea.ProjectI\SqliteAdoNetHelper.cs 中: 第 27 行
在 Canaan.TestIdea.ProjectJ.Program.Main(String[] args) 在 D:\NetFrameworkProject\Canaan\Canaan.TestIdea.ProjectJ\Program.cs 中: 第 17 行

处理

安装NuGet包SQLitePCLRaw.provider.sqlite3

修改代码

SqliteAdoNetHelper


using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Canaan.TestIdea.ProjectI
{
    public class SqliteAdoNetHelper
    {
        public static SqliteAdoNetHelper Instance = new SqliteAdoNetHelper();

        public SqliteAdoNetHelper()
        {
            SQLitePCL.raw.SetProvider(new SQLitePCL.SQLite3Provider_sqlite3());
        }

        public int ExecuteNonQuery(string dbPath, string dbName
            , string sql)
        {
           
            var builder = CreateConnBuilder(dbPath, dbName);

            using (var connection = new SqliteConnection(builder.ConnectionString))
            {
                
                connection.Open();

                using (SqliteCommand command = new SqliteCommand(sql, connection))
                {
                    return command.ExecuteNonQuery();
                }
            }
        }

       
        private SqlConnectionStringBuilder CreateConnBuilder(string dbPath, string dbName)
        {
            
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder["Data Source"] = $"{dbPath}\\{dbName}.db";

            return builder;
        }
    }
}

出现新的异常
System.TypeInitializationException
HResult=0x80131534
Message=“Canaan.TestIdea.ProjectI.SqliteAdoNetHelper”的类型初始值设定项引发异常。

内部异常 1:
DllNotFoundException: 无法加载 DLL“sqlite3”: 找不到指定的模块。 (异常来自 HRESULT:0x8007007E)。

找了下，确实没有sqlite3.dll

先去NuGet找下

安装完还是出现同样的异常，这时候项目I和项目J下面都有了sqlite3.dll

把x64文件夹的sqlite3.dll直接放到项目J的下面，出现异常