ASP.NET 整合 SQLite 完整實戰：ADO.NET Provider 安裝、連線字串、CRUD 操作與參數化查詢

環境準備：安裝 ADO.NET Provider

1. 下載 System.Data.SQLite (ADO.NET 2.0/4.x) 官方安裝包：
   https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
2. 執行安裝程式，勾選「Install into GAC」與「Register for Entity Framework」
3. 專案右鍵 → 「管理 NuGet 套件」 → 搜尋 System.Data.SQLite → 安裝 System.Data.SQLite.Core (含原生二進位) 或 System.Data.SQLite (完整包)

連線字串配置

<connectionStrings>
  <!-- 檔案型資料庫，App_Data 目錄 -->
  <add name="SQLiteConn" 
       connectionString="Data Source=|DataDirectory|\AppData.db;Version=3;Foreign Keys=True;" 
       providerName="System.Data.SQLite" />
  
  <!-- 記憶體資料庫 (測試/快取用) -->
  <add name="SQLiteMemory" 
       connectionString="Data Source=:memory:;Version=3;" 
       providerName="System.Data.SQLite" />
</connectionStrings>

完整 CRUD 範例

using System;
using System.Data;
using System.Data.SQLite;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    private string ConnStr => ConfigurationManager.ConnectionStrings["SQLiteConn"].ConnectionString;

    protected void btnTest_Click(object sender, EventArgs e)
    {
        // 1. 建立資料庫檔案 (若不存在)
        string dbPath = Server.MapPath("~/App_Data/AppData.db");
        if (!File.Exists(dbPath))
        {
            SQLiteConnection.CreateFile(dbPath);
            InitializeSchema();
        }

        using (var conn = new SQLiteConnection(ConnStr))
        {
            conn.Open();
            Response.Write("✅ 開啟資料庫成功<br/>");

            // 2. 建立資料表
            ExecuteNonQuery(conn, @"
                CREATE TABLE IF NOT EXISTS Users (
                    UserID INTEGER PRIMARY KEY AUTOINCREMENT,
                    UserName TEXT NOT NULL,
                    UserPassword TEXT NOT NULL,
                    CreatedAt TEXT DEFAULT (datetime('now'))
                )");
            Response.Write("✅ 資料表就緒<br/>");

            // 3. 批次插入 (參數化)
            using (var txn = conn.BeginTransaction())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO Users (UserName, UserPassword) VALUES (@name, @pwd)";
                var pName = cmd.CreateParameter(); pName.ParameterName = "@name";
                var pPwd = cmd.CreateParameter(); pPwd.ParameterName = "@pwd";
                cmd.Parameters.Add(pName); cmd.Parameters.Add(pPwd);

                for (int i = 1; i <= 100; i++)
                {
                    pName.Value = $"TestUser_{i}";
                    pPwd.Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    cmd.ExecuteNonQuery();
                }
                txn.Commit();
            }
            Response.Write("✅ 批次插入 100 筆完成<br/>");

            // 4. 單筆查詢 (ExecuteScalar)
            string name = ExecuteScalar(conn, "SELECT UserName FROM Users WHERE UserID = 1")?.ToString();
            Response.Write($"✅ 單值查詢: {name}<br/>");

            // 5. 多筆查詢 (DataReader)
            Response.Write("<br/>📋 所有使用者:<br/>");
            using (var cmd = new SQLiteCommand("SELECT * FROM Users", conn))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Response.Write($"ID: {reader["UserID"]}, Name: {reader["UserName"]}, Pwd: {reader["UserPassword"]}<br/>");
                }
            }

            // 6. 更新
            ExecuteNonQuery(conn, "UPDATE Users SET UserPassword = 'updated_pwd' WHERE UserID = 1");
            Response.Write("✅ 更新完成<br/>");

            // 7. 刪除
            ExecuteNonQuery(conn, "DELETE FROM Users WHERE UserID > 50");
            Response.Write("✅ 刪除 ID>50 完成<br/>");
        }
    }

    private void InitializeSchema()
    {
        using (var conn = new SQLiteConnection(ConnStr))
        {
            conn.Open();
            ExecuteNonQuery(conn, @"
                CREATE TABLE IF NOT EXISTS Users (
                    UserID INTEGER PRIMARY KEY AUTOINCREMENT,
                    UserName TEXT NOT NULL UNIQUE,
                    UserPassword TEXT NOT NULL,
                    CreatedAt TEXT DEFAULT (datetime('now'))
                )");
        }
    }

    private int ExecuteNonQuery(SQLiteConnection conn, string sql, params SQLiteParameter[] parameters)
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = sql;
            if (parameters != null) cmd.Parameters.AddRange(parameters);
            return cmd.ExecuteNonQuery();
        }
    }

    private object ExecuteScalar(SQLiteConnection conn, string sql, params SQLiteParameter[] parameters)
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = sql;
            if (parameters != null) cmd.Parameters.AddRange(parameters);
            return cmd.ExecuteScalar();
        }
    }
}

關鍵 API 對照表

SQLite 特有功能

1. JSON1 擴充 (內建)

// 儲存 JSON
cmd.CommandText = "INSERT INTO Logs (Data) VALUES (json(@json))";
cmd.Parameters.AddWithValue("@json", "{\"user\":123,\"action\":\"login\"}");

// 查詢 JSON 欄位
cmd.CommandText = "SELECT json_extract(Data, '$.user') FROM Logs";

2. 全文搜尋 FTS5

CREATE VIRTUAL TABLE Articles USING fts5(title, content);
INSERT INTO Articles VALUES ('SQLite 教學', '內容...');
SELECT * FROM Articles WHERE Articles MATCH 'SQLite';

3. 日期時間處理

// SQLite 無 DateTime 型別，儲存為 TEXT (ISO8601) / REAL (Julian Day) / INTEGER (Unix Timestamp)
INSERT INTO Events (EventTime) VALUES (datetime('now'));           // 當下 UTC
INSERT INTO Events (EventTime) VALUES (strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime')); // 本地時間
SELECT date(EventTime) FROM Events;                                // 只取日期
SELECT * FROM Events WHERE EventTime > datetime('now', '-7 days'); // 最近 7 天

效能優化清單

• WAL 模式：PRAGMA journal_mode=WAL; (讀寫並行、不阻塞讀取)
• 交易批次：單次交易提交百/千筆 INSERT
• 索引建立：CREATE INDEX idx_users_name ON Users(UserName);
• 連線共用：ASP.NET 應用使用連線池，避免頻繁 Open/Close
• 參數重用：循環插入時建立一次 Command/Parameters，循環內只賦值

常見坑點

總結

SQLite 在 ASP.NET 中是輕量級、零設定、單檔案的優秀選擇，適合：中小型網站、嵌入式裝置、單元測試資料庫、本地快取、桌面應用配套資料。關鍵成功因素：參數化查詢防注入、交易批次寫入、WAL 模式並行、正確日期時間格式、原生 DLL 架構匹配。配合 System.Data.SQLite.Core NuGet 套件，部署極簡單 (僅需複製資料庫檔案)。

1. 上一篇文章： C# 參數修飾詞完整指南：ref、out、params 差異、多載規則與記憶體語意
2. 下一篇文章： ASP.NET HttpModule 實作 SQL 注入防護與動態 JS/CSS 載入：雙重實用模組範例

ASP.NET 基礎

RabbitMQ .NET 客戶端深度實戰：生產者/消費者模式與可靠性保障

環境搭建與準備 下載安裝 Erlang OTP 19.0

ASP.NET MVC 專案整合 Spring.NET 實現 IOC 依賴注入：從控制台到 Web 完整遷移

在上一篇分層架構中，Controller 雖依賴 IBLL

CSS 濾鏡實現圖片傾斜效果（僅限 IE 瀏覽器）

IE 專屬 Matrix 濾鏡 早期 Internet Ex

ASP.NET MVC 解析淘寶網頁亂碼問題：GBK 編碼與 UTF-8 環境的正確處理姿勢

問題情境 在 ASP.NET MVC 專案中使用 HttpC