关于C#操作SQLite数据库的一些函数封装

主要功能：增删改查、自定义SQL执行、批量执行（事务）、防SQL注入、异常处理

---

1.NuGet中安装System.Data.SQLite

---

2.SQLiteHelper的封装：

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Security.Cryptography;namespace inventory_management_system.jdbc
{public class SQLiteHelper{private SQLiteConnection _connection;public SQLiteHelper(string databasePath){_connection = new SQLiteConnection($"Data Source={databasePath};Version=3;BinaryGUID=False;");}public void OpenConnection(){if (_connection.State != ConnectionState.Open){_connection.Open();}}public void CloseConnection(){if (_connection.State != ConnectionState.Closed){_connection.Close();}}/// <summary>/// 执行插入操作/// </summary>/// <param name="tableName">表名</param>/// <param name="key_values">键值对字典</param>/// <returns>int</returns>public int Insert(string tableName, List<SQLiteParameter> parameters){using (var cmd = _connection.CreateCommand()){// 添加参数到命令对象  cmd.Parameters.AddRange(parameters.ToArray());// 构建INSERT语句的列名部分和值部分  string columnNames = string.Join(",", parameters.Select(p => p.ParameterName));string placeholders = string.Join(",", parameters.Select(p => "@"+p.ParameterName));// 构建完整的INSERT语句  string query = $"INSERT INTO {tableName} ({columnNames}) VALUES ({placeholders});";cmd.CommandText = query;// 执行命令并返回受影响的行数  return cmd.ExecuteNonQuery();}}/// <summary>/// 执行插入操作/// </summary>/// <param name="tableName">表名</param>/// <param name="key_values">键值对字典</param>/// <returns>int</returns>public int Insert(string tableName, Dictionary<string, object> key_values){using (var cmd = _connection.CreateCommand()){List<string> columns = new List<string>();List<SQLiteParameter> parameters = new List<SQLiteParameter>();int index = 0;foreach (var kvp in key_values){columns.Add(kvp.Key);parameters.Add(new SQLiteParameter($"@{kvp.Key}", kvp.Value));cmd.Parameters.Add(parameters[index]);index++;}string query = $"INSERT INTO {tableName} ({string.Join(",", columns)}) VALUES ({string.Join(",", parameters.Select(p => "@" + p.ParameterName))});";cmd.CommandText = query;return cmd.ExecuteNonQuery();}}/// <summary>/// 执行更新操作/// </summary>/// <param name="tableName">表名</param>/// <param name="setValues">新数据</param>/// <param name="whereClause">条件</param>/// <param name="parameters">条件数据</param>/// <returns>int</returns>public int Update(string tableName, Dictionary<string, object> setValues, string whereClause, List<SQLiteParameter> parameters){using (var cmd = _connection.CreateCommand()){List<string> setColumns = new List<string>();int index = 0;foreach (var kvp in setValues){setColumns.Add($"{kvp.Key} = @{kvp.Key}");cmd.Parameters.Add(new SQLiteParameter($"@{kvp.Key}", kvp.Value));index++;}string query = $"UPDATE {tableName} SET {string.Join(",", setColumns)} WHERE {whereClause}";cmd.CommandText = query;cmd.Parameters.AddRange(parameters.ToArray());return cmd.ExecuteNonQuery();}}/// <summary>/// 执行删除操作/// </summary>/// <param name="tableName">表名</param>/// <param name="whereClause">条件</param>/// <param name="parameters">参数数据</param>/// <returns>int</returns>public int Delete(string tableName, string whereClause, List<SQLiteParameter> parameters){using (var cmd = _connection.CreateCommand()){cmd.CommandText = $"DELETE FROM {tableName} WHERE {whereClause};";cmd.Parameters.AddRange(parameters.ToArray());return cmd.ExecuteNonQuery();}}/// <summary>/// 执行查询操作/// </summary>/// <param name="sql">sql语句</param>/// <param name="parameters">参数数据</param>/// <returns>DataTable</returns>public DataTable Select(string sql, List<SQLiteParameter> parameters){DataTable dt = new DataTable();using (var cmd = _connection.CreateCommand()){cmd.CommandText = sql;cmd.Parameters.AddRange(parameters.ToArray());using (var reader = cmd.ExecuteReader()){dt.Load(reader);}}return dt;}/// <summary>/// 执行自定义SQL语句，返回影响行数/// </summary>/// <param name="sql"></param>/// <returns>int类型</returns>public int ExecuteSQL(string sql){using (var cmd = _connection.CreateCommand()){cmd.CommandText = sql;return cmd.ExecuteNonQuery();}}/// <summary>/// 执行自定义SQL语句，返回影响行数/// </summary>/// <param name="sql"></param>/// <param name="parameters"></param>/// <returns>int类型</returns>public int ExecuteSQL(string sql, List<SQLiteParameter> parameters){using (var cmd = _connection.CreateCommand()){cmd.CommandText = sql;cmd.Parameters.AddRange(parameters.ToArray());return cmd.ExecuteNonQuery();}}/// <summary>/// 执行自定义sql查询语句,如果你计划对返回的 DataTable 进行大量的后续操作（例如，添加或删除行，修改列值等），那么使用 SQLiteDataAdapter 可能会更有优势，因为它提供了更高级的数据绑定和更新功能/// </summary>/// <param name="sql"></param>/// <param name="parameters"></param>/// <returns>DataTable</returns>public DataTable ExecuteSelect(string sql, List<SQLiteParameter> parameters){using (SQLiteCommand command = _connection.CreateCommand()){command.CommandText = sql;command.Parameters.AddRange(parameters.ToArray());using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command)){DataTable dataTable = new DataTable();adapter.Fill(dataTable); // 填充数据表  return dataTable; // 返回查询结果的数据表  }}}/// <summary>/// 批量操作/// </summary>/// <param name="sql"></param>/// <param name="parameters"></param>/// <returns></returns>public int BatchExecuteSQL(string sql, List<List<SQLiteParameter>> parameters){int affectedRows = 0;using (var transaction = _connection.BeginTransaction()){try{using (var cmd = _connection.CreateCommand()){cmd.Transaction = transaction;foreach (var paramList in parameters){cmd.CommandText = sql;cmd.Parameters.Clear();cmd.Parameters.AddRange(paramList.ToArray());affectedRows += cmd.ExecuteNonQuery();}transaction.Commit(); // 提交事务  }}catch (Exception ex){transaction.Rollback(); // 发生异常时回滚事务  throw; // 重新抛出异常，以便上层调用者处理  }}return affectedRows;}}
}

---

3.SQLiteHelper使用示例：

public void test(){string databasePath = "path_to_your_database.db"; // 替换为你的数据库文件路径  SQLiteHelper helper = new SQLiteHelper(databasePath);try{// 打开数据库连接  helper.OpenConnection();// 插入数据示例  Dictionary<string, object> valuesToInsert = new Dictionary<string, object>{{ "Name", "John Doe" },{ "Age", 30 },{ "Email", "[e-mail]" }};int insert_count = helper.Insert("Users", valuesToInsert);// 插入数据示例  List<SQLiteParameter> insert_parameters = new List<SQLiteParameter>(){new SQLiteParameter( "Name", "John Doe" ),new SQLiteParameter("Age", 30 ),new SQLiteParameter("Email", "[e-mail]")};int insert_count2 = helper.Insert("Users", insert_parameters);// 更新数据示例  Dictionary<string, object> valuesToUpdate = new Dictionary<string, object>{{ "Age", 31 }};int update_count = helper.Update("Users", valuesToUpdate, "Name = @Name", new List<SQLiteParameter>(){ new SQLiteParameter("@Name", "John Doe") });// 删除数据示例  int delete_count = helper.Delete("Users", "Name = @Name", new List<SQLiteParameter>() { new SQLiteParameter("@Name", "John Doe") });// 查询数据示例  DataTable dataTable = helper.Select("SELECT * FROM Users WHERE Name = @Name", new List<SQLiteParameter>() { new SQLiteParameter("@Name", "John Doe") });foreach (DataRow row in dataTable.Rows){Console.WriteLine($"Name: {row["Name"]}, Age: {row["Age"]}, Email: {row["Email"]}");}//批量操作示例string sql = "INSERT INTO Users (Name,Age,Email) VALUES (@Name,@Age,@Email);";List<List<SQLiteParameter>> parameterList = new List<List<SQLiteParameter>>();for (int i = 0; i < 10; i++){List<SQLiteParameter> parameters = new List<SQLiteParameter>();parameters.Add(new SQLiteParameter("@Name", $"名字{i}"));parameters.Add(new SQLiteParameter("@Age", i));parameters.Add(new SQLiteParameter("@Email", $"邮箱{i}"));parameterList.Add(parameters);}int batch_count = helper.BatchExecuteSQL(sql, parameterList);}catch (Exception ex){Console.WriteLine("An error occurred: " + ex.Message);}finally{// 关闭数据库连接  helper.CloseConnection();}}

注意:在构建sql语句时，占位符尽量不要用"?"，虽然大多数据库用"?"是标准做法，但是本人用System.Data.SQLite实际操作过程中，很多情况会报数据类型不匹配异常，最好还是用"@前缀"好一点