VB.net - 打开本地 sqlite 表

VB.net - Open local sqlite table

提问by mitchellJ

I'm trying to open a locally stored file (SQLite) just to pull some lines from specific tables. The issue that I'm having is that I keep getting errors that the connection could not be made (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) which makes perfect sense because this is not a sql server it is just information stored in a local database file. So am I going about this the wrong way or is this the only method to process a .db or similar file and I've overlooked something?

我试图打开一个本地存储的文件 (SQLite) 只是为了从特定表中提取一些行。我遇到的问题是，我不断收到无法建立连接的错误（提供者：SQL 网络接口，错误：26 - 错误定位服务器/实例指定），这很有意义，因为这不是 sql 服务器只是存储在本地数据库文件中的信息。那么我是否以错误的方式处理这个问题，或者这是处理 .db 或类似文件的唯一方法，而我忽略了一些东西？

    Dim connection As String = "Data Source=" & Form1.connstring & "; Integrated Security=true"
    Dim SQLConn As New SqlConnection
    Dim SQLcmd As New SqlCommand
    Dim SQLdr As SqlDataReader

    SQLConn.ConnectionString = connection
    SQLConn.Open()

    SQLcmd.Connection = SQLConn
    SQLcmd.CommandText = SQLstr
    SQLdr = SQLcmd.ExecuteReader()
    While SQLdr.Read()
        MessageBox.Show(SQLdr.ToString)
        Form1.ListBox1.Text = SQLdr.ToString
    End While

    SQLdr.Close()
    SQLConn.Close()

Form1.connstring is the full file path. I've tried a few different types of syntax on the "Data Source=" line to try and reference this as a local file but so far I'm lost.

Form1.connstring 是完整的文件路径。我在“Data Source=”行上尝试了几种不同类型的语法，尝试将其作为本地文件引用，但到目前为止我迷路了。

This is for a tool that will have to scan different database files - all locally stored - to farm information

这是一个工具，必须扫描不同的数据库文件 - 所有本地存储 - 以获取信息

EDIT

编辑

Solution:

解决方案：

Imports System.Data.SQLite 'Interop available on NuGet

Public Class _SQLite

Public Shared Sub SQLInq()
    'Value to search as SQL Query - return first match
    Dim SQLstr As String = "Select * FROM FsFileVersion WHERE FileDescription_LTH = 'Entry' LIMIT 1;"

    'Define file to open - .path passed from parent form
    Dim connection As String = "Data Source=" & _Compression.path
    Dim SQLConn As New SQLiteConnection(connection)
    Dim SQLcmd As New SQLiteCommand(SQLConn)
    Dim SQLdr As SQLiteDataReader
    SQLConn.Open()

    SQLcmd.Connection = SQLConn
    SQLcmd.CommandText = SQLstr
    SQLdr = SQLcmd.ExecuteReader()

    'For each query returned
    While SQLdr.Read()
        'Insert into textbox
        Form1.Textbox1.Text = (SQLdr.GetString(SQLdr.GetOrdinal("FileVersion_LTH")))
    End While

    'End the connection
    SQLdr.Close()
    SQLConn.Close()
End Sub

End Class

结束班

采纳答案by Steve

You need to use the classes provided by the specific ADO.NET provider for SQLite.
First, however you need to install the bits required downloading them from the source site.
Be sure to read carefully about the versions for 64bit and 32bit provider and supported framework.

您需要使用 SQLite 的特定 ADO.NET 提供程序提供的类。
但是，首先，您需要安装从源站点下载所需的位。
请务必仔细阅读 64 位和 32 位提供程序的版本和支持的框架。

After that, you need to reference the provider in your project references section and add the appropriate Imports System.Data.SQLitestatement in your source file (replacing the System.Data.SqlClientones)

之后，您需要在您的项目引用部分引用提供程序并Imports System.Data.SQLite在源文件中添加适当的语句（替换System.Data.SqlClient那些）

Finally you need to change your code to use the SQLite classes and fix your connection stringfor SQLite syntax

最后，您需要更改代码以使用 SQLite 类并修复SQLite 语法的连接字符串

Dim connection As String = "Data Source=" & Form1.connstring & ";Version=3"
Dim SQLConn As New SQLiteConnection
Dim SQLcmd As New SQLiteCommand
Dim SQLdr As SQLiteDataReader

SQLConn.ConnectionString = connection
SQLConn.Open()

SQLcmd.Connection = SQLConn
SQLcmd.CommandText = SQLstr
SQLdr = SQLcmd.ExecuteReader()
While SQLdr.Read()
    MessageBox.Show(SQLdr(fieldIndexHere).ToString)
    Form1.ListBox1.Text = SQLdr(fieldIndexHere).ToString
End While

SQLdr.Close()
SQLConn.Close()