依赖:System.Data.SQLite.dll
SQLiteHelper代码:
测试类:
使用TestDrivern.NET
author:pcenshao
uri:http://www.todayx.org
SQLiteHelper代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SQLite;
namespace SQLite {
/// <summary>
/// SQLite数据库操作帮助类
/// 提供一系列方便的调用:
/// Execute,Save,Update,Delete...
///
/// 不是线程安全的
///
/// @author pcenshao
/// </summary>
public class SQLiteHelper {
private bool _showSql = true;
/// <summary>
/// 是否输出生成的SQL语句
/// </summary>
public bool ShowSql {
get {
return this._showSql;
}
set {
this._showSql = value;
}
}
private readonly string _dataFile;
private SQLiteConnection _conn;
public SQLiteHelper(string dataFile) {
if (dataFile == null)
throw new ArgumentNullException("dataFile=null");
this._dataFile = dataFile;
}
/// <summary>
/// <para>打开SQLiteManager使用的数据库连接</para>
/// </summary>
public void Open() {
this._conn = OpenConnection(this._dataFile);
}
public void Close() {
if (this._conn != null) {
this._conn.Close();
}
}
/// <summary>
/// <para>安静地关闭连接,保存不抛出任何异常</para>
/// </summary>
public void CloseQuietly() {
if (this._conn != null) {
try {
this._conn.Close();
} catch { }
}
}
/// <summary>
/// <para>创建一个连接到指定数据文件的SQLiteConnection,并Open</para>
/// <para>如果文件不存在,创建之</para>
/// </summary>
/// <param name="dataFile"></param>
/// <returns></returns>
public static SQLiteConnection OpenConnection(string dataFile) {
if (dataFile == null)
throw new ArgumentNullException("dataFile=null");
if (!File.Exists(dataFile)) {
SQLiteConnection.CreateFile(dataFile);
}
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder();
conStr.DataSource = dataFile;
conn.ConnectionString = conStr.ToString();
conn.Open();
return conn;
}
/// <summary>
/// <para>读取或设置SQLiteManager使用的数据库连接</para>
/// </summary>
public SQLiteConnection Connection {
get {
return this._conn;
}
set {
if (value == null) {
throw new ArgumentNullException();
}
this._conn = value;
}
}
protected void EnsureConnection() {
if (this._conn == null) {
throw new Exception("SQLiteManager.Connection=null");
}
}
public string GetDataFile() {
return this._dataFile;
}
/// <summary>
/// <para>判断表table是否存在</para>
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public bool TableExists(string table) {
if (table == null)
throw new ArgumentNullException("table=null");
this.EnsureConnection();
// SELECT count(*) FROM sqlite_master WHERE type='table' AND name='test';
SQLiteCommand cmd = new SQLiteCommand("SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name=@tableName ");
cmd.Connection = this.Connection;
cmd.Parameters.Add(new SQLiteParameter("tableName", table));
SQLiteDataReader reader = cmd.ExecuteReader();
reader.Read();
int c = reader.GetInt32(0);
reader.Close();
reader.Dispose();
cmd.Dispose();
//return false;
return c == 1;
}
/// <summary>
/// <para>执行SQL,返回受影响的行数</para>
/// <para>可用于执行表创建语句</para>
/// <para>paramArr == null 表示无参数</para>
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql,SQLiteParameter[] paramArr) {
if (sql == null) {
throw new ArgumentNullException("sql=null");
}
this.EnsureConnection();
if (this.ShowSql) {
Console.WriteLine("SQL: " + sql);
}
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = sql;
if (paramArr != null) {
foreach (SQLiteParameter p in paramArr) {
cmd.Parameters.Add(p);
}
}
cmd.Connection = this.Connection;
int c = cmd.ExecuteNonQuery();
cmd.Dispose();
return c;
}
/// <summary>
/// <para>执行SQL,返回SQLiteDataReader</para>
/// <para>返回的Reader为原始状态,须自行调用Read()方法</para>
/// <para>paramArr=null,则表示无参数</para>
/// </summary>
/// <param name="sql"></param>
/// <param name="paramArr"></param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string sql,SQLiteParameter[] paramArr) {
return (SQLiteDataReader)ExecuteReader(sql, paramArr, (ReaderWrapper)null);
}
/// <summary>
/// <para>执行SQL,如果readerWrapper!=null,那么将调用readerWrapper对SQLiteDataReader进行包装,并返回结果</para>
/// </summary>
/// <param name="sql"></param>
/// <param name="paramArr">null 表示无参数</param>
/// <param name="readerWrapper">null 直接返回SQLiteDataReader</param>
/// <returns></returns>
public object ExecuteReader(string sql, SQLiteParameter[] paramArr, ReaderWrapper readerWrapper) {
if (sql == null) {
throw new ArgumentNullException("sql=null");
}
this.EnsureConnection();
SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection);
if (paramArr != null) {
foreach (SQLiteParameter p in paramArr) {
cmd.Parameters.Add(p);
}
}
SQLiteDataReader reader = cmd.ExecuteReader();
object result = null;
if (readerWrapper != null) {
result = readerWrapper(reader);
} else {
result = reader;
}
reader.Close();
reader.Dispose();
cmd.Dispose();
return result;
}
/// <summary>
/// <para>执行SQL,返回结果集,使用RowWrapper对每一行进行包装</para>
/// <para>如果结果集为空,那么返回空List (List.Count=0)</para>
/// <para>rowWrapper = null时,使用WrapRowToDictionary</para>
/// </summary>
/// <param name="sql"></param>
/// <param name="paramArr"></param>
/// <param name="rowWrapper"></param>
/// <returns></returns>
public List<object> ExecuteRow(string sql, SQLiteParameter[] paramArr, RowWrapper rowWrapper) {
if (sql == null) {
throw new ArgumentNullException("sql=null");
}
this.EnsureConnection();
SQLiteCommand cmd = new SQLiteCommand(sql, this.Connection);
if (paramArr != null) {
foreach (SQLiteParameter p in paramArr) {
cmd.Parameters.Add(p);
}
}
if (rowWrapper == null) {
rowWrapper = new RowWrapper(SQLiteHelper.WrapRowToDictionary);
}
SQLiteDataReader reader = cmd.ExecuteReader();
List<object> result = new List<object>();
if (reader.HasRows) {
int rowNum = 0;
while (reader.Read()) {
object row = rowWrapper(rowNum, reader);
result.Add(row);
rowNum++;
}
}
reader.Close();
reader.Dispose();
cmd.Dispose();
return result;
}
public static object WrapRowToDictionary(int rowNum, SQLiteDataReader reader) {
int fc = reader.FieldCount;
Dictionary<string, object> row = new Dictionary<string, object>();
for (int i = 0; i < fc; i++) {
string fieldName = reader.GetName(i);
object value = reader.GetValue(i);
row.Add(fieldName, value);
}
return row;
}
/// <summary>
/// <para>执行insert into语句</para>
/// </summary>
/// <param name="table"></param>
/// <param name="entity"></param>
/// <returns></returns>
public int Save(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));
}
private static 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();
}
private static 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();
}
private static 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();
}
/// <summary>
/// <para>执行update语句</para>
/// <para>where参数不必要包含'where'关键字</para>
///
/// <para>如果where=null,那么忽略whereParams</para>
/// <para>如果where!=null,whereParams=null,where部分无参数</para>
/// </summary>
/// <param name="table"></param>
/// <param name="entity"></param>
/// <param name="where"></param>
/// <param name="whereParams"></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[] arr = BuildParamArray(entity);
if (where != null) {
sql += " where " + where;
if (whereParams != null) {
SQLiteParameter[] newArr = new SQLiteParameter[arr.Length + whereParams.Length];
Array.Copy(arr, newArr, arr.Length);
Array.Copy(whereParams, 0, newArr, arr.Length, whereParams.Length);
arr = newArr;
}
}
return this.ExecuteNonQuery(sql, arr);
}
/// <summary>
/// <para>查询一行记录,无结果时返回null</para>
/// <para>conditionCol = null时将忽略条件,直接执行select * from table </para>
/// </summary>
/// <param name="table"></param>
/// <param name="conditionCol"></param>
/// <param name="conditionVal"></param>
/// <returns></returns>
public Dictionary<string, 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;
}
if (this.ShowSql) {
Console.WriteLine("SQL: " + sql);
}
List<object> list = this.ExecuteRow(sql, new SQLiteParameter[] {
new SQLiteParameter(conditionCol,conditionVal)
}, null);
if (list.Count == 0)
return null;
return (Dictionary<string, object>)list[0];
}
/// <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 this.ExecuteNonQuery(sql, whereParams);
}
}
/// <summary>
/// 在SQLiteManager.Execute方法中回调,将SQLiteDataReader包装成object
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public delegate object ReaderWrapper(SQLiteDataReader reader);
/// <summary>
/// 将SQLiteDataReader的行包装成object
/// </summary>
/// <param name="rowNum"></param>
/// <param name="reader"></param>
/// <returns></returns>
public delegate object RowWrapper(int rowNum,SQLiteDataReader reader);
}
测试类:
使用TestDrivern.NET
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
namespace SQLite {
class Test {
private SQLiteHelper _mgr;
public Test() {
this._mgr = new SQLiteHelper("sqlite.db");
this._mgr.Open();
}
public void TestTableExists() {
Console.WriteLine("表test是否存在: " + this._mgr.TableExists("test"));
}
public void TestExecuteRow() {
List<object> list = this._mgr.ExecuteRow("select * from test", null, null);
foreach (object o in list) {
Dictionary<string, object> d = (Dictionary<string, object>) o;
foreach (string k in d.Keys) {
Console.Write(k + "=" + d[k] + ",");
}
Console.WriteLine();
}
}
public void TestSave() {
Dictionary<string, object> entity = new Dictionary<string, object>();
entity.Add("username", "u1");
entity.Add("password", "p1");
this._mgr.Save("test", entity);
}
public void TestUpdate() {
Dictionary<string, object> entity = new Dictionary<string, object>();
entity.Add("username", "u1");
entity.Add("password", "123456");
int c = this._mgr.Update("test", entity, "username=@username", new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("username","u1")
});
Console.WriteLine(c);
}
public void TestQueryOne() {
Dictionary<string, object> entity = this._mgr.QueryOne("test", "username", "a");
foreach (string k in entity.Keys) {
Console.Write(k + "=" + entity[k] + ",");
}
}
public void TestDelete() {
int c = this._mgr.Delete("test", "username=@username", new SQLiteParameter[] {
new SQLiteParameter("username","a")
});
Console.WriteLine("c=" + c);
}
public static void Test0() {
Test t = new Test();
t.TestTableExists();
t.TestExecuteRow();
//t.TestSave();
//t.TestUpdate();
// t.TestQueryOne();
t.TestDelete();
}
}
}
author:pcenshao
uri:http://www.todayx.org
分享到：
发表评论
相关推荐
一些小型的应用程序需要使用到数据库，sqlite可以说是最好的选择。这里个人整理了一般简单通用的操作类SQLiteHelper
是使用sqlite封装好的帮助类 ，有执行sql语句和执行带参数的sql语句及一次执行多条（事务）及执行带存储过程参数的的SQL语句等基础操作。
这个压缩包包含两个关键文件，一个是`SqliteHelper.cs`，它很可能是一个C#类，提供了与Sqlite数据库进行交互的通用方法。这个类可能包含了连接数据库、执行SQL语句、插入、更新、删除和查询数据等基本操作的封装。...
SQLiteHelper dbHelper = new SQLiteHelper(); string sql = "INSERT INTO TableName (Column1, Column2) VALUES (@Value1, @Value2)"; int result = dbHelper.ExecuteNonQuery(sql, new object[] { value1, value2 ...
SQLiteHelper sqlite = new SQLiteHelper ( " C:\database.db " ); 其中C：\ database.db是数据库的路径。 然后只需调用所需的方法，例如获取字符串 string StrValue = Convert . ToString ( sqlite . Get ( " ...
安装`System.Data.SQLite`库后，我们可以在C#项目中使用SQLite的相关类和方法。 批量更新的基本思路是将多条SQL更新语句组合在一起，一次性提交到数据库，以减少网络往返时间和数据库事务开销。在C#中，我们可以...
总之，SQLiteHelper类在C#中为SQLite数据库的操作提供了一种方便的抽象层，使得开发人员能够更高效地执行SQL命令，同时保持代码的整洁和安全。通过使用参数化查询、静态方法和SQLiteCommand对象，开发者可以轻松地...
这可以通过`Stopwatch`类来实现，它可以精确地测量代码执行的时间，帮助优化并发策略。 **第一演示项目（firstdemo）**： 这个项目的源代码可能包含了如何在C#中使用多线程和同步锁与SQLite交互的示例。通常，它会...
本篇将深入探讨如何在C#中利用SQLiteHelper类来实现类似SqlHelper的功能，以方便地与SQLite数据库进行交互。 首先，SqlHelper类在.NET框架中是用于简化SQL Server数据库操作的一个工具类，它提供了一系列方法来执行...
SQLiteHelper是Android开发中常用的工具类，用于简化SQLite数据库的操作。SQLite是一款轻量级的、嵌入式的、关系型数据库，适用于移动设备和资源有限的环境。它的优势在于体积小、速度快速、易于集成，因此在Android...
C#连接sqlite分页显示详细实用教程 源码下载地址：http://download.csdn.net/detail/min20062020/5491029
本文实例讲述了C#解决SQlite并发异常问题的方法。分享给大家供大家参考，具体如下： 使用C#访问sqlite时，常会遇到多线程并发导致SQLITE数据库损坏的问题。 SQLite是文件级别的数据库，其锁也是文件级别的：多个线程...
SQLiteHelper， SQLite数据库的SQLiteHelper帮助类 [C#] 改进SqliteHelper, 减少拼接SQL语句
8. **数据模型映射**：使用 ORM（对象关系映射）工具如 Entity Framework 或 Dapper，可以与 SQLiteHelper 结合，自动将数据库表映射到 C# 类，简化数据访问代码。 9. **错误处理和日志记录**：SQLiteHelper 可能会...
接下来，我们创建一个SQLite数据库操作的工具类（例如命名为SqliteHelper），该类主要包含以下功能： 1. 连接数据库：使用`SQLiteConnection`类初始化数据库连接，如`new SQLiteConnection("Data Source=mydb....
SqliteHelper.cs是封装了C#项目里对sqlite的-增删改查等基本操作的小巧类文件，希望对大家有帮助。
SQLiteHelper类文件，类似SQLHelper
修改过的SQLiteHelper类,使用到轻量级数据库,附加到程序直接使用
SQLiteHelper工具类通常包含ExecuteNonQuery()方法用于执行不返回结果的SQL（如INSERT、UPDATE、DELETE），ExecuteScalar()用于返回单个值（如SELECT COUNT(*)），以及ExecuteReader()用于处理返回多行数据的结果集...