快捷導(dǎo)航

C#連接SQLite數(shù)據(jù)庫(kù)并實(shí)現(xiàn)基本操作

更新時(shí)間：2024年12月31日 09:21:26 作者：我曾經(jīng)是個(gè)程序員

本文介紹了SQLite,一個(gè)輕量級(jí)的跨平臺(tái)數(shù)據(jù)庫(kù)管理系統(tǒng),以及如何在C#中使用System.Data.SQLite庫(kù)進(jìn)行操作,包括創(chuàng)建、修改和查詢數(shù)據(jù)庫(kù),以及使用SQLiteHelper類簡(jiǎn)化SQL使用,此外,還提到了DB文件查看工具SQLiteSpy的應(yīng)用,需要的朋友可以參考下

1.安裝并引用System.Data.SQLite

通過(guò)NuGet包管理器安裝，Install-Package System.Data.SQLite

2.創(chuàng)建數(shù)據(jù)庫(kù)

string dbFilename =  "test.db";
if (!File.Exists(dbFilename))
{
    SQLiteConnection.CreateFile(dbFilename);
}

3.設(shè)置數(shù)據(jù)庫(kù)密碼

string connectionString = string.Format("Data Source={0};Version=3;",dbFilename);
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();//打開(kāi)數(shù)據(jù)庫(kù)
    connection.ChangePassword("123456");//設(shè)置密碼
}

4.連接數(shù)據(jù)庫(kù)

string connectionString =string.Format("Data Source={0}; Version=3; Password={1};",dbFilename,"123456");
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
}

5.創(chuàng)建表

using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
string commandText = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100), Code VARCHAR(100),Password VARCHAR(100))";

using (SQLiteCommand command = new SQLiteCommand(commandText, connection))
    {
        command.ExecuteNonQuery();//執(zhí)行sql
    }
}

6.添加數(shù)據(jù)

using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
string commandText = "insert into Users (Name, Code,Password) values (@name, @code,@password)";
using (SQLiteCommand command = new SQLiteCommand(commandText, connection))
    {
        // 設(shè)置參數(shù)值
        command.Parameters.AddWithValue("@name", "管理員");
        command.Parameters.AddWithValue("@code", "admin");
        command.Parameters.AddWithValue("@password", "123456");
        // 執(zhí)行語(yǔ)句
        command.ExecuteNonQuery();
    }
}

7.修改數(shù)據(jù)

using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string commandText = "update Users SET Password=@password WHERE Code = @code";
    using (SQLiteCommand command = new SQLiteCommand(commandText, connection))
    {
        // 設(shè)置參數(shù)值
        command.Parameters.AddWithValue("@code", "admin");
        command.Parameters.AddWithValue("@password", "admin123456");
        // 執(zhí)行語(yǔ)句
        command.ExecuteNonQuery();
    }
}

8.查詢數(shù)據(jù)

using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string commandText  = "select * from Users";
    using (SQLiteCommand command = new SQLiteCommand(commandText, connection))
    {
        using (SQLiteDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["Id"]}, 名稱: {reader["Name"]}, 編碼: {reader["Code"]}");
            }
        }
    }
}

9.刪除數(shù)據(jù)

using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string commandText = "delete from  Users where Code = @code";
    using (SQLiteCommand command = new SQLiteCommand(sql, connection))
    {
        // 設(shè)置參數(shù)值
        command.Parameters.AddWithValue("@code", "admin");
        // 執(zhí)行語(yǔ)句
        command.ExecuteNonQuery();
    }
}

以上就是C#連接SQLite數(shù)據(jù)庫(kù)并實(shí)現(xiàn)基本操作的詳細(xì)內(nèi)容，更多關(guān)于C#連接SQLite的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: