Exemple #1
0
        public int Count <T>(Expression <Func <T, bool> > whereBy) where T : class, IViewer, new()
        {
            try
            {
                Contract.Requires(whereBy != null);

                var attr = GetTableAttr <T>();

                var sql = new StringBuilder();
                sql.Append("SELECT COUNT(*) FROM " + attr.Name);

                var condition = new ConditionBuilder();
                condition.Build(whereBy.Body);

                if (!string.IsNullOrEmpty(condition.Condition))
                {
                    sql.Append(" WHERE " + condition.Condition);
                }

                object count;

                if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
                {
                    count = DataAccess.ExecuteScalar(sql.ToString(), condition.SqlArguments.ToArray());
                }
                else
                {
                    count = DataAccess.ExecuteScalar(sql.ToString());
                }

                return(Convert.ToInt32(count));
            }
            catch (Exception ex)
            {
                _log.Error(ex, new LogInfo
                {
                    MethodInstance = MethodBase.GetCurrentMethod(),
                    ThreadInstance = Thread.CurrentThread
                });

                throw;
            }
        }
Exemple #2
0
        public List <T> Query <T>(IPager pager, Expression <Func <T, bool> > whereBy, string orderBy = null)
            where T : class, IViewer, new()
        {
            try
            {
                Contract.Requires(pager != null);
                Contract.Requires(whereBy != null);

                var list = new List <T>();

                var attr = GetTableAttr <T>();

                // Generate WhereBy Clause
                var condition = new ConditionBuilder();
                condition.Build(whereBy.Body);

                // Generate OrderBy Clause
                var strOrderBy = !string.IsNullOrEmpty(attr.Sort) ? attr.Sort : attr.Key;

                if (!string.IsNullOrEmpty(orderBy))
                {
                    strOrderBy = orderBy;
                }

                // Get TotalCount First
                var countSql = string.Format("SELECT COUNT({1}) AS TotalCount FROM {0} WHERE {2}", attr.Name, attr.Key,
                                             condition.Condition);

                DataSet ds;

                if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
                {
                    ds = DataAccess.ExecuteDataset(countSql, condition.SqlArguments.ToArray());
                }
                else
                {
                    ds = DataAccess.ExecuteDataset(countSql);
                }

                pager.SetTotalCount((int)ds.Tables[0].Rows[0]["TotalCount"]);

                // Build Sql and Execute
                var innerSql = string.Format("(SELECT ROW_NUMBER() OVER(ORDER BY {1}) AS RowNo, * FROM {0} WHERE {2})",
                                             attr.Name, strOrderBy, condition.Condition);

                string sql =
                    $"SELECT * FROM {innerSql} AS t WHERE t.RowNo BETWEEN {pager.CurrentPage * pager.PagingSize + 1} AND {(pager.CurrentPage + 1) * pager.PagingSize}";

                //sql += string.Format("SELECT COUNT({1}) AS TotalCount FROM {0} WHERE {2}", attr.Name, attr.Key, condition.Condition);

                if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
                {
                    ds = DataAccess.ExecuteDataset(sql, condition.SqlArguments.ToArray());
                }
                else
                {
                    ds = DataAccess.ExecuteDataset(sql);
                }

                var dt = ds.Tables[0];

                if (ds.Tables[0].Rows.Count > 0)
                {
                    using (var reader = dt.CreateDataReader())
                    {
                        list = reader.DataReaderMapTo <T>().ToList();
                    }
                }

                //pager.SetTotalCount((int)ds.Tables[1].Rows[0]["TotalCount"]);

                return(list);
            }
            catch (Exception ex)
            {
                _log.Error(ex, new LogInfo
                {
                    MethodInstance = MethodBase.GetCurrentMethod(),
                    ThreadInstance = Thread.CurrentThread
                });

                throw;
            }
        }
Exemple #3
0
        public List <T> Query <T>(Expression <Func <T, bool> > whereBy) where T : class, IViewer, new()
        {
            try
            {
                Contract.Requires(whereBy != null);

                var list = new List <T>();

                var attr = GetTableAttr <T>();

                var sql = new StringBuilder();
                sql.Append("SELECT * FROM " + attr.Name);

                var condition = new ConditionBuilder();
                condition.Build(whereBy.Body);

                if (!string.IsNullOrEmpty(condition.Condition))
                {
                    //var where = string.Format(condition.Condition, condition.Arguments);
                    sql.Append(" WHERE " + condition.Condition);
                }

                if (!string.IsNullOrEmpty(attr.Sort))
                {
                    sql.Append(" ORDER BY " + attr.Sort);
                }

                DataSet ds;

                if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
                {
                    ds = DataAccess.ExecuteDataset(sql.ToString(), condition.SqlArguments.ToArray());
                }
                else
                {
                    ds = DataAccess.ExecuteDataset(sql.ToString());
                }

                var dt = ds.Tables[0];

                if (dt.Rows.Count > 0)
                {
                    using (var reader = dt.CreateDataReader())
                    {
                        list = reader.DataReaderMapTo <T>().ToList();
                    }
                }

                return(list);
            }
            catch (Exception ex)
            {
                _log.Error(ex, new LogInfo
                {
                    MethodInstance = MethodBase.GetCurrentMethod(),
                    ThreadInstance = Thread.CurrentThread
                });

                throw;
            }
        }