SQLite

官方網站:SQLite

取得資料庫裡的Table名稱

  • 相關欄位:type, name, tbl_name, rootpage, sql
SELECT * FROM sqlite_master Where Type In ('table','view')
  • 取得Table裡的欄位資料
PRAGMA table_info(Table名稱)

建立資料表

CREATE TABLE IF NOT EXISTS TableName (
  ID INTEGER PRIMARY KEY,
  Title TEXT, Subtitle TEXT,
  Content TEXT, 
  Icon TEXT, 
  Date TEXT
)

新增資料但避免新增重複的資料

INSERT INTO Table(ID, ColumnName)
SELECT 5, 'Value'
WHERE NOT EXISTS (SELECT 1 FROM Table WHERE ID = '5');

使用 C# 操作 SQLite

專案加入 System.Data.SQLiteSystem.Data 參考

Install-Package System.Data.SQLite
/// <summary>建立資料庫連線</summary>
/// <param name="database">資料庫名稱</param>
/// <returns></returns>
public SQLiteConnection OpenConnection(string database)
{
    var conntion = new SQLiteConnection()
    {
        ConnectionString = $"Data Source={database};Version=3;New=False;Compress=True;"
    };
    if (conntion.State == ConnectionState.Open) conntion.Close();
    conntion.Open();
    return conntion;
}
/// <summary>建立新資料庫</summary>
/// <param name="database">資料庫名稱</param>
public void CreateDatabase(string database)
{
    var connection = new SQLiteConnection()
    {
        ConnectionString = $"Data Source=Data/{database};Version=3;New=True;Compress=True;"
    };
    connection.Open();
    connection.Close();
}
/// <summary>建立新資料表</summary>
/// <param name="database">資料庫名稱</param>
/// <param name="sqlCreateTable">建立資料表的 SQL 語句</param>
public void CreateTable(string database, string sqlCreateTable)
{
    var connection = OpenConnection(database);
    connection.Open();
    var command = new SQLiteCommand(sqlCreateTable, connection);
    var mySqlTransaction = connection.BeginTransaction();
    try
    {
        command.Transaction = mySqlTransaction;
        command.ExecuteNonQuery();
        mySqlTransaction.Commit();
    }
    catch (Exception ex)
    {
        mySqlTransaction.Rollback();
        throw (ex);
    }
    if (connection.State == ConnectionState.Open) connection.Close();
}
/// <summary>新增\修改\刪除資料</summary>
/// <param name="database">資料庫名稱</param>
/// <param name="sqlManipulate">資料操作的 SQL 語句</param>
public void Manipulate(string database, string sqlManipulate)
{
    var connection = OpenConnection(database);
    var command = new SQLiteCommand(sqlManipulate, connection);
    var mySqlTransaction = connection.BeginTransaction();
    try
    {
        command.Transaction = mySqlTransaction;
        command.ExecuteNonQuery();
        mySqlTransaction.Commit();
    }
    catch (Exception ex)
    {
        mySqlTransaction.Rollback();
        throw (ex);
    }
    if (connection.State == ConnectionState.Open) connection.Close();
}
/// <summary>讀取資料</summary>
/// <param name="database">資料庫名稱</param>
/// <param name="sqlQuery">資料查詢的 SQL 語句</param>
/// <returns></returns>
public DataTable GetDataTable(string database, string sqlQuery)
{
    var connection = OpenConnection(database);
    var dataAdapter = new SQLiteDataAdapter(sqlQuery, connection);
    var myDataTable = new DataTable();
    var myDataSet = new DataSet();
    myDataSet.Clear();
    dataAdapter.Fill(myDataSet);
    myDataTable = myDataSet.Tables[0];
    if (connection.State == ConnectionState.Open) connection.Close();
    return myDataTable;
}

使用上述程式碼做一個簡單範例

public void Main()
{
    // 建立 SQLite 資料庫
    CreateDatabase("data.db");

    // 建立資料表 TestTable
    var createtablestring = @"CREATE TABLE TestTable (Foo double, Bar double);";
    CreateTable("data.db", createtablestring);

    // 插入資料到 TestTable 表中
    var insertstring = @"
        INSERT INTO TestTable (Foo, Bar) VALUES ('10', '100');
        INSERT INTO TestTable (Foo, Bar) VALUES ('20', '200');
    ";
    Manipulate("data.db", insertstring);

    // 讀取資料
    var dataTable = GetDataTable("data.db", @"SELECT * FROM TestTable");
}

使用 EF Core SQLite Provider 的限制

參考資料:SQLite EF Core Database Provider Limitations

使用 Entity Framework Core 來處理 SQLite 資料庫時,有一些 Migrations 的限制,請參考下表:

Operation Supported?
AddColumn 增加欄位
AddForeignKey 增加外來鍵
AddPrimaryKey 增加主鍵
AddUniqueConstraint 增加唯一限制
AlterColumn 變更欄位
CreateIndex 新增索引
CreateTable 新增資料表
DropColumn 刪除欄位
DropForeignKey 刪除外來鍵
DropIndex 刪除索引
DropPrimaryKey 刪除主鍵
DropTable 刪除資料表
DropUniqueConstraint 刪除唯一限制
RenameColumn 變更欄位名稱
RenameIndex 變更索引名稱
RenameTable 變更資料表名稱

SQLite 批次 INSERT in C#

SQLite FAQ#19 提到,一秒最快能完成 50,000 筆以上的 INSERT,但一秒只能完成幾十筆 Transation,因此如果需要大量寫入資料,可參考黑暗執行緒 - SQLite 批次 INSERT 的蝸牛陷阱下列寫法。

using (var cnSqlite = new SQLiteConnection(csSqlite))
{
    cnSqlite.Open();
    Stopwatch sw = new Stopwatch();
    sw.Start();
    using (SQLiteTransaction tran = cnSqlite.BeginTransaction())
    {
        var totalCount = list.Count;
        var index = 0;
        foreach (var voc in list)
        {
            Console.WriteLine(
                $"{index++}/{totalCount}({index * 100.0 / totalCount:n1}%) {voc.Word}");
            cnSqlite.Execute(
                "INSERT INTO Dictionary VALUES(@Word, @KKSymbol, @Explanation)", (object)voc);
        }
        tran.Commit();
    }
    sw.Stop();
    Console.Write($"Duration={sw.ElapsedMilliseconds:n0}ms");
}

參考資料:


Poy Chang

Trial and Error