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 参数绑定与