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

        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="userName"></param>
 /// <returns></returns>
 public static User GetUser(string userName)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.SingleOrDefault<Models.User>("WHERE [email protected] ", userName);
     }
 }

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

            var customer = new Customer { FirstName = "Ian", LastName = "Russell", Email = "[email protected]" };

            petaPoco.Insert(customer);

            var id = customer.CustomerId;

            customer = petaPoco.Single<Customer>(id);

            ObjectDumper.Write(customer);

            customer.Country = "United Kingdom";

            petaPoco.Update(customer);

            customer = petaPoco.Single<Customer>(id);

            ObjectDumper.Write(customer);

            petaPoco.Delete<Customer>(id);

            customer = petaPoco.SingleOrDefault<Customer>(id);

            ObjectDumper.Write(customer);
        }

        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 IHttpActionResult Post([FromUri] string id, [FromBody]System.Collections.Generic.List<Entities.Contact> contactlist)
        {
            if (contactlist != null)
            {
                var db = new PetaPoco.Database("AGSoftware");

                List<Entities.Contact> newcontactlist = new List<Entities.Contact>();

                foreach (Entities.Contact contact in contactlist)
                {
                    var iscontact = db.SingleOrDefault<Entities.AspNetUsers>("Select * From ASPNetUsers Where PhoneNumber = @0", contact.PhoneNumber);

                    if (iscontact != null)
                    {
                        newcontactlist.Add(new Entities.Contact(iscontact.Id, iscontact.PhoneNumber, contact.FirstName, contact.LastName));
                    }
                }

                if (newcontactlist.Count > 0)
                    return Ok(newcontactlist);
                else
                    return NotFound();
            }
            else
            {
                return BadRequest("Contact List was empty");
            }
        }

 /// <summary>
 /// 获取配置
 /// </summary>
 /// <param name="title"></param>
 /// <returns></returns>
 public static Settings GetSetting(string title)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.SingleOrDefault<Models.Settings>("WHERE [email protected]", title);
     }
 }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                // Create a PetaPoco database object
                var db = new PetaPoco.Database("sqlite");

                // find the (presumably) most recently created foo
                int id = db.ExecuteScalar<int>("SELECT max(id) from foo");

                // Get a record
                var foo = db.SingleOrDefault<foo>("SELECT * FROM foo WHERE [email protected]", id);

                // Change it
                foo.name = "PetaPoco changed your name!";

                // Save it
                db.Update("foo", "Id", foo);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
            }

            this.fooQuery1.Refresh();
        }

        //
        // GET: /Classes/Details/5
        public ActionResult Details(int id)
        {
            var db = new PetaPoco.Database("MissNancy");
            var myClass = db.SingleOrDefault<Classes>("SELECT * FROM tblClasses WHERE ClassKey = @0", id);

            return View(myClass);
        }

 public static User GetUserByOpenID(string openid)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.SingleOrDefault<Models.User>("WHERE [email protected] ", openid);
     }
 }

 public static Models.UserGroup GetUserGroup(int id)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         return db.SingleOrDefault<Models.UserGroup>("WHERE [email protected] ", id);
     }
 }

        private static Entities.AspNetUsers GetUser(string username)
        {
            var db = new PetaPoco.Database("AGSoftware");

            var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where UserName = @0", username);

            return a;
        }

        private static Entities.AspNetUsers GetUserById(string id)
        {
            var db = new PetaPoco.Database("AGSoftware");

            var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", id);

            return a;
        }

        /// <summary>
        /// Gets a snapshot for a give ID if one exists
        /// </summary>
        /// <param name="aggregateRootId">Aggregate root ID</param>
        /// <returns>Snapshot details</returns>
        public SnapshotDetail GetSnapshot(Guid aggregateRootId)
        {
            CreateSnapshotTable();

            using (var db = new PetaPoco.Database(_snapshotConnectionStringName))
            {
                return db.SingleOrDefault<SnapshotDetail>(TopOneSnapshot, aggregateRootId);
            }
        }

        public IHttpActionResult Get(string id)
        {
            var db = new PetaPoco.Database("AGSoftware");

            var a = db.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where UserId = @0", id);

            if (a != null)
                return Ok(a);
            else
                return NotFound();
        }

        private void NotifyNewStory()
        {
            var db = new PetaPoco.Database("AGSoftware");
            var db2 = new PetaPoco.Database("AGSoftware");
            var db3 = new PetaPoco.Database("AGSoftware");

            foreach (var a in db.Query<Entities.StorytimeUserList>("Select * From StorytimeUserList Where UserNotified = 0"))
            {
                var b = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", a.UserId);
                var storytime = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", a.StorytimeId);

                if (storytime.UserId != b.Id && b.DeviceToken != null)
                {
                    CreatePushNotification(newstory + storytime.StorytimeTitle, b.DeviceToken);
                }

                a.UserNotified = true;
                db2.Update(a);
            }

            foreach (var c in db.Query<Entities.StorytimeGroup>("Select * from StorytimeGroup Where UsersNotified = 0"))
            {
                var d = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);
                var storytime = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", c.StorytimeId);

                foreach (var e in db3.Query<Entities.UserGroupUser>("Select * from UserGroupUser Where UserGroupId = @0", d.UserGroupId))
                {
                    var f = db2.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", e.UserId);

                    if (f != null && f.Id != storytime.UserId && f.DeviceToken != null)
                    {
                        CreatePushNotification(newstory + storytime.StorytimeTitle, f.DeviceToken);
                    }
                }

                c.UsersNotified = true;
                db3.Update(c);
            }
        }