最近想在 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=="
  }
]

補充幾則注意事項:

  1. 與 SQL、ORACLE 不同,SQLite 不需預先安裝伺服器及建立資料庫,不少應用情境是在程式執行時從無到有產生 .sqlite 檔案,建立資料表後寫入資料。所以我也體驗了一下這種玩法:先檢查 .sqlite 檔案是否存在,若不存在代表尚未初始化。建立 SQLiteConnection 時 SQLite 會自動建立 Test.sqlite 檔案,接著執行 CREATE TABLE 建立資料表。
  2. SQLite 的資料型別與 SQL 語法跟 MSSQL、ORACLE 有些出入,需花點時間熟悉。但關聯式資料庫的觀念大同小異,若已有相關開發經驗不難上手。(我在 tutorialspoint 找到蠻淺顯完整的教學,比官方文件易讀,是不錯的入門教材)
  3. 程式測試了基本的 INSERT、SELECT、Primary Key 重複檢核、int/string/DateTime/byte[] 型別的資料庫對應,全部順利過關。
  4. 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

Post a comment