实现操作SQLite

internal class SQLiteHelper : IDisposable
    {
        private SQLiteConnection conn = null;
        protected SQLiteCommand cmd = null;
        protected SQLiteDataReader sdr = null;
        ///
        /// 构造函数
        ///
        public SQLiteHelper(string connStr)
        {
            conn = new SQLiteConnection(connStr);
        }
        ///
        /// 获取连接结果，未连接打开连接
        ///
        /// 连接结果
        protected SQLiteConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        ///
        /// 获取连接结果，未连接打开连接
        ///
        /// 连接结果
        protected void Close()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        ///
        /// 该方法执行传入的增删改SQL语句
        ///
        /// 要执行的增删改SQL语句
        /// 返回更新的记录数
        public int ExecuteNonQuery(string sql)
        {
            int res;
            try
            {
                cmd = new SQLiteCommand(sql, GetConn());
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                res = -1;
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return res;
        }
        ///
        /// 执行带参数的SQL增删改语句
        ///
        /// SQL增删改语句
        /// 参数集合
        /// 返回更新的记录数
        public int ExecuteNonQuery(string sql, SQLiteParameter[] paras)
        {
            int res;
            using (cmd = new SQLiteCommand(sql, GetConn()))
            {
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }
        ///
        /// 该方法执行传入的SQL查询语句
        ///
        /// SQL查询语句
        /// 返回数据集合
        public DataTable ExecuteQuery(string sql)
        {
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, GetConn());
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        ///
        /// 执行带参数的SQL查询语句
        ///
        /// SQL查询语句
        /// 参数集合
        /// 返回数据集合
        public DataTable ExecuteQuery(string sql, SQLiteParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, GetConn());
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }


        /// <summary>
        /// 反回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public string GetString(string sql, SQLiteParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, GetConn());
            cmd.Parameters.AddRange(paras);
            var reval = cmd.ExecuteScalar();
            return reval.ToString();
        }
        /// <summary>
        /// 反回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int GetInt(string sql, SQLiteParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, GetConn());
            cmd.Parameters.AddRange(paras);
            var reval = cmd.ExecuteScalar();
            return Convert.ToInt32(reval);
        }

        ///
        /// 执行带参数的SQL查询判断语句
        ///
        /// SQL查询语句
        /// 参数集合
        /// 返回是否为空
        public bool BoolExecuteQuery(string sql, SQLiteParameter[] paras)
        {
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, GetConn());
            cmd.Parameters.AddRange(paras);
            try
            {
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            DataRow[] rows = dt.Select();
            bool temp = false;
            if (rows.Length > 0)
            {
                temp = true;
            }
            return temp;
        }
        ///
        /// 该方法执行传入的SQL查询判断语句
        ///
        /// SQL查询语句
        /// 返回是否为空
        public bool BoolExecuteQuery(string sql)
        {
            DataTable dt = new DataTable();
            cmd = new SQLiteCommand(sql, GetConn());
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            DataRow[] rows = dt.Select();
            bool temp = false;
            if (rows.Length > 0)
            {
                temp = true;
            }
            return temp;
        }

        public void Dispose()
        {
            Close();
        }
    }

 internal class Common
    {

         /// <summary>
        /// 将dataTable转成List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> List<T>(DataTable dt)
        {
            var list = new List<T>();
            Type t = typeof(T);
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
 
            foreach (DataRow item in dt.Rows)
            {
                T s = System.Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        if (!Convert.IsDBNull(item[i]))
                        {
                            info.SetValue(s, item[i], null);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        } 


        /// <summary>
        /// 将数组转为 '1','2' 这种格式的字符串 用于 where id in(  )
        /// </summary>
        /// <param name="array"></param>
        /// <returns></returns>
        public static string ToJoinSqlInVal(object[] array)
        {
            if (array == null || array.Length == 0)
            {
                return "''";
            }
            else
            {
                return string.Join(",", array.Where(c => c != null).Select(c => "'" + (c + "").Replace("'", "''") + "'"));//除止SQL注入
            }
        }

        public static SQLiteParameter[] GetObjectToSQLiteParameter(object obj)
        {
            List<SQLiteParameter> listParams = new List<SQLiteParameter>();
            var propertiesObj = obj.GetType().GetProperties();
            string replaceGuid = Guid.NewGuid().ToString();
            foreach (PropertyInfo r in propertiesObj)
            {
                listParams.Add(new SQLiteParameter("@" + r.Name, r.GetValue(obj,null).ToString()));
            }

            return listParams.ToArray();
        }

        public static Dictionary<string,string> GetObjectToDictionary(object obj)
        {
            Dictionary<string,string> reval = new Dictionary<string,string>();
            var propertiesObj = obj.GetType().GetProperties();
            string replaceGuid = Guid.NewGuid().ToString();
            foreach (PropertyInfo r in propertiesObj)
            {
                reval.Add(r.Name, r.GetValue(obj,null).ToString());
            }

            return reval;
        }

        public static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type)
        {
            string sb = "(";
            //先处理左边
            sb += ExpressionRouter(left);
            sb += ExpressionTypeCast(type);
            //再处理右边
            string tmpStr = ExpressionRouter(right);
            if (tmpStr == "null")
            {
                if (sb.EndsWith(" ="))
                    sb = sb.Substring(0, sb.Length - 2) + " is null";
                else if (sb.EndsWith("<>"))
                    sb = sb.Substring(0, sb.Length - 2) + " is not null";
            }
            else
                sb += tmpStr;
            return sb += ")";
        }
        //表达式路由计算 
        static string ExpressionRouter(Expression exp)
        {
            string sb = string.Empty;
            if (exp is BinaryExpression)
            {
                BinaryExpression be = ((BinaryExpression)exp);
                return BinarExpressionProvider(be.Left, be.Right, be.NodeType);
            }
            else if (exp is MemberExpression)
            {
                MemberExpression me = ((MemberExpression)exp);
                return me.Member.Name;
            }
            else if (exp is NewArrayExpression)
            {
                NewArrayExpression ae = ((NewArrayExpression)exp);
                StringBuilder tmpstr = new StringBuilder();
                foreach (Expression ex in ae.Expressions)
                {
                    tmpstr.Append(ExpressionRouter(ex));
                    tmpstr.Append(",");
                }
                return tmpstr.ToString(0, tmpstr.Length - 1);
            }
            else if (exp is MethodCallExpression)
            {
                MethodCallExpression mce = (MethodCallExpression)exp;
                if (mce.Method.Name == "Like")
                    return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
                else if (mce.Method.Name == "NotLike")
                    return string.Format("({0} Not like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
                else if (mce.Method.Name == "In")
                    return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
                else if (mce.Method.Name == "NotIn")
                    return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
            }
            else if (exp is ConstantExpression)
            {
                ConstantExpression ce = ((ConstantExpression)exp);
                if (ce.Value == null)
                    return "null";
                else if (ce.Value is ValueType)
                    return ce.Value.ToString();
                else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
                    return string.Format("'{0}'", ce.Value.ToString());
            }
            else if (exp is UnaryExpression)
            {
                UnaryExpression ue = ((UnaryExpression)exp);
                return ExpressionRouter(ue.Operand);
            }
            return null;
        }
        static string ExpressionTypeCast(ExpressionType type)
        {
            switch (type)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    return " AND ";
                case ExpressionType.Equal:
                    return " =";
                case ExpressionType.GreaterThan:
                    return " >";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    return " Or ";
                case ExpressionType.Add:
                case ExpressionType.AddChecked:
                    return "+";
                case ExpressionType.Subtract:
                case ExpressionType.SubtractChecked:
                    return "-";
                case ExpressionType.Divide:
                    return "/";
                case ExpressionType.Multiply:
                case ExpressionType.MultiplyChecked:
                    return "*";
                default:
                    return null;
            }
        }
    }

public class ClassGenerating
    {
        /// <summary>
        /// 根据DataTable获取实体类的字符串
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="className"></param>
        /// <returns></returns>
        public static string DataTableToClass(DataTable dt,string tableName,string nameSpace)
        {
            StringBuilder reval = new StringBuilder();
            StringBuilder propertiesValue = new StringBuilder();
            string replaceGuid = Guid.NewGuid().ToString();
            foreach (DataColumn r in dt.Columns)
            {
                propertiesValue.AppendFormat("           public {0} {1} {2}", r.DataType, r.ColumnName, "{get;set;}\r\n");

            }
            reval.AppendFormat(@"using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace  {2}{{

 public class {0}{{
{1}
  }}
}} ", tableName, propertiesValue,nameSpace);


            return reval.ToString();
        }
    }

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Linq.Expressions;

public class Easyliter
    {
        private string _connstr = null;
        public string message { get; set; }
        public Easyliter(string connstr)
        {
            _connstr = connstr;
        }

        /// <summary>
        /// 根据SQL查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pars">例如 : new { id>0 }</param>
        /// <returns>查询结果</returns> 
        public List<T> Select<T>(string sql, object pars)
        {
            var dt = GetDataTable(sql, Common.GetObjectToSQLiteParameter(pars));
            var reval = Common.List<T>(dt);
            return reval;
        }

        /// <summary>
        /// 根据拉姆达表达示查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereExpressions">查询表达示</param>
        /// <returns></returns>
        public List<T> Select<T>(params  Expression<Func<T, bool>>[] whereExpressions) where T : new()
        {

            var tEntity = new T();
            var type = tEntity.GetType();
            string whereStr = string.Empty;
            if (whereExpressions != null && whereExpressions.Length > 0)
            {
                foreach (var expression in whereExpressions)
                {
                    if (expression.Body is BinaryExpression)
                    {
                        BinaryExpression be = ((BinaryExpression)expression.Body);
                        whereStr = " and " + Common.BinarExpressionProvider(be.Left, be.Right, be.NodeType);
                    }
                }
            }
            string sql = string.Format("select * from {0} where 1=1  {1} ", type.Name, whereStr);
            var dt = GetDataTable(sql);
            var reval = Common.List<T>(dt);
            return reval;
        }


        /// <summary>
        /// 根据拉姆达表达示查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="order">id asc 或者 id desc</param>
        /// <param name="expressions"></param>
        /// <returns></returns>
        public List<T> SelectPage<T>(int pageIndex, int pageSize, ref int pageCount, string orderBy, params  Expression<Func<T, bool>>[] expressions) where T : new()
        {

            var tEntity = new T();
            var type = tEntity.GetType();
            string whereStr = string.Empty;
            foreach (var expression in expressions)
            {
                if (expression.Body is BinaryExpression)
                {
                    BinaryExpression be = ((BinaryExpression)expression.Body);
                    whereStr =" and " + Common.BinarExpressionProvider(be.Left, be.Right, be.NodeType);
                }
            }
            string sql = string.Format("select * from {0} where 1=1  {1} order by {2}  LIMIT {3},{4} ", type.Name,  whereStr, orderBy, (pageIndex - 1) * pageSize, pageSize);
            string sqlCount = string.Format("select count(*) from {0} where 1=1  {1} order by {2}   ", type.Name,   whereStr, orderBy);

            var dt = GetDataTable(sql);
            pageCount = GetInt(sqlCount);
            var reval = Common.List<T>(dt);
            return reval;
        }


        /// <summary>
        /// 创建类
        /// </summary>
        /// <param name="nameSpace">命名空间</param>
        /// <param name="fullPath">生成类的存放物理目录</param>
        /// <param name="tableNames">指定生成的表名，不填为生成所有表</param>
        /// <returns>执行结果</returns>
        public string CreateCalss(string nameSpace, string dirPath, params string[] tableNames)
        {

            StringBuilder reval = new StringBuilder();
            if (nameSpace == null)
            {
                return "nameSpace不能为空";
            }
            if (dirPath == null)
            {
                return "DirPath不能为空";
            }
            if (!System.IO.Directory.Exists(dirPath))
            {
                return "找不到dicPath路径";
            }

            string sql = "select name from sqlite_master where type='table'  order by name";
            var dt = GetDataTable(sql);
            if (dt == null || dt.Rows.Count == 0)
            {
                throw new Exception("数据库没有可生成的表！");
            }
            else
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string tableName = dr["name"].ToString();
                    if (!tableName.Contains("_old") && tableName != "sqlite_sequence")
                    {
                        if (tableNames.Length == 0 || tableNames.Contains(tableName))
                        {
                            string classsql = string.Format("select  * from {0} Limit 1", tableName);
                            CreateCalssBySql(nameSpace, dirPath, tableName, classsql);
                            reval.AppendFormat("表【{0}】：创建成功！\r\n", tableName);
                        }
                    }
                }
            }
            return reval.ToString();
        }
        /// <summary>
        /// 根据SQL创建类
        /// </summary>
        /// <param name="nameSpace">命名空间</param>
        /// <param name="fullPath">生成类的存放物理目录</param>
        /// <param name="tableNames">指定生成的表名，不填为生成所有表</param>
        /// <param name="classsql">sql语句</param>
        public bool CreateCalssBySql(string nameSpace, string dirPath, string tableName, string classsql)
        {
            DataTable classSource = GetDataTable(classsql);
            string classString = ClassGenerating.DataTableToClass(classSource, tableName, nameSpace);

            string calssPath = string.Format("{0}\\{1}.cs", dirPath.TrimEnd('\\'), tableName);
            if (!File.Exists(calssPath))
            {
                FileInfo file = new FileInfo(calssPath);
                using (FileStream stream = file.Create())
                {
                    using (StreamWriter writer = new StreamWriter(stream, Encoding.UTF8))
                    {
                        //写入字符串     
                        writer.Write(classString);

                        //输出
                        writer.Flush();
                    }
                }

            }
            System.IO.File.WriteAllText(calssPath, classString, Encoding.UTF8);
            return true;
        }

        /// <summary>
        /// 插入
        /// 用法:
        /// Product p = new Product()
        /// {
        ///  category_id = 2,
        ///  sku = "sku",
        ///  title = "title"
        /// };
        /// e.Insert<Product>(p);
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <param name="isIdentity">主键是否为自增长,true可以不填,false必填</param>
        /// <returns></returns>
        public bool Insert<TEntity>(TEntity entity, bool isIdentity = true) where TEntity : class
        {
            Type type = entity.GetType();
            var primaryKeyName = GetPrimaryKey(type);
            List<SQLiteParameter> pars = new List<SQLiteParameter>();
            //2.获得实体的属性集合 
            PropertyInfo[] props = type.GetProperties();

            //实例化一个StringBuilder做字符串的拼接 
            StringBuilder sb = new StringBuilder();

            sb.Append("insert into " + type.Name + " (");

            //3.遍历实体的属性集合 
            foreach (PropertyInfo prop in props)
            {
                //EntityState,@EntityKey
                if (isIdentity == false || (isIdentity && prop.Name != primaryKeyName))
                {
                    //4.将属性的名字加入到字符串中 
                    sb.Append(prop.Name + ",");
                }
            }
            //**去掉最后一个逗号 
            sb.Remove(sb.Length - 1, 1);
            sb.Append(" ) values(");

            //5.再次遍历，形成参数列表"(@xx,@xx@xx)"的形式 
            foreach (PropertyInfo prop in props)
            {
                //EntityState,@EntityKey
                if (isIdentity == false || (isIdentity && prop.Name != primaryKeyName))
                {
                    sb.Append("@" + prop.Name + ",");
                    object val = prop.GetValue(entity, null);
                    if (val == null)
                        val = DBNull.Value;
                    pars.Add(new SQLiteParameter("@" + prop.Name, val));
                }
            }
            //**去掉最后一个逗号 
            sb.Remove(sb.Length - 1, 1);
            sb.Append(")");

            var sql = sb.ToString();
            var addRowCount = ExecuteNonQuery(sql, pars.ToArray());
            message = string.Format("{0}行受影响", addRowCount);
            return addRowCount > 0;

        }


        /// <summary>
        /// 更新
        /// 用法:
        /// Update<ClassName>(new { name='小张',sex='男'},new {id=1})
        /// </summary>
        /// <typeparam name="TEntity">更新实体类型</typeparam>
        public bool Update<TEntity>(object rowObj, object whereObj) where TEntity : class
        {
            if (rowObj == null) { message = "rowObj不能为空"; return false; };
            if (whereObj == null) { message = "rowObj不能为空"; return false; };

            using (SQLiteHelper db = new SQLiteHelper(_connstr))
            {
                Type type = typeof(TEntity);
                string sql = string.Format(" update {0} set ", type.Name);
                Dictionary<string, string> rows = Common.GetObjectToDictionary(rowObj);
                foreach (var r in rows)
                {
                    sql += string.Format(" {0} =@{0}  ,", r.Key);
                }
                sql = sql.TrimEnd(',');
                sql += string.Format(" where  1=1  ");
                Dictionary<string, string> wheres = Common.GetObjectToDictionary(whereObj);
                foreach (var r in wheres)
                {
                    sql += string.Format(" and {0} =@{0}", r.Key);
                }
                List<SQLiteParameter> parsList = new List<SQLiteParameter>();
                parsList.AddRange(rows.Select(c => new SQLiteParameter("@" + c.Key, c.Value)));
                parsList.AddRange(wheres.Select(c => new SQLiteParameter("@" + c.Key, c.Value)));
                var updateRowCount = db.ExecuteNonQuery(sql, parsList.ToArray());
                message = string.Format("{0}行受影响", updateRowCount);
                return updateRowCount > 0;
            }
        }

        /// <summary>
        /// 批量删除
        /// 用法:
        /// Delete<T>(new int[]{1,2,3})
        /// 或者
        /// Delete<T>(3)
        /// </summary>
        /// <param name="oc"></param>
        /// <param name="whereIn">in的集合</param>
        /// <param name="whereIn">主键为实体的第一个属性</param>
        public bool Delete<TEntity>(params dynamic[] whereIn)
        {
            try
            {
                using (SQLiteHelper db = new SQLiteHelper(_connstr))
                {

                    Type type = typeof(TEntity);
                    string key = type.FullName;
                    bool isSuccess = false;
                    if (whereIn != null && whereIn.Length > 0)
                    {
                        string sql = string.Format("delete from {0} where {1} in ({2})", type.Name, GetPrimaryKey(type), Common.ToJoinSqlInVal(whereIn));
                        int deleteRowCount = db.ExecuteNonQuery(sql);
                        message = string.Format("{0}行受影响", deleteRowCount);
                        isSuccess = deleteRowCount > 0;
                    }
                    return isSuccess;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// 该方法执行传入的增删改SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQuery(string sql, params SQLiteParameter[] pars)
        {
            using (SQLiteHelper db = new SQLiteHelper(_connstr))
            {
                return db.ExecuteNonQuery(sql, pars);
            }
        }

        /// <summary>
        /// 反回datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, params SQLiteParameter[] pars)
        {
            using (SQLiteHelper db = new SQLiteHelper(_connstr))
            {
                return db.ExecuteQuery(sql, pars);
            }
        }

        /// <summary>
        /// 反回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetString(string sql, params SQLiteParameter[] pars)
        {
            using (SQLiteHelper db = new SQLiteHelper(_connstr))
            {
                return db.GetString(sql, pars);
            }
        }
        /// <summary>
        /// 反回第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetInt(string sql, params SQLiteParameter[] pars)
        {
            using (SQLiteHelper db = new SQLiteHelper(_connstr))
            {
                return db.GetInt(sql, pars);
            }
        }


        /// <summary>
        /// 获取主键
        /// </summary>
        /// <param name="type"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        private string GetPrimaryKey(Type type)
        {
            return type.GetProperties()[0].Name;
        }
    }