//添加 System.Data.SQLite.dll 方可使用
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
namespace Deep.SQLite
{
/// <summary>
/// SQLiteUtil 为 SQLite数据库帮助静态类
/// </summary>
public static class SQLiteUtil
{
#region 创建数据库
/// <summary>
/// 创建 SQLite 数据库
/// </summary>
/// <param name="filename">文件名,如 "DB.sqlite"</param>
public static void CreateDb(string filename)
{
SQLiteConnection.CreateFile(filename);
}
#endregion
#region 通用执行语句
/// <summary>
/// 执行数据库操作(新增、更新或删除)
/// </summary>
/// <param name="connStr">数据库连接字符串,如"Data Source=Db.sqlite;Version=3;"</param>
/// <param name="cmdText">sql 字符串</param>
/// <param name="cmdType">sql 命令类型</param>
/// <param name="cmdParams">命令参数</param>
/// <param name="useTrans">是否使用数据库事务</param>
/// <returns>所受影响的行数</returns>
public static int ExecuteNonQuery(string connStr,string cmdText, SQLiteParameter[] cmdParams = null, CommandType cmdType = CommandType.Text,bool useTrans = false)
{
using (var sqlConn = new SQLiteConnection(connStr))
{
var result = 0;
var cmd = PrepareCommand(sqlConn, cmdText, cmdParams, cmdType);
if (useTrans)
{
var trans = sqlConn.BeginTransaction();
cmd.Transaction = trans;
try
{
result = cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
}
else
{
result = cmd.ExecuteNonQuery();
}
return result;
}
}
/// <summary>
/// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据
/// </summary>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="cmdText">sql 字符串</param>
/// <param name="cmdType">sql 命令类型</param>
/// <param name="cmdParams">命令参数</param>
/// <param name="useTrans">是否使用数据库事务</param>
/// <returns>查询所得的第1行第1列数据</returns>
public static object ExecuteScalar(string connStr, string cmdText, SQLiteParameter[] cmdParams = null, CommandType cmdType = CommandType.Text, bool useTrans = false)
{
using (var sqlConn = new SQLiteConnection(connStr))
{
var cmd = PrepareCommand(sqlConn, cmdText, cmdParams, cmdType);
if (useTrans)
{
var trans = sqlConn.BeginTransaction();
cmd.Transaction = trans;
try
{
return cmd.ExecuteScalar();
}
catch
{
trans.Rollback();
return null;
}
}
else
{
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
/// </summary>
/// <param name="conn">Connection对象</param>
/// <param name="cmdType">SQL字符串执行类型</param>
/// <param name="cmdText">SQL Text</param>
/// <param name="cmdParms">SQLiteParameter 参数</param>
/// <param name="useTrans">是否使用事务</param>
/// <returns>数据库命令对象</returns>
public static SQLiteCommand PrepareCommand(SQLiteConnection conn, string cmdText, SQLiteParameter[] cmdParms, CommandType cmdType = CommandType.Text, bool useTrans = false)
{
if (conn.State != ConnectionState.Open)
conn.Open();
var cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (useTrans)
{
cmd.Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
}
if (cmdParms != null)
{
cmd.Parameters.AddRange(cmdParms);
}
return cmd;
}
#endregion
#region 常见数据库对象
/// <summary>
/// 执行数据库查询,返回SqlDataReader对象
/// </summary>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="cmdText">sql 字符串</param>
/// <param name="cmdType">sql 命令类型</param>
/// <param name="cmdParams">命令参数</param>
/// <param name="useTrans">是否使用数据库事务</param>
/// <returns>SqlDataReader对象</returns>
public static SQLiteDataReader GetDataReader(string connStr,string cmdText, SQLiteParameter[] cmdParams = null, CommandType cmdType = CommandType.Text)
{
return PrepareCommand(new SQLiteConnection(connStr), cmdText, cmdParams, cmdType).ExecuteReader();
}
/// <summary>
/// 执行数据库查询,返回DataSet对象
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="cmdText">sql 查询命令字符串</param>
/// <param name="cmdParams">命令参数</param>
/// <param name="cmdType">sql 命令类型</param>
/// <returns>DataSet对象</returns>
public static DataSet GetDataSet(string connStr,string cmdText, SQLiteParameter[] cmdParams = null, CommandType cmdType = CommandType.Text)
{
using (var con = new SQLiteConnection(connStr))
{
var ds = new DataSet();
var cmd = PrepareCommand(con, cmdText, cmdParams,cmdType);
var sda = new SQLiteDataAdapter(cmd);
cmd.ExecuteNonQuery();
sda.Fill(ds);
return ds;
}
}
/// <summary>
/// 执行数据库查询,返回DataSet对象
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="cmdText">sql 查询命令字符串</param>
/// <param name="cmdParams">命令参数</param>
/// <param name="cmdType">sql 命令类型</param>
/// <returns>DataTable对象</returns>
public static DataTable GetDataTable(string connStr, string cmdText, SQLiteParameter[] cmdParams = null, CommandType cmdType = CommandType.Text)
{
return GetDataSet(connStr,cmdText,cmdParams,cmdType).Tables[0];
}
#endregion
/ <summary>
/ 通用分页查询方法
/ </summary>
/ <param name="connectionString">连接字符串</param>
/ <param name="tableName">表名</param>
/ <param name="strColumns">查询字段名</param>
/ <param name="conditions">where条件</param>
/ <param name="strOrder">排序条件</param>
/ <param name="pageSize">每页数据数量</param>
/ <param name="currentIndex">当前页数</param>
/ <param name="recordOut">数据总量</param>
/ <returns>DataTable数据表</returns>
//public static DataTable SelectPaging(string tableName, string strColumns, string conditions, string strOrder, int pageSize, int currentIndex, out int recordOut, string connectionString = null)
//{
// if (!string.IsNullOrWhiteSpace(conditions) && conditions.IndexOf("where", 0, StringComparison.CurrentCultureIgnoreCase) == -1)
// {
// conditions = "WHERE " + conditions;
// }
// var dt = new DataTable();
// recordOut = Convert.ToInt32(ExecuteScalar("select count(*) from " + tableName, co: connectionString));
// string pagingTemplate = "select {0} from {1} {2} order by {3} limit {4} offset {5} ";
// int offsetCount = (currentIndex - 1) * pageSize;
// string cmdText = String.Format(pagingTemplate, strColumns, tableName, conditions, strOrder, pageSize, offsetCount);
// using (var reader = ExecuteReader(cmdText, connectionString: connectionString))
// {
// if (reader != null)
// {
// dt.Load(reader);
// }
// }
// return dt;
//}
#region 工具方法
/// <summary>
/// 构造 SQLite INSERT 语句,并返回构造好的 SQL 语句和 SQL 参数列表
/// </summary>
/// <param name="tableName">要插入的表的表名</param>
/// <param name="columnValues">要插入值的列和值对</param>
/// <param name="parameters">构造好的 SQL 参数,已经给予赋值</param>
/// <returns>返回构造好的 SQLite INSERT 语句</returns>
public static string GetInsertSql(string tableName, Dictionary<string, object> columnValues, out List<SQLiteParameter> parameters)
{
if (columnValues == null || columnValues.Keys.Count <= 0)
{
throw new ArgumentException("columnValues");
}
var sb1 = new StringBuilder();
var sb2 = new StringBuilder();
parameters = new List<SQLiteParameter>();
foreach (var column in columnValues)
{
sb1.Append(column.Key);
sb1.Append(",");
sb2.Append("@");
sb2.Append(column.Key);
sb2.Append(",");
parameters.Add(new SQLiteParameter("@" + column.Key, column.Value));
}
return "INSERT INTO " + tableName + "(" + sb1.Remove(sb1.Length - 1, 1) + ") VALUES(" + sb2.Remove(sb2.Length - 1, 1) + ")";
}
/// <summary>
/// 构造 SQLite UPDATE 语句,并返回构造好的 SQL 语句和 SQL 参数列表
/// </summary>
/// <param name="tableName">更新的表的表名</param>
/// <param name="columnValues">要更新值的列和值对</param>
/// <param name="parameters">构造好的 SQL 参数,已经给予赋值</param>
/// <param name="where">更新的其他 WHERE 条件,使用需自带“where ”关键字</param>
/// <returns>返回构造好的 SQLite UPDATE 语句</returns>
public static string GetUpdateSql(string tableName, Dictionary<string, object> columnValues, out List<SQLiteParameter> parameters, string where = "")
{
if (columnValues == null || columnValues.Keys.Count <= 0)
{
throw new ArgumentException("columnValues");
}
var sb = new StringBuilder();
parameters = new List<SQLiteParameter>();
foreach (var column in columnValues)
{
sb.AppendFormat("{0}=@{0},", column.Key);
parameters.Add(new SQLiteParameter("@" + column.Key, column.Value));
}
return "UPDATE " + tableName + " SET " + sb.Remove(sb.Length - 1, 1) + " " + where;
}
/// <summary>
/// 构建 SQL "age IN(18,19,27)" 格式的 IN 语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">值列表</param>
/// <param name="columnName">列名</param>
/// <param name="isString">泛型类型T 是否应转为SQL字符串格式</param>
/// <returns>返回构造好的 SQL IN 语句</returns>
public static string In<T>(string columnName, IEnumerable<T> list, bool isString = false)
{
var tmp = String.Empty;
var items = list as T[] ?? list.ToArray();
if (list != null && list.Any())
{
var sb = new StringBuilder(" ");
sb.Append(columnName);
sb.Append(" IN(");
if (isString)
{
foreach (var item in items)
{
sb.Append("'");
sb.Append(item);
sb.Append("'");
sb.Append(",");
}
}
else
{
foreach (var item in items)
{
sb.Append(item);
sb.Append(",");
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
tmp = sb.ToString();
}
return tmp;
}
/// <summary>
/// 构建 "a=@a and b=@b" 格式的 sql 参数化语句,并返回 已经赋值的 SqlParameter 列表
/// </summary>
/// <param name="columnValues">列和值对</param>
/// <param name="parameters">返回 SQL 参数对象,已经赋值</param>
/// <returns>返回构造好的 SQL AND 语句</returns>
public static string And(Dictionary<string, object> columnValues, out List<SQLiteParameter> parameters)
{
if (columnValues == null || columnValues.Keys.Count <= 0)
{
throw new ArgumentException("columnValues");
}
var sb = new StringBuilder(" ");
parameters = new List<SQLiteParameter>();
foreach (var column in columnValues)
{
sb.AppendFormat("{0}=@{0} AND ", column.Key);
parameters.Add(new SQLiteParameter("@" + column.Key, column.Value));
}
return sb.Remove(sb.Length - 4, 4) + " ";
}
#endregion
}
}