Esempio n. 1
0
        public IActionResult PageList([FromQuery] UserInfoPagedDto param)
        {
            var userData   = new UserInfoDataBase();
            var resultData = userData.pageList(param);
            var rows       = resultData.Values.FirstOrDefault();
            var total      = resultData.Keys.FirstOrDefault();

            return(Json(new { total = total, rows = rows, index = param.offset }));
        }
Esempio n. 2
0
        public Dictionary <int, List <UserInfoDto> > pageList(UserInfoPagedDto parm)
        {
            string sqls     = string.Format(@"select a.Id,a.Address,a.UserName, a.Phone, a.Address,a.MakeRoomTime ,a.SellingPoint,a.RoomNumber,a.Price,a.AllPrice,a.Areas,a.Apartment,a.Remark
                  ,(select  Url from img where UserInfoId = a.id order by CreateTime desc limit 1 offset 0) as url from UserInfo as a where a.Id is not null");
            string sqlCount = "select count(*) from UserInfo where Id  is not null";
            string sqlwhere = null;

            //if (strRegionIds != null)
            //{
            //    strsql = strRegionIds + strsql;
            //    sqlCount = strRegionIds + sqlCount;
            //    sqlwhere += " and exists(select RegionId from RegionIds where RegionId =c.RegionId)";
            //}
            if (!string.IsNullOrWhiteSpace(parm.Address))
            {
                sqlwhere += $" and Address like '" + parm.Address + "%'";
            }
            if (!string.IsNullOrWhiteSpace(parm.UserName))
            {
                sqlwhere += $" and UserName like  '" + parm.UserName + "%'";
            }
            if (!string.IsNullOrWhiteSpace(parm.Apartment))
            {
                sqlwhere += " and Apartment like  '" + parm.Apartment + "%'";
            }
            if (!string.IsNullOrWhiteSpace(parm.Keywords))
            {
                sqlwhere += $" and (SellingPoint like '" + parm.Keywords + "%' or Apartment like   '" + parm.Keywords + "%' )";
            }
            string sqlsize = "";

            sqlCount += sqlwhere;
            sqls     += sqlwhere;
            if (parm.limit != 0 && parm.offset > 0)
            {
                var skipCount = parm.limit * (parm.offset - 1);
                sqlsize = string.Format(" order by CreateTime asc limit {0} offset {1}", parm.limit, skipCount);
                sqls   += sqlsize;
            }
            var dt       = SQLiteHelper.ExecuteDataTable(sqls);
            var userData = new List <UserInfoDto>();

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    var item = new UserInfoDto
                    {
                        Id           = Guid.Parse(dr["Id"].ToString()),
                        UserName     = dr["UserName"].ToString(),
                        Phone        = dr["Phone"].ToString(),
                        Address      = dr["Address"].ToString(),
                        AllPrice     = dr["AllPrice"].ToString(),
                        Price        = dr["Price"].ToString(),
                        Areas        = dr["Areas"].ToString(),
                        Apartment    = dr["Apartment"].ToString(),
                        RoomNumber   = dr["RoomNumber"].ToString(),
                        SellingPoint = dr["SellingPoint"].ToString(),
                        MakeRoomTime = dr["MakeRoomTime"].ToString(),
                        Url          = dr["url"].ToString()
                    };
                    userData.Add(item);
                }
            }
            var count = SQLiteHelper.ExecuteScalar(sqlCount);
            Dictionary <int, List <UserInfoDto> > result = new Dictionary <int, List <UserInfoDto> >();

            result.Add(Convert.ToInt16(count), userData);
            return(result);
        }