C# SQLiteDatabase.CommitTransaction方法代码示例

 public static bool Delete(string userId, string profileId)
 {
     bool deleted = false;
     SQLiteDatabase db = new SQLiteDatabase(true);
     // Can only delete planned
     string sql = @"delete from cycli_profile_spots where ProfileId = @p";
     db.ExecuteNonQuery(sql, "@p", profileId);
     sql = @"delete from cycli_profiles where ProfileId = @p";
     db.ExecuteNonQuery(sql, "@p", profileId);
     try
     {
       db.CommitTransaction();
       deleted = true;
     }    // No need to close Db as this is done by CommitTranaction
     catch (Exception ex)
     {
       db.RollbackTransaction();
       Console.WriteLine(ex);
     }
     return deleted;
 }

        public void Start()
        {
            // If real person has joined - finish the race straight away
            if (Participants.Where(p => p.Status == @"Scheduled" && p.RiderType == Rider.RiderType.User).Count() == 0)
            {
                this.Status = @"Finished";
            }
            else
            {
                this.Status = @"Started";
            }
            System.Diagnostics.Debug.WriteLine("BANG! " + this.Name + " started at " + DateTime.UtcNow.ToString());
            SQLiteDatabase db = new SQLiteDatabase(true);
            try
            {
                string sqlUpdate = @"update cycli_races set Status='" + this.Status + "' " +
            "where [email protected]";
                db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId);

                // Next query won't set any unscheduled to started
                sqlUpdate = @"update cycli_race_riders set Status='Started' " +
              "where [email protected] and Status='Scheduled'";

                db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId);

                // Tell the ride manager
                db.CommitTransaction();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                db.RollbackTransaction();
            }
            foreach (Participant p in Participants)
            {
                if (p.Status == @"Scheduled")
                {
                    p.Status = @"Started";
                }
            }
        }

        public void Schedule()
        {
            // Set race to scheduled
            string sqlRiderUpdate = @"update cycli_race_riders set Status='Scheduled' " +
            "where [email protected] and Status='Joined'";
            string sqlRaceUpdate = @"update cycli_races set Status='Scheduled' " +
              "where [email protected] and Status='Planned'";

            SQLiteDatabase db = new SQLiteDatabase(true);
            try
            {
                db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId);
                db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId);
                db.CommitTransaction();
            }
            catch (Exception ex)
            {
                try
                {
                    db.RollbackTransaction();
                }
                catch (Exception ex1)
                {
                }
            }
            finally
            {
                // Don't need to close on a transaction
            }
            this.Status = @"Scheduled";
            foreach (Participant p in this.Participants)
            {
                if (p.Status == @"Joined")
                {
                    p.Status = @"Scheduled";
                }
            }
        }

        public void Save(string userId)
        {
            // Check the time - only allow a minimum of 2 minutes
            long timeNow = Utilities.DbTime.ToDbSecs(DateTime.UtcNow);
            if (this.StartDateTime < timeNow - (timeNow % 60) + 120)
            {
                this.StartDateTime = timeNow - (timeNow % 60) + 120;
            }

            SQLiteDatabase db = new SQLiteDatabase(true);
            // Query will only allow the race director to save
            string sqlRace = @"update cycli_races set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], " +
                "[email protected], [email protected], " +
              "[email protected], [email protected], [email protected], [email protected] " +
              "where [email protected] and Status='Planned' and [email protected]";
            db.ExecuteNonQuery(sqlRace, "@n", this.Name, "@de", this.Description,
                                    "@tt", this.TargetType,
                                    "@c", this.Configuration,
                                    "@d", this.StartDateTime,
                                    "@p", this.PowerNormalisedSpeed.ToString(),
                                    "@dr", this.Drafting.ToString(),
                                    "@h", this.HandicapType.ToString(),
                                    "@pi", string.IsNullOrEmpty(this.ProfileId) ? "" : this.ProfileId,
                                    "@pt", string.IsNullOrEmpty(this.ProfileType) ? "" : this.ProfileType,
                                    "@mn", this.ProfileMin,
                                    "@mx", this.ProfileMax,
                                    "@r", this.RaceId,
                                    "@u", userId);

            // Delete any old riders
            string sqlDelete = "delete from cycli_race_riders where raceId = @r";
            db.ExecuteNonQuery(sqlDelete, "@r", this.RaceId);

            // Add in the new riders
            string sqlParticipant = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType, Handicap) " +
                                  "values (@u, @r, @s, @t, @h)";

            foreach (Participant p in this.Participants)
            {
                db.ExecuteNonQuery(sqlParticipant, "@u", p.UserId, "@r", this.RaceId, "@s", p.Status, "@t",
                    (p.RiderType == Rider.RiderType.User ? "USER" : "VIRTUAL"), "@h", p.Handicap);
            }

            try
            {
                db.CommitTransaction();
            }    // No need to close Db as this is done by CommitTranaction
            catch (Exception ex)
            {
                db.RollbackTransaction();
                Console.WriteLine(ex);
            }
        }

        public void Finish(RaceSpot[] rss, Dictionary<string, Stack<RaceSpot>> history)
        {
            string sqlRiderUpdate = @"update cycli_race_riders set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] " +
            "where [email protected] and UserId[email protected]";
            string sqlRaceUpdate = @"update cycli_races set Status='Finished' " +
              "where [email protected] and Status='Started'";

            SQLiteDatabase db = new SQLiteDatabase(true);
            try
            {
                db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId);
                foreach (RaceSpot rs in rss)
                {
                    db.ExecuteNonQuery(sqlRiderUpdate, "@p", rs.pos, "@d", rs.d, "@t", rs.t, "@e", (int)rs.e, "@k", (int)rs.k, "@b", (int)rs.b, "@i", (int)rs.i, "@r", this.RaceId, "@u", rs.id);
                }
                db.CommitTransaction();
            }
            catch (Exception ex)
            {
                try
                {
                    db.RollbackTransaction();
                }
                catch (Exception ex1)
                {
                }
            }
            finally
            {
                // Don't need to close on a transaction
            }
            // Do points as a separate transaction
            string raceSpotSql = @"insert into cycli_race_spots (UserId, RaceId, Time, Distance, Speed, Cadence, HR, Power) " +
                            "values (@u, @r, @t, @d, @s, @c, @h, @p)";

            db = new SQLiteDatabase(true);
            try
            {
                foreach (string userId in history.Keys)
                {
                    Stack<RaceSpot> userHistory = history[userId];
                    foreach (RaceSpot rs in userHistory)
                    {
                        db.ExecuteNonQuery(raceSpotSql, "@r", this.RaceId, "@u", userId, "@t", rs.t, "@d", rs.d, "@s", rs.s, "@c", rs.c, "@h", rs.h, "@p", rs.p);
                    }
                }
                db.CommitTransaction();
            }
            catch (Exception ex)
            {
                try
                {
                    db.RollbackTransaction();
                }
                catch (Exception ex1)
                {
                }
            }
            finally
            {
            }
            // Mark the race as Finished
            this.Status = @"Finished";
        }

        public static Dictionary<string, int> SetHandicaps(string raceId)
        {
            Dictionary<string, int> handicaps = new Dictionary<string, int>();

            Race race = Load(raceId);
            foreach (Participant p in race.Participants)
            {
                handicaps.Add(p.UserId, 100);
            }
            if (race != null)
            {

                SQLiteDatabase db = new SQLiteDatabase();
                long yearAgo = DbTime.ToDbSecs(DateTime.UtcNow);

                string sql = "select rr1.UserId, " +
                              "r.StartDateTime, " +
                              "(1000 * cast(rr1.Energy as real)/ cast(rr1.Time as real)) as Power " +
                              "From cycli_race_riders rr1, cycli_race_riders rr2, cycli_races r " +
                              "where r.StartDateTime > @t " +
                              "and r.RaceId = rr2.RaceId " +
                              "and rr1.Time > 0 " +
                              "and rr1.Energy > 0 " +
                              "and (rr1.Status='Finished' or rr1.Status='Abandoned') " +
                              "and rr1.UserId = rr2.UserId " +
                              "and r.RaceId = @r " +
                              "order by rr1.Time";
                DataTable dt = db.GetDataTable(sql, "@t", yearAgo, "@r", raceId);
                db.Close();

                // Weight the average power by (a) time in the past
                //                             (b) closeness to the selected distance

                Dictionary<string, double> userPowers = dt.AsEnumerable()
                    .GroupBy(p => p.Field<string>("UserId"))
                    .ToDictionary(p => p.Key,
                    p => p.Sum(q => (double)(yearAgo - q.Field<long>("StartDateTime")) * q.Field<double>("Power"))/
                                p.Sum(q => yearAgo - q.Field<long>("StartDateTime")));

                // Total AveragePower across all users
                double meanUserPower = userPowers.Values.Average(p => p);
                if (meanUserPower > 0)
                {
                    foreach (KeyValuePair<string, double> k in userPowers)
                    {
                        int handicap = (int)Math.Round(100 * meanUserPower / k.Value);
                        handicaps[k.Key] = handicap;
                    }
                }

                string updateSql = "update cycli_race_riders set [email protected] where [email protected] and [email protected]";
                db = new SQLiteDatabase(true);
                try
                {
                    foreach (KeyValuePair<string, int> h in handicaps)
                    {
                        db.ExecuteNonQuery(updateSql, "@h", h.Value, "@r", raceId, "@u", h.Key);
                    }
                    try
                    {
                        db.CommitTransaction();
                    }
                    catch (Exception ex)
                    {
                        db.RollbackTransaction();
                    }
                }
                finally
                {
                }

            }
            return handicaps;
        }

        public static Race New(string userId)
        {
            Race race = null;
            UserRider rider = UserRider.Load(userId);
            if (rider != null)
            {
                // Check we haven't exeeded our limit - this is a backstop test
                // First check prevents a user creating races
                Race[] existingRaces = Race.LoadPlannedOrRacing(userId);
                if (existingRaces.Length < rider.MaximumRaces)
                {

                    string newRaceId = Guid.NewGuid().ToString();
                    long startDateTime = Utilities.DbTime.ToDbSecs(DateTime.UtcNow.AddDays(7));
                    SQLiteDatabase db = new SQLiteDatabase(true);

                    string sql = "insert into cycli_races (RaceId, StartDateTime, RaceDirectorId) " +
                                  "values (@r, @s, @rd)";
                    db.ExecuteNonQuery(sql, "@r", newRaceId, "@s", startDateTime, "@rd", userId);
                    sql = "insert into cycli_race_riders (UserId, RaceId) " +
                                  "values (@u, @r)";
                    db.ExecuteNonQuery(sql, "@r", newRaceId, "@u", userId);
                    try
                    {
                        db.CommitTransaction();
                        try
                        {
                            race = Load(newRaceId);
                        }
                        catch (Exception ex1)
                        {
                            Console.WriteLine(ex1);
                        }
                    }    // No need to close Db as this is done by CommitTranaction
                    catch (Exception ex)
                    {
                        db.RollbackTransaction();
                        Console.WriteLine(ex);
                    }
                }
            }
            return race;
        }

        // Returns invites, joins and finishes - in that order
        public static void Cancel(string userId, string raceId)
        {
            // Load the race as we need to check status - can only delete planned
            Race r = Load(raceId);
            // Protection - we can only cancel if we're the race director.  Good try Mr, Mrs or Ms Hacker
            if (r != null && r.RaceDirectorId == userId && r.Status == @"Planned")
            {

                SQLiteDatabase db = new SQLiteDatabase(true);
                // Can only delete planned
                string sql = @"delete from cycli_race_riders where RaceId = @r";
                db.ExecuteNonQuery(sql, "@r", r.RaceId);
                sql = @"delete from cycli_races where RaceId = @r";
                db.ExecuteNonQuery(sql, "@r", r.RaceId);
                try
                {
                    db.CommitTransaction();
                }    // No need to close Db as this is done by CommitTranaction
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    Console.WriteLine(ex);
                }
            }
        }

 public void Save(string userId)
 {
     if (userId != this.UserId)
       {
     // stop anyone other than the owner changing
     return;
       }
       long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow);
       // It's an update
       string sqlProfile = @"update cycli_profiles set [email protected], [email protected], [email protected], [email protected] where [email protected]";
       string sqlDeleteSpots = @"delete from cycli_profile_spots where [email protected]";
       string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y, PointOrder) values (@p, @x, @y, @i)";
       SQLiteDatabase db = new SQLiteDatabase(true);
       try
       {
     db.ExecuteNonQuery(sqlProfile,
                         "@n", this.Name,
                         "@d", this.Description,
                         "@f", this.Favourite.ToString(),
                         "@m", timeNowDb,
                         "@p", this.ProfileId);
     db.ExecuteNonQuery(sqlDeleteSpots,"@p", this.ProfileId);
     int i = 0;
     foreach (float[] pt in data)
     {
       db.ExecuteNonQuery(sqlSpot,
                         "@p", this.ProfileId,
                         "@x", pt[0],
                         "@y", pt[1],
                         "@i",i);
       i++;
     }
     db.CommitTransaction();
       }
       catch (Exception ex)
       {
     try
     {
       db.RollbackTransaction();
     }
     catch (Exception ex1)
     {
     }
       }
       finally
       {
       }
 }

        public static Profile New(string userId)
        {
            Profile p = new Profile();

              long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow);
              p.ProfileId = Guid.NewGuid().ToString();
              p.UserId = userId;
              p.Name = "New Profile";
              p.Description = "";
              p.Favourite = false;
              // It's a new profile
              string sqlProfile = @"insert into cycli_profiles (ProfileId, UserId, Name, Description, Favourite, CreatedOn, ModifiedOn) " +
                          "values (@p, @u, @n, @d, @f, @c, @m)";
              string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y) values (@p, @x, @y)";
              SQLiteDatabase db = new SQLiteDatabase(true);
              try
              {
            db.ExecuteNonQuery(sqlProfile,
                                "@p", p.ProfileId,
                                "@u", p.UserId,
                                "@n", p.Name,
                                "@d", p.Description,
                                "@f", p.Favourite.ToString(),
                                "@c", timeNowDb,
                                "@m", timeNowDb);
            db.ExecuteNonQuery(sqlSpot,
                                "@p", p.ProfileId,
                                "@x", 0,
                                "@y", 50);
            db.ExecuteNonQuery(sqlSpot,
                                "@p", p.ProfileId,
                                "@x", 100,
                                "@y", 50);

            db.CommitTransaction();
              }
              catch (Exception ex)
              {
            try
            {
              db.RollbackTransaction();
            }
            catch(Exception ex1)
            {
            }
              }
              finally
              {
              }
              return p;
        }

        public static void Delete(string userId, string riderId)
        {
            // Has this rider been employed in a race
             string sqlPlayed = @"select count(*) from cycli_race_riders where [email protected] and (Status='Finished' or Status='Abandoned')";

             string sqlRiderDelete = @"delete from cycli_virtual_riders where ownerid = @o and userid = @u";
             string sqlRiderRaceDelete = @"delete from cycli_race_riders where userid = @u";
             string sqlRiderInactive = @"update cycli_virtual_riders set status='Inactive' where ownerid = @o and userid = @u";

             bool riderPlayed = false;
             SQLiteDatabase db = new SQLiteDatabase();

             try
              {
            riderPlayed = (int.Parse(db.ExecuteScalar(sqlPlayed,"@u",riderId)) > 0);
              }
              finally
              {
            db.Close();
              }

             // Now reopen for transactions
             db = new SQLiteDatabase(true);

             try
             {
               if (riderPlayed)
               {
             db.ExecuteNonQuery(sqlRiderInactive, "@o", userId, "@u", riderId);
               }
               else
               {
             // Get rid of rider from any pending races
             db.ExecuteNonQuery(sqlRiderRaceDelete, "@u", riderId);
             db.ExecuteNonQuery(sqlRiderDelete, "@o", userId, "@u", riderId);
               }
               // Commit closes db
               db.CommitTransaction();
             }
             catch (Exception ex)
             {
               db.RollbackTransaction();
             }
        }