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;
}
}
}