C# SQLiteDatabase.Close方法代码示例

        public static RiderMessage[] GetMessages(string userId,string raceId)
        {
            List<RiderMessage> messages = new List<RiderMessage>();
              SQLiteDatabase db = new SQLiteDatabase();
              string sql = "select m.RaceId, u.userid,u.username, m.message, m.SendTime " +
                    "from cycli_rider_messages m, cycli_riders u " +
                    "where m.SenderId = u.UserId and " +
                    "m.RaceId = @r " +
                      "order by m.SendTime";

              DataTable dt = db.GetDataTable(sql,"@r",raceId, "@t", "fromTime");
              db.Close();
              foreach (DataRow dr in dt.Rows)
              {
            RiderMessage message = new RiderMessage();
            message.SenderId = (string)dr["userid"];
            message.Sender = (string)dr["username"];
            message.RaceId = (string)dr["RaceId"];
            message.Message = (string)dr["Message"];
            message.SendTime = (long)(int)dr["SendTime"];
            messages.Add(message);
              }

              return messages.ToArray();
        }

 public static string ChangeFriendCode(string userId)
 {
     SQLiteDatabase db = new SQLiteDatabase();
       // Some logic here - we only allow a new friend if
       // (a) The friendship does not already exist
       // (b) The friendCode is correct
       string g = Guid.NewGuid().ToString();
       string sqlFriendCode = "update cycli_riders set [email protected] where UserId = @u";
       db.ExecuteNonQuery(sqlFriendCode, "@g", g, "@u", userId);
       // This query checks that the user has not already been invited
       db.Close();
       return g;
 }

 protected void Page_Init(object sender, EventArgs e)
 {
     SQLiteDatabase db = new SQLiteDatabase();
       string sql = @"select nationality from cycli_nationalities";
       DataTable dtNationality = db.GetDataTable(sql);
       foreach (DataRow dr in dtNationality.Rows)
       {
     nationality.Items.Add((string)(dr[0]));
       }
       sql = @"select type from cycli_turbos order by type";
       DataTable dtTurbos = db.GetDataTable(sql);
       foreach (DataRow dr in dtTurbos.Rows)
       {
     turbo.Items.Add((string)(dr[0]));
       }
       db.Close();
 }

 public static Dictionary<string, float[][]> Biometrics(string userId, string riderId, string raceId)
 {
     Race race = Race.Load(raceId);
     Dictionary<string, float[][]> biometrics = new Dictionary<string, float[][]>();
     SQLiteDatabase db = new SQLiteDatabase();
     string sql = "select s.Time, s.Distance, s.Speed, s.Cadence, s.Hr, s.Power, r.TargetType, r.Configuration " +
                   "From cycli_race_spots s, cycli_races r " +
                   "where s.UserId [email protected] " +
                   "and s.RaceId = @r " +
                   "and s.RaceId = r.RaceId " +
                   "order by s.Time";
     DataTable dt = db.GetDataTable(sql, "@u", riderId, "@r", raceId);
     if (dt.Rows.Count > 0)
     {
         // Need to scale by race type
         float[][] speedProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<double>("Speed") }).ToArray();
         biometrics.Add("speed", speedProfile);
         float[][] cadenceProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Cadence") }).ToArray();
         biometrics.Add("cadence", cadenceProfile);
         float[][] hrProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Hr") }).ToArray();
         biometrics.Add("hr", hrProfile);
         float[][] powerProfile = dt.AsEnumerable().Select(p => new float[] { (float)p.Field<dynamic>(race.RaceType.TargetProgress), (float)p.Field<long>("Power") }).ToArray();
         biometrics.Add("power", powerProfile);
     }
     db.Close();
     return biometrics;
 }

 public static void BailVirtualRider(string userId, string riderId, string raceId)
 {
     SQLiteDatabase db = new SQLiteDatabase();
     string sql = "delete from cycli_race_riders " +
                 "where Status='Joined' and [email protected] and [email protected] " +
                 "and exists (select 1 from cycli_races where [email protected] and Status='Planned' and [email protected])";
     db.ExecuteNonQuery(sql, "@r", raceId, "@f", riderId, "r1", raceId, "@u", userId);
     db.Close();
     Race race = Race.Load(raceId);
     if (race != null)
     {
         CycliManager.Instance.SendRiderBail(race, riderId);
     }
 }

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

 public static void SetRiderHandicap(string userId, string riderId, string raceId, int handicap)
 {
     SQLiteDatabase db = new SQLiteDatabase();
     // Update with checks on race status and ownership
     string sql = @"update cycli_race_riders set [email protected] " +
       "where [email protected] and [email protected] and (Status='Invited' or Status='Joined') " +
       "and exists (select 1 from cycli_races where [email protected] and Status='Planned' and [email protected])";
     db.ExecuteNonQuery(sql, "@h", handicap, "@r", raceId, "@rr", riderId,"@r1",raceId, "@u", userId);
     db.Close();
 }

        public static VirtualRider[] LoadAll(string userId)
        {
            List<VirtualRider> riders = new List<VirtualRider>();
             SQLiteDatabase db = new SQLiteDatabase();
             string sql = @"select UserId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression " +
               "From cycli_virtual_riders r " +
            "where [email protected] and Status='Active'";
             // Only load active accounts
             DataTable dtUser = db.GetDataTable(sql,"@u", userId);
              foreach (DataRow dr in dtUser.Rows)
              {

               VirtualRider thisRider = new VirtualRider();
               thisRider.UserName = dr.Field<string>("Username");
               thisRider.UserId = dr.Field<string>("UserId");
               thisRider.OwnerId = userId;
               thisRider.BikeWheelSizeMm = 700;
               thisRider.PowerMin = (int)dr.Field<long>("PowerMinimum");
               thisRider.Power1Hr = (int)dr.Field<long>("Power1Hr");
               thisRider.Power5Min = (int)dr.Field<long>("Power5Min");
               thisRider.Power1Min = (int)dr.Field<long>("Power1Min");
               thisRider.Power5Sec = (int)dr.Field<long>("Power5Sec");
               thisRider.Aggression = (int)dr.Field<long>("Aggression");
               riders.Add(thisRider);
             }
             db.Close();
             return riders.ToArray();
        }

        public static Race Load(string raceId)
        {
            Race race = null;
            SQLiteDatabase db = new SQLiteDatabase();

            string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
                          "r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , " +
                          "r.RaceDirectorId as RaceDirectorId, " +
                        "r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, " +
                        "r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
                        "r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
                        "u.ThresholdPower as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
                        "rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, " +
                        "rr.RiderType as RiderType, rr.Handicap as Handicap " +
                          "From cycli_races r, cycli_race_riders rr, cycli_riders u " +
                          "where " +
                          "u.UserId = rr.UserId and " +
                          "rr.RaceId = r.RaceId and " +
                          "r.RaceId = @r1 " +
                          "union " +
                          "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " +
                          "r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , " +
                          "r.RaceDirectorId as RaceDirectorId, " +
                        "r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, " +
                        "r.ProfileId as ProfileId, r.ProfileType as ProfileType, " +
                        "r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " +
                        "u.Power1Hr as ThresholdPower,rr.Status as Status, rr.Position as Position, rr.Distance as Distance, rr.Time as Time, rr.Energy as Energy, " +
                        "rr.PedalStrokes as PedalStrokes, rr.Heartbeats as Heartbeats, rr.TSS as TSS, "+
                        "rr.RiderType as RiderType, rr.Handicap as Handicap " +
                          "From cycli_races r, cycli_race_riders rr, cycli_virtual_riders u " +
                          "where " +
                          "u.UserId = rr.UserId and " +
                          "rr.RaceId = r.RaceId and " +
                          "r.RaceId = @r2 " +
                          " order by UserName";

            DataTable dtRaces = db.GetDataTable(sql, "@r1", raceId, "@r2", raceId);
            db.Close();
            Race[] races = BuildRaceArray(dtRaces);
            if (races.Length > 0)
            {
                race = races[0];
            }
            return race;
        }

 public static void JoinRace(string userId, string raceId)
 {
     // What is the race status
     SQLiteDatabase db = new SQLiteDatabase();
     string sql = @"update cycli_race_riders set Status='Joined' where [email protected] and RaceId In (" +
       "select r.RaceId from cycli_races r, cycli_race_riders rr where [email protected] and r.RaceId = rr.RaceId and r.Status='Planned')";
     db.ExecuteNonQuery(sql, "@r", raceId, "@u", userId);
     db.Close();
     Race race = Race.Load(raceId);
     if (race != null)
     {
         CycliManager.Instance.SendRiderJoin(race, userId);
     }
 }

        public static TurboTrainer Load(string userId, string turboType, bool turboIsCalibrated)
        {
            TurboTrainer thisTurbo = null;
              SQLiteDatabase db = new SQLiteDatabase();

              string sqlNonCalibrated = @"select t.type, t.power_model_c1, t.power_model_c2, t.power_model_c3 from " +
            "cycli_turbos t where [email protected]";

              string sqlCalibrated = @"select t.type, t.power_model_c1, t.power_model_c2, t.power_model_c3 from " +
            "cycli_rider_turbos t where [email protected] and [email protected]";

              DataTable dtUser = null;
              if (turboIsCalibrated)
              {
            dtUser = db.GetDataTable(sqlCalibrated,"@u",userId,"@t",turboType);
            // Not defined yet - use the non standard values
            if (dtUser.Rows.Count == 0)
            {
              dtUser = db.GetDataTable(sqlNonCalibrated, "@t", turboType);
            }
              }
              else
              {
            dtUser = db.GetDataTable(sqlNonCalibrated, "@t", turboType);
              }

              if (dtUser.Rows.Count > 0)
              {
            DataRow dr = dtUser.Rows[0];
            thisTurbo = new TurboTrainer();
            thisTurbo.UserId = userId;
            thisTurbo.Type = (string)(dr["type"]);
            thisTurbo.Coefficients = new double[]{(double)dr["power_model_c1"],(double)dr["power_model_c2"],(double)dr["power_model_c3"]};
              }
              db.Close();
              return thisTurbo;
        }

        public void Save(string userId)
        {
            SQLiteDatabase db = new SQLiteDatabase();
              // Query forces identity

              string sql = @"update cycli_rider_turbos set [email protected], " +
            "[email protected], [email protected], [email protected] " +
            "where [email protected]";
              int updated = db.ExecuteNonQuery(sql, "@t", this.Type,
                      "@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0],
                      "@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1],
                      "@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2],
                        "@u", userId);
              if (updated == 0)
              {
            // It's a new entry
            sql = @"insert into cycli_rider_turbos (UserId, Type, Power_Model_C1, Power_Model_C2, Power_Model_C3) " +
                    "values (@u, @t, @c1, @c2, @c3)";
            db.ExecuteNonQuery(sql,
                          "@u", userId,
                          "@t", this.Type,
                      "@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0],
                      "@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1],
                      "@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2]);
              }
              db.Close();
        }

        public static TurboTrainer[] LoadAll()
        {
            TurboTrainer[] turbos = new TurboTrainer[]{};

            SQLiteDatabase db = new SQLiteDatabase();
            string sql = @"select type, power_model_c1, power_model_c2, power_model_c3 from cycli_turbos order by type";
            DataTable dtTurbos = db.GetDataTable(sql);
            db.Close();

            if (dtTurbos.Rows.Count > 0)
            {
            turbos = dtTurbos.AsEnumerable().Select(p => new TurboTrainer
            {
                Type = (string)(p["type"]),
                Coefficients = new double[] { (double)p["power_model_c1"], (double)p["power_model_c2"], (double)p["power_model_c3"] }
            }).ToArray();
            }
            return turbos;
        }

        public static Rider LoadAny(string userId)
        {
            Rider thisRider = null;
              SQLiteDatabase db = new SQLiteDatabase();
              string sql = @"select r.UserId as UserId, r.Username as Username, r.BikeWheelSizeMm as BikeWheelSizeMm, r.Turbo as Turbo, " +
            "r.TurboIsCalibrated as TurboIsCalibrated, r.EstimatedPower as EstimatedPower " +
            "From cycli_riders r " +
            "where [email protected] and AccountStatus='Active'" +
            "union " +
            "select r.UserId as UserId, r.Username as Username, 700 as BikeWheelSizeMm, null as Turbo, " +
            "'False' as TurboIsCalibrated, 'False' as EstimatedPower " +
            "From cycli_virtual_riders r " +
            "where [email protected] and Status='Active'";

             // Only load active accounts
               DataTable dtUser = db.GetDataTable(sql, "@u1", userId, "@u2",userId);
              if (dtUser.Rows.Count > 0)
              {
            DataRow dr = dtUser.Rows[0];
            thisRider = new Rider();
            thisRider.UserName = dr.Field<string>("Username");
            thisRider.UserId= dr.Field<string>("UserId");
            thisRider.BikeWheelSizeMm = (int)dr.Field<long>("BikeWheelSizeMm");
            thisRider.CurrentTurbo = dr.Field<string>("Turbo");
            thisRider.TurboIsCalibrated = (dr.Field<string>("TurboIsCalibrated") == bool.TrueString);
            thisRider.EstimatedPower = (dr.Field<string>("EstimatedPower") == bool.TrueString);

              }
              db.Close();
              return thisRider;
        }

 public override void SaveDetails(string userId)
 {
     SQLiteDatabase db = new SQLiteDatabase();
      // Query forces identity
      string sql = @"update cycli_virtual_riders set [email protected], [email protected], [email protected], " +
        "[email protected], [email protected], [email protected], [email protected] " +
        "where [email protected] and [email protected] ";
      db.ExecuteNonQuery(sql, "@n", this.UserName, "@p4", this.PowerMin, "@p3", this.Power1Hr,
                     "@p2", this.Power5Min, "@p1", this.Power1Min, "@p0", this.Power5Sec,"@a", this.Aggression,
                     "@u", userId, "@r", this.UserId);
      db.Close();
 }