C#中SQLite数据库的帮助类指南

发表时间: 2024-01-24 08:01

首先,确保你已经安装了System.Data.SQLite库。你可以通过NuGet包管理器来安装它:

bashInstall-Package System.Data.SQLite

接下来是C#的帮助类:

csharpusing System;using System.Data;using System.Data.SQLite;public class SQLiteHelper{    private string connectionString;    public SQLiteHelper(string connectionString)    {        this.connectionString = connectionString;    }    // 查询方法    public DataTable ExecuteQuery(string query)    {        using (SQLiteConnection conn = new SQLiteConnection(connectionString))        {            conn.Open();            SQLiteCommand cmd = new SQLiteCommand(query, conn);            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);            DataTable dt = new DataTable();            da.Fill(dt);            return dt;        }    }    // 插入方法    public int ExecuteNonQuery(string query)    {        using (SQLiteConnection conn = new SQLiteConnection(connectionString))        {            conn.Open();            SQLiteCommand cmd = new SQLiteCommand(query, conn);            return cmd.ExecuteNonQuery();        }    }    // 更新方法    public int ExecuteNonQuery(string query, SQLiteParameter[] parameters)    {        using (SQLiteConnection conn = new SQLiteConnection(connectionString))        {            conn.Open();            SQLiteCommand cmd = new SQLiteCommand(query, conn);            cmd.Parameters.AddRange(parameters);            return cmd.ExecuteNonQuery();        }    }    // 删除方法    public int ExecuteNonQueryDelete(string query)    {        return ExecuteNonQuery(query);    }}

使用这个帮助类的示例:

csharpstring connectionString = "Data Source=:memory:;Version=3;"; // 修改为你的连接字符串,这里使用内存数据库示例。SQLiteHelper helper = new SQLiteHelper(connectionString);// 查询数据示例:获取所有记录,假设表名为"users",字段名为"id"和"name"DataTable dt = helper.ExecuteQuery("SELECT * FROM users"); foreach (DataRow row in dt.Rows) {     Console.WriteLine("ID: {0}, Name: {1}", row["id"], row["name"]); }  // 插入数据示例:向"users"表插入一条新记录,假设"id"为自增长,字段只有"name"为"Alice" int affectedRows = helper.ExecuteNonQuery("INSERT INTO users (name) VALUES (?)", new SQLiteParameter[] { new SQLiteParameter("@name", "Alice") }); Console.WriteLine($"{affectedRows} record inserted.");  // 更新数据示例:更新"users"表中"name"为"Bob"的记录的"name"为"Charlie",假设"id"为1 参数绑定与