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

        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("结束");
        }

        private void SelectAll()
        {
            // Create a PetaPoco database object
            var db = new PetaPoco.Database("sqlite");

            string query = "SELECT * FROM foo";

            StringBuilder sb = new StringBuilder();
            sb.AppendLine(query);
            sb.AppendLine("--------------");

            try
            {
                // Show all foo
                foreach (var a in db.Query<foo>(query))
                {
                    sb.AppendLine(string.Format("{0} - {1}", a.Id, a.name));
                }
            }
            catch (Exception ex)
            {
                sb.AppendLine(ex.Message);
                sb.Append(ex.StackTrace);
            }

            this.richTextBox1.Text = sb.ToString();
        }

 public static List<Models.Page> GetAllPages()
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Query<Models.Page>("").ToList();
     }
 }

 /// <summary>
 /// 获取所有的
 /// </summary>
 /// <returns></returns>
 public static List<Settings> GetAllSetting()
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Query<Models.Settings>("").ToList();
     }
 }

 public static List<string> GetAllTag()
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Query<string>("SELECT tag_title  FROM  jexus_tags  ORDER BY tag_title DESC").ToList();
     }
 }

 public static List<Models.Category> GetAllCategory()
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.Query<Models.Category>("").ToList();
     }
 }

        public IHttpActionResult Get(string id)
        {
            var db = new PetaPoco.Database("AGSoftware");
            var db2 = new PetaPoco.Database("AGSoftware");
            System.Collections.Generic.List<Entities.StorytimePost> storytimepostlist = new List<Entities.StorytimePost>();

            string UserId = "";

            foreach (Entities.StorytimePost c in db.Query<Entities.StorytimePost>("Select * From StorytimePost Where SeriesId = @0 Order By DateCreated Desc", id))
            {
                c.ImagePath = Providers.ImageHelper.GetImagePath(c.ImagePath);
                c.ImagePath = c.ImagePath.Replace(@"\", @"/");
                UserId = Providers.UserHelper.GetUserId(this.User.Identity.Name);
                var voted  = db2.SingleOrDefault<Entities.Vote>("Select * From Vote Where StorytimePostId = @0 And UserId = @1", new object []{c.StorytimePostId, UserId});

                if (voted != null)
                    c.Voted = true;
                else
                    c.Voted = false;

                if (c.UserId == UserId)
                    c.UserPostedImage = true;

                c.PhoneNumber = Providers.UserHelper.GetPhoneNumberById(c.UserId);

                storytimepostlist.Add(c);
            }

            if (storytimepostlist.Count > 0)
                return Ok(storytimepostlist);
            else
                return NotFound();
        }

 public void InitDataBase(string connectionString)
 {
     var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
     db.Execute(Const.DBCreateScript);
     foreach (var province in ProvinceData.GetProvinces())
     {
         db.Insert("Province", "Id", new{Name = province.Name, Code = province.Code});
     }
     var provinces = db.Query<dynamic>(@"SELECT *
     from Province").ToList();
     BulkUploadToSql bulk =
            BulkUploadToSql.Load(
                HomeData.GetHomes()
                    .Select(
                        i =>
                            new Bulk.Home
                            {
                                AddTime = DateTime.Now,
                                BuildYear = i.BuildYear,
                                City = i.City,
                                Description = i.Description,
                                Price = i.Price,
                                Surface = i.Surface,
                                ProvinceId = provinces.First(j => j.Code == i.HomeProvince.Code).Id,
                            }), "Home", 10000, connectionString);
     bulk.Flush();
 }

 public IEnumerable<HoiVien> GetCustomers()
 {
     var db = new PetaPoco.Database("MyConnection");
     IEnumerable<HoiVien> kq;
     if (TempData["dieukienloc"] == null)//truy van mac dinh
     {
         kq = db.Query<HoiVien>("select * from HoiVien where Disable = '0'");
     }
     else//Truy van khi da co tempdata(da co dieu kien loc)
     {
         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[1] != "")
         {
             qr = qr + string.Format(" and Address like N'%{0}%'", frmc[1]);
         }
         if (frmc[2] != "")
         {
             qr = qr + string.Format(" and Phone like N'%{0}%'", frmc[2]);
         }
         if (frmc[3] != "")
         {
             qr = qr + string.Format(" and MemberID like N'%{0}%'", frmc[3]);
         }
         if (frmc[4] != "")
         {
             qr = qr + string.Format(" and Identify like N'%{0}%'", frmc[4]);
         }
         if (frmc[5] != "")
         {
             qr = qr + string.Format(" and Fax like N'%{0}%'", frmc[5]);
         }
         if (frmc[6] != "")
         {
             qr = qr + string.Format(" and MemberName like N'%{0}%'", frmc[6]);
         }
         if (frmc[7] != "")
         {
             qr = qr + string.Format(" and Tel like N'%{0}%'", frmc[7]);
         }
         if (frmc[8] != "")
         {
             qr = qr + string.Format(" and Email like N'%{0}%'", frmc[8]);
         }
         if (frmc[9] == "")
         {
             qr = qr + string.Format(" and Disable = '0'");
         }
         kq = db.Query<HoiVien>(qr);
     }
     return kq;
 }

 public object GET(int id) {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT N.*")
         .Append("FROM Novedad N")
         .Append("WHERE [email protected]", id);
     var items = db.Query<dynamic>(sql);
     return (new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
 }

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

            var customers = petaPoco.Query<Customer>("SELECT * FROM customer WHERE Country = 'United Kingdom'");

            foreach (var customer in customers)
                ObjectDumper.Write(customer);
        }

 public object GET(int id) {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT C.*")
         .Append("FROM Contacto C")
         .Append("WHERE [email protected]", id);
     var items = db.Query<Contacto>(sql);
     return (new JsonResult() { Data = new { data = items }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
 }

 public object getAll() {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT SEC.*")
         .Append("FROM SubEstadoContacto SEC");
     var items = db.Query<SubEstadoContacto>(sql);
     JsonResult rta = new JsonResult() { Data = items, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
     return rta.Data;
 }

 public object get(int id) {
     var db = new PetaPoco.Database("jlapc");
     var sql = PetaPoco.Sql.Builder
         .Append("SELECT SEC.*")
         .Append("FROM SubEstadoContacto SEC")
         .Append("WHERE [email protected]", id);
     var items = db.Query<SubEstadoContacto>(sql);
     return (new JsonResult() { Data = items, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
 }

 public void GetYueSaoList()
 {
     var db = new PetaPoco.Database("BBTDataBaseConnection");
     var hotelList = db.Query<dat_sclient_base>("select * from dat_sclient_base");
     foreach (var item in hotelList)
     {
         Console.Write(item.c_addr);
     }
 }