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 article_id=@0",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("m.machineid=@0", 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 , 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 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 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 =@0 ", fid); total = (int)pg.TotalPages; return(pg.Items); } }
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; }
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 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 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()); } }
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); } } }
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.Storytime>(int.Parse(page), int.Parse(count), "Select * from Storytime Where UserId = @0 UNION Select st.* from Storytime st inner join StorytimeGroup stg on st.StorytimeId = stg.StorytimeId Where stg.UserGroupId in (Select ug.UserGroupId From UserGroup ug inner join UserGroupUser ugu on ug.UserGroupId = ugu.UserGroupId Where ugu.UserId = @0) UNION Select st.* from Storytime st inner join StorytimeUserList stul on st.StorytimeId = stul.StorytimeId Where stul.UserId = @0", new object[] { userid }); int offset = (int.Parse(page) - 1) * int.Parse(count); var b = db.Page <Entities.Storytime>(int.Parse(page), int.Parse(count), "with c as (Select st.*, anu.PhoneNumber from Storytime st inner join AspNetUsers anu on st.UserId = anu.Id Where UserId = @0 UNION Select st.*, anu.PhoneNumber from Storytime st inner join AspNetUsers anu on st.UserId = anu.Id inner join StorytimeGroup stg on st.StorytimeId = stg.StorytimeId Where stg.UserGroupId in (Select ug.UserGroupId From UserGroup ug inner join UserGroupUser ugu on ug.UserGroupId = ugu.UserGroupId Where ugu.UserId = @0) UNION Select st.*, anu.PhoneNumber from Storytime st inner join AspNetUsers anu on st.UserId = anu.Id inner join StorytimeUserList stul on st.StorytimeId = stul.StorytimeId Where stul.UserId = @0) select count(*) from c", new object[] { userid }, "Select * From (Select st.*, anu.PhoneNumber from Storytime st inner join AspNetUsers anu on st.UserId = anu.Id Where UserId = @0 UNION Select st.*, anu.PhoneNumber from Storytime st inner join AspNetUsers anu on st.UserId = anu.Id inner join StorytimeGroup stg on st.StorytimeId = stg.StorytimeId Where stg.UserGroupId in (Select ug.UserGroupId From UserGroup ug inner join UserGroupUser ugu on ug.UserGroupId = ugu.UserGroupId Where ugu.UserId = @0) UNION Select st.*, anu.PhoneNumber from Storytime st inner join AspNetUsers anu on st.UserId = anu.Id inner join StorytimeUserList stul on st.StorytimeId = stul.StorytimeId Where stul.UserId = @0) as result order by DateCreated Desc Offset " + offset + " Rows Fetch Next " + count + " Rows Only", new object[] { userid }); //var b = db.Page<Entities.Storytime>(int.Parse(page), int.Parse(count), "Select st.* from Storytime st Where UserId = @0 UNION Select st.* from Storytime st inner join StorytimeGroup stg on st.StorytimeId = stg.StorytimeId Where stg.UserGroupId in (Select ug.UserGroupId From UserGroup ug inner join UserGroupUser ugu on ug.UserGroupId = ugu.UserGroupId Where ugu.UserId = @0) UNION Select st.* from Storytime st inner join StorytimeUserList stul on st.StorytimeId = stul.StorytimeId Where stul.UserId = @0 group by st.datecreated, st.storytimeid, st.storytimetitle, st.storytimetypeid, st.userid order by st.datecreated desc, st.storytimeid, st.storytimetitle, st.storytimetypeid, st.userid", 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 =@0 ", 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; } } }