C# SQLiteDatabase.ExecuteNonQuery方法代码示例

 private void InitializeTables(SQLiteDatabase db)
 {
     db.ExecuteNonQuery("BEGIN EXCLUSIVE");
     for(int i = 0; i < CREATE_Commands.Length; i++)
     {
         db.ExecuteNonQuery(CREATE_Commands[i]);
     }
 }

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

 public EventRecorder(string pPath, string pDatabaseFilename)
 {
     //check if file exists
     if (System.IO.File.Exists(pPath + pDatabaseFilename)) {
         _database = new SQLiteDatabase(pPath + pDatabaseFilename);
         Console.WriteLine("opened old database");
     }
     else {
         if(!System.IO.Directory.Exists(pPath))
             System.IO.Directory.CreateDirectory(pPath);
         SQLiteConnection.CreateFile(pPath + pDatabaseFilename);
         _database = new SQLiteDatabase(pPath + pDatabaseFilename);
         _database.ExecuteNonQuery(
         @"CREATE TABLE [events] (
         [id] INTEGER  NOT NULL PRIMARY KEY,
         [name] VARCHAR(64)  NULL,
         [time_start] INTEGER NOT NULL,
         [time_end] INTEGER NOT NULL)");
     }
 }

 /// <summary>
 ///     Handles the OK button, adds a word
 /// </summary>
 private void btnOK_Click(object sender, EventArgs e)
 {
     // Checks whether the description and word are filled in
     if (txtDesc.Text != "" && txtWord.Text != "")
     {
         SQLiteDatabase db = new SQLiteDatabase();
         /* Attempts an insert and if successful it clears the desc and word boxes
         *  to allow further entry, else it shows a messagebox and since most errors
         *  will be that the word is already there, the error is shown */
         try
         {
             db.ExecuteNonQuery(string.Format("INSERT INTO words VALUES (\"{0}\", \"{1}\")",
                 txtWord.Text, txtDesc.Text));
             txtDesc.Clear();
             txtWord.Clear();
         }
         catch (Exception ex)
         {
             MessageBox.Show(
                 string.Format("Something went wrong, the error is given below:\n\n{0}", ex.Message),
                 "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
         }
     }
 }

        /// <summary>
        ///     Adds a student to the system
        /// </summary>
        /// <param name="name">The student's full name</param>
        /// <param name="pass">The student's password</param>
        /// <param name="stuClass">The student's class</param>
        public static void addStudent(string name, string pass, string stuClass)
        {
            /* The following variables are
             * db        - Instance of the SQLiteDatabase class
             * hash      - The SHA-256 hash of the password to be added as a string
             * added     - Used to avoid a bug with adding students if one has been deleted
             * attempts  - See above
             * studentID - The ID of the student to be added
             */
            SQLiteDatabase db = new SQLiteDatabase();
            string hash       = Password.hashAsString(pass);
            bool added        = false;
            int attempts      = 0;
            int studentID     = Convert.ToInt16(db.ExecuteScalar("SELECT id FROM students ORDER BY id DESC")) + 1;

            // While the db call hasn't executed
            while (!added)
            {
                try
                {
                    db.ExecuteNonQuery(string.Format("INSERT INTO students VALUES ({0}, \"{1}\", \"{2}\", \"{3}\");",
                        studentID, name, stuClass, hash));
                    added = true;
                }
                // Checks the number of attempts and if they're less than 10 it adds one to the studentID
                catch
                {
                    if (attempts > 9)
                    {
                        throw new Exception("Too many attempts");
                    }
                    studentID++;
                    attempts++;
                }
            }
        }

        public static void JoinVirtualRider(string userId, string riderId, string raceId)
        {
            // What is the race status
            // Validation occurs first on the client - this is the backstop
            UserRider owner = UserRider.Load(userId);
            if (owner != null)
            {

                // Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
                SQLiteDatabase db = new SQLiteDatabase();
                // This query checks that the user has not already been invited
                string sql = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType) " +
                              "select @f, @r, 'Joined', 'VIRTUAL' where exists (select 1 from cycli_races where " +
                              "[email protected] and Status='Planned' and [email protected]) " +
                              "and not exists (select 1 from cycli_race_riders where [email protected] and [email protected]) " +
                              "and (select count(*) from cycli_race_riders where [email protected]) < @c ";
                db.ExecuteNonQuery(sql, "@f", riderId, "@r", raceId, "@r1", raceId, "@u", userId, "@f1", riderId, "@r2", raceId, "@c", owner.MaximumRiders);
                db.Close();
                Race race = Race.Load(raceId);
                if (race != null)
                {
                    CycliManager.Instance.SendRiderJoin(race, riderId);
                }
            }
        }

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

        private SQLiteDatabase OpenDB(string fileName)
        {
            if(File.Exists(fileName))
                File.Delete(fileName);

            var db = new SQLiteDatabase(fileName);

            for(int i = 0; i < PRAGMA_Commands.Length; i++)
            {
                db.ExecuteNonQuery(PRAGMA_Commands[i]);
            }

            return db;
        }

  private static void TestCsharpSqlite()
  {
    SQLiteDatabase db;
    SQLiteVdbe stmt;
    SQLiteVdbe c1, c2;

    bool found;
    int i;

    string databaseName = "Benchmark_cs-SQLite.sqlite";
    if ( File.Exists( databaseName ) ) File.Delete( databaseName );

    db = new SQLiteDatabase( databaseName );
    for ( i = 0; i < PRAGMA_Commands.Length; i++ ) { db.ExecuteNonQuery( PRAGMA_Commands[i] ); }

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    for ( i = 0; i < CREATE_Commands.Length; i++ ) { db.ExecuteNonQuery( CREATE_Commands[i] ); }
    stmt = new SQLiteVdbe( db, INSERT_Command );
    long start = DateTime.Now.Ticks;
    long key = 1999;
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      stmt.Reset();
      stmt.BindLong( 1, key );
      stmt.BindText( 2, key.ToString() );
      stmt.ExecuteStep();
    }
    stmt.Close();
    db.ExecuteNonQuery( "END" );
    timer[1, 0] = DateTime.Now.Ticks - start;

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    c1 = new SQLiteVdbe( db, SELECT_Bind_i );
    c2 = new SQLiteVdbe( db, SELECT_Bind_s );
    key = 1999;
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      c1.Reset();
      c1.BindLong( 1, key );
      c1.ExecuteStep();

      c2.Reset();
      c2.BindText( 1, key.ToString() );
      c2.ExecuteStep();

      long id = (long)c1.Result_Long( 0 );
      Debug.Assert( id == (long)c2.Result_Long( 0 ) );

    }
    c1.Close();
    c2.Close();
    db.ExecuteNonQuery( "END" );
    timer[1, 1] = DateTime.Now.Ticks - start;

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    key = Int64.MinValue;
    i = 0;
    c1 = new SQLiteVdbe( db, SELECT_Command_i );
    while ( c1.ExecuteStep() != Sqlite3.SQLITE_DONE )
    {
      long intKey = (long)c1.Result_Long( 0 );
      Debug.Assert( intKey >= key );
      key = intKey;
      i += 1;
    }
    c1.Close();
    Debug.Assert( i == nRecords );

    String strKey = "";
    i = 0;
    c2 = new SQLiteVdbe( db, SELECT_Command_s );
    while ( c2.ExecuteStep() != Sqlite3.SQLITE_DONE )
    {
      string recStrKey = (string)c2.Result_Text( 1 );
      Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 );
      strKey = recStrKey;
      i += 1;
    }
    c2.Close();
    Debug.Assert( i == nRecords );
    timer[1, 2] = DateTime.Now.Ticks - start;
    db.ExecuteNonQuery( "END" );

    db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
    start = DateTime.Now.Ticks;
    key = 1999;
    stmt = new SQLiteVdbe( db, DELETE_Bind );
    for ( i = 0; i < nRecords; i++ )
    {
      key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
      stmt.Reset();
      stmt.BindLong( 1, key );
      stmt.ExecuteStep();
    }
    stmt.Close();
    db.ExecuteNonQuery( "END" );
//.........这里部分代码省略.........

        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 FinishRider(string userId)
 {
     SQLiteDatabase db = new SQLiteDatabase();
     try
     {
         string sqlRiderUpdate = @"update cycli_race_riders set Status='Finished' " +
             "where [email protected] and [email protected] and Status='Started'";
         db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId, "@u", userId);
     }
     finally
     {
         db.Close();
     }
     // SHould always be here
     _Participants[userId].Status = @"Finished";
 }

 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 Friend MakeFriend(string userId, string friendCode)
        {
            Friend friend = null;
              UserRider rider = UserRider.Load(userId);
              if (rider != null)
              {
            Friend[] existingFriends = Load(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
            // (c) We haven't exeeded out friend limit
            string sqlFriend = "select UserId, UserName from cycli_riders where FriendCode = @c";
            DataTable dt = db.GetDataTable(sqlFriend, "@c", friendCode);
            if (dt.Rows.Count > 0)
            {
              string friendId = dt.Rows[0].Field<string>("UserId");
              string friendName = dt.Rows[0].Field<string>("UserName");
              if (existingFriends.Count() < rider.MaximumFriends && existingFriends.Count(p => p.UserId == friendId) == 0)
              {
            string sql = "insert into cycli_friends (UserId, FriendId, Status) values (@u, @f, 'Accepted')";
            if (db.ExecuteNonQuery(sql, "@u", userId, "@f", friendId) == 1)
            {
              friend = new Friend { UserName = friendName, UserId = friendId, Status = "Accepted" };
            }
              }
            }

            // This query checks that the user has not already been invited
            db.Close();
              }
              return friend;
        }

        public static VirtualRider New(string userId)
        {
            VirtualRider r = new VirtualRider();
             UserRider u = UserRider.Load(userId);
             r.UserId = Guid.NewGuid().ToString();
             r.OwnerId = userId;
             r.UserName = "New Rider";
             r.BikeWheelSizeMm = 700;

             r.EstimatedPower = false;
             r.TurboIsCalibrated = false;
             r.CurrentTurbo = "";
             r.Aggression = 50;
             r.PowerMin = 150;
             r.Power1Hr = 200;
             r.Power5Min = 250;
             r.Power1Min = 300;
             r.Power5Sec = 500;

             // It's a new profile
             string sqlCount = @"select count(*) from cycli_virtual_riders where [email protected]";

             string sqlRider = @"insert into cycli_virtual_riders (UserId, OwnerId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression) " +
                         "values (@u, @o, @n, @p4, @p3, @p2, @p1, @p0, @a) ";
             SQLiteDatabase db = new SQLiteDatabase();
             try
             {
               // This is a backstop test - the front end should prevent extra riders
               if (int.Parse(db.ExecuteScalar(sqlCount, "@o", userId)) < u.MaximumVirtualRiders)
               {

             db.ExecuteNonQuery(sqlRider,
                                 "@u", r.UserId,
                                 "@o", r.OwnerId,
                                 "@n", r.UserName,
                                 "@p4", r.PowerMin,
                                 "@p3", r.Power1Hr,
                                 "@p2", r.Power5Min,
                                 "@p1", r.Power1Min,
                                 "@p0", r.Power5Sec,
                                 "@a", r.Aggression);
               }
             }
             finally
             {
               db.Close();
             }
             return r;
        }