天天看點
傳回

C#中SQLite的使用及工具類

SQLite是一款輕型的資料庫，一個資料庫就是一個檔案。

SQLite 資料類型是一個用來指定任何對象的資料類型的屬性。SQLite 中的每一列，每個變量和表達式都有相關的資料類型。

您可以在建立表的同時使用這些資料類型。SQLite 使用一個更普遍的動态類型系統。在 SQLite 中，值的資料類型與值本身是相關的，而不是與它的容器相關。

SQLite資料與常見的MySQL、SQL等的資料庫不一樣，它是動态類型資料庫，每個值在資料庫占的存儲空間根據值的大小确定，使用時需要注意資料類型的問題。

目錄

  • SQLite簡介
    • 存儲類
    • 親和類型
  • 引用System.Data.SQLite.dll
    • 軟體包分類
    • 使用本機庫預加載
    • 常用部署包
  • 工具類
  • 參考資料

SQLite簡介

SQLite是一款輕型的資料庫，一個資料庫就是一個檔案，詳細介紹參考官網：https://www.sqlite.org/index.html

SQLite 資料類型是一個用來指定任何對象的資料類型的屬性。SQLite 中的每一列，每個變量和表達式都有相關的資料類型。

您可以在建立表的同時使用這些資料類型。SQLite 使用一個更普遍的動态類型系統。在 SQLite 中，值的資料類型與值本身是相關的，而不是與它的容器相關。

SQLite資料與常見的MySQL、SQL等的資料庫不一樣，它是動态類型資料庫，每個值在資料庫占的存儲空間根據值的大小确定，使用時需要注意資料類型的問題。

存儲類

每個存儲在 SQLite 資料庫中的值都具有以下存儲類之一：

存儲類 描述
NULL 值是一個 NULL 值。
INTEGER 值是一個帶符号的整數，根據值的大小存儲在 1、2、3、4、6 或 8 位元組中。
REAL 值是一個浮點值，存儲為 8 位元組的 IEEE 浮點數字。
TEXT 值是一個文本字元串，使用資料庫編碼（UTF-8、UTF-16BE 或 UTF-16LE）存儲。
BLOB 值是一個 blob 資料，完全根據它的輸入存儲。

親和類型

SQLite支援列的親和類型概念，任何列仍然可以存儲任何類型的資料，當資料插入時該字段的資料将會優先采用親和類型作為該值的存儲方式。

建立 SQLite3 表時可使用的各種資料類型名稱及相應的親和類型，如下：

資料類型 親和類型

INT

INTEGER

TINYINT

SMALLINT

MEDIUMINT

BIGINT

UNSIGNED BIG INT

INT2

INT8

 INTEGER：對于親緣類型為INTEGER的字段，其規則等同于NUMERIC，唯一差别是在執行CAST表達式時。

CHARACTER(20)

VARCHAR(255)

VARYING CHARACTER(255)

NCHAR(55)

NATIVE CHARACTER(70)

NVARCHAR(100)

TEXT

CLOB

 TEXT：數值型資料在被插入之前，需要先被轉換為文本格式，之後再插入到目标字段中。

BLOB

no datatype specified

 NONE：不做任何的轉換，直接以該資料所屬的資料類型進行存儲。

REAL

DOUBLE

DOUBLE PRECISION

FLOAT

 REAL：其規則基本等同于NUMERIC，唯一的差别是不會将"30000.0"這樣的文本資料轉換為INTEGER存儲方式。

NUMERIC

DECIMAL(10,5)

BOOLEAN

DATE

DATETIME

NUMERIC

當文本資料被插入到親緣性為NUMERIC的字段中時：

如果轉換操作不會導緻資料資訊丢失以及完全可逆，那麼SQLite就會将該文本資料轉換為INTEGER或REAL類型的資料；

如果轉換失敗，SQLite仍會以TEXT方式存儲該資料。

對于NULL或BLOB類型的新資料，SQLite将不做任何轉換，直接以NULL或BLOB的方式存儲該資料。

注：對于浮點格式的常量文本，如"30000.0"，如果該值可以轉換為INTEGER同時又不會丢失數值資訊，那麼SQLite就會将其轉換為INTEGER的存儲方式。

引用System.Data.SQLite.dll

在C#中使用SQLite資料庫需要引用System.Data.SQLite.dll，下載下傳連結：http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

注：System.Data.SQLite是SQLite的ADO.NET提供程式，兩者是兩個不同的開源項目，現在System.Data.SQLite的開發和維護工作大部分由SQLite開發團隊執行。

System.Data.SQLite的下載下傳頁面選項太多，一般人進來都不清楚要下載下傳那些内容，下面對下載下傳界面中的軟體包做一個簡單介紹。

軟體包分類

下載下傳内容按類型分為安裝包、非靜态連接配接的二進制包和靜态連接配接的二進制包三種，差別如下：

  • 安裝程式包僅用于在開發人員計算機上安裝，然後僅在需要VisualStudio的設計時元件時安裝，不建議安裝在客戶機器上（理論上也可以）。
  • 安裝包會安裝相關的動态庫到系統内，并注冊到GAC（Global Assembly Cache）。
  • 二進制軟體包旨在供開發人員使用，以便獲得開發所需的彙編二進制檔案，并通過XCOPY部署将其應用程式部署到客戶機上。
  • 兩種二進制包的差別在于非托管部分的連接配接方式不同，非靜态連接配接的二進制包在使用時需要VC運作時庫的支援。

注：如果所有目标機器已經安裝了VisualC++運作時，或者可以容易地部署，則應該避免“靜态”包。

每個類型都按.NET版本分成了若幹小組，每個.NET版本又分為32位和64位兩組：

  • 支援的.NET版本有 2.0 SP2 、 3.5 SP2 、4.0 、 4.5 、 4.5.1 、4.6 。
  • 選用32位還是64位是根據使用系統來決定的，如開發時是64位（使用64位dll）系統而釋出後運作在32位（使用32位dll）系統上。

注：雖然.NET高版本相容低版本，但強烈建議選擇與目标.NET Framework版本比對的包。

在每個.NET版本-位數分組中都有2個檔案包，一個帶有“bundle”字樣，另一個沒有：

  • 帶有“bundle”字樣的表示動态庫是按混合模式編譯的，在使用的時候隻需要System.Data.SQLite.dll就可以了。
  • 不帶“bundle”的則是将非托管部分和托管部分分别編譯，System.Data.SQLite.dll不能獨立使用，還需要有SQLite.Interop.dll才能使用。

注：除非認為絕對必要，否則應避免使用“bundle”包。

根據上面的介紹，如果開發機器和客戶機器可能具有不同的處理器體系結構，則可能需要一個以上的二進制程式包。

使用本機庫預加載

本機庫預加載功能從1.0.80.0版本開始可用，并且預設情況下已啟用，能夠自動适應目前系統的位數。為了利用此功能，必須将單獨的托管程式集和互操作程式集與XCOPY部署一起使用（混合模式程式集、安裝軟體包部署不支援此功能）。

使用本機庫預加載功能時，應用程式部署看起來如下（ bin 表示将在目标計算機上部署應用程式二進制檔案的目錄）：

  • bin \ App.exe（可選，僅受管應用程式可執行程式集）
  • bin \ App.dll（可選，僅托管應用程式庫程式集）
  • bin \ System.Data.SQLite.dll（必需，僅受管核心程式集）
  • bin \ System.Data.SQLite.Linq.dll（可選，僅托管LINQ程式集）
  • bin \ System.Data.SQLite.EF6.dll（可選，僅托管EF6程式集）
  • bin \ x86 \ SQLite.Interop.dll（必需，x86本機互操作程式集）
  • bin \ x64 \ SQLite.Interop.dll（必需，x64本機互操作程式集）

啟用本機庫預加載功能并顯示上面的應用程式部署後，System.Data.SQLite僅限托管程式集将嘗試自動檢測目前程序的處理器體系結構并預加載适當的本機庫，此時不用考慮客戶機器的是64位還是32位。

常用部署包

我把.NET的4.0 、4.5版本對應的軟體包按本機庫預加載功能的要求重新組裝，使用時直接複制到Debug目錄下即可：

  • sqlite-netFx45-binary-Win32-x64-2012-1.0.113.0.zip 提取碼: r4yy
  • sqlite-netFx40-binary-Win32-x64-2010-1.0.113.0.zip 提取碼: j88h
  • sqlite-netFx45-static-binary-Win32-x64-2012-1.0.113.0.zip 提取碼: 33kp
  • sqlite-netFx40-static-binary-Win32-x64-2010-1.0.113.0.zip 提取碼: iqr2

注：官方建議不使用靜态的二進制包，我個人則喜歡用靜态的二進制包，這樣就不用考慮客戶機器上是否安裝有對應的VC運作時庫了。

工具類

工具類大部分内容來自c# Sqlite幫助類，考慮到SQLite是一個資料庫一個檔案、一個項目可能需要多個資料庫，我将工具類改為通過對象執行個體操作資料庫并提供一個靜态的對象執行個體字典。

工具類代碼如下：

public class SQLiteHelper
{
    /// <summary>
    /// 資料庫清單
    /// </summary>
    public static Dictionary<string, SQLiteHelper> DataBaceList = new Dictionary<string, SQLiteHelper>();

    /// <summary>
    /// 構造函數
    /// </summary>
    /// <param name="filename">資料庫檔案名</param>
    public SQLiteHelper(string filename=null) 
    {
        DataSource = filename;                     
    }

    /// <summary>
    /// 資料庫位址
    /// </summary>
    public string DataSource { get; set; }        

    /// <summary>
    /// 建立資料庫，如果資料庫檔案存在則忽略此操作
    /// </summary>
    public void CreateDataBase() 
    {
        string path = Path.GetDirectoryName(DataSource);
        if ((!string.IsNullOrWhiteSpace(path)) && (!Directory.Exists(path))) Directory.CreateDirectory(path);
        if (!File.Exists(DataSource)) SQLiteConnection.CreateFile(DataSource);          
    }



    /// <summary>
    /// 獲得連接配接對象
    /// </summary>
    /// <returns>SQLiteConnection</returns>       
    public SQLiteConnection GetSQLiteConnection()
    {
        string connStr =string.Format("Data Source={0}", DataSource);            
        var con = new SQLiteConnection(connStr);
        return con;
    }

    /// <summary>
    /// 準備操作指令參數
    /// </summary>
    /// <param name="cmd">SQLiteCommand</param>
    /// <param name="conn">SQLiteConnection</param>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">參數數組</param>
    private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Parameters.Clear();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 30;
        if (data != null && data.Count >= 1)
        {
            foreach (KeyValuePair<String, String> val in data)
            {
                cmd.Parameters.AddWithValue(val.Key, val.Value);
            }
        }
    }

    /// <summary>
    /// 查詢，傳回DataSet
    /// </summary>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">參數數組</param>
    /// <returns>DataSet</returns>
    public DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data = null)
    {
        var ds = new DataSet();
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            var da = new SQLiteDataAdapter(command);
            da.Fill(ds);
        }
        return ds;
    }

    /// <summary>
    /// 查詢，傳回DataTable
    /// </summary>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">參數數組</param>
    /// <returns>DataTable</returns>
    public DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data = null)
    {
        var dt = new DataTable();
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            SQLiteDataReader reader = command.ExecuteReader();
            dt.Load(reader);
        }
        return dt;
    }

    /// <summary>
    /// 傳回一行資料
    /// </summary>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">參數數組</param>
    /// <returns>DataRow</returns>
    public DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data = null)
    {
        DataSet ds = ExecuteDataset(cmdText, data);
        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            return ds.Tables[0].Rows[0];
        return null;
    }

    /// <summary>
    /// 執行資料庫操作
    /// </summary>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">傳入的參數</param>
    /// <returns>傳回受影響的行數</returns>
    public int ExecuteNonQuery(string cmdText, Dictionary<string, string> data=null)
    {
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            return command.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 傳回SqlDataReader對象
    /// </summary>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">傳入的參數</param>
    /// <returns>SQLiteDataReader</returns>
    public SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data = null)
    {
        var command = new SQLiteCommand();
        SQLiteConnection connection = GetSQLiteConnection();
        try
        {
            PrepareCommand(command, connection, cmdText, data);
            SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        catch
        {
            connection.Close();
            command.Dispose();
            throw;
        }
    }

    /// <summary>
    /// 傳回結果集中的第一行第一列，忽略其他行或列
    /// </summary>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="data">傳入的參數</param>
    /// <returns>object</returns>
    public object ExecuteScalar(string cmdText, Dictionary<string, string> data = null)
    {
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, cmdText, data);
            return cmd.ExecuteScalar();
        }
    }

    /// <summary>
    /// 分頁查詢
    /// </summary>
    /// <param name="recordCount">總記錄數</param>
    /// <param name="pageIndex">頁牽引</param>
    /// <param name="pageSize">頁大小</param>
    /// <param name="cmdText">Sql指令文本</param>
    /// <param name="countText">查詢總記錄數的Sql文本</param>
    /// <param name="data">指令參數</param>
    /// <returns>DataSet</returns>
    public DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data = null)
    {
        if (recordCount < 0)
            recordCount = int.Parse(ExecuteScalar(countText, data).ToString());
        var ds = new DataSet();
        using (SQLiteConnection connection = GetSQLiteConnection())
        {
            var command = new SQLiteCommand();
            PrepareCommand(command, connection, cmdText, data);
            var da = new SQLiteDataAdapter(command);
            da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
        }
        return ds;
    }

    /// <summary>
    /// 重新組織資料庫：VACUUM 将會從頭重新組織資料庫
    /// </summary>
    public void ResetDataBass()
    {
        using (SQLiteConnection conn = GetSQLiteConnection())
        {
            var cmd = new SQLiteCommand();

            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Parameters.Clear();
            cmd.Connection = conn;
            cmd.CommandText = "vacuum";
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 30;
            cmd.ExecuteNonQuery();
        }
    }

}

工具類使用方法如下：

static void Main(string[] args)
{
    SQLiteHelper testDb = new SQLiteHelper("test.db");
    SQLiteHelper.DataBaceList.Add("TEST", testDb);

    //建庫
    testDb.CreateDataBase();

    //建表            
    StringBuilder sbr = new StringBuilder();
    sbr.AppendLine("CREATE TABLE IF NOT EXISTS `test_table`(");
    sbr.AppendLine("`id` INTEGER PRIMARY KEY AUTOINCREMENT,");//自增id主鍵
    sbr.AppendLine("`name` VARCHAR(100) NOT NULL,");
    sbr.AppendLine("`password` VARCHAR(40) NOT NULL,");
    sbr.AppendLine("`create_time` datetime DEFAULT CURRENT_TIMESTAMP,");
    sbr.AppendLine("`update_time` datetime DEFAULT CURRENT_TIMESTAMP );");            
    sbr.AppendLine();

    sbr.AppendLine("CREATE TRIGGER  IF NOT EXISTS `trigger_test_table_update_time` ");//觸發器-自動更新update_time
    sbr.AppendLine("AFTER UPDATE ON `test_table` ");
    sbr.AppendLine("FOR EACH ROW ");
    sbr.AppendLine("BEGIN ");
    sbr.AppendLine("UPDATE `test_table` SET `update_time` = CURRENT_TIMESTAMP WHERE id = old.id; ");
    sbr.AppendLine("END;");

    string cmdText = sbr.ToString();
    int val = testDb.ExecuteNonQuery(cmdText);            
    Console.WriteLine("影響行數：" + val);

    //增
    sbr.Clear();
    sbr.Append("INSERT INTO test_table (name,password) VALUES ");
    sbr.Append("(11,111), ");
    sbr.Append("(12,222); ");
    cmdText = sbr.ToString();
    val = testDb.ExecuteNonQuery(cmdText);
    Console.WriteLine("影響行數：" + val);

    //删
    sbr.Clear();
    sbr.Append("DELETE FROM test_table ");
    sbr.Append("WHERE id=1;");
    cmdText = sbr.ToString();
    val = testDb.ExecuteNonQuery(cmdText);
    Console.WriteLine("影響行數：" + val);

    //改
    sbr.Clear();
    sbr.Append("UPDATE test_table SET ");
    sbr.Append("name='13', ");
    sbr.Append("password='333' ");
    sbr.Append("WHERE id=@id;");
    cmdText = sbr.ToString();
    Dictionary<string, string> data = new Dictionary<string, string>();
    data.Add("@id", "2");           
    val = testDb.ExecuteNonQuery(cmdText, data);
    Console.WriteLine("影響行數：" + val);

    //查
    sbr.Clear();
    sbr.Append("SELECT name,password FROM test_table ");
    sbr.Append("WHERE id=@id;");
    cmdText = sbr.ToString();
    DataTable dt = testDb.ExecuteDataTable(cmdText, data);
    Console.WriteLine("結果行數：" + dt.Rows.Count);                                   

    //删除表
    sbr.Clear();
    sbr.Append("DROP TABLE test_table;");
    cmdText = sbr.ToString();
    val = SQLiteHelper.DataBaceList["TEST"].ExecuteNonQuery(cmdText);
    Console.WriteLine("影響行數：" + val);

    //重組資料庫
    SQLiteHelper.DataBaceList["TEST"].ResetDataBass();

    Console.ReadKey();
}

參考資料

  • c# Sqlite幫助類
  • 讓使用SQLite的.NET應用自适應32位/64位系統
  • System.Data.SQLite
  • sqlite3自增key設定(建立自增字段)
  • Sqlite如何自動更新時間字段
資料庫資料庫
上一篇: 【LeetCode】053.最大子序和 (Maximum Subarray) 動态規劃/分治法
下一篇: The maximum-subarray problem

繼續閱讀