SQLite工具类

//添加 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
    }
}