C# PetaPoco.Database.Page方法代码示例

        private void Form1_Load(object sender, EventArgs e)
        {
            var db = new PetaPoco.Database("tencentcloud");

            //To query a scalar
            foreach(var a in db.Query<article>("select * from articles"))
            {
                listBox1.Items.Add(string.Format("{0}-{1}", a.article_id, a.title));
            }

            listBox1.Items.Add("\r\n");
            long count = db.ExecuteScalar<long>("select Count(*) from articles");
            listBox1.Items.Add(string.Format("count: {0}",count ));
            listBox1.Items.Add("\r\n");
            //@0  代表占位符  SingleOrDefault
            var abc = db.SingleOrDefault<article>("select * from articles where [email protected]",1);
            listBox1.Items.Add(abc);
            listBox1.Items.Add("\r\n");

            //Paged Fetches 分页
            var result = db.Page<article>(1, 3, "select * from articles where draft=1 order by date_created ");

            foreach (var temp in result.Items)
            {
                listBox1.Items.Add(string.Format("title: {0}", temp.title));
            }

            listBox1.Items.Add("\r\n");
            listBox1.Items.Add("结束");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        public void dataBind(int pageIndex = 1, int pageSize = 10, string startTime = null, string endTime = null)
        {
            PetaPoco.Sql sql = new PetaPoco.Sql();
            sql.Select("m.[description] as name,a.*").From("t_MachineStop_rd a");
            sql.LeftJoin("Point_Machine_Map m").On("m.pointname=a.pointname");
            if (!String.IsNullOrEmpty(startTime))
            {
                sql.Where("a.starttime>@0", startTime);
            }
            if (!String.IsNullOrEmpty(endTime))
            {
                sql.Where("a.starttime<@0", endTime);
            }
            List<int> machineids = new List<int>();
            for (int i = 0; i < chkboxlist.Items.Count; i++)
            {
                if (chkboxlist.Items[i].Selected)
                {
                    machineids.Add(int.Parse(chkboxlist.Items[i].Value));
                }
            }
            if (machineids.Count > 1)
                sql.Where("m.machineid in (@0)", machineids);
            else if (machineids.Count == 1)
                sql.Where("[email protected]", machineids[0]);

            sql.OrderBy("a.starttime desc");

            var db = new PetaPoco.Database("dbconn");
            PetaPoco.Page<BootRecordSelect> pageitems = db.Page<BootRecordSelect>(pageIndex, pageSize, sql);
            rpt_RulelogS_Des.DataSource = pageitems.Items;
            rpt_RulelogS_Des.DataBind();
            AspNetPager1.RecordCount = (int)pageitems.TotalItems;
        }

 public object GET(int pageNumber, int itemsPerPage) {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT EC.*")
         .Append("FROM EstadoContacto EC");
     var items = db.Page<dynamic>(pageNumber, itemsPerPage, sql);
     JsonResult rta = new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
     return rta.Data;
 }

        public object GET(int pageNumber, int itemsPerPage
               , int EstadoContactoID
              , int EmpresaID
              , string ComienzoDesde
              , string ComienzoHasta
            //   , string Codigo
            //   , string RazonSocial
            ) {
                JsonResult rta;
            try {
                var db = new PetaPoco.Database("jlapc");
                var sql = PetaPoco.Sql.Builder
                    .Append("SELECT ")
                    .Append("C.*")
                    //.Append("C.Asunto, C.FechaAlta, C.TipoContactoID")
                    .Append(",Emp.RazonSocial,T.Descripcion as TemaDescripcion, Emp.Codigo as EmpresaCodigo")
                    .Append(",GU.Descripcion as GrupoUsuarioDescripcion, GU.GrupoID, G.Descripcion as GrupoDescripcion")
                    .Append(",EC.Descripcion as EstadoContactoDescripcion,SEC.Descripcion as SubEstadoContactoDescripcion")
                    //.Append(",C.EmpresaID")
                    .Append("FROM Contacto C")
                    .Append("LEFT JOIN Tema T on T.TemaID = C.TemaID")
                    .Append("LEFT JOIN EstadoContacto EC on EC.EstadoContactoID = C.EstadoContactoID")
                    //JLA>Pregunta>Para enganchar el subestado hay que tener en cuenta el GrupoID?, y el TipoCuentaID? de donde lo saco?
                    .Append("LEFT JOIN SubEstadoContacto SEC on SEC.EstadoContactoID = C.EstadoContactoID")
                    .Append("LEFT JOIN GrupoUsuario GU on GU.GrupoUsuarioID = C.GrupoUsuarioID")
                    .Append("LEFT JOIN Grupo G on G.GrupoID = GU.GrupoID")
                    .Append("LEFT JOIN Empresa Emp on Emp.EmpresaID = C.EmpresaID");

                sql.Append("WHERE 1 = 1");
                if (EmpresaID != -1) {
                    sql.Append(" AND C.EmpresaID = @0", EmpresaID);
                }
                if (EstadoContactoID != -1) {
                    sql.Append(" AND C.EstadoContactoID = @0", EstadoContactoID);
                }
                if (ComienzoDesde != null && ComienzoDesde != "") {
                    sql.Append(" AND C.Comienzo >= @0", ComienzoDesde);
                }
                if (ComienzoHasta != null && ComienzoHasta != "") {
                    sql.Append(" AND C.Comienzo <= @0", ComienzoHasta);
                }
                /*
                if (Codigo != null) {
                    sql.Append(" AND Emp.Codigo = @0", Codigo);
                }

                if (RazonSocial != null) {
                    sql.Append(" AND Emp.RazonSocial = @0", RazonSocial);
                }
                */
                var items = db.Page<dynamic>(pageNumber, itemsPerPage, sql);
                rta = new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            } catch (Exception e) {
                rta = new JsonResult() { Data = new { data = "", error = e.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
            return rta.Data;
        }

 public ActionResult LoadDefault(int trang)
 {
     var db = new PetaPoco.Database("MyConnection");
     PetaPoco.Page<HoiVien> kq;
     if (TempData["dieukienloc"] == null)
     {
         //PetaPoco.Page<HoiVien> rs = MyConnectionDB.GetInstance().Page<HoiVien>(1, 2, "select * from HoiVien");
         kq = db.Page<HoiVien>(trang,10,"select * from HoiVien");
     }
     else
     {
         TempData.Keep("dieukienloc");
         string[] frmc = (string[])TempData["dieukienloc"];
         //Thiet lap cau truy van dua tren dieu kien loc
         string qr = "select * from HoiVien where 1=1";
         if (frmc[0] != "")
         {
             qr = qr + string.Format(" and DivisionID like N'%{0}%'", frmc[0]);
         }
         if (frmc[3] != "")
         {
             qr = qr + string.Format(" and Address like N'%{0}%'", frmc[3]);
         }
         if (frmc[5] != "")
         {
             qr = qr + string.Format(" and Phone like N'%{0}%'", frmc[5]);
         }
         if (frmc[1] != "")
         {
             qr = qr + string.Format(" and MemberID like N'%{0}%'", frmc[1]);
         }
         if (frmc[4] != "")
         {
             qr = qr + string.Format(" and Identify like N'%{0}%'", frmc[4]);
         }
         if (frmc[7] != "")
         {
             qr = qr + string.Format(" and Fax like N'%{0}%'", frmc[7]);
         }
         if (frmc[2] != "")
         {
             qr = qr + string.Format(" and MemberName like N'%{0}%'", frmc[2]);
         }
         if (frmc[6] != "")
         {
             qr = qr + string.Format(" and Tel like N'%{0}%'", frmc[6]);
         }
         if (frmc[8] != "")
         {
             qr = qr + string.Format(" and Email like N'%{0}%'", frmc[8]);
         }
         kq = db.Page<HoiVien>(trang,10,qr);
     }
     TempData["total"] = kq.TotalPages;
     return Json(kq.Items, JsonRequestBehavior.AllowGet);
 }

 public object GETTOPONE() {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder.Append("SELECT ").Append(" E.* ")
         .Append(" FROM Novedad E")
         .Append(" Order By E.Fecha DESC");
         ;
     var items = db.Page<dynamic>(1, 1, sql);
     JsonResult rta = new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
     return rta.Data;
 }

 public object GET(int pageNumber, int itemsPerPage) {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT T.*,G.Descripcion as GrupoDescripcion")
         .Append("FROM Tema T")
         .Append("INNER JOIN Grupo G on G.GrupoID = T.GrupoID");
     var items = db.Page<dynamic>(pageNumber, itemsPerPage, sql);
     JsonResult rta = new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
     return rta.Data;
 }

        private static void PagedQuery()
        {
            var petaPoco = new PetaPoco.Database("Chinook");

            var result = petaPoco.Page<Customer>(1, 5, "SELECT * FROM customer");

            foreach (var customer in result.Items)
                ObjectDumper.Write(customer);

            ObjectDumper.Write(string.Format("There are {0} records matching the criteria", result.TotalItems));
        }

 public object GET(int pageNumber, int itemsPerPage, int ContactoID) {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT ")
         .Append("E.*")
         .Append("FROM Novedad E")
         .Append("WHERE E.ContactoID = @0", ContactoID)
         .Append(" Order By E.Fecha DESC");
     var items = db.Page<dynamic>(pageNumber, itemsPerPage, sql);
     JsonResult rta = new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
     return rta.Data;
 }

        public IHttpActionResult Get(string page, string count)
        {
            var db = new PetaPoco.Database("AGSoftware");
            string userid = Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name);

            var b = db.Page<Entities.UserGroup>(int.Parse(page), int.Parse(count), "Select * from UserGroup Where UserId = @0 order by DateCreated Desc", new object[] { userid });

            if (b.Items.Count > 0)
            {
                return Ok(b);
            }
            else
            {
                return NotFound();
            }
        }

 public static List<Models.Comment> GetComments(int fid, int pageSize, int pageIndex, ref int total)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         var pg = db.Page<Models.Comment>(pageIndex, pageSize, "WHERE fid [email protected] ", fid);
         total = (int)pg.TotalPages;
         return pg.Items;
     }
 }

      private PetaPoco.Page<HistoryEntry> PageInternal(long page, long itemsPerPage, QueryParameters parameters, BonusCalculationType bonusCalculation)
      {
         Debug.Assert(TableExists(SqlTable.WuHistory));

         var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
         select.Append(WhereBuilder.Execute(parameters));
         GetProduction.BonusCalculation = bonusCalculation;
         using (var connection = new SQLiteConnection(ConnectionString))
         {
            connection.Open();
            using (var database = new PetaPoco.Database(connection))
            {
               PetaPoco.Page<HistoryEntry> query = database.Page<HistoryEntry>(page, itemsPerPage, select);
               Debug.Assert(query != null);
               return query;
            }
         }
      }