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

        public void BasicCRUD()
        {
            var db = new PetaPoco.Database("Chinook");
            var newID = db.Insert(new Artist
            {
                Name = "Woot"
            });

            var acdcnew = db.Update(new Artist { ArtistId = 1, Name = "AC/DC Blah" });
            var acdc = db.Update(new Artist { ArtistId = 1, Name = "AC/DC" });
        }

        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 object POST([FromBody] Novedad item) {
     try {
         var db = new PetaPoco.Database("jlapc");
         if (item.NovedadID == 0) {
             db.Insert(item);
         } else {
             db.Update(item);
         }
         return (new JsonResult() { Data = new { ok = true, error = "" }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
     } catch (Exception e) {
         return (new JsonResult() { Data = new { ok = false, error = e.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
     }
 }

        public static bool AddComment(Models.Comment comment)
        {
            var f = GetForum(comment.FId);
            if (f == null) return false;
            using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
            {
                f.LastReply = DateTime.Now;
                f.Comments++;
                f.Ruid = comment.UId;
                db.Update(f, new string[] { "lastreply", "comments","ruid" });

                return db.Insert(comment) != null;
            }
        }

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

        private void fbLogin(HttpContext context)
        {
            string token = context.Request.Params["token"];
            Facebook.FacebookClient client = new Facebook.FacebookClient(token);
            //client.Post()
            client.UseFacebookBeta = client.IsSecureConnection = true;
            Facebook.JsonObject o = (Facebook.JsonObject)client.Get("/me");
            var db = new PetaPoco.Database(Common.HairStyleConnectionString, "System.Data.SqlClient");
            using (var scope = db.GetTransaction())
            {

                try
                {
                    string first_name = (string)o["first_name"];
                    string name = (string)o["name"];
                    decimal id = Convert.ToDecimal(o["id"]);
                    POCOS.Facebook fb = new POCOS.Facebook();
                    fb.name = name;
                    fb.first_name = first_name;
                    fb.gender = (string)o["gender"];
                    fb.id = id;
                    fb.last_name = (string)o["last_name"];
                    fb.link = (string)o["link"];
                    fb.locale = (string)o["locale"];
                    fb.timezone = Convert.ToDouble(o["timezone"]);
                    string updatedtime = (string)o["updated_time"];
                    DateTime dt;
                    if (DateTime.TryParse(updatedtime, out dt))
                        fb.updated_time = dt;
                    if (db.Exists<POCOS.Facebook>(id))
                        db.Update(fb);
                    else
                        db.Insert(fb);
                    POCOS.AppUser au = POCOS.AppUser.FirstOrDefault("Select top 1 * from AppUsers where [email protected]", id);
                    if (au == null)
                    {
                        au = new POCOS.AppUser();
                        au.FirstName = first_name;
                        au.facebookid = id;
                        db.Insert(au);
                    }
                    scope.Complete();
                    CookieUtil.WriteCookie(Common.AuthCookie, EncDec.Encrypt(JsonConvert.SerializeObject(new { ID = au.ID }), Common.DefaultPassword), false);
                    CookieUtil.WriteCookie(Common.InfoCookie, JsonConvert.SerializeObject(new { email = au.Email, name = au.Name, avatar = string.IsNullOrWhiteSpace(au.Avatar) ? null : Common.UploadedImageRelPath + au.Avatar }), false);
                }
                finally
                {
                    scope.Dispose();
                }
            }
        }

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

            foreach (var a in db.Query<Entities.SeriesWinner>("Select ss.StorytimeSeriesId, s.StorytimeTypeId, s.StorytimeId, s.StorytimeTitle, ss.SeriesText, u.Username, sp1.Votes From StorytimeSeries ss inner join Storytime s on s.StorytimeId = ss.StorytimeId inner join AspNetUsers u on ss.UserId = u.Id inner join StorytimePost sp1 on sp1.SeriesId = ss.StorytimeSeriesId And sp1.Votes = (Select MAX(sp2.Votes) From StorytimePost sp2 Where sp2.StorytimeId = sp1.StorytimeId) Where ss.UsersNotified = 0 And ss.DateCreated < DateAdd(hh, -24, GetDate())"))
            {
                if (a.StorytimeTypeId == 1)
                {
                    var b = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
                    var c = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", b.UserGroupId);

                    foreach (var d in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", c.UserGroupId))
                    {
                        if (d != null)
                        {
                            var e = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", d.UserId);

                            if (e != null && e.DeviceToken != null)
                            {
                                string notificationtext = string.Empty;
                                if (a.Votes > 0)
                                    notificationtext = serieswinner + " " + a.Username + " has won the series " + a.SeriesText + " for Story " + a.StorytimeTitle + " with " + a.Votes + " votes!";
                                else
                                    notificationtext = nowinner + a.SeriesText + " for Story " + a.StorytimeTitle;
                                CreatePushNotification(notificationtext, e.DeviceToken);
                            }
                        }
                    }
                }
                else if (a.StorytimeTypeId == 2)
                {
                    foreach (var f in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
                    {
                        var g = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);

                        if (g != null && g.DeviceToken != null)
                        {
                            string notificationtext = string.Empty;
                            if (a.Votes > 0)
                                notificationtext = serieswinner + " " + a.Username + " has won the series " + a.SeriesText + " for Story " + a.StorytimeTitle + " with " + a.Votes + " votes!";
                            else
                                notificationtext = nowinner + a.SeriesText + " for Story " + a.StorytimeTitle;
                            CreatePushNotification(notificationtext, g.DeviceToken);
                        }
                    }
                }

                var storytimeseries = db4.SingleOrDefault<Entities.StorytimeSeries>("Select * from StorytimeSeries Where StorytimeSeriesId = @0", a.StorytimeSeriesId);

                storytimeseries.UsersNotified = true;
                db4.Update(storytimeseries);

            }
        }

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

            foreach (var a in db.Query<Entities.Vote>("Select * From Vote v Where v.UserNotified = 0"))
            {
                var b = db2.SingleOrDefault<Entities.StorytimePost>("Select * from Storytimepost Where StorytimePostId = @0", a.StorytimePostId);
                var c = db2.SingleOrDefault<Entities.Storytime>("Select * from Storytime Where StorytimeId = @0", b.StorytimeId);
                var d = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", c.StorytimeId);

                if (c.StorytimeTypeId == 1)
                {
                    var e = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", d.UserGroupId);

                    foreach(var f in db3.Query<Entities.UserGroupUser>("Select * From UserGroupUser Where UserGroupId = @0", e.UserGroupId))
                    {
                        if (f != null)
                        {
                            var g = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", f.UserId);

                            if (g != null && g.DeviceToken != null)
                            {
                                string notificationtext = newvote + " " + g.UserName + " has voted for " + b.PostText;
                                CreatePushNotification(newvote, g.DeviceToken);
                            }
                        }
                    }
                }
                else if(c.StorytimeTypeId == 2)
                {
                    foreach (var h in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", c.StorytimeId))
                    {
                        var i = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", h.UserId);

                        if (i != null && i.DeviceToken != null)
                        {
                            string notificationtext = newvote + " " + i.UserName + " has voted for " + b.PostText;
                            CreatePushNotification(newvote, i.DeviceToken);
                        }
                    }
                }

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

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

            foreach (var a in db.Query<Entities.Storytime>("select * from storytime s where (select count(*) from storytimeseries ss where ss.storytimeid = s.storytimeid) = 10 and s.UsersNotified = 0"))
            {
                var b = db2.SingleOrDefault<Entities.StoryWinner>("Select top 1 sum(sp.votes) as votes, u.username, s.storytimetitle from storytimeseries ss inner join storytimepost sp on ss.StorytimeSeriesId = sp.SeriesId inner join AspNetUsers u on u.id = sp.UserId inner join storytime s on s.storytimeid = ss.storytimeid where ss.storytimeid = @0 group by u.username, s.storytimetitle order by votes desc", a.StorytimeId);

                if (a.StorytimeTypeId == 1)
                {
                    var c = db2.SingleOrDefault<Entities.StorytimeGroup>("Select * from StorytimeGroup Where StorytimeId = @0", a.StorytimeId);
                    var d = db2.SingleOrDefault<Entities.UserGroup>("Select * from UserGroup Where UserGroupId = @0", c.UserGroupId);

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

                            if (f != null && f.DeviceToken != null)
                            {
                                CreatePushNotification(storywinner + f.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", f.DeviceToken);
                            }
                        }
                    }
                }
                else if (a.StorytimeTypeId == 2)
                {
                    foreach (var g in db3.Query<Entities.StorytimeUserList>("Select * from StorytimeUserList Where StorytimeId = @0", a.StorytimeId))
                    {
                        var h = db4.SingleOrDefault<Entities.AspNetUsers>("Select * from AspNetUsers Where Id = @0", g.UserId);

                        if (h != null && h.DeviceToken != null)
                        {
                            CreatePushNotification(storywinner + h.UserName + " has won the story " + a.StorytimeTitle + " with " + b.Votes + " !", h.DeviceToken);
                        }
                    }
                }

                a.UsersNotified = true;
                db4.Update(a);
            }
        }

 private static int Update100Test(string connectionString)
 {
     var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
     var homes = db.Query<Home>("SELECT * FROM home WHERE [email protected]", 2014).ToList();
     int count = homes.Count();
     foreach (var home in homes)
     {
         if (home != null)
         {
             home.BuildYear = 2015;
             db.Update("Home", "Id", home);
         }
     }
     return count;
 }

 public static Models.Forums GetForum(int id)
 {
     using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
     {
         var f = db.SingleOrDefault<Models.Forums>("WHERE [email protected]", id);
         if (f != null)
         {
             f.Views++;
             db.Update(f, new string[] { "views" });
         }
         return f;
     }
 }

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