SQLite 試用

SQLite.v3 with Dapper 試用筆記。

引言

SQLite 終於找到機會試用看看了。試用版本為 3.42.0。

關鍵知識

SQLite 是一個輕量級的關聯式資料庫管理系統。一開始只是商業授權資料庫的替代品沒想來越來越受歡迎。已是小型資料庫應用程式的最佳選擇。

預設不啟動交易；
預設不啟動 Foreign Keys 檢驗；
預設 Query 都是 NO LOCK；
預設版本為第 2 版。

在 .NET 環境想要存取 SQLite 有以下方法：

Dapper + System.Data.SQLite.Core
Entity Framework Core
SQLite -.NET
ADO.NET

本試用只打算透過 Dapper 存取SQLite。

連線字串

SQLite 連線字串範例：

Data Source=./Database.sqlite;Version=3;Foreign Keys=True;Journal Mode=WAL;

參數說明：

Data Source=./Database.sqlite; 資料庫檔案位置。
Version=3; // 指定版號。預設為第2版。
Foreign Keys=True; // 啟動 Foreign Keys 檢驗。預設不啟動。
Journal Mode=WAL; // 啟動(可rollback)交易。預設不啟動。

還有其他參數，有用到再說吧。

開發環境

平台: .NET6 IDE: Visual Studio 2022 框架: Blazor Server App

安裝套件

本試用只打算透過 Dapper 存取SQLite。

Dapper Version="2.1.28" ------ Dapper 模組
System.Data.SQLite.Core Version="1.0.118" ------ SQLite.v3.42.0 引擎。

Datatypes In SQLite

SQLite datatypes 經分分合合在第3版只剩下面5種，其他型別則用 Type Affinity 技巧轉換成最親近的型別。

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.

也就是實體上只有5種型別，然實際上有十幾種可以用。

CREATE TABLE ex2(
  a VARCHAR(10),
  b NVARCHAR(15),
  c TEXT,
  d INTEGER,
  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYING CHARACTER (24),
  l NATIONAL VARYING CHARACTER(16)
);

Type Affinity SQL database engines that use rigid typing will usually try to automatically convert values to the appropriate datatype.

Ex: Rigidly-typed database will convert the string '123' into an integer 123 and the integer 456 into a string '456' prior to doing the insert.

SQLite data type 很特別

請直接參考下面附件。

關鍵程式碼紀錄

SQLiteLabService.cs

using Dapper;
using System.Data.SQLite;
using System.Text.Json.Serialization;

public class SQLiteLabService
{
  readonly IConfiguration _config;
  public SQLiteLabService(IConfiguration config)
  {
    _config = config;
  }

  private SQLiteConnection OpenConnection()
  {
    var conn = new SQLiteConnection(_config.GetConnectionString("Default"));
    conn.Open();
    return conn;
  }

  public int CreateTableWhenNotExists()
  {
    const string create_table_cmd = @"
CREATE TABLE IF NOT EXISTS Player (
    Sn INTEGER,
    IdName VARCHAR(32),
    RegDate DATETIME,
    Score INTEGER,
    BinData BLOB,
    PRIMARY KEY(Sn AUTOINCREMENT)
); ";

    using var conn = OpenConnection();
    int ret = conn.Execute(create_table_cmd);
    return ret;
  }

  public List<Player> QueryDataList()
  {
    using var conn = OpenConnection();
    var dataList = conn.Query<Player>(@"SELECT * FROM Player ").AsList();
    return dataList;
  }

  public Player InsertData()
  {
    // var newItem = new Player(-1L, $"名稱{DateTime.Now:ss}", DateTime.Now, DateTime.Now.Millisecond, Guid.NewGuid().ToByteArray());
    var newItem = new Player
    {
      IdName = $"名稱{DateTime.Now:ss}",
      RegDate = DateTime.Now,
      Score = DateTime.Now.Millisecond,
      BinData = Guid.NewGuid().ToByteArray()
    };

    using var conn = OpenConnection();
    using var txn = conn.BeginTransaction();

    var insertCmd = "INSERT INTO Player (IdName,RegDate,Score,BinData) VALUES (@IdName, @RegDate, @Score, @BinData)";
    conn.Execute(insertCmd, newItem, txn);
    newItem = newItem with
    {
      Sn = conn.LastInsertRowId
    };

    txn.Commit();
    return newItem;
  }

  public int DeleteFirstRow()
  {
    using var conn = OpenConnection();
    using var txn = conn.BeginTransaction();

    // select TOP 1;
    string queryCmd = @"SELECT * FROM Player LIMIT 1 ";
    var targetItem = conn.QueryFirstOrDefault<Player>(queryCmd, transaction: txn);
    if (targetItem == null)
      return 0;

    string deleteCmd = @"DELETE FROM Player WHERE Sn = @Sn ";
    int ret = conn.Execute(deleteCmd, new { Sn = targetItem.Sn }, transaction: txn);

    txn.Commit();
    return ret;
  }

  public int UpdateAllRows()
  {
    using var conn = OpenConnection();
    using var txn = conn.BeginTransaction();

    // select TOP 1;
    string updateCmd = @"UPDATE Player SET RegDate = @RegDate ";
    int ret = conn.Execute(updateCmd, new { RegDate = DateTime.Now }, transaction: txn);

    txn.Commit();
    return ret;
  }
}

public record Player
{
  public long Sn { get; set; }
  public string IdName { get; set; } = default!;
  public DateTime RegDate { get; set; }
  public int Score { get; set; }
  public byte[] BinData { get; set; } = default!;

  [JsonIgnore]
  public Guid? BinDataGuid => BinData != null ? new Guid(BinData) : null;
}