Popular blog tags

How to connect to a SQLite database

Published

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

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