SQLite是一个轻量级、跨平台的关系型数据库，在小型项目中，方便，易用，同时支持多种开发语言。本文将用C#语言对SQLite 的一个封装，实现数据的增删改查。需要的可以参考一下
Tuple<bool, string, int> 第一个参数 bool 代表执行结果，第二个参数 string 代表错误信息，第三个参数 int 代表影响的行数。
Tuple<bool, string, DataSet> 第一个参数 bool 代表执行结果，第二个参数 string 代表错误信息，第三个参数 DataSet 代表返回的表单数据。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Text;
namespace MySQLiteHelper
public class SQLiteHelper
#region 字段
/// <summary>
/// 事务的基类
/// </summary>
private DbTransaction DBtrans;
/// <summary>
/// 使用静态变量字典解决多线程实例本类，实现一个数据库对应一个clslock
/// </summary>
private static readonly Dictionary<string, ClsLock> RWL = new Dictionary<string, ClsLock>();
/// <summary>
/// 数据库地址
/// </summary>
private readonly string mdataFile;
/// <summary>
/// 数据库密码
/// </summary>
private readonly string mPassWord;
private readonly string LockName = null;
/// <summary>
/// 数据库连接定义
/// </summary>
private SQLiteConnection mConn;
#endregion
#region 构造函数
/// <summary>
/// 根据数据库地址初始化
/// </summary>
/// <param name="dataFile">数据库地址</param>
public SQLiteHelper(string dataFile)
this.mdataFile = dataFile ?? throw new ArgumentNullException("dataFile=null");
//this.mdataFile = AppDomain.CurrentDomain.BaseDirectory + dataFile;
this.mdataFile = dataFile;
if (!RWL.ContainsKey(dataFile))
LockName = dataFile;
RWL.Add(dataFile, new ClsLock());
/// <summary>
/// 使用密码打开数据库
/// </summary>
/// <param name="dataFile">数据库地址</param>
/// <param name="PassWord">数据库密码</param>
public SQLiteHelper(string dataFile, string PassWord)
this.mdataFile = dataFile ?? throw new ArgumentNullException("dataFile is null");
this.mPassWord = PassWord ?? throw new ArgumentNullException("PassWord is null");
//this.mdataFile = AppDomain.CurrentDomain.BaseDirectory + dataFile;
this.mdataFile = dataFile;
if (!RWL.ContainsKey(dataFile))
LockName = dataFile;
RWL.Add(dataFile, new ClsLock());
#endregion
#region 打开/关闭 数据库
/// <summary>
/// 打开 SQLiteManager 使用的数据库连接
/// </summary>
public void Open()
if (string.IsNullOrWhiteSpace(mPassWord))
mConn = OpenConnection(this.mdataFile);
mConn = OpenConnection(this.mdataFile, mPassWord);
Console.WriteLine("打开数据库成功");
/// <summary>
/// 关闭连接
/// </summary>
public void Close()
if (this.mConn != null)
this.mConn.Close();
if (RWL.ContainsKey(LockName))
RWL.Remove(LockName);
catch
Console.WriteLine("关闭失败");
Console.WriteLine("关闭数据库成功");
#endregion
#region 事务
/// <summary>
/// 开始事务
/// </summary>
public void BeginTrain()
EnsureConnection();
DBtrans = mConn.BeginTransaction();
/// <summary>
/// 提交事务
/// </summary>
public void DBCommit()
DBtrans.Commit();
catch (Exception)
DBtrans.Rollback();
#endregion
#region 工具
/// <summary>
/// 打开一个SQLite数据库文件，如果文件不存在，则创建（无密码）
/// </summary>
/// <param name="dataFile"></param>
/// <returns>SQLiteConnection 类</returns>
private SQLiteConnection OpenConnection(string dataFile)
if (dataFile == null)
throw new ArgumentNullException("dataFiledataFile=null");
if (!File.Exists(dataFile))
SQLiteConnection.CreateFile(dataFile);
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder
DataSource = dataFile
conn.ConnectionString = conStr.ToString();
conn.Open();
return conn;
/// <summary>
/// 打开一个SQLite数据库文件，如果文件不存在，则创建（有密码）
/// </summary>
/// <param name="dataFile"></param>
/// <param name="Password"></param>
/// <returns>SQLiteConnection 类</returns>
private SQLiteConnection OpenConnection(string dataFile, string Password)
if (dataFile == null)
throw new ArgumentNullException("dataFile=null");
if (!File.Exists(Convert.ToString(dataFile)))
SQLiteConnection.CreateFile(dataFile);
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder
DataSource = dataFile,
Password = Password
conn.ConnectionString = conStr.ToString();
conn.Open();
return conn;
catch (Exception)
return null;
/// <summary>
/// 读取 或 设置 SQLiteManager 使用的数据库连接
/// </summary>
public SQLiteConnection Connection
return mConn;
private set
mConn = value ?? throw new ArgumentNullException();
/// <summary>
/// 确保数据库是连接状态
/// </summary>
/// <exception cref="Exception"></exception>
protected void EnsureConnection()
if (this.mConn == null)
throw new Exception("SQLiteManager.Connection=null");
if (mConn.State != ConnectionState.Open)
mConn.Open();
/// <summary>
/// 获取数据库文件的路径
/// </summary>
/// <returns></returns>
public string GetDataFile()
return this.mdataFile;
/// <summary>
/// 判断表 table 是否存在
/// </summary>
/// <param name="table"></param>
/// <returns>存在返回true，否则返回false</returns>
public bool TableExists(string table)
if (table == null)
throw new ArgumentNullException("table=null");
EnsureConnection();
SQLiteDataReader reader = ExecuteReader("SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name=@tableName ", new SQLiteParameter[] { new SQLiteParameter("tableName", table) });
if (reader == null)
return false;
reader.Read();
int c = reader.GetInt32(0);
reader.Close();
reader.Dispose();
//return false;
return c == 1;
/// <summary>
/// VACUUM 命令（通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件）
/// </summary>
/// <returns></returns>
public bool Vacuum()
using (SQLiteCommand Command = new SQLiteCommand("VACUUM", Connection))
Command.ExecuteNonQuery();
return true;
catch (System.Data.SQLite.SQLiteException)
return false;
#endregion
#region 执行SQL
/// <summary>
/// 执行SQL, 并返回 SQLiteDataReader 对象结果
/// </summary>
/// <param name="sql"></param>
/// <param name="paramArr">null 表示无参数</param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] paramArr)
if (sql == null)
throw new ArgumentNullException("sql=null");
EnsureConnection();
using (RWL[LockName].Read())
using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
if (paramArr != null)
cmd.Parameters.AddRange(paramArr);
SQLiteDataReader reader = cmd.ExecuteReader();
cmd.Parameters.Clear();
return reader;
catch (Exception)
return null;
/// <summary>
/// 执行查询，并返回dataset对象
/// </summary>
/// <param name="sql">SQL查询语句</param>
/// <param name="paramArr">参数数组</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string sql, SQLiteParameter[] paramArr)
if (sql == null)
throw new ArgumentNullException("sql=null");
this.EnsureConnection();
using (RWL[LockName].Read())
using (SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection))
if (paramArr != null)
cmd.Parameters.AddRange(paramArr);
SQLiteDataAdapter da = new SQLiteDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
cmd.Parameters.Clear();
da.Dispose();
return ds;
catch (Exception)
return null;
/// <summary>
/// 执行SQL查询，并返回dataset对象。
/// </summary>
/// <param name="strTable">映射源表的名称</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramArr">SQL参数数组</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string strTable, string sql, SQLiteParameter[] paramArr)
if (sql == null)
throw new ArgumentNullException("sql=null");
this.EnsureConnection();
using (RWL[LockName].Read())
using (SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection))
if (paramArr != null)
cmd.Parameters.AddRange(paramArr);
SQLiteDataAdapter da = new SQLiteDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds, strTable);
cmd.Parameters.Clear();
da.Dispose();
return ds;
catch (Exception)
return null;
/// <summary>
/// 执行SQL，返回受影响的行数，可用于执行表创建语句，paramArr == null 表示无参数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, SQLiteParameter[] paramArr)
if (sql == null)
throw new ArgumentNullException("sql=null");
this.EnsureConnection();
using (RWL[LockName].Read())
using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
if (paramArr != null)
foreach (SQLiteParameter p in paramArr)
cmd.Parameters.Add(p);
int c = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return c;
catch (SQLiteException)
return 0;
/// <summary>
/// 执行SQL，返回结果集第一行，如果结果集为空，那么返回空 List(List.Count=0)，
/// rowWrapper = null 时，使用 WrapRowToDictionary
/// </summary>
/// <param name="sql"></param>
/// <param name="paramArr"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, SQLiteParameter[] paramArr)
if (sql == null)
throw new ArgumentNullException("sql=null");
this.EnsureConnection();
using (RWL[LockName].Read())
using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
if (paramArr != null)
cmd.Parameters.AddRange(paramArr);
object reader = cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.Dispose();
return reader;
catch (Exception)
return null;
/// <summary>
/// 查询一行记录，无结果时返回 null，conditionCol = null 时将忽略条件，直接执行 select * from table
/// </summary>
/// <param name="table">表名</param>
/// <param name="conditionCol"></param>
/// <param name="conditionVal"></param>
/// <returns></returns>
public object QueryOne(string table, string conditionCol, object conditionVal)
if (table == null)
throw new ArgumentNullException("table=null");
this.EnsureConnection();
string sql = "select * from " + table;
if (conditionCol != null)
sql += " where " + conditionCol + "=@" + conditionCol;
object result = ExecuteScalar(sql, new SQLiteParameter[] { new SQLiteParameter(conditionCol, conditionVal) });
return result;
#endregion
#region 增 删 改
/// <summary>
/// 执行 insert into 语句
/// </summary>
/// <param name="table"></param>
/// <param name="entity"></param>
/// <returns></returns>
public int InsertData(string table, Dictionary<string, object> entity)
if (table == null)
throw new ArgumentNullException("table=null");
this.EnsureConnection();
string sql = BuildInsert(table, entity);
return this.ExecuteNonQuery(sql, BuildParamArray(entity));
/// <summary>
/// 执行 update 语句，注意：如果 where = null，那么 whereParams 也为 null，
/// </summary>
/// <param name="table">表名</param>
/// <param name="entity">要修改的列名和列名的值</param>
/// <param name="where">查找符合条件的列</param>
/// <param name="whereParams">where条件中参数的值</param>
/// <returns></returns>
public int Update(string table, Dictionary<string, object> entity, string where, SQLiteParameter[] whereParams)
if (table == null)
throw new ArgumentNullException("table=null");
this.EnsureConnection();
string sql = BuildUpdate(table, entity);
SQLiteParameter[] parameter = BuildParamArray(entity);
if (where != null)
sql += " where " + where;
if (whereParams != null)
SQLiteParameter[] newArr = new SQLiteParameter[(parameter.Length + whereParams.Length)];
Array.Copy(parameter, newArr, parameter.Length);
Array.Copy(whereParams, 0, newArr, parameter.Length, whereParams.Length);
parameter = newArr;
return this.ExecuteNonQuery(sql, parameter);
/// <summary>
/// 执行 delete from table 语句，where不必包含'where'关键字，where = null 时将忽略 whereParams
/// </summary>
/// <param name="table"></param>
/// <param name="where"></param>
/// <param name="whereParams"></param>
/// <returns></returns>
public int Delete(string table, string where, SQLiteParameter[] whereParams)
if (table == null)
throw new ArgumentNullException("table=null");
this.EnsureConnection();
string sql = "delete from " + table + " ";
if (where != null)
sql += "where " + where;
return ExecuteNonQuery(sql, whereParams);
/// <summary>
/// 将 Dictionary 类型数据 转换为 SQLiteParameter[] 类型
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SQLiteParameter[] BuildParamArray(Dictionary<string, object> entity)
List<SQLiteParameter> list = new List<SQLiteParameter>();
foreach (string key in entity.Keys)
list.Add(new SQLiteParameter(key, entity[key]));
if (list.Count == 0)
return null;
return list.ToArray();
/// <summary>
/// 将 Dictionary 类型数据 转换为 插入数据 的 SQL语句
/// </summary>
/// <param name="table">表名</param>
/// <param name="entity">字典</param>
/// <returns></returns>
private string BuildInsert(string table, Dictionary<string, object> entity)
StringBuilder buf = new StringBuilder();
buf.Append("insert into ").Append(table);
buf.Append(" (");
foreach (string key in entity.Keys)
buf.Append(key).Append(",");
buf.Remove(buf.Length - 1, 1); // 移除最后一个,
buf.Append(") ");
buf.Append("values(");
foreach (string key in entity.Keys)
buf.Append("@").Append(key).Append(","); // 创建一个参数
buf.Remove(buf.Length - 1, 1);
buf.Append(") ");
return buf.ToString();
/// <summary>
/// 将 Dictionary 类型数据 转换为 修改数据 的 SQL语句
/// </summary>
/// <param name="table">表名</param>
/// <param name="entity">字典</param>
/// <returns></returns>
private string BuildUpdate(string table, Dictionary<string, object> entity)
StringBuilder buf = new StringBuilder();
buf.Append("update ").Append(table).Append(" set ");
foreach (string key in entity.Keys)
buf.Append(key).Append("=").Append("@").Append(key).Append(",");
buf.Remove(buf.Length - 1, 1);
buf.Append(" ");
return buf.ToString();
#endregion
UsingLock.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
namespace MySQLiteHelper
/// <summary>
/// 使用using代替lock操作的对象，可指定写入和读取锁定模式
/// </summary>
public sealed class ClsLock
#region 内部类
/// <summary>
/// 利用IDisposable的using语法糖方便的释放锁定操作内部类
/// </summary>
private struct Lock : IDisposable
/// <summary>
/// 读写锁对象
/// </summary>
private readonly ReaderWriterLockSlim _Lock;
/// <summary>
/// 是否为写入模式
/// </summary>
private bool _IsWrite;
/// <summary>
/// 利用IDisposable的using语法糖方便的释放锁定操作构造函数
/// </summary>
/// <param name="rwl">读写锁</param>
/// <param name="isWrite">写入模式为true，读取模式为false</param>
public Lock(ReaderWriterLockSlim rwl, bool isWrite)
_Lock = rwl;
_IsWrite = isWrite;
/// <summary>
/// 释放对象时退出指定锁定模式
/// </summary>
public void Dispose()
if (_IsWrite)
if (_Lock.IsWriteLockHeld)
_Lock.ExitWriteLock();
if (_Lock.IsReadLockHeld)
_Lock.ExitReadLock();
/// <summary>
/// 空的可释放对象，免去了调用时需要判断是否为null的问题内部类
/// </summary>
private class Disposable : IDisposable
/// <summary>
/// 空的可释放对象
/// </summary>
public static readonly Disposable Empty = new Disposable();
/// <summary>
/// 空的释放方法
/// </summary>
public void Dispose() { }
#endregion
/// <summary>
/// 读写锁
/// </summary>
private readonly ReaderWriterLockSlim _LockSlim = new ReaderWriterLockSlim();
/// <summary>
/// 使用using代替lock操作的对象，可指定写入和读取锁定模式构造函数
/// </summary>
public ClsLock()
Enabled = true;
/// <summary>
/// 是否启用，当该值为false时，Read()和Write()方法将返回 Disposable.Empty
/// </summary>
public bool Enabled { get; set; }
/// <summary>
/// 进入读取锁定模式，该模式下允许多个读操作同时进行，
/// 退出读锁请将返回对象释放，建议使用using语块,
/// Enabled为false时，返回Disposable.Empty，
/// 在读取或写入锁定模式下重复执行，返回Disposable.Empty;
/// </summary>
public IDisposable Read()
if (Enabled == false || _LockSlim.IsReadLockHeld || _LockSlim.IsWriteLockHeld)
return Disposable.Empty;
_LockSlim.EnterReadLock();
return new Lock(_LockSlim, false);
/// <summary>
/// 进入写入锁定模式,该模式下只允许同时执行一个读操作，
/// 退出读锁请将返回对象释放，建议使用using语块，
/// Enabled为false时，返回Disposable.Empty，
/// 在写入锁定模式下重复执行，返回Disposable.Empty
/// </summary>
/// <exception cref="NotImplementedException">读取模式下不能进入写入锁定状态</exception>
public IDisposable Write()
if (Enabled == false || _LockSlim.IsWriteLockHeld)
return Disposable.Empty;
else if (_LockSlim.IsReadLockHeld)
throw new NotImplementedException("读取模式下不能进入写入锁定状态");
_LockSlim.EnterWriteLock();
return new Lock(_LockSlim, true);
Form1.cs
using MySQLiteHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
namespace SQLiteDemo
public partial class Form1 : Form
public Form1()
InitializeComponent();
private SQLiteHelper SQLiteHelpers = null;
private const string DBAddress = "D:\\SQLiteData\\test_record.db3";
private void Form1_Load(object sender, EventArgs e)
SQLiteHelpers = new SQLiteHelper(DBAddress,"123456");
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_OpenDB_Click(object sender, EventArgs e)
SQLiteHelpers.Open();
Label_DBOpenState.Text = "打开";
/// <summary>
/// 关闭数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_CloseDB_Click(object sender, EventArgs e)
SQLiteHelpers.Close();
Label_DBOpenState.Text = "关闭";
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_Query_Click(object sender, EventArgs e)
SQLiteParameter[] parameter = new SQLiteParameter[]
new SQLiteParameter("address", "济南")
string sql = "SELECT * FROM student WHERE address = @address";
DataSet dataSet = SQLiteHelpers.ExecuteDataSet(sql, parameter);
if (dataSet != null)
dataGridView1.DataSource = dataSet.Tables[0];
/// <summary>
/// 插入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_Add_Click(object sender, EventArgs e)
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("ID", 6);
dic.Add("name", "王二麻子");
dic.Add("age", 44);
dic.Add("address", "陕西");
int result = SQLiteHelpers.InsertData("student", dic);
Console.WriteLine("插入结果，受影响的行数：" + result);
/// <summary>
/// 修改数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_Modify_Click(object sender, EventArgs e)
Dictionary<string, object> dic = new Dictionary<string, object>();
//将列名 name 的值改为 “猴子”
dic.Add("name", "猴子");
//将列名 address 的值改为 花果山
dic.Add("address", "花果山");
//where条件
string where = "ID = @ID AND age = @Age";
//where条件中对应的参数
SQLiteParameter[] parameter = new SQLiteParameter[]
new SQLiteParameter("ID", 4),
new SQLiteParameter("Age",23)
int result = SQLiteHelpers.Update("student", dic, where, parameter);
Console.WriteLine("修改结果，受影响的行数：" + result);
/// <summary>
/// 删除数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_Delete_Click(object sender, EventArgs e)
//where条件
string where = "ID = @ID";
//where条件中对应的参数
SQLiteParameter[] parameter = new SQLiteParameter[]
new SQLiteParameter("ID", 6),
int result = SQLiteHelpers.Delete("student", where, parameter);
Console.WriteLine("删除结果，受影响的行数：" + result);
/// <summary>
/// 判断表是否存在
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button_TableExists_Click(object sender, EventArgs e)
string title = "dddd";
bool result = SQLiteHelpers.TableExists(title);
Console.WriteLine(string.Format("{0} 表是否存在，结果：{1}", title, result));
//输出各表中的数据
//public static void PrintValues(DataSet ds)
// foreach (DataTable table in ds.Tables)
// {
// Console.WriteLine("表名称：" + table.TableName);
// foreach (DataRow row in table.Rows)
// {
// foreach (DataColumn column in table.Columns)
// {
// Console.Write(row[column] + "");
// }
// Console.WriteLine();
// }
// }
以上就是C#操作SQLite实现数据的增删改查的详细内容，更多关于C# SQLite增删改查的资料请关注脚本之家其它相关文章！
您可能感兴趣的文章:
WCF基础介绍并创建简单应用程序2022-01-01
C#通过标签软件Bartender的ZPL命令打印条码2022-01-01
C#使用标签软件Bartender打印标签模板2022-01-01
如何用C#实现SAGA分布式事务2022-01-01
美国设下计谋,用娘炮文化重塑日本,已影响至中国2021-11-19
时空伴随者是什么意思?时空伴随者介绍2021-11-09
工信部称网盘企业免费用户最低速率应满足基本下载需求，天翼云盘回应：坚决支持，始终2021-11-05
2022年放假安排出炉：五一连休5天 2022年所有节日一览表2021-10-26
电脑版 - 返回首页
2006-2023 脚本之家 JB51.Net , All Rights Reserved.
苏ICP备14036222号
