SQLite 試用
SQLite.v3 with Dapper 試用筆記。
引言
關鍵知識
連線字串
開發環境
安裝套件
Datatypes In SQLite
SQLite data type 很特別
關鍵程式碼紀錄
GUI 管理介面
結論
參考資料
Last updated
SQLite.v3 with Dapper 試用筆記。
Last updated
Data Source=./Database.sqlite;Version=3;Foreign Keys=True;Journal Mode=WAL;Dapper Version="2.1.28" ------ Dapper 模組
System.Data.SQLite.Core Version="1.0.118" ------ SQLite.v3.42.0 引擎。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)
);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;
}