SQLite 資料庫 C# 程式範例-使用 Dapper
5 | 50,583 |
最近想在 Coding4Fun 專案使用資料庫管理英文單字及測驗結果。情境本身有些小尷尬,評估規模與複雜度,若用資料物件配合序列化存檔實作有點吃力,搬出 SQL Express 又顯殺雞用牛刀,於是我想起免安裝又超級輕巧的 In-Process 資料庫首選-SQLite!
完全沒有 SQLite 使用經驗,開啟 Visual Studio 寫個極簡範例當入門吧!
資料物件故意安排了 string、DateTime、int、byte[] 四種屬性,想測驗 SQLite 是否能滿足不同資料型別需求。
class Player
{
public string Id { get; set; }
public string Name { get; set; }
public DateTime RegDate { get; set; }
public int Score { get; set; }
public byte[] BinData { get; set; }
public Player(string id, string name, DateTime regDate, int score)
{
Id = id;
Name = name;
RegDate = regDate;
Score = score;
BinData = Guid.NewGuid().ToByteArray().Take(4).ToArray();
}
}
static Player[] TestData = new Player[]
{
new Player("P01", "Jeffrey", DateTime.Now, 32767),
new Player("P02", "Darkthread", DateTime.Now, 65535),
};
這年頭只要該應用夠熱門夠普及,通常不需要特別去搜尋元件或程式庫,往往在 NuGet 敲敲關鍵字試手氣就搞定,SQLite 就是個成功案例。
在 NuGet 搜尋 sqlite,前幾名全是我們要的結果-由 SQLite Development Team 提供的官方元件。System.Data.SQLite 是完整版,支援 LINQ、EF;既然 SQLite 標榜輕薄短小,資料庫存取方式我選擇用 Dapper,一路輕巧簡便到底。如果要走 Dapper,安裝 System.Data.SQLite.Core 就夠了:
SQLite 建立連線,執行指令的做法也是依循 ADO.NET 標準,跟 SQL 或 ORACLE 沒什麼兩樣,只是將 SqlConnection、OracleConnection 換成 SQLiteConnection,cn.Query、cn.Execute 等細節都一樣。
完整程式範例如下:
static void Main(string[] args)
{
InitSQLiteDb();
TestInsert();
TestSelect();
Console.Read();
}
static string dbPath = @".\Test.sqlite";
static string cnStr = "data source=" + dbPath;
static void InitSQLiteDb()
{
if (File.Exists(dbPath)) return;
using (var cn = new SQLiteConnection(cnStr))
{
cn.Execute(@"
CREATE TABLE Player (
Id VARCHAR(16),
Name VARCHAR(32),
RegDate DATETIME,
Score INTEGER,
BinData BLOB,
CONSTRAINT Player_PK PRIMARY KEY (Id)
)");
}
}
static void TestInsert()
{
using (var cn = new SQLiteConnection(cnStr))
{
cn.Execute("DELETE FROM Player");
//參數是用@paramName
var insertScript =
"INSERT INTO Player VALUES (@Id, @Name, @RegDate, @Score, @BinData)";
cn.Execute(insertScript, TestData);
//測試Primary Key
try
{
//故意塞入錯誤資料
cn.Execute(insertScript, TestData[0]);
throw new ApplicationException("失敗:未阻止資料重複");
}
catch (Exception ex)
{
Console.WriteLine($"測試成功:{ex.Message}");
}
}
}
static void TestSelect()
{
using (var cn = new SQLiteConnection(cnStr))
{
var list = cn.Query("SELECT * FROM Player");
Console.WriteLine(
JsonConvert.SerializeObject(list, Formatting.Indented));
}
}
執行結果如下:
測試成功:constraint failed
UNIQUE constraint failed: Player.Id
[
{
"Id": "P01",
"Name": "Jeffrey",
"RegDate": "2017-06-04T21:03:07.2899539",
"Score": 32767,
"BinData": "ZIYfzw=="
},
{
"Id": "P02",
"Name": "Darkthread",
"RegDate": "2017-06-04T21:03:07.305952",
"Score": 65535,
"BinData": "PeOUJA=="
}
]
補充幾則注意事項:
- 與 SQL、ORACLE 不同,SQLite 不需預先安裝伺服器及建立資料庫,不少應用情境是在程式執行時從無到有產生 .sqlite 檔案,建立資料表後寫入資料。所以我也體驗了一下這種玩法:先檢查 .sqlite 檔案是否存在,若不存在代表尚未初始化。建立 SQLiteConnection 時 SQLite 會自動建立 Test.sqlite 檔案,接著執行 CREATE TABLE 建立資料表。
- SQLite 的資料型別與 SQL 語法跟 MSSQL、ORACLE 有些出入,需花點時間熟悉。但關聯式資料庫的觀念大同小異,若已有相關開發經驗不難上手。(我在 tutorialspoint 找到蠻淺顯完整的教學,比官方文件易讀,是不錯的入門教材)
- 程式測試了基本的 INSERT、SELECT、Primary Key 重複檢核、int/string/DateTime/byte[] 型別的資料庫對應,全部順利過關。
- SQLite 參數比照 MSSQL 以 @paramName 表示(ORACLE 則用 :paramName)
最後強力推薦好用工具一枚-SQLite 界的 SSMS(或是 PLSQL Developer、Toad)-Firefox SQLite Manager Add-On,對於初接觸 SQLite 的新手來說,在不熟 SQLite 語法的情況下,能有 GUI 工具輔助建立資料庫、管理資料表、Index,測試 SQL 指令,猶如身處茫茫大海發現燈塔,自此不再徬徨無措,作者功德無量啊~ (關於更詳細的 SQLite Manager 的介紹,可參考梅干桑的文章)
祝大家 SQLite 輕鬆上手~
Comments
# by Ze-Yu
我也適用 using System.Data.SQLite。想請問為何我沒有 .excute 這個方法??
# by Jeffrey
to Ze-Yu, Execute() 是 Dapper 提供的擴充方法,需使用 NuGet 安裝 Dapper 並 using Dapper; 才會出現。
# by 浮雲
請問黑暗大,Firefox SQLite Manager Add-On是不是下架了,Add-On目前能找到的是另外一套 XD
# by Jeffrey
to 浮雲,Firefox 57+ 改版後,原來的 SQLite Manager 已不相容,目前它只能在 Firefox Extended Support Release (ESR) 版本上使用。參考: https://addons.thunderbird.net/en-us/firefox/addon/sqlite-manager/ 現在找到的另一套 SQLite Manager webext,GUI 設計上比較弱,沒有舊版順手。
# by DD
感謝樓上 Firefox 57+ 之後一堆不能用,讓我在也沒下再安裝過firefox