本类定义了对SQLite数据库的常用操作,包括创建数据库连接,创建表,对表记录的增、删、改、查,可满足常见的数据库操作需求。
在创建数据库连接时,构造SQLiteConnection对象时,指定参数storeDateTimeAsTicks为true,则日期类型将按照ticks来存储,即保存为BIGINT类型。
类定义如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace XXXWebService.Common
{
/// <summary>
/// Sqlite Dot Net
/// </summary>
public class SDNConn
{
/// <summary>
/// 创建SQLite连接
/// .db文件不存在时创建,存在时读写,日期类型存为ticks
/// </summary>
/// <param name="sDbPath">.db文件路径</param>
/// <returns>数据库的连接</returns>
public static SDNConn CreateInstance(string sDbPath)
{
return new SDNConn() { Connection = new SQLite.SQLiteConnection(sDbPath, SQLite.SQLiteOpenFlags.ReadWrite | SQLite.SQLiteOpenFlags.Create | SQLite.SQLiteOpenFlags.SharedCache, true) };
}
/// <summary>
/// 数据库连接
/// </summary>
public SQLite.SQLiteConnection Connection { get; set; }
/// <summary>
/// 创建表
/// </summary>
/// <typeparam name="T">表结构</typeparam>
/// <param name="createFlags">创建标记</param>
public void CreateTable<T>(SQLite.CreateFlags createFlags = SQLite.CreateFlags.None)
{
Connection.CreateTable<T>(createFlags);
}
/// <summary>
/// 根据主键查询记录是否存在
/// </summary>
/// <typeparam name="T">类型(表结构)</typeparam>
/// <param name="aPrimaryKey">主键值</param>
/// <returns>是否存在</returns>
public bool Exists<T>(object aPrimaryKey) where T : new()
{
return null != Connection.Find<T>(aPrimaryKey);
}
/// <summary>
/// 根据主键获取记录
/// </summary>
/// <typeparam name="T">类型(表结构)</typeparam>
/// <param name="aPrimaryKey">主键值</param>
/// <returns>记录对象(一条)</returns>
public T Get<T>(object aPrimaryKey) where T : new()
{
return Connection.Get<T>(aPrimaryKey);
}
/// <summary>
/// 根据条件查询记录
/// </summary>
/// <typeparam name="T">类型(表结构)</typeparam>
/// <param name="query">查询条件(支持字符串格式化)</param>
/// <param name="args">参数值</param>
/// <returns>记录集合</returns>
public List<T> Query<T>(string query, params object[] args) where T : new()
{
return Connection.Query<T>(query, args);
}
/// <summary>
/// 根据评分查询记录
/// </summary>
/// <typeparam name="T">类型(表结构)</typeparam>
/// <param name="aPrimaryKey">主键值</param>
/// <returns>记录(一条)</returns>
public T Select<T>(object aPrimaryKey) where T : new()
{
return Connection.Find<T>(aPrimaryKey);
}
/// <summary>
/// 插入新记录
/// </summary>
/// <param name="aRecord">记录对象(单条或多条)</param>
/// <returns>受影响记录条数</returns>
public int Insert(object aRecord)
{
return Connection.Insert(aRecord);
}
/// <summary>
/// 插入或修改记录
/// </summary>
/// <param name="aRecord">记录对象(单条或多条)</param>
/// <returns>受影响记录条数</returns>
public int InsertOrReplace(object aRecord)
{
int i = Connection.InsertOrReplace(aRecord);
Connection.Commit();
return i;
}
/// <summary>
/// 删除记录
/// </summary>
/// <param name="aRecord">记录对象(单条或多条)</param>
/// <returns>受影响记录条数</returns>
public int Delete(object aRecord)
{
return Connection.Delete(aRecord);
}
/// <summary>
/// 更新记录
/// </summary>
/// <param name="aRecord">记录对象(单条或多条)</param>
/// <returns>受影响记录条数</returns>
public int Update(object aRecord)
{
return Connection.Update(aRecord);
}
}
}