C# NpgsqlCommand.ExecuteBlind方法代码示例

        internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation)
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);

            _conn = conn;
            _isolation = isolation;

            StringBuilder commandText = new StringBuilder("BEGIN; SET TRANSACTION ISOLATION LEVEL ");

            if ((isolation == IsolationLevel.RepeatableRead) ||
                (isolation == IsolationLevel.Serializable) ||
                (isolation == IsolationLevel.Snapshot))
            {
                commandText.Append("SERIALIZABLE");
            }
            else
            {
                // Set isolation level default to read committed.
                _isolation = IsolationLevel.ReadCommitted;
                commandText.Append("READ COMMITTED");
            }

            commandText.Append(";");

            NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector);
            command.ExecuteBlind();
            _conn.Connector.Transaction = this;
        }

        /*/// <value>Counts the numbers of Connections that share
        /// this Connector. Used in Release() to decide wether this
        /// connector is to be moved to the PooledConnectors list.</value>
        // internal int mShareCount;*/
        /// <summary>
        /// Opens the physical connection to the server.
        /// </summary>
        /// <remarks>Usually called by the RequestConnector
        /// Method of the connection pool manager.</remarks>
        internal void Open()
        {
            ServerVersion = null;
            // If Connection.ConnectionString specifies a protocol version, we will
            // not try to fall back to version 2 on failure.

            _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown)
                                          ? ProtocolVersion.Version3
                                          : settings.Protocol;

            // Reset state to initialize new connector in pool.
            CurrentState = NpgsqlClosedState.Instance;

            // Get a raw connection, possibly SSL...
            CurrentState.Open(this);
            try
            {
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }
            catch (NpgsqlException ne)
            {
                // Check for protocol not supported.  If we have been told what protocol to use,
                // we will not try this step.
                if (settings.Protocol != ProtocolVersion.Unknown)
                {
                    throw;
                }
                // If we attempted protocol version 3, it may be possible to drop back to version 2.
                if (BackendProtocolVersion != ProtocolVersion.Version3)
                {
                    throw;
                }
                NpgsqlError Error0 = (NpgsqlError) ne.Errors[0];

                // If NpgsqlError..ctor() encounters a version 2 error,
                // it will set its own protocol version to version 2.  That way, we can tell
                // easily if the error was a FATAL: protocol error.
                if (Error0.BackendProtocolVersion != ProtocolVersion.Version2)
                {
                    throw;
                }
                // Try using the 2.0 protocol.
                _mediator.ResetResponses();
                BackendProtocolVersion = ProtocolVersion.Version2;
                CurrentState = NpgsqlClosedState.Instance;

                // Get a raw connection, possibly SSL...
                CurrentState.Open(this);
                // Establish protocol communication and handle authentication...
                CurrentState.Startup(this);
            }

            // Change the state of connection to open and ready.
            _connection_state = ConnectionState.Open;
            CurrentState = NpgsqlReadyState.Instance;

            // Fall back to the old way, SELECT VERSION().
            // This should not happen for protocol version 3+.
            if (ServerVersion == null)
            {
                NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this);
                ServerVersion = new Version(PGUtil.ExtractServerVersion((string) command.ExecuteScalar()));
            }

            // Adjust client encoding.

            NpgsqlParameterStatus clientEncodingParam = null;
            if(
                !ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) ||
                (!string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase))
              )
                new NpgsqlCommand("SET CLIENT_ENCODING TO UTF8", this).ExecuteBlind();

            if (!string.IsNullOrEmpty(settings.SearchPath))
            {
                /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String);
                p.Value = settings.SearchPath;
                NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this);
                commandSearchPath.Parameters.Add(p);
                commandSearchPath.ExecuteNonQuery();*/

                /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String);
                p.Value = settings.SearchPath;
                NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this);
                commandSearchPath.Parameters.Add(p);
                commandSearchPath.ExecuteNonQuery();*/

                // TODO: Add proper message when finding a semicolon in search_path.
                // This semicolon could lead to a sql injection security hole as someone could write in connection string:
                // searchpath=public;delete from table; and it would be executed.
//.........这里部分代码省略.........

        /// <summary>
        /// Creates a transaction save point.
        /// </summary>

        public void Save(String savePointName)
        {
            
            CheckDisposed();

            if (_conn == null)
            {
                throw new InvalidOperationException(resman.GetString("Exception_NoTransaction"));
            }
            
            if (!_conn.Connector.SupportsSavepoint)
            {
                throw new InvalidOperationException(resman.GetString("Exception_SavePointNotSupported"));
            }
                
            
            if (savePointName.Contains(";"))
            {
                throw new InvalidOperationException(resman.GetString("Exception_SavePointWithSemicolon"));
                      
            }
            
            
            NpgsqlCommand command = new NpgsqlCommand("SAVEPOINT " + savePointName, _conn.Connector);
            command.ExecuteBlind();
            
        }

 public void PrepareTransaction()
 {
     if (!_prepared)
     {
         NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "PrepareTransaction");
         NpgsqlConnection connection = GetConnection();
         NpgsqlCommand command = new NpgsqlCommand(string.Format("PREPARE TRANSACTION '{0}'", _txName), connection);
         command.ExecuteBlind();
         _prepared = true;
     }
 }

        /// <summary>
        /// Rolls back a transaction from a pending state.
        /// </summary>
        public override void Rollback()
        {
            CheckDisposed();

            if (_conn == null)
            {
                throw new InvalidOperationException(resman.GetString("Exception_NoTransaction"));
            }

            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Rollback");

            NpgsqlCommand command = new NpgsqlCommand("ROLLBACK", _conn.Connector);
            command.ExecuteBlind();
            _conn.Connector.Transaction = null;
            _conn = null;
        }

        /// <summary>
        /// This method is responsible to release all portals used by this Connector.
        /// </summary>
        internal void ReleasePlansPortals()
        {
            Int32 i = 0;

            if (_planIndex > 0)
            {
                for (i = 1; i <= _planIndex; i++)
                {
                    try
                    {
                        //Query(new NpgsqlCommand(String.Format("deallocate \"{0}\";", _planNamePrefix + i), this));
                        using(NpgsqlCommand cmd = new NpgsqlCommand(String.Format("deallocate \"{0}\";", _planNamePrefix + i.ToString()), this))
                        {
                            cmd.ExecuteBlind();
                        }
                    }

                    // Ignore any error which may occur when releasing portals as this portal name may not be valid anymore. i.e.: the portal name was used on a prepared query which had errors.
                    catch(Exception) {}
                }
            }

            _portalIndex = 0;
            _planIndex = 0;
        }

        /// <summary>
        /// Creates a prepared version of the command on a PostgreSQL server.
        /// </summary>
        public override void Prepare()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Prepare");

            // Check the connection state.
            CheckConnectionState();

            // reset any responses just before getting new ones
            Connector.Mediator.ResetResponses();

            // Set command timeout.
            m_Connector.Mediator.CommandTimeout = CommandTimeout;

            if (!m_Connector.SupportsPrepare)
            {
                return; // Do nothing.
            }

            if (m_Connector.BackendProtocolVersion == ProtocolVersion.Version2)
            {
                using (NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), m_Connector))
                {
                    command.ExecuteBlind();
                }
            }
            else
            {
                using (m_Connector.BlockNotificationThread())
                {
                    try
                    {
                        // Use the extended query parsing...
                        planName = m_Connector.NextPlanName();
                        String portalName = m_Connector.NextPortalName();

                        parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] { });

                        m_Connector.Parse(parse);

                        // We need that because Flush() doesn't cause backend to send
                        // ReadyForQuery on error. Without ReadyForQuery, we don't return 
                        // from query extended processing.

                        // We could have used Connector.Flush() which sends us back a
                        // ReadyForQuery, but on postgresql server below 8.1 there is an error
                        // with extended query processing which hinders us from using it.
                        m_Connector.RequireReadyForQuery = false;
                        m_Connector.Flush();


                        // Description...
                        NpgsqlDescribe describe = new NpgsqlDescribe('S', planName);


                        m_Connector.Describe(describe);

                        NpgsqlRowDescription returnRowDesc = m_Connector.Sync();

                        Int16[] resultFormatCodes;


                        if (returnRowDesc != null)
                        {
                            resultFormatCodes = new Int16[returnRowDesc.NumFields];

                            for (int i = 0; i < returnRowDesc.NumFields; i++)
                            {
                                NpgsqlRowDescription.FieldData returnRowDescData = returnRowDesc[i];


                                if (returnRowDescData.TypeInfo != null && returnRowDescData.TypeInfo.NpgsqlDbType == NpgsqlDbType.Bytea)
                                {
                                    // Binary format
                                    resultFormatCodes[i] = (Int16)FormatCode.Binary;
                                }
                                else
                                {
                                    // Text Format
                                    resultFormatCodes[i] = (Int16)FormatCode.Text;
                                }
                            }
                        }
                        else
                        {
                            resultFormatCodes = new Int16[] { 0 };
                        }

                        bind = new NpgsqlBind("", planName, new Int16[Parameters.Count], null, resultFormatCodes);
                    }
                    catch (IOException e)
                    {
                        throw ClearPoolAndCreateException(e);
                    }
                    catch
                    {

                        // As per documentation:
//.........这里部分代码省略.........

        /// <summary>
        /// Modify the backend statement_timeout value if needed.
        /// </summary>
        /// <param name="timeout">New timeout</param>
        internal void SetBackendCommandTimeout(int timeout)
        {
            if (Mediator.BackendCommandTimeout == -1 || Mediator.BackendCommandTimeout != timeout)
            {
                NpgsqlCommand toq = new NpgsqlCommand(string.Format("SET statement_timeout = {0}", timeout * 1000), this);

                toq.ExecuteBlind();

                Mediator.BackendCommandTimeout = timeout;
            }
        }

//.........这里部分代码省略.........

                if (!string.IsNullOrEmpty(settings.SearchPath))
                {
                    // TODO: Add proper message when finding a semicolon in search_path.
                    // This semicolon could lead to a sql injection security hole as someone could write in connection string:
                    // searchpath=public;delete from table; and it would be executed.

                    if (settings.SearchPath.Contains(";"))
                    {
                        throw new InvalidOperationException();
                    }

                    // This is using string concatenation because set search_path doesn't allow type casting. ::text
                    sbInitQueries.WriteLine("SET SEARCH_PATH = {0};", settings.SearchPath);
                }

                if (!string.IsNullOrEmpty(settings.ApplicationName))
                {
                    if (!SupportsApplicationName)
                    {
                        //TODO
                        //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported"));
                        throw new InvalidOperationException("ApplicationName not supported.");
                    }

                    if (settings.ApplicationName.Contains(";"))
                    {
                        throw new InvalidOperationException();
                    }

                    sbInitQueries.WriteLine("SET APPLICATION_NAME='{0}';", settings.ApplicationName);
                }

                /*
                 * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made
                 * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it.
                 * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here.
                 * This only works on postgresql servers where the ssl renegotiation settings is supported of course.
                 * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information.
                 */

                if (SupportsSslRenegotiationLimit)
                {
                    sbInitQueries.WriteLine("SET ssl_renegotiation_limit=0;");
                }

                /*
                 * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3.
                 * Check bug report #1010992 for more information.
                 */

                if (SupportsExtraFloatDigits3)
                {
                    sbInitQueries.WriteLine("SET extra_float_digits=3;");
                }
                else if (SupportsExtraFloatDigits)
                {
                    sbInitQueries.WriteLine("SET extra_float_digits=2;");
                }

                /*
                 * Set lc_monetary format to 'C' in order to get a culture agnostic representation of money.
                 * I noticed that on Windows, even when the lc_monetary is English_United States.UTF-8, negative
                 * money is formatted as ($value) with parentheses to indicate negative value.
                 * By going with a culture agnostic format, we get a consistent behavior.
                 */

                sbInitQueries.WriteLine("SET lc_monetary='C';");
            }
            else
            {
                // Some connection parameters for protocol 3 had been sent in the startup packet.
                // The rest will be setted here.
                if (SupportsExtraFloatDigits3)
                {
                    sbInitQueries.WriteLine("SET extra_float_digits=3;");
                }

                if (SupportsSslRenegotiationLimit)
                {
                    sbInitQueries.WriteLine("SET ssl_renegotiation_limit=0;");
                }

            }

            initQueries = sbInitQueries.ToString();

            NpgsqlCommand initCommand = new NpgsqlCommand(initQueries, this, 60);
            initCommand.ExecuteBlind();

            // Make a shallow copy of the type mapping that the connector will own.
            // It is possible that the connector may add types to its private
            // mapping that will not be valid to another connector, even
            // if connected to the same backend version.
            NativeToBackendTypeConverterOptions.OidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone();

            // The connector is now fully initialized. Beyond this point, it is
            // safe to release it back to the pool rather than closing it.
            IsInitialized = true;
        }

        internal void ReleaseWithDiscard()
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand("DISCARD ALL", this, 60))
            {
                cmd.ExecuteBlind();
            }

            // The initial connection parameters will be restored via IsValid() when get connector from pool later
        }

 internal void ReleaseRegisteredListen()
 {
     //Query(new NpgsqlCommand("unlisten *", this));
     using(NpgsqlCommand cmd = new NpgsqlCommand("unlisten *", this))
     {
         cmd.ExecuteBlind();
     }
 }

        public void TestNotify(NpgsqlConnector context)
        {
            //ZA  Hnotifytest CNOTIFY Z
            //Qlisten notifytest;notify notifytest;
            Stream stm = context.Stream;
            //            string uuidString = "uuid" + Guid.NewGuid().ToString("N");
            string uuidString = string.Format("uuid{0:N}", Guid.NewGuid());
            Queue<byte> buffer = new Queue<byte>();
            byte[] convertBuffer = new byte[36];

            PGUtil.WriteStringNullTerminated(stm, "Qlisten {0};notify {0};", uuidString);

            for (;;)
            {
                int newByte = stm.ReadByte();
                if (newByte == -1)
                {
                    throw new EndOfStreamException();
                }
                buffer.Enqueue((byte) newByte);
                if (buffer.Count > 35)
                {
                    buffer.CopyTo(convertBuffer, 0);
                    if (BackendEncoding.UTF8Encoding.GetString(convertBuffer) == uuidString)
                    {
                        for (;;)
                        {
                            switch (stm.ReadByte())
                            {
                                case -1:
                                    throw new EndOfStreamException();
                                case 'Z':
                                    //context.Query(new NpgsqlCommand("UNLISTEN *", context));
                                    using(NpgsqlCommand cmd = new NpgsqlCommand("UNLISTEN *", context))
                                    {
                                        cmd.ExecuteBlind();
                                    }
                                    return;
                            }
                        }
                    }
                    else
                    {
                        buffer.Dequeue();
                    }
                }
            }
        }

        private void PrepareInternal()
        {
            if (m_Connector.BackendProtocolVersion == ProtocolVersion.Version2)
            {
                planName = Connector.NextPlanName();

                // BackendEncoding.UTF8Encoding.GetString() is temporary.  A new optimization for
                // ExecuteBlind() will negate the need.
                using (NpgsqlCommand command = new NpgsqlCommand(BackendEncoding.UTF8Encoding.GetString(GetCommandText(true, false)), m_Connector))
                {
                    command.ExecuteBlind();
                    prepared = PrepareStatus.V2Prepared;
                }
            }
            else
            {
                // Use the extended query parsing...
                planName = m_Connector.NextPlanName();
                String portalName = "";
                NpgsqlParse parse = new NpgsqlParse(planName,  GetCommandText(true, true), new Int32[] { });
                NpgsqlDescribe statementDescribe = new NpgsqlDescribeStatement(planName);
                IEnumerable<IServerResponseObject> responseEnum;
                NpgsqlRowDescription returnRowDesc = null;

                // Write Parse, Describe, and Sync messages to the wire.
                m_Connector.Parse(parse);
                m_Connector.Describe(statementDescribe);
                m_Connector.Sync();

                // Flush and wait for response.
                responseEnum = m_Connector.ProcessBackendResponsesEnum();

                // Look for a NpgsqlRowDescription in the responses, discarding everything else.
                foreach (IServerResponseObject response in responseEnum)
                {
                    if (response is NpgsqlRowDescription)
                    {
                        returnRowDesc = (NpgsqlRowDescription)response;
                    }
                    else if (response is IDisposable)
                    {
                        (response as IDisposable).Dispose();
                    }
                }

                Int16[] resultFormatCodes;

                if (returnRowDesc != null)
                {
                    resultFormatCodes = new Int16[returnRowDesc.NumFields];

                    for (int i = 0; i < returnRowDesc.NumFields; i++)
                    {
                        NpgsqlRowDescription.FieldData returnRowDescData = returnRowDesc[i];

                        if (returnRowDescData.TypeInfo != null)
                        {
                            // Binary format?
                            // PG always defaults to text encoding.  We can fix up the row description
                            // here based on support for binary encoding.  Once this is done,
                            // there is no need to request another row description after Bind.
                            returnRowDescData.FormatCode = returnRowDescData.TypeInfo.SupportsBinaryBackendData ? FormatCode.Binary : FormatCode.Text;
                            resultFormatCodes[i] = (Int16)returnRowDescData.FormatCode;
                        }
                        else
                        {
                            // Text format (default).
                            resultFormatCodes[i] = (Int16)FormatCode.Text;
                        }
                    }
                }
                else
                {
                    resultFormatCodes = new Int16[] { 0 };
                }

                // Save the row description for use with all future Executes.
                currentRowDescription = returnRowDesc;

                // The Bind and Execute message objects live through multiple Executes.
                // Only Bind changes at all between Executes, which is done in BindParameters().
                bind = new NpgsqlBind(portalName, planName, new Int16[Parameters.Count], null, resultFormatCodes);
                execute = new NpgsqlExecute(portalName, 0);

                prepared = PrepareStatus.V3Prepared;
            }
        }

        private void PrepareInternal()
        {
            if (m_Connector.BackendProtocolVersion == ProtocolVersion.Version2)
            {
                using (NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), m_Connector))
                {
                    command.ExecuteBlind();
                    prepared = PrepareStatus.V2Prepared;
                }
            }
            else
            {
                // Use the extended query parsing...
                planName = m_Connector.NextPlanName();
                String portalName = "";
                NpgsqlParse parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] { });
                NpgsqlDescribe statementDescribe = new NpgsqlDescribeStatement(planName);
                IEnumerable<IServerResponseObject> responseEnum;
                NpgsqlRowDescription returnRowDesc = null;

                // Write Parse, Describe, and Sync messages to the wire.
                m_Connector.Parse(parse);
                m_Connector.Describe(statementDescribe);
                m_Connector.Sync();

                // Flush and wait for response.
                responseEnum = m_Connector.ProcessBackendResponsesEnum();

                // Look for a NpgsqlRowDescription in the responses, discarding everything else.
                foreach (IServerResponseObject response in responseEnum)
                {
                    if (response is NpgsqlRowDescription)
                    {
                        returnRowDesc = (NpgsqlRowDescription)response;
                    }
                    else if (response is IDisposable)
                    {
                        (response as IDisposable).Dispose();
                    }
                }

                Int16[] resultFormatCodes;

                if (returnRowDesc != null)
                {
                    resultFormatCodes = new Int16[returnRowDesc.NumFields];

                    for (int i = 0; i < returnRowDesc.NumFields; i++)
                    {
                        NpgsqlRowDescription.FieldData returnRowDescData = returnRowDesc[i];

                        if (returnRowDescData.TypeInfo != null)
                        {
                            // Binary format?
                            resultFormatCodes[i] = returnRowDescData.TypeInfo.SupportsBinaryBackendData ? (Int16)FormatCode.Binary : (Int16)FormatCode.Text;
                        }
                        else
                        {
                            // Text Format
                            resultFormatCodes[i] = (Int16)FormatCode.Text;
                        }
                    }
                }
                else
                {
                    resultFormatCodes = new Int16[] { 0 };
                }

                // The Bind and Execute message objects live through multiple Executes.
                // Only Bind changes at all between Executes, which is done in BindParameters().
                bind = new NpgsqlBind(portalName, planName, new Int16[Parameters.Count], null, resultFormatCodes);
                execute = new NpgsqlExecute(portalName, 0);
                prepared = PrepareStatus.V3Prepared;
            }
        }