C# Sqlite.SqliteStatement类代码示例

    /// <summary>
    /// Initializes the statement and attempts to get all information about parameters in the statement
    /// </summary>
    /// <param name="sqlbase">The base SQLite object</param>
    /// <param name="stmt">The statement</param>
    /// <param name="strCommand">The command text for this statement</param>
    /// <param name="previous">The previous command in a multi-statement command</param>
    internal SqliteStatement(SQLiteBase sqlbase, SqliteStatementHandle stmt, string strCommand, SqliteStatement previous)
    {
      _sql     = sqlbase;
      _sqlite_stmt = stmt;
      _sqlStatement  = strCommand;

      // Determine parameters for this statement (if any) and prepare space for them.
      int nCmdStart = 0;
      int n = _sql.Bind_ParamCount(this);
      int x;
      string s;

      if (n > 0)
      {
        if (previous != null)
          nCmdStart = previous._unnamedParameters;

        _paramNames = new string[n];
        _paramValues = new SqliteParameter[n];

        for (x = 0; x < n; x++)
        {
          s = _sql.Bind_ParamName(this, x + 1);
          if (String.IsNullOrEmpty(s))
          {
            s = String.Format(CultureInfo.InvariantCulture, ";{0}", nCmdStart);
            nCmdStart++;
            _unnamedParameters++;
          }
          _paramNames[x] = s;
          _paramValues[x] = null;
        }
      }
    }

        internal override int Reset(SqliteStatement stmt)
        {
            int n = UnsafeNativeMethods.sqlite3_reset(stmt._sqlite_stmt);

            // If the schema changed, try and re-prepare it
            if (n == 17) // SQLITE_SCHEMA
            {
                // Recreate a dummy statement
                var timeout = (uint)(stmt._command._commandTimeout * 1000);
                string str;
                using (SqliteStatement tmp = Prepare(null, stmt._sqlStatement, null, timeout, out str))
                {
                    // Finalize the existing statement
                    stmt._sqlite_stmt.Dispose();
                    // Reassign a new statement pointer to the old statement and clear the temporary one
                    stmt._sqlite_stmt = tmp._sqlite_stmt;
                    tmp._sqlite_stmt = null;

                    // Reapply parameters
                    stmt.BindParameters();
                }

                return -1; // Reset was OK, with schema change
            }

            if (n == 6 || n == 5) // SQLITE_LOCKED || SQLITE_BUSY
            {
                return n;
            }

            if (n > 0)
            {
                throw new SqliteException(n, SQLiteLastError());
            }

            return 0; // We reset OK, no schema changes
        }

    /// <summary>
    /// Moves to the next resultset in multiple row-returning SQL command.
    /// </summary>
    /// <returns>True if the command was successful and a new resultset is available, False otherwise.</returns>
    public override bool NextResult()
    {
      CheckClosed();

      SqliteStatement stmt = null;
      int fieldCount;

      while (true)
      {
        if (_activeStatement != null && stmt == null)
        {
          // Reset the previously-executed statement
          _activeStatement._sql.Reset(_activeStatement);

          // If we're only supposed to return a single rowset, step through all remaining statements once until
          // they are all done and return false to indicate no more resultsets exist.
          if ((_commandBehavior & CommandBehavior.SingleResult) != 0)
          {
            for (; ; )
            {
              stmt = _command.GetStatement(_activeStatementIndex + 1);
              if (stmt == null) break;
              _activeStatementIndex++;

              stmt._sql.Step(stmt);
              if (stmt._sql.ColumnCount(stmt) == 0)
              {
                if (_rowsAffected == -1) _rowsAffected = 0;
                _rowsAffected += stmt._sql.Changes;
              }
              stmt._sql.Reset(stmt); // Gotta reset after every step to release any locks and such!
            }
            return false;
          }
        }

        // Get the next statement to execute
        stmt = _command.GetStatement(_activeStatementIndex + 1);

        // If we've reached the end of the statements, return false, no more resultsets
        if (stmt == null)
          return false;

        // If we were on a current resultset, set the state to "done reading" for it
        if (_readingState < 1)
          _readingState = 1;

        _activeStatementIndex++;

        fieldCount = stmt._sql.ColumnCount(stmt);

        // If the statement is not a select statement or we're not retrieving schema only, then perform the initial step
        if ((_commandBehavior & CommandBehavior.SchemaOnly) == 0 || fieldCount == 0)
        {
          if (stmt._sql.Step(stmt))
          {
            _readingState = -1;
          }
          else if (fieldCount == 0) // No rows returned, if fieldCount is zero, skip to the next statement
          {
            if (_rowsAffected == -1) _rowsAffected = 0;
            _rowsAffected += stmt._sql.Changes;
            stmt._sql.Reset(stmt);
            continue; // Skip this command and move to the next, it was not a row-returning resultset
          }
          else // No rows, fieldCount is non-zero so stop here
          {
            _readingState = 1; // This command returned columns but no rows, so return true, but HasRows = false and Read() returns false
          }
        }

        // Ahh, we found a row-returning resultset eligible to be returned!
        _activeStatement = stmt;
        _fieldCount = fieldCount;
        _fieldTypeArray = null;

        if ((_commandBehavior & CommandBehavior.KeyInfo) != 0)
          LoadKeyInfo();

        return true;
      }
    }

 internal override int GetCursorForTable(SqliteStatement stmt, int db, int rootPage)
 {
     return -1;
 }

 internal override bool IsNull(SqliteStatement stmt, int index)
 {
     return this.ColumnAffinity(stmt, index) == TypeAffinity.Null;
 }

        internal override long GetBytes(SqliteStatement stmt, int index, int nDataOffset, byte[] bDest, int nStart,
                                        int nLength)
        {
            int nCopied = nLength;
            int nlen = UnsafeNativeMethods.sqlite3_column_bytes(stmt._sqlite_stmt, index);
            var ptr = UnsafeNativeMethods.sqlite3_column_blob(stmt._sqlite_stmt, index);

            if (bDest == null)
            {
                return nlen;
            }
            
            if (nCopied + nStart > bDest.Length)
            {
                nCopied = bDest.Length - nStart;
            }
            if (nCopied + nDataOffset > nlen)
            {
                nCopied = nlen - nDataOffset;
            }

            if (nCopied > 0)
            {
                Array.Copy(ptr, nStart + nDataOffset, bDest, 0, nCopied);
            }
            else
            {
                nCopied = 0;
            }

            return nCopied;
        }

 internal override string GetText(SqliteStatement stmt, int index)
 {
     return UTF8ToString(UnsafeNativeMethods.sqlite3_column_text(stmt._sqlite_stmt, index), -1);
 }

 internal override int Bind_ParamCount(SqliteStatement stmt)
 {
     return UnsafeNativeMethods.sqlite3_bind_parameter_count(stmt._sqlite_stmt);
 }

 internal override void Bind_Null(SqliteStatement stmt, int index)
 {
     int n = UnsafeNativeMethods.sqlite3_bind_null(stmt._sqlite_stmt, index);
     if (n > 0) throw new SqliteException(n, SQLiteLastError());
 }

 internal override void Bind_Blob(SqliteStatement stmt, int index, byte[] blobData)
 {
     int n = UnsafeNativeMethods.sqlite3_bind_blob(stmt._sqlite_stmt, index, blobData, blobData.Length, null);
     if (n > 0) throw new SqliteException(n, SQLiteLastError());
 }

 internal override void Bind_DateTime(SqliteStatement stmt, int index, DateTime dt)
 {
     var value = ToUTF8(dt);
     int n = UnsafeNativeMethods.sqlite3_bind_text(stmt._sqlite_stmt, index, value, value.Length, null);
     if (n > 0) throw new SqliteException(n, SQLiteLastError());
 }

 internal override void Bind_Text(SqliteStatement stmt, int index, string value)
 {
     int n = UnsafeNativeMethods.sqlite3_bind_text(stmt._sqlite_stmt, index, value, value.Length, null);
     if (n > 0) throw new SqliteException(n, SQLiteLastError());
 }

 internal override void Bind_Int64(SqliteStatement stmt, int index, long value)
 {
     int n = UnsafeNativeMethods.sqlite3_bind_int64(stmt._sqlite_stmt, index, value);
     if (n > 0) throw new SqliteException(n, SQLiteLastError());
 }

        internal override SqliteStatement Prepare(SqliteConnection cnn, string strSql, SqliteStatement previous,
                                                  uint timeout, out string strRemain)
        {
            SqliteStatementHandle stmt = null;
            string ptr = null;
     
            int len = 0;
            int n = 17;
            int retries = 0;
            SqliteStatement cmd = null;
            var rnd = new Random();
            var starttick = (uint) Environment.TickCount;

                while ((n == 17 || n == 6 || n == 5) && retries < 3)
                {
                    n = UnsafeNativeMethods.sqlite3_prepare16(_sql, strSql, strSql.Length, out stmt, out ptr);
                    len = -1;

                    if (n == 17)
                    {
                        retries++;
                    }
                    else if (n == 1)
                    {
                        if (String.Compare(SQLiteLastError(), "near \"TYPES\": syntax error",
                                           StringComparison.OrdinalIgnoreCase) == 0)
                        {
                            int pos = strSql.IndexOf(';');
                            if (pos == -1)
                            {
                                pos = strSql.Length - 1;
                            }

                            string typedefs = strSql.Substring(0, pos + 1);
                            strSql = strSql.Substring(pos + 1);

                            strRemain = "";

                            while (cmd == null && strSql.Length > 0)
                            {
                                cmd = Prepare(cnn, strSql, previous, timeout, out strRemain);
                                strSql = strRemain;
                            }

                            if (cmd != null)
                            {
                                cmd.SetTypes(typedefs);
                            }

                            return cmd;
                        }
                        else if (_buildingSchema == false &&
                                 String.Compare(SQLiteLastError(), 0, "no such table: TEMP.SCHEMA", 0, 26,
                                                StringComparison.OrdinalIgnoreCase) == 0)
                        {
                            strRemain = "";
                            _buildingSchema = true;
                            try
                            {
                                while (cmd == null && strSql.Length > 0)
                                {
                                    cmd = Prepare(cnn, strSql, previous, timeout, out strRemain);
                                    strSql = strRemain;
                                }

                                return cmd;
                            }
                            finally
                            {
                                _buildingSchema = false;
                            }
                        }
                    }
                    else if (n == 6 || n == 5) // Locked -- delay a small amount before retrying
                    {
                        // Keep trying, but if we've exceeded the command's timeout, give up and throw an error
                        if ((uint) Environment.TickCount - starttick > timeout)
                        {
                            throw new SqliteException(n, SQLiteLastError());
                        }
                        else
                        {
                            // Otherwise sleep for a random amount of time up to 150ms
                            Sleep(rnd.Next(1, 150));
                        }
                    }
                }

                if (n > 0) throw new SqliteException(n, SQLiteLastError());

                strRemain = UTF8ToString(ptr, len);

                var hdl = stmt;
                if (stmt != null)
                {
                    cmd = new SqliteStatement(this, hdl, strSql.Substring(0, strSql.Length - strRemain.Length), previous);
                }

                return cmd;
        }

 internal override double GetDouble(SqliteStatement stmt, int index)
 {
     return UnsafeNativeMethods.sqlite3_column_double(stmt._sqlite_stmt, index);
 }