How to connect to a SQLite database-iaspnetcore.com

A Beginner's Guide to Using SQLite in a C# Application

There are several C# wrappers around the native SQLite engine that .NET developers can use.

SQLite 安装
- 在 Windows 上安装 SQLite
- 在 Linux 上安装 SQLite
step 1.Download the SQLite library
step 2. using Microsoft.Data.Sqlite
SQLite-net
SQLite Show Tables: Listing All Tables in a Database

SQLite 安装

https://www.runoob.com/sqlite/sqlite-installation.html

在 Windows 上安装 SQLite

https://www.sqlite.org/download.html

下载 sqlite-tools-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。
创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。
添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果。
C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

在 Linux 上安装 SQLite

目前，几乎所有版本的 Linux 操作系统都附带 SQLite。所以，只要使用下面的命令来检查您的机器上是否已经安装了 SQLite。

$ sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

step 1.Download the SQLite library

SQLite: Download the SQLite library for your project. You can obtain the SQLite library from the SQLite official website.https://www.sqlite.org/download.html

A new SQLite database (LocalDatabase.db) is created in the project root folder the first time the API is started.

step 2. using Microsoft.Data.Sqlite

Microsoft.Data.Sqlite is a lightweight ADO.NET provider for SQLite. The Entity Framework Core provider for SQLite is built on top of this library. However, it can also be used independently or with other data access libraries.

dotnet add package Microsoft.Data.Sqlite

using (var connection = new SqliteConnection("Data Source=hello.db"))
{
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText =
    @"
        SELECT name
        FROM user
        WHERE id = $id
    ";
    command.Parameters.AddWithValue("$id", id);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(0);

            Console.WriteLine($"Hello, {name}!");
        }
    }
}

https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=netcore-cli

SQLite-net

Many .NET developers use a popular C# wrapper called SQLite-net.

SQLite-net is an object-relational mapper. It helps simplify the process of defining database schemas by enabling you to use the models that are defined in your projects to serve as the schema.

Manage NuGet Packages

sqlite-net-pcl

using SQLite;
using People.Models;
...

private SQLiteConnection conn;
...
private Init()
{
   if (conn != null)
      return;

   conn = new SQLiteConnection(dbPath);
   conn.CreateTable<Person>();
}

https://docs.microsoft.com/en-us/learn/dotnet-maui/store-local-data/3-store-data-locally-with-sqlite

SQLite Show Tables: Listing All Tables in a Database

SELECT name FROM sqlite_schema 
WHERE type = 'table' 
AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

SQLite

SQLite是个开源的文件数据库。文件数据库是很牛逼的东西，比如早年的微软Access就撑起了中国互联网半边天。现在我们手机上的APP，如果需要在手机本地存数据，想搞个数据库的话，一般来说都是用这个SQLite。

比如说我们非常熟悉的微信，它背后的存储就是用了SQLite这个开源数据库。用SQLLite的APP非常多。

https://www.sqlite.org/index.html

Useful links