C# SQLite基本使用示例

发布于：2025-05-15 ⋅ 阅读:(21) ⋅ 点赞:(0)

目录

1 基本使用流程

1.1 步骤1：添加SQLite依赖

1.2 ​步骤2：建立连接

1.3 步骤3：执行SQL命令

1.4 步骤4：查询数据

1.5 步骤5：使用事务

2 SQLite基本使用示例

2.1 准备工作

2.2 完整示例

2.3 案例代码解析

2.3.1 连接管理

2.3.2 执行非查询命令

2.3.3 参数化查询

2.3.4 使用事务

2.3.5 查询和结果处理

本节将通过代码示例展示如何在C#中使用SQLite数据库。这个示例包含了创建连接、创建表、执行CRUD操作、事务处理和使用参数化查询等基本功能。在C#应用程序中使用SQLite是一个常见的需求，特别是对于需要轻量级数据存储的应用，将详细总结C#中使用SQLite数据库的基本流程，并解释案例中的代码含义和一些重要注意事项。

1 基本使用流程

1.1 步骤1：添加SQLite依赖

在C#项目中使用SQLite，首先需要添加适当的NuGet包：

System.Data.SQLite (完整版)
或
Microsoft.Data.Sqlite (轻量版，.NET Core/.NET 5+推荐)

通过Visual Studio的NuGet包管理器或命令行添加：

Install-Package System.Data.SQLite

或

dotnet add package Microsoft.Data.Sqlite

1.2 ​步骤2：建立连接

// 引入命名空间
using System.Data.SQLite;
​
// 连接字符串
string connectionString = "Data Source=database.db;Version=3;";
​
// 创建连接
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    
    // 执行数据库操作...
    
    // 使用using语句确保连接被正确关闭
}

1.3 步骤3：执行SQL命令

// 执行非查询命令(DDL或DML)
using (SQLiteCommand command = new SQLiteCommand(connection))
{
    command.CommandText = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT, Email TEXT)";
    command.ExecuteNonQuery();
}
​
// 插入数据
using (SQLiteCommand command = new SQLiteCommand(connection))
{
    command.CommandText = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
    command.Parameters.AddWithValue("@Name", "张三");
    command.Parameters.AddWithValue("@Email", "zhangsan@example.com");
    command.ExecuteNonQuery();
}

1.4 步骤4：查询数据

// 查询数据
using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Users", connection))
{
    using (SQLiteDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string name = reader.GetString(1);
            string email = reader.GetString(2);
            
            Console.WriteLine($"ID: {id}, Name: {name}, Email: {email}");
        }
    }
}

1.5 步骤5：使用事务

using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    
    using (SQLiteTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // 执行多个数据库操作
            using (SQLiteCommand command = new SQLiteCommand(connection, transaction))
            {
                command.CommandText = "INSERT INTO Users (Name) VALUES (@Name)";
                command.Parameters.AddWithValue("@Name", "李四");
                command.ExecuteNonQuery();
                
                command.CommandText = "UPDATE Users SET Email = @Email WHERE Name = @Name";
                command.Parameters.AddWithValue("@Email", "lisi@example.com");
                command.Parameters.AddWithValue("@Name", "李四");
                command.ExecuteNonQuery();
            }
            
            // 如果一切正常，提交事务
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // 发生错误，回滚事务
            transaction.Rollback();
            Console.WriteLine($"事务回滚: {ex.Message}");
        }
    }
}

2 SQLite基本使用示例

2.1 准备工作

首先，你需要安装SQLite的C#驱动。在Visual Studio中，通过NuGet包管理器安装System.Data.SQLite或Microsoft.Data.Sqlite包。

// 使用NuGet包管理器控制台安装
// Install-Package System.Data.SQLite
// 或者
// Install-Package Microsoft.Data.Sqlite

2.2 完整示例

using System;
using System.Data.SQLite; // 使用System.Data.SQLite包
using System.IO;

namespace SQLiteDemo
{
    class Program
    {
        // 数据库连接字符串
        private static string dbFile = "mydatabase.db";
        private static string connectionString = $"Data Source={dbFile};Version=3;";

        static void Main(string[] args)
        {
            try
            {
                // 确保数据库文件存在，如果不存在则创建
                CreateDatabaseIfNotExists();

                // 创建表
                CreateTables();

                // 插入数据
                InsertData();

                // 查询数据
                QueryData();

                // 更新数据
                UpdateData();

                // 使用事务批量插入
                BatchInsertWithTransaction();

                // 删除数据
                DeleteData();

                // 参数化查询示例
                ParameterizedQueryExample();

                Console.WriteLine("所有操作已完成！");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"发生错误: {ex.Message}");
                Console.WriteLine(ex.StackTrace);
            }

            Console.ReadKey();
        }

        // 创建数据库文件
        static void CreateDatabaseIfNotExists()
        {
            if (!File.Exists(dbFile))
            {
                Console.WriteLine("创建新的数据库文件...");
                SQLiteConnection.CreateFile(dbFile);
                Console.WriteLine("数据库文件已创建。");
            }
            else
            {
                Console.WriteLine("使用现有数据库文件。");
            }
        }

        // 创建表
        static void CreateTables()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("已连接到数据库。");

                // 创建用户表
                string createUserTableSql = @"
                    CREATE TABLE IF NOT EXISTS Users (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        Email TEXT UNIQUE,
                        Age INTEGER,
                        RegisterDate TEXT DEFAULT CURRENT_TIMESTAMP
                    )";

                // 创建订单表，演示外键关系
                string createOrderTableSql = @"
                    CREATE TABLE IF NOT EXISTS Orders (
                        OrderId INTEGER PRIMARY KEY AUTOINCREMENT,
                        UserId INTEGER,
                        ProductName TEXT NOT NULL,
                        Quantity INTEGER DEFAULT 1,
                        OrderDate TEXT DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (UserId) REFERENCES Users(Id)
                    )";

                using (var command = new SQLiteCommand(createUserTableSql, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Users表已创建。");
                }

                using (var command = new SQLiteCommand(createOrderTableSql, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Orders表已创建。");
                }
            }
        }

        // 插入数据
        static void InsertData()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 插入用户数据
                string insertUserSql = @"
                    INSERT INTO Users (Name, Email, Age) 
                    VALUES (@Name, @Email, @Age)";

                using (var command = new SQLiteCommand(insertUserSql, connection))
                {
                    // 添加参数
                    command.Parameters.AddWithValue("@Name", "张三");
                    command.Parameters.AddWithValue("@Email", "zhangsan@example.com");
                    command.Parameters.AddWithValue("@Age", 28);

                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"插入了 {rowsAffected} 行用户数据。");

                    // 获取自增ID
                    string getLastIdSql = "SELECT last_insert_rowid()";
                    using (var idCommand = new SQLiteCommand(getLastIdSql, connection))
                    {
                        long userId = (long)idCommand.ExecuteScalar();
                        Console.WriteLine($"新插入用户的ID是: {userId}");

                        // 为该用户添加订单
                        string insertOrderSql = @"
                            INSERT INTO Orders (UserId, ProductName, Quantity)
                            VALUES (@UserId, @ProductName, @Quantity)";

                        using (var orderCommand = new SQLiteCommand(insertOrderSql, connection))
                        {
                            orderCommand.Parameters.AddWithValue("@UserId", userId);
                            orderCommand.Parameters.AddWithValue("@ProductName", "笔记本电脑");
                            orderCommand.Parameters.AddWithValue("@Quantity", 1);

                            rowsAffected = orderCommand.ExecuteNonQuery();
                            Console.WriteLine($"插入了 {rowsAffected} 行订单数据。");
                        }
                    }
                }
            }
        }

        // 查询数据
        static void QueryData()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 简单查询
                string queryUsersSql = "SELECT * FROM Users";
                using (var command = new SQLiteCommand(queryUsersSql, connection))
                {
                    Console.WriteLine("\n所有用户:");
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["Id"]}, 姓名: {reader["Name"]}, " +
                                              $"邮箱: {reader["Email"]}, 年龄: {reader["Age"]}, " +
                                              $"注册日期: {reader["RegisterDate"]}");
                        }
                    }
                }

                // 联表查询
                string queryOrdersSql = @"
                    SELECT o.OrderId, u.Name as UserName, o.ProductName, o.Quantity, o.OrderDate
                    FROM Orders o
                    JOIN Users u ON o.UserId = u.Id
                    ORDER BY o.OrderDate DESC";

                using (var command = new SQLiteCommand(queryOrdersSql, connection))
                {
                    Console.WriteLine("\n所有订单:");
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"订单ID: {reader["OrderId"]}, 用户: {reader["UserName"]}, " +
                                              $"商品: {reader["ProductName"]}, 数量: {reader["Quantity"]}, " +
                                              $"订单日期: {reader["OrderDate"]}");
                        }
                    }
                }

                // 聚合查询
                string aggregateQuerySql = @"
                    SELECT COUNT(*) as UserCount, AVG(Age) as AvgAge
                    FROM Users";

                using (var command = new SQLiteCommand(aggregateQuerySql, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"\n统计信息: 用户总数: {reader["UserCount"]}, 平均年龄: {reader["AvgAge"]}");
                        }
                    }
                }
            }
        }

        // 更新数据
        static void UpdateData()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string updateUserSql = @"
                    UPDATE Users 
                    SET Age = @NewAge, Name = @NewName
                    WHERE Email = @Email";

                using (var command = new SQLiteCommand(updateUserSql, connection))
                {
                    command.Parameters.AddWithValue("@NewAge", 29);
                    command.Parameters.AddWithValue("@NewName", "张三(已更新)");
                    command.Parameters.AddWithValue("@Email", "zhangsan@example.com");

                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"\n更新了 {rowsAffected} 行用户数据。");
                }
            }
        }

        // 使用事务批量插入
        static void BatchInsertWithTransaction()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 开始事务
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        string insertUserSql = @"
                            INSERT INTO Users (Name, Email, Age) 
                            VALUES (@Name, @Email, @Age)";

                        // 批量插入10个用户
                        using (var command = new SQLiteCommand(insertUserSql, connection, transaction))
                        {
                            for (int i = 1; i <= 10; i++)
                            {
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@Name", $"批量用户{i}");
                                command.Parameters.AddWithValue("@Email", $"user{i}@example.com");
                                command.Parameters.AddWithValue("@Age", 20 + i);

                                command.ExecuteNonQuery();
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                        Console.WriteLine("\n事务批量插入成功，已添加10位用户。");
                    }
                    catch (Exception ex)
                    {
                        // 出错时回滚事务
                        transaction.Rollback();
                        Console.WriteLine($"\n事务回滚: {ex.Message}");
                    }
                }
            }
        }

        // 删除数据
        static void DeleteData()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 删除指定用户
                string deleteUserSql = "DELETE FROM Users WHERE Name LIKE @NamePattern";

                using (var command = new SQLiteCommand(deleteUserSql, connection))
                {
                    command.Parameters.AddWithValue("@NamePattern", "批量用户%");
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"\n删除了 {rowsAffected} 行用户数据。");
                }
            }
        }

        // 参数化查询示例 - 防止SQL注入
        static void ParameterizedQueryExample()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 这是安全的方式，使用参数化查询
                string safeQuerySql = "SELECT * FROM Users WHERE Name LIKE @NamePattern";

                using (var command = new SQLiteCommand(safeQuerySql, connection))
                {
                    // 即使用户输入包含SQL注入尝试，也会被当作普通字符串处理
                    string userInput = "张三' OR '1'='1";
                    command.Parameters.AddWithValue("@NamePattern", "%" + userInput + "%");

                    Console.WriteLine("\n执行参数化查询(防止SQL注入):");
                    using (var reader = command.ExecuteReader())
                    {
                        bool hasResults = false;
                        while (reader.Read())
                        {
                            hasResults = true;
                            Console.WriteLine($"ID: {reader["Id"]}, 姓名: {reader["Name"]}");
                        }

                        if (!hasResults)
                        {
                            Console.WriteLine("没有找到匹配的记录。");
                        }
                    }
                }

                // 注意: 这里演示如何使用执行单值查询
                string countSql = "SELECT COUNT(*) FROM Users";
                using (var command = new SQLiteCommand(countSql, connection))
                {
                    int count = Convert.ToInt32(command.ExecuteScalar());
                    Console.WriteLine($"\n数据库中共有 {count} 个用户。");
                }
            }
        }
    }
}

2.3 案例代码解析

2.3.1 连接管理

static void SetupExampleData()
{
    using (var connection = new SQLiteConnection(connectionString))
    {
        connection.Open();
        
        // 数据库操作...
    }
}

解析:

  • using语句确保即使发生异常，连接也会被正确释放和关闭，避免资源泄漏

  • connection.Open()建立与SQLite数据库文件的物理连接

  • 连接字符串格式通常为Data Source=文件路径;Version=3;，其中文件路径可以是相对或绝对路径

2.3.2 执行非查询命令

static void ExecuteNonQuery(SQLiteConnection connection, string sql, SQLiteTransaction transaction = null)
{
    using (var command = new SQLiteCommand(sql, connection, transaction))
    {
        command.ExecuteNonQuery();
    }
}

解析:

  • 这是一个辅助方法，用于执行不返回结果集的SQL命令（如CREATE, INSERT, UPDATE, DELETE）

  • 参数化设计允许重用并支持事务

  • ExecuteNonQuery()返回受影响的行数（对于DDL语句如CREATE TABLE通常返回0）

  • 同样使用using语句确保命令资源被正确释放

2.3.3 参数化查询

ExecuteNonQuery(connection, @"
    INSERT INTO Employees (Name, DeptId, Salary, HireDate) VALUES
    ('张三', 1, 15000, '2020-01-15')");

更安全的参数化版本:

using (var command = new SQLiteCommand(connection))
{
    command.CommandText = "INSERT INTO Employees (Name, DeptId, Salary, HireDate) VALUES (@Name, @DeptId, @Salary, @HireDate)";
    command.Parameters.AddWithValue("@Name", "张三");
    command.Parameters.AddWithValue("@DeptId", 1);
    command.Parameters.AddWithValue("@Salary", 15000);
    command.Parameters.AddWithValue("@HireDate", "2020-01-15");
    command.ExecuteNonQuery();
}

解析:

  • 参数化查询防止SQL注入攻击

  • 使用@前缀的参数名称是约定

  • AddWithValue方法会自动根据传入参数的类型设置SQLite参数类型

2.3.4 使用事务

using (var transaction = connection.BeginTransaction())
{
    try
    {
        // 执行多个操作
        ExecuteNonQuery(connection, "INSERT INTO Departments (DeptId, Name) VALUES (4, '人力资源部')", transaction);
        ExecuteNonQuery(connection, "UPDATE Employees SET Salary = Salary * 1.1 WHERE DeptId = 1", transaction);
        
        // 假设这里有条件判断是否提交
        bool shouldCommit = true;
        
        if (shouldCommit)
        {
            transaction.Commit();
            Console.WriteLine("事务已提交。");
        }
        else
        {
            transaction.Rollback();
            Console.WriteLine("事务已回滚。");
        }
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        Console.WriteLine($"发生错误，事务已回滚: {ex.Message}");
    }
}

解析:

  • BeginTransaction()开始一个新事务

  • 事务内的所有操作要么全部成功，要么全部失败

  • Commit()提交所有更改并结束事务

  • Rollback()撤销所有更改并结束事务

  • try-catch块确保在发生异常时事务被正确回滚

2.3.5 查询和结果处理

static void DisplayQueryResults(SQLiteConnection connection, string sql)
{
    using (var command = new SQLiteCommand(sql, connection))
    {
        using (var reader = command.ExecuteReader())
        {
            // 获取列名
            List<string> columns = new List<string>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                columns.Add(reader.GetName(i));
            }
            
            // 输出列名
            Console.WriteLine(string.Join(" | ", columns));
            Console.WriteLine(new string('-', columns.Count * 15));
            
            // 输出行
            while (reader.Read())
            {
                List<string> values = new List<string>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    values.Add(reader[i]?.ToString() ?? "NULL");
                }
                Console.WriteLine(string.Join(" | ", values));
            }
        }
    }
}

解析:

  • ExecuteReader()返回一个SQLiteDataReader对象，用于逐行读取结果集

  • reader.FieldCount获取结果集的列数

  • reader.GetName(i)获取特定列的名称

  • reader.Read()将读取指针移到下一行，如果有下一行则返回true

  • reader[i]获取当前行中索引为i的列的值

  • ?? "NULL"处理可能的NULL值

  • 嵌套的using语句确保读取器和命令都被正确关闭

3 数据类型映射

C#类型和SQLite类型之间的映射：

C#类型 SQLite类型
int, long INTEGER
double, float REAL
string TEXT
byte[] BLOB
DateTime TEXT/INTEGER
bool INTEGER

在读取时需要注意类型转换，SQLite的动态类型系统可能需要额外的类型转换处理。

4 使用注意事项

  1. 连接管理：

    •  总是使用using语句确保连接正确关闭

    • 避免长时间保持连接打开

    • 对于多线程应用，考虑使用连接池 

  2. 参数化查询：

    • 始终使用参数化查询防止SQL注入

    • 不要通过字符串拼接构建SQL语句

  3. 事务使用：

    • 批量操作使用事务提高性能

    • 保持事务尽可能短小

    • 正确处理事务的异常情况

  4. 性能考虑：

    • 为频繁查询的列创建索引

    • 使用EXPLAIN QUERY PLAN分析查询性能

    • 大量数据操作时考虑批处理

  5. 版本兼容性：

    • 确认目标SQLite版本是否支持需要的功能

    • 窗口函数需要SQLite 3.25+

    • FTS5需要特殊编译支持

以上就是关于如何在C#中使用SQLite数据库的基本流程，下一节我们将通过其他的C#示例代码来讲解SQLite高级功能的实现！！！

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布