コード例 #1
0
        /// <summary>
        /// 根据条件查询一个DataTable
        /// </summary>
        /// <typeparam name="T">动态对象</typeparam>
        /// <param name="condition">筛选条件</param>
        /// <param name="timeout">超时时间</param>
        /// <returns></returns>
        public DataTable FindTable <T>(Expression <Func <T, bool> > condition, int?timeout = Timeout) where T : class
        {
            DataTable res = new DataTable(typeof(T).Name);

            this.Logger(this.GetType(), "根据条件查询一个DataTable-FindTable", () =>
            {
                LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
                lambda.AddAndWhere(condition);
                string where = lambda.Where();

                string sql = DatabaseCommon.SelectSql <T>(where, true).ToString();

                if (this.BaseDbTransaction == null)
                {
                    using (IDbConnection connection = this.BaseDbConnection)
                    {
                        var reader = connection.ExecuteReader(sql, null, null, timeout, CommandType.Text);
                        res.Load(reader);
                    }
                }
                else
                {
                    var reader = this.BaseDbTransaction.Connection.ExecuteReader(sql, null, this.BaseDbTransaction, timeout, CommandType.Text);
                    res.Load(reader);
                }
            }, e =>
            {
                this.Rollback();
            });
            return(res);
        }
コード例 #2
0
        /// <summary>
        /// 根据条件以及指定属性名称更新
        /// </summary>
        /// <typeparam name="T">动态对象</typeparam>
        /// <param name="entity">待更新实体</param>
        /// <param name="condition">筛选条件</param>
        /// <param name="timeout">超时时间</param>
        /// <returns></returns>
        public int Update <T>(object entity, Expression <Func <T, bool> > condition, int?timeout = Timeout) where T : class
        {
            int res = 0;

            this.Logger(this.GetType(), "根据条件以及指定属性名称更新-Update", () =>
            {
                if (entity != null)
                {
                    LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
                    lambda.AddAndWhere(condition);
                    string where = lambda.Where();
                    string sql   = DatabaseCommon.UpdateSql <T>(entity, "", where).ToString();

                    if (this.BaseDbTransaction == null)
                    {
                        using (IDbConnection connection = this.BaseDbConnection)
                        {
                            res = connection.Execute(sql, entity, null, timeout, CommandType.Text);
                        }
                    }
                    else
                    {
                        res = this.BaseDbTransaction.Connection.Execute(sql, entity, this.BaseDbTransaction, timeout, CommandType.Text);
                    }
                }
            }, e =>
            {
                this.Rollback();
            });
            return(res);
        }
コード例 #3
0
ファイル: MsSqlDatabase.cs プロジェクト: 1625453870/Bill
        /// <summary>
        /// 集合查询(Lambda)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public T FindEntity <T>(Expression <Func <T, bool> > condition)
            where T : class, new()
        {
            var lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.SelectSql <T>(where).ToString();

            return(FindEntity <T>(sql));
        }
コード例 #4
0
ファイル: MsSqlDatabase.cs プロジェクト: 1625453870/Bill
        /// <summary>
        /// 集合查询(Lambda)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition)
            where T : new()
        {
            var lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.SelectSql <T>(where).ToString();

            return(this.FindList <T>(sql));
        }
コード例 #5
0
ファイル: MsSqlDatabase.cs プロジェクト: 1625453870/Bill
        /// <summary>
        /// 分页查询-Lambda
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public PaginationDTO <IEnumerable <T> > FindPageList <T>(Expression <Func <T, bool> > condition, PaginationQuery pagination)
            where T : new()
        {
            var lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.SelectSql <T>(where).ToString();

            return(FindPageList <T>(sql, pagination));
        }
コード例 #6
0
        /// <summary>
        /// 根据条件查询出一个集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition) where T : class, new()
        {
            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.SelectSql <T>(where).ToString();

            using (var dbConnection = Connection)
            {
                return(dbConnection.Query <T>(sql).ToList());
            }
        }
コード例 #7
0
        /// <summary>
        /// 根据条件查询一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public T FindEntity <T>(Expression <Func <T, bool> > condition) where T : class, new()
        {
            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.SelectSql <T>(where).ToString();

            using (var dbConnection = Connection)
            {
                var data = dbConnection.Query <T>(sql);
                return(data.FirstOrDefault());
            }
        }
コード例 #8
0
ファイル: MsSqlDatabase.cs プロジェクト: 1625453870/Bill
        /// <summary>
        /// lambdas删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        public void Delete <T>(Expression <Func <T, bool> > condition)
            where T : class, new()
        {
            var lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.DeleteSql <T>(where).ToString();

            using (var db = Connection)
            {
                db.Execute(sql);
            }
        }
コード例 #9
0
ファイル: MsSqlDatabase.cs プロジェクト: 1625453870/Bill
        /// <summary>
        /// 数据条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int Count <T>(Expression <Func <T, bool> > condition)
            where T : class, new()
        {
            var lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.SelectSql <T>(where).ToString();

            using (var db = Connection)
            {
                return(db.Execute(sql));
            }
        }
コード例 #10
0
        /// <summary>
        /// 根据条件获取分页数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition">条件</param>
        /// <param name="orderField">排序字段,多个用英文逗号隔开,类似:Id Asc,Name Desc</param>
        /// <param name="isAsc">是否升序</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">索引</param>
        /// <param name="total">总记录</param>
        /// <returns></returns>
        public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
        {
            StringBuilder sb = new StringBuilder();

            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int    num     = (pageIndex - 1) * pageSize;
            int    num1    = (pageIndex) * pageSize;
            string orderBy = "";

            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();

            //表名
            string table  = EntityAttributeHelper.GetEntityTable <T>();
            string strSql = DatabaseCommon.SelectSql <T>(where, true).ToString();

            if (!string.IsNullOrEmpty(orderField))
            {
                if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
                {
                    orderBy = "Order By " + orderField;
                }
                else
                {
                    orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
                }
            }
            else
            {
                orderBy = "Order By (Select 0)";
            }
            sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
            sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

            using (var dbConnection = Connection)
            {
                string selectCountSql = "Select Count(*) From " + table + " WHERE 1 = 1";
                total = (int)dbConnection.ExecuteScalar(selectCountSql);

                IEnumerable <T> data = dbConnection.Query <T>(sb.ToString()).ToList();
                return(data);
            }
        }
コード例 #11
0
        /// <summary>
        /// 根据条件以及指定属性名称更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="modelModifyProps"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int Update <T>(T modelModifyProps, Expression <Func <T, bool> > condition) where T : class, new()
        {
            int res = 0;

            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();

            string sql = DatabaseCommon.UpdateSql <T>(modelModifyProps, "", where).ToString();

            //DbParameter[] parameter = DatabaseCommon.GetParameter<T>(modelModifyProps);

            res = ExecuteBySql(sql, modelModifyProps);

            return(res);
        }
コード例 #12
0
        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int Delete <T>(Expression <Func <T, bool> > condition) where T : class, new()
        {
            int res = 0;

            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();

            string sql = DatabaseCommon.DeleteSql <T>(where).ToString();

            using (var connection = Connection)
            {
                res = connection.Execute(sql);
            }
            return(res);
        }
コード例 #13
0
ファイル: UnitTest.cs プロジェクト: ZhaoYis/LambdaToSQL
        public void TestMethod()
        {
            LambdaExpConditions <UserEntity> lambda = new LambdaExpConditions <UserEntity>();

            lambda.Add(u => u.Where(o => o.Id == 1 && o.IsEnable == false && o.Birthday > new DateTime(2017, 10, 12)).OrderByDescending(o => o.Id));

            Console.WriteLine("Where:" + lambda.Where() + ",OrderBy:" + lambda.OrderBy());
            Console.WriteLine("=========================================================");

            UserEntity user = new UserEntity
            {
                Id = 1
            };

            lambda = new LambdaExpConditions <UserEntity>();
            lambda.AddAndWhere(() => { return(user.Id > 1); }, u => u.UserName.Contains("大师兄") && u.IsEnable == false, u => u.UserName == "大师兄" && u.IsEnable == false);
            lambda.AddOrderBy(u => u.Id);
            Console.WriteLine("Where:" + lambda.Where() + ",OrderBy:" + lambda.OrderBy());
            Console.WriteLine("=========================================================");
        }
コード例 #14
0
        /// <summary>
        /// 根据条件更新
        /// </summary>
        /// <param name="modelModifyProps">要修改的列及修改后列的值集合</param>
        /// <param name="condition">修改的条件</param>
        /// <returns>返回受影响行数</returns>
        public int Update <T>(T modelModifyProps, Expression <Func <T, bool> > condition) where T : class, new()
        {
            int res = 0;
            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon.UpdateSql <T>(modelModifyProps, "", where).ToString();

            if (SqlTransaction == null)
            {
                using (var conn = Connection)
                {
                    res = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
                }
            }
            else
            {
                res = SqlHelper.ExecuteNonQuery(SqlTransaction, CommandType.Text, sql);

                //res = this.Commit();
            }
            return(res);
        }
コード例 #15
0
        /// <summary>
        /// 根据条件获取分页数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition">条件</param>
        /// <param name="orderField">排序字段,多个用英文逗号隔开,类似:Id Asc,Name Desc</param>
        /// <param name="isAsc">是否升序</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">索引</param>
        /// <param name="total">总记录</param>
        /// <returns></returns>
        public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
        {
            StringBuilder sb = new StringBuilder();

            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int    num     = (pageIndex - 1) * pageSize;
            int    num1    = (pageIndex) * pageSize;
            string orderBy = "";

            LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();

            lambda.AddAndWhere(condition);
            string where = lambda.Where();

            //表名
            //string table = EntityAttributeHelper.GetEntityTable<T>();
            string strSql = DatabaseCommon.SelectSql <T>(where, true).ToString();

            if (!string.IsNullOrEmpty(orderField))
            {
                if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
                {
                    orderBy = "Order By " + orderField;
                }
                else
                {
                    orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
                }
            }
            else
            {
                orderBy = "Order By (Select 0)";
            }
            sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
            sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

            using (var dbConnection = Connection)
            {
                IEnumerable <T> data           = new List <T>();
                string          selectCountSql = "Select Count(*) From (" + strSql + ") AS t";
                total = (int)SqlHelper.ExecuteScalar(dbConnection, CommandType.Text, selectCountSql);

                DataSet dataSet = SqlHelper.ExecuteDataset(dbConnection, CommandType.Text, sb.ToString());
                if (dataSet.Tables.Count > 0 && dataSet.Tables[0].IsExistRows())
                {
                    DataTable dataTable = dataSet.Tables[0];
                    data = dataTable.DataTableToList <T>();
                }
                return(data);
            }

            //MethodCallExpression resultExp = null;

            //var tempData = this.FindList<T>(condition).AsQueryable();

            //string[] order = orderField.Split(',');
            //foreach (string item in order)
            //{
            //    string orderPart = item;
            //    orderPart = Regex.Replace(orderPart, @"\s+", " ");
            //    string[] orderArry = orderPart.Split(' ');

            //    bool sort = isAsc;
            //    if (orderArry.Length == 2)
            //    {
            //        sort = orderArry[1].ToUpper() == "ASC" ? true : false;
            //    }
            //    var parameter = Expression.Parameter(typeof(T), "t");
            //    var property = typeof(T).GetProperty(orderArry[0]);
            //    if (property != null)
            //    {
            //        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            //        var orderByExp = Expression.Lambda(propertyAccess, parameter);
            //        resultExp = Expression.Call(typeof(Queryable), sort ? "OrderBy" : "OrderByDescending", new Type[] { typeof(T), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp));
            //    }
            //}
            //if (resultExp != null)
            //    tempData = tempData.Provider.CreateQuery<T>(resultExp);
            //total = tempData.Count();
            //tempData = tempData.Skip<T>(pageSize * (pageIndex - 1)).Take<T>(pageSize).AsQueryable();

            //return tempData.ToList();
        }
コード例 #16
0
        /// <summary>
        /// 根据条件获取分页数据
        /// </summary>
        /// <typeparam name="T">动态对象</typeparam>
        /// <param name="condition">筛选条件</param>
        /// <param name="orderField">排序字段,多个用英文逗号隔开,类似:Id Asc,Name Desc</param>
        /// <param name="isAsc">是否升序</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="pageIndex">索引</param>
        /// <param name="total">总页数</param>
        /// <param name="timeout">超时时间</param>
        /// <returns></returns>
        public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total,
                                            int?timeout = Timeout) where T : IEntity
        {
            IEnumerable <T> res  = default(IEnumerable <T>);
            int             temp = 0;

            this.Logger(this.GetType(), "执行sql语句,获取分页数据-FindList", () =>
            {
                StringBuilder sb = new StringBuilder();
                if (pageIndex == 0)
                {
                    pageIndex = 1;
                }
                int num        = (pageIndex - 1) * pageSize;
                int num1       = (pageIndex) * pageSize;
                string orderBy = "";

                LambdaExpConditions <T> lambda = new LambdaExpConditions <T>();
                lambda.AddAndWhere(condition);
                string where = lambda.Where();

                //表名
                string table  = EntityAttributeHelper.GetEntityTable <T>();
                string strSql = DatabaseCommon.SelectSql <T>(where, true).ToString();

                if (!string.IsNullOrEmpty(orderField))
                {
                    if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
                    {
                        orderBy = "Order By " + orderField;
                    }
                    else
                    {
                        orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
                    }
                }
                else
                {
                    orderBy = "Order By (Select 0)";
                }
                sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
                sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");

                //查询总记录数
                string selectCountSql = "Select Count(*) From " + table + " WHERE 1 = 1";

                if (this.BaseDbTransaction == null)
                {
                    using (IDbConnection connection = this.BaseDbConnection)
                    {
                        temp = (int)connection.ExecuteScalar(selectCountSql);
                        res  = connection.Query <T>(sb.ToString(), null, null, true, timeout, CommandType.Text);
                    }
                }
                else
                {
                    temp = (int)this.BaseDbTransaction.Connection.ExecuteScalar(selectCountSql, null, this.BaseDbTransaction);
                    res  = this.BaseDbTransaction.Connection.Query <T>(sb.ToString(), null, this.BaseDbTransaction, true, timeout, CommandType.Text);
                }
            }, e =>
            {
                this.Rollback();
            });
            total = temp;
            return(res);
        }