// created on 21/5/2002 at 20:03

// Npgsql.NpgsqlCommand.cs
//
// Author:
//	Francisco Jr. (fxjrlists@yahoo.com.br)
//
//	Copyright (C) 2002 The Npgsql Development Team
//	npgsql-general@gborg.postgresql.org
//	http://gborg.postgresql.org/project/npgsql/projdisplay.php
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

using System;
using System.Data;
using System.Text;
using System.Resources;
using System.ComponentModel;
using System.Collections;

using NpgsqlTypes;

using System.Text.RegularExpressions;

#if WITHDESIGN
using Npgsql.Design;
#endif

namespace Npgsql
{
    /// <summary>
    /// Represents a SQL statement or function (stored procedure) to execute
    /// against a PostgreSQL database. This class cannot be inherited.
    /// </summary>
    #if WITHDESIGN
    [System.Drawing.ToolboxBitmapAttribute(typeof(NpgsqlCommand)), ToolboxItem(true)]
    #endif
    public sealed class NpgsqlCommand : Component, IDbCommand, ICloneable
    {
        // Logging related values
        private static readonly String CLASSNAME = "NpgsqlCommand";
        private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommand));

        private NpgsqlConnection            connection;
        private NpgsqlConnector             connector;
        private NpgsqlTransaction           transaction;
        private String                      text;
        private Int32                       timeout;
        private CommandType                 type;
        private NpgsqlParameterCollection   parameters;
        private String                      planName;

        private NpgsqlParse                 parse;
        private NpgsqlBind                  bind;

        private Boolean						invalidTransactionDetected = false;
        
        private CommandBehavior             commandBehavior;

        // Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class.
        /// </summary>
        public NpgsqlCommand() : this(String.Empty, null, null)
        {}
        /// <summary>
        /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query.
        /// </summary>
        /// <param name="cmdText">The text of the query.</param>
        public NpgsqlCommand(String cmdText) : this(cmdText, null, null)
        {}
        /// <summary>
        /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query and a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>.
        /// </summary>
        /// <param name="cmdText">The text of the query.</param>
        /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
        public NpgsqlCommand(String cmdText, NpgsqlConnection connection) : this(cmdText, connection, null)
        {}
        /// <summary>
        /// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query, a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>, and the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
        /// </summary>
        /// <param name="cmdText">The text of the query.</param>
        /// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
        /// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
        public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);

            planName = String.Empty;
            text = cmdText;
            this.connection = connection;
            
            if (this.connection != null)
                this.connector = connection.Connector;

            parameters = new NpgsqlParameterCollection();
            timeout = 20;
            type = CommandType.Text;
            this.Transaction = transaction;
            commandBehavior = CommandBehavior.Default;
            
            
        }

        /// <summary>
        /// Used to execute internal commands.
        /// </summary>
        internal NpgsqlCommand(String cmdText, NpgsqlConnector connector)
        {
            resman = new System.Resources.ResourceManager(this.GetType());
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
            

            planName = String.Empty;
            text = cmdText;
            this.connector = connector;
            type = CommandType.Text;
            commandBehavior = CommandBehavior.Default;
            
            parameters = new NpgsqlParameterCollection();
            timeout = 20;
        }

        // Public properties.
        /// <summary>
        /// Gets or sets the SQL statement or function (stored procedure) to execute at the data source.
        /// </summary>
        /// <value>The Transact-SQL statement or stored procedure to execute. The default is an empty string.</value>
        [Category("Data"), DefaultValue("")]
        public String CommandText {
            get
            {
                return text;
            }

            set
            {
                // [TODO] Validate commandtext.
                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandText", value);
                text = value;
                planName = String.Empty;
                parse = null;
                bind = null;
                commandBehavior = CommandBehavior.Default;
            }
        }

        /// <summary>
        /// Gets or sets the wait time before terminating the attempt
        /// to execute a command and generating an error.
        /// </summary>
        /// <value>The time (in seconds) to wait for the command to execute.
        /// The default is 20 seconds.</value>
        [DefaultValue(20)]
        public Int32 CommandTimeout {
            get
            {
                return timeout;
            }

            set
            {
                if (value < 0)
                    throw new ArgumentOutOfRangeException(resman.GetString("Exception_CommandTimeoutLessZero"));

                timeout = value;
                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandTimeout", value);
            }
        }

        /// <summary>
        /// Gets or sets a value indicating how the
        /// <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> property is to be interpreted.
        /// </summary>
        /// <value>One of the <see cref="System.Data.CommandType">CommandType</see> values. The default is <see cref="System.Data.CommandType">CommandType.Text</see>.</value>
        [Category("Data"), DefaultValue(CommandType.Text)]
        public CommandType CommandType {
            get
            {
                return type;
            }

            set
            {
                type = value;
                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "CommandType", value);
            }
        }

        IDbConnection IDbCommand.Connection 
        {
            get
            {
                return Connection;
            }

            set
            {
                Connection = (NpgsqlConnection) value;
                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Connection", value);
            }
        }

        /// <summary>
        /// Gets or sets the <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>
        /// used by this instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
        /// </summary>
        /// <value>The connection to a data source. The default value is a null reference.</value>
        [Category("Behavior"), DefaultValue(null)]
        public NpgsqlConnection Connection {
            get
            {
                NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Connection");
                return connection;
            }

            set
            {
                if (this.Connection == value)
                    return;

                //if (this.transaction != null && this.transaction.Connection == null)
                  //  this.transaction = null;
                                    
                if (this.transaction != null && this.connection != null && this.Connector.Transaction != null)
                    throw new InvalidOperationException(resman.GetString("Exception_SetConnectionInTransaction"));


                this.connection = value;
                Transaction = null;
                if (this.connection != null)
                    connector = this.connection.Connector;

                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Connection", value);
            }
        }

        internal NpgsqlConnector Connector {
            get
            {
                if (connector == null && this.connection != null)
                    connector = this.connection.Connector;

                return connector;
            }
        }

        IDataParameterCollection IDbCommand.Parameters {
            get
            {
                return Parameters;
            }
        }

        /// <summary>
        /// Gets the <see cref="Npgsql.NpgsqlParameterCollection">NpgsqlParameterCollection</see>.
        /// </summary>
        /// <value>The parameters of the SQL statement or function (stored procedure). The default is an empty collection.</value>
        #if WITHDESIGN
        [Category("Data"), DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
        #endif
        
        public NpgsqlParameterCollection Parameters {
            get
            {
                NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Parameters");
                return parameters;
            }
        }

        
        IDbTransaction IDbCommand.Transaction 
        {
            get
            {
                return Transaction;
            }

            set
            {
                Transaction = (NpgsqlTransaction) value;
                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "IDbCommand.Transaction", value);
            }
        }
        
        /// <summary>
        /// Gets or sets the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>
        /// within which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.
        /// </summary>
        /// <value>The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
        /// The default value is a null reference.</value>
        #if WITHDESIGN
        [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        #endif
        
        public NpgsqlTransaction Transaction {
            get
            {
                NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "Transaction");

                if (this.transaction != null && this.transaction.Connection == null)
                {
                    this.transaction = null;
                }
                return this.transaction;
            }

            set
            {
                NpgsqlEventLog.LogPropertySet(LogLevel.Debug, CLASSNAME, "Transaction" ,value);

                this.transaction = (NpgsqlTransaction) value;
            }
        }

        /// <summary>
        /// Gets or sets how command results are applied to the <see cref="System.Data.DataRow">DataRow</see>
        /// when used by the <see cref="System.Data.Common.DbDataAdapter.Update">Update</see>
        /// method of the <see cref="System.Data.Common.DbDataAdapter">DbDataAdapter</see>.
        /// </summary>
        /// <value>One of the <see cref="System.Data.UpdateRowSource">UpdateRowSource</see> values.</value>
        #if WITHDESIGN
        [Category("Behavior"), DefaultValue(UpdateRowSource.Both)]
        #endif
        
        public UpdateRowSource UpdatedRowSource {
            get
            {

                NpgsqlEventLog.LogPropertyGet(LogLevel.Debug, CLASSNAME, "UpdatedRowSource");

                return UpdateRowSource.Both;
            }

            set
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// Attempts to cancel the execution of a <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
        /// </summary>
        /// <remarks>This Method isn't implemented yet.</remarks>
        public void Cancel()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Cancel");

            // [TODO] Finish method implementation.
            throw new NotImplementedException();
        }
        
        /// <summary>
        /// Create a new command based on this one.
        /// </summary>
        /// <returns>A new NpgsqlCommand object.</returns>
        Object ICloneable.Clone()
        {
            return Clone();
        }

        /// <summary>
        /// Create a new connection based on this one.
        /// </summary>
        /// <returns>A new NpgsqlConnection object.</returns>
        public NpgsqlCommand Clone()
        {
            // TODO: Add consistency checks.

            return new NpgsqlCommand(CommandText, Connection, Transaction);
        }

        /// <summary>
        /// Creates a new instance of an <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.
        /// </summary>
        /// <returns>An <see cref="System.Data.IDbDataParameter">IDbDataParameter</see> object.</returns>
        IDbDataParameter IDbCommand.CreateParameter()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.CreateParameter");

            return (NpgsqlParameter) CreateParameter();
        }

        /// <summary>
        /// Creates a new instance of a <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.
        /// </summary>
        /// <returns>A <see cref="Npgsql.NpgsqlParameter">NpgsqlParameter</see> object.</returns>
        public NpgsqlParameter CreateParameter()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CreateParameter");

            return new NpgsqlParameter();
        }

        /// <summary>
        /// Executes a SQL statement against the connection and returns the number of rows affected.
        /// </summary>
        /// <returns>The number of rows affected.</returns>
        public Int32 ExecuteNonQuery()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteNonQuery");

            ExecuteCommand();
            
            UpdateOutputParameters();
            
            
            // If nothing is returned, just return -1.
            if(Connector.Mediator.CompletedResponses.Count == 0)
            {
                return -1;
            }

            // Check if the response is available.
            String firstCompletedResponse = (String)Connector.Mediator.CompletedResponses[0];

            if (firstCompletedResponse == null)
                return -1;

            String[] ret_string_tokens = firstCompletedResponse.Split(null);        // whitespace separator.


            // Check if the command was insert, delete or update.
            // Only theses commands return rows affected.
            // [FIXME] Is there a better way to check this??
            if ((String.Compare(ret_string_tokens[0], "INSERT", true) == 0) ||
                    (String.Compare(ret_string_tokens[0], "UPDATE", true) == 0) ||
                    (String.Compare(ret_string_tokens[0], "DELETE", true) == 0))

                // The number of rows affected is in the third token for insert queries
                // and in the second token for update and delete queries.
                // In other words, it is the last token in the 0-based array.

                return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
            else
                return -1;
        }
        
        
        
        private void UpdateOutputParameters()
        {
            // Check if there was some resultset returned. If so, put the result in output parameters.
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "UpdateOutputParameters");
            
            // Get ResultSets.
            ArrayList resultSets = Connector.Mediator.ResultSets;
            
            if (resultSets.Count != 0)
            {
                NpgsqlResultSet nrs = (NpgsqlResultSet)resultSets[0];
                                
                if ((nrs != null) && (nrs.Count > 0))
                {
                    NpgsqlAsciiRow nar = (NpgsqlAsciiRow)nrs[0];
                    
                    Int32 i = 0;
                    Boolean hasMapping = false;
                                        
                    // First check if there is any mapping between parameter name and resultset name.
                    // If so, just update output parameters which has mapping.
                    
                    foreach (NpgsqlParameter p in Parameters)
                    {
                        try
                        {
                            if (nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1)) > -1)
                            {
                                hasMapping = true;
                                break;
                            }
                        }
                        catch(ArgumentOutOfRangeException)
                        {}
                    }
                                        
                    
                    if (hasMapping)
                    {
                        foreach (NpgsqlParameter p in Parameters)
                        {
                            if (((p.Direction == ParameterDirection.Output) ||
                                (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
                            {
                                try
                                {
                                    p.Value = nar[nrs.RowDescription.FieldIndex(p.ParameterName.Substring(1))];
                                    i++;
                                }
                                catch(ArgumentOutOfRangeException)
                                {}
                            }
                        }
                        
                    }
                    else
                        foreach (NpgsqlParameter p in Parameters)
                        {
                            if (((p.Direction == ParameterDirection.Output) ||
                                (p.Direction == ParameterDirection.InputOutput)) && (i < nrs.RowDescription.NumFields ))
                            {
                                p.Value = nar[i];
                                i++;
                            }
                        }
                }
                
            }   
            
            
        }

        /// <summary>
        /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
        /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
        /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
        /// </summary>
        /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
        IDataReader IDbCommand.ExecuteReader()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader");

            return (NpgsqlDataReader) ExecuteReader();
        }

        /// <summary>
        /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
        /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
        /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
        /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
        /// </summary>
        /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
        /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
        IDataReader IDbCommand.ExecuteReader(CommandBehavior cb)
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "IDbCommand.ExecuteReader", cb);

            return (NpgsqlDataReader) ExecuteReader(cb);
        }

        /// <summary>
        /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
        /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
        /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>.
        /// </summary>
        /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
        public NpgsqlDataReader ExecuteReader()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader");

            return ExecuteReader(CommandBehavior.Default);
        }

        /// <summary>
        /// Sends the <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> to
        /// the <see cref="Npgsql.NpgsqlConnection">Connection</see> and builds a
        /// <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see>
        /// using one of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.
        /// </summary>
        /// <param name="cb">One of the <see cref="System.Data.CommandBehavior">CommandBehavior</see> values.</param>
        /// <returns>A <see cref="Npgsql.NpgsqlDataReader">NpgsqlDataReader</see> object.</returns>
        /// <remarks>Currently the CommandBehavior parameter is ignored.</remarks>
        public NpgsqlDataReader ExecuteReader(CommandBehavior cb)
        {
            // [FIXME] No command behavior handling.

            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteReader", cb);
            commandBehavior = cb;

            ExecuteCommand();
            
            UpdateOutputParameters();
            
            // Get the resultsets and create a Datareader with them.
            return new NpgsqlDataReader(Connector.Mediator.ResultSets, Connector.Mediator.CompletedResponses, cb, this);
        }

        ///<summary>
        /// This method binds the parameters from parameters collection to the bind
        /// message.
        /// </summary>
        private void BindParameters()
        {

            if (parameters.Count != 0)
            {
                Object[] parameterValues = new Object[parameters.Count];
                for (Int32 i = 0; i < parameters.Count; i++)
                {
                    // Do not quote strings, or escape existing quotes - this will be handled by the backend.
                    // DBNull or null values are returned as null.
                    // TODO: Would it be better to remove this null special handling out of ConvertToBackend??
                    parameterValues[i] = parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, true);
                }
                bind.ParameterValues = parameterValues;
            }

            Connector.Bind(bind);
            Connector.Mediator.RequireReadyForQuery = false;
            Connector.Flush();

            connector.CheckErrorsAndNotifications();
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row
        /// in the result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <returns>The first column of the first row in the result set,
        /// or a null reference if the result set is empty.</returns>
        public Object ExecuteScalar()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "ExecuteScalar");

            /*if ((type == CommandType.Text) || (type == CommandType.StoredProcedure))
              if (parse == null)
            			connection.Query(this); 
               else
               {
                 BindParameters();
                 connection.Execute(new NpgsqlExecute(bind.PortalName, 0));
               }
            else
            	throw new NotImplementedException(resman.GetString("Exception_CommandTypeTableDirect"));
            */

            ExecuteCommand();

            // Now get the results.
            // Only the first column of the first row must be returned.

            // Get ResultSets.
            ArrayList resultSets = Connector.Mediator.ResultSets;

            // First data is the RowDescription object.
            // Check all resultsets as insert commands could have been sent along
            // with resultset queries. The insert commands return null and and some queries
            // may return empty resultsets, so, if we find one of these, skip to next resultset.
            // If no resultset is found, return null as per specification.

            NpgsqlAsciiRow ascii_row = null;
            foreach( NpgsqlResultSet nrs in resultSets )
            {
                if( (nrs != null) && (nrs.Count > 0) )
                {
                    ascii_row = (NpgsqlAsciiRow) nrs[0];
                    return ascii_row[0];
                }
            }

            return null;
        }

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

            // Check the connection state.
            CheckConnectionState();

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

            if (connector.BackendProtocolVersion == ProtocolVersion.Version2)
            {
                NpgsqlCommand command = new NpgsqlCommand(GetPrepareCommandText(), connector );
                command.ExecuteNonQuery();
            }
            else
            {
                // Use the extended query parsing...
                //planName = "NpgsqlPlan" + Connector.NextPlanIndex();
                planName = Connector.NextPlanName();
                String portalName = Connector.NextPortalName();

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

                Connector.Parse(parse);
                Connector.Mediator.RequireReadyForQuery = false;
                Connector.Flush();

                // Check for errors and/or notifications and do the Right Thing.
                connector.CheckErrorsAndNotifications();

                bind = new NpgsqlBind("", planName, new Int16[] {0}, null, new Int16[] {0});
            }
        }

        /*
        /// <summary>
        /// Releases the resources used by the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see>.
        /// </summary>
        protected override void Dispose (bool disposing)
        {
            
            if (disposing)
            {
                // Only if explicitly calling Close or dispose we still have access to 
                // managed resources.
                NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Dispose");
                if (connection != null)
                {
                    connection.Dispose();
                }
                base.Dispose(disposing);
                
            }
        }*/

        ///<summary>
        /// This method checks the connection state to see if the connection
        /// is set or it is open. If one of this conditions is not met, throws
        /// an InvalidOperationException
        ///</summary>
        private void CheckConnectionState()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckConnectionState");


            // Check the connection state.
            if (Connector == null || Connector.State != ConnectionState.Open)
            {
                throw new InvalidOperationException(resman.GetString("Exception_ConnectionNotOpen"));
            }
        }

        /// <summary>
        /// This method substitutes the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see>, if exist, in the command
        /// to their actual values.
        /// The parameter name format is <b>:ParameterName</b>.
        /// </summary>
        /// <returns>A version of <see cref="Npgsql.NpgsqlCommand.CommandText">CommandText</see> with the <see cref="Npgsql.NpgsqlCommand.Parameters">Parameters</see> inserted.</returns>
        internal String GetCommandText()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetCommandText");

            if (planName == String.Empty)
                return GetClearCommandText();
            else
                return GetPreparedCommandText();
        }


        private String GetClearCommandText()
        {
            if (NpgsqlEventLog.Level == LogLevel.Debug)
                NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetClearCommandText");

            Boolean addProcedureParenthesis = false;    // Do not add procedure parenthesis by default.
            
            Boolean functionReturnsRecord = false;      // Functions don't return record by default.
            
            Boolean functionReturnsRefcursor = false;   // Functions don't return refcursor by default.   
            
            String result = text;

            if (type == CommandType.StoredProcedure)
            {
                
                functionReturnsRecord = CheckFunctionReturnRecord();
                
                functionReturnsRefcursor = CheckFunctionReturnRefcursor();
                
                // Check if just procedure name was passed. If so, does not replace parameter names and just pass parameter values in order they were added in parameters collection.
                if (!result.Trim().EndsWith(")"))  
                {
                    addProcedureParenthesis = true;
                    result += "(";
                }
                
                if (Connector.SupportsPrepare)
                    result = "select * from " + result; // This syntax is only available in 7.3+ as well SupportsPrepare.
                else
                    result = "select " + result;        //Only a single result return supported. 7.2 and earlier.
            }
            else if (type == CommandType.TableDirect)
                return "select * from " + result;       // There is no parameter support on table direct.

            if (parameters == null || parameters.Count == 0)
            {
                if (addProcedureParenthesis)
                    result += ")";
                
                
                // If function returns ref cursor just process refcursor-result function call 
                // and return command which will be used to return data from refcursor.
                
                if (functionReturnsRefcursor)
                    return ProcessRefcursorFunctionReturn(result);
                
                        
                if (functionReturnsRecord)
                    result = AddFunctionReturnsRecordSupport(result);
                
                
                result = AddSingleRowBehaviorSupport(result);
                                           
                return result;
            }   

             
            // Get parameters in query string to translate them to their actual values.
             
            // This regular expression gets all the parameters in format :param or @param 
            // and everythingelse.
            // This is only needed if query string has parameters. Else, just append the
            // parameter values in order they were put in parameter collection.
            
            
            // If parenthesis don't need to be added, they were added by user with parameter names. Replace them.
            if (!addProcedureParenthesis)
            {
            
                Regex a = new Regex(@"(:[\w]*)|(@[\w]*)|(.)");
                
                //CheckParameters();
    
                StringBuilder sb = new StringBuilder();
                
                for ( Match m = a.Match(result); m.Success; m = m.NextMatch() )
                {
                    String s = m.Groups[0].ToString();
                    
                    if ((s.StartsWith(":") ||
                         s.StartsWith("@")) &&
                         Parameters.Contains(s))
                    {
                        // It's a parameter. Lets handle it.
                    
                        NpgsqlParameter p = Parameters[s];
                        if ((p.Direction == ParameterDirection.Input) ||
                             (p.Direction == ParameterDirection.InputOutput))
                        {
                            
                            // FIXME DEBUG ONLY
                            // adding the '::<datatype>' on the end of a parameter is a highly
                            // questionable practice, but it is great for debugging!
                            sb.Append(p.TypeInfo.ConvertToBackend(p.Value, false));
                            
                            // Only add data type info if we are calling an stored procedure.
                            
                            if (type == CommandType.StoredProcedure)
                            {
                                sb.Append("::");
                                sb.Append(p.TypeInfo.Name);
                                
                                if (p.TypeInfo.UseSize && (p.Size > 0))
                                    sb.Append("(").Append(p.Size).Append(")");
                            }
                        }
                            
                    }   
                    else 
                        sb.Append(s);
                    
                }
                
                result = sb.ToString();
            }
                
                
            else
            {
                                
                for (Int32 i = 0; i < parameters.Count; i++)
                {
                    NpgsqlParameter Param = parameters[i];
    
                    
                    if ((Param.Direction == ParameterDirection.Input) ||
                         (Param.Direction == ParameterDirection.InputOutput))
                    
                        
                        result += Param.TypeInfo.ConvertToBackend(Param.Value, false) + "::" + Param.TypeInfo.Name + ",";
                }
            
            
                // Remove a trailing comma added from parameter handling above. If any.
                // Maybe there are only output parameters. If so, there will be no comma.
                if (result.EndsWith(","))
                    result = result.Remove(result.Length - 1, 1);
                
                result += ")";
            }

            if (functionReturnsRecord)
                result = AddFunctionReturnsRecordSupport(result);
            
            // If function returns ref cursor just process refcursor-result function call 
            // and return command which will be used to return data from refcursor.
                
            if (functionReturnsRefcursor)
                return ProcessRefcursorFunctionReturn(result);
                
                
            return AddSingleRowBehaviorSupport(result);
        }
        
        
        
        private Boolean CheckFunctionReturnRecord()
        {
        
            if (Parameters.Count == 0)
                return false;
                
            String returnRecordQuery = "select count(*) > 0 from pg_proc where prorettype = ( select oid from pg_type where typname = 'record' ) and proargtypes='{0}' and proname='{1}';";
            
            StringBuilder parameterTypes = new StringBuilder("");
            
            foreach(NpgsqlParameter p in Parameters)
            {
                if ((p.Direction == ParameterDirection.Input) ||
                (p.Direction == ParameterDirection.InputOutput))
                {
                    parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
                }
            }
        
                
            NpgsqlCommand c = new NpgsqlCommand(String.Format(returnRecordQuery, parameterTypes.ToString(), CommandText), Connection);
            
            Boolean ret = (Boolean) c.ExecuteScalar();
            
            // reset any responses just before getting new ones
            connector.Mediator.ResetResponses();
            return ret;
            
        
        }
        
        private Boolean CheckFunctionReturnRefcursor()
        {
            
            String returnRecordQuery = "select count(*) > 0 from pg_proc where prorettype = ( select oid from pg_type where typname = 'refcursor' ) and proargtypes='{0}' and proname='{1}';";
            
            StringBuilder parameterTypes = new StringBuilder("");
            
            foreach(NpgsqlParameter p in Parameters)
            {
                if ((p.Direction == ParameterDirection.Input) ||
                     (p.Direction == ParameterDirection.InputOutput))
                {
                    parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID + " ");
                }
            }
        
                
            NpgsqlCommand c = new NpgsqlCommand(String.Format(returnRecordQuery, parameterTypes.ToString(), CommandText), Connection);
            
            Boolean ret = (Boolean) c.ExecuteScalar();
            
            // reset any responses just before getting new ones
            connector.Mediator.ResetResponses();
            return ret;
            
        
        }
        
        
        private String AddFunctionReturnsRecordSupport(String OriginalResult)
        {
                                
            StringBuilder sb = new StringBuilder(OriginalResult);
            
            sb.Append(" as (");
            
            foreach(NpgsqlParameter p in Parameters)
            {
                if ((p.Direction == ParameterDirection.Output) ||
                (p.Direction == ParameterDirection.InputOutput))
                {
                    sb.Append(String.Format("{0} {1}, ", p.ParameterName.Substring(1), p.TypeInfo.Name));
                }
            }
            
            String result = sb.ToString();
            
            result = result.Remove(result.Length - 2, 1);
            
            result += ")";
            
            
            
            return result;
            
            
        }
        
        ///<summary>
        /// This methods takes a string with a function call witch returns a refcursor or a set of
        /// refcursor. It will return the names of the open cursors/portals which will hold
        /// results. In turn, it returns the string which is needed to get the data of this cursors
        /// in form of one resultset for each cursor open. This way, clients don't need to do anything
        /// else besides calling function normally to get results in this way.
        ///</summary>
               
        private String ProcessRefcursorFunctionReturn(String FunctionCall)
        {
            NpgsqlCommand c = new NpgsqlCommand(FunctionCall, Connection);
            
            NpgsqlDataReader dr = c.ExecuteReader();
            
            StringBuilder sb = new StringBuilder();
            
            while (dr.Read())
            {
                sb.Append("fetch all from \"").Append(dr.GetString(0)).Append("\";");
                
            }
            
            sb.Append(";"); // Just in case there is no response from refcursor function return.
            
            // reset any responses just before getting new ones
            connector.Mediator.ResetResponses();
            
            return sb.ToString();
                    
            
        }



        private String GetPreparedCommandText()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPreparedCommandText");

            if (parameters.Count == 0)
                return "execute " + planName;


            StringBuilder result = new StringBuilder("execute " + planName + '(');


            for (Int32 i = 0; i < parameters.Count; i++)
            {
                result.Append(parameters[i].TypeInfo.ConvertToBackend(parameters[i].Value, false) + ',');
            }

            result = result.Remove(result.Length - 1, 1);
            result.Append(')');

            return result.ToString();

        }



        private String GetParseCommandText()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetParseCommandText");

            Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.
            
            String parseCommand = text;

            if (type == CommandType.StoredProcedure)
            {
                // Check if just procedure name was passed. If so, does not replace parameter names and just pass parameter values in order they were added in parameters collection.
                if (!parseCommand.Trim().EndsWith(")"))  
                {
                    addProcedureParenthesis = true;
                    parseCommand += "(";
                }
                
                parseCommand = "select * from " + parseCommand; // This syntax is only available in 7.3+ as well SupportsPrepare.
            }
            else if (type == CommandType.TableDirect)
                return "select * from " + parseCommand; // There is no parameter support on TableDirect.

            if (parameters.Count > 0)
            {
                // The ReplaceParameterValue below, also checks if the parameter is present.

                String parameterName;
                Int32 i;

                for (i = 0; i < parameters.Count; i++)
                {
                    if ((parameters[i].Direction == ParameterDirection.Input) ||
                    (parameters[i].Direction == ParameterDirection.InputOutput))
                    {
                    
                        if (!addProcedureParenthesis)
                        {
                            //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
                            parameterName = parameters[i].ParameterName;
                            //textCommand = textCommand.Replace(':' + parameterName, "$" + (i+1));
                            parseCommand = ReplaceParameterValue(parseCommand, parameterName, "$" + (i+1) + "::" + parameters[i].TypeInfo.Name);
                        }
                        else
                            parseCommand += "$" + (i+1) + "::" + parameters[i].TypeInfo.Name;
                    }

                }
            }

            if (addProcedureParenthesis)
                return parseCommand + ")";
            else
                return parseCommand;

        }


        private String GetPrepareCommandText()
        {
            NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "GetPrepareCommandText");

            Boolean addProcedureParenthesis = false;  // Do not add procedure parenthesis by default.

            planName = Connector.NextPlanName();

            StringBuilder command = new StringBuilder("prepare " + planName);

            String textCommand = text;

            if (type == CommandType.StoredProcedure)
            {
                // Check if just procedure name was passed. If so, does not replace parameter names and just pass parameter values in order they were added in parameters collection.
                if (!textCommand.Trim().EndsWith(")"))  
                {
                    addProcedureParenthesis = true;
                    textCommand += "(";
                }
                
                textCommand = "select * from " + textCommand;
            }
            else if (type == CommandType.TableDirect)
                return "select * from " + textCommand; // There is no parameter support on TableDirect.


            if (parameters.Count > 0)
            {
                // The ReplaceParameterValue below, also checks if the parameter is present.

                String parameterName;
                Int32 i;

                for (i = 0; i < parameters.Count; i++)
                {
                    if ((parameters[i].Direction == ParameterDirection.Input) ||
                    (parameters[i].Direction == ParameterDirection.InputOutput))
                    {
                    
                        if (!addProcedureParenthesis)
                        {
                            //result = result.Replace(":" + parameterName, parameters[i].Value.ToString());
                            parameterName = parameters[i].ParameterName;
                            // The space in front of '$' fixes a parsing problem in 7.3 server
                            // which gives errors of operator when finding the caracters '=$' in
                            // prepare text
                            textCommand = ReplaceParameterValue(textCommand, parameterName, " $" + (i+1));
                        }
                        else
                            textCommand += " $" + (i+1);
                    }

                }

                //[TODO] Check if there is any missing parameters in the query.
                // For while, an error is thrown saying about the ':' char.

                command.Append('(');

                for (i = 0; i < parameters.Count; i++)
                {
                    //                    command.Append(NpgsqlTypesHelper.GetDefaultTypeInfo(parameters[i].DbType));
                    command.Append(parameters[i].TypeInfo.Name);

                    command.Append(',');
                }

                command = command.Remove(command.Length - 1, 1);
                command.Append(')');

            }
            
            if (addProcedureParenthesis)
                textCommand += ")";

            command.Append(" as ");
            command.Append(textCommand);


            return command.ToString();

        }


        private String ReplaceParameterValue(String result, String parameterName, String paramVal)
        {
            Int32 resLen = result.Length;
            Int32 paramStart = result.IndexOf(parameterName);
            Int32 paramLen = parameterName.Length;
            Int32 paramEnd = paramStart + paramLen;
            Boolean found = false;


            while(paramStart > -1)
            {
                if((resLen > paramEnd) && !Char.IsLetterOrDigit(result, paramEnd))
                {
                    result = result.Substring(0, paramStart) + paramVal + result.Substring(paramEnd);
                    found = true;
                }
                else if(resLen == paramEnd)
                {
                    result = result.Substring(0, paramStart)+ paramVal;
                    found = true;
                }
                else
                    break;
                resLen = result.Length;
                paramStart = result.IndexOf(parameterName, paramStart);
                paramEnd = paramStart + paramLen;

            }//while
            if(!found)
                throw new IndexOutOfRangeException (String.Format(resman.GetString("Exception_ParamNotInQuery"), parameterName));


            return result;
        }//ReplaceParameterValue
        
        
        private String AddSingleRowBehaviorSupport(String ResultCommandText)
        {
            
            ResultCommandText = ResultCommandText.Trim();
        
            if ((commandBehavior & CommandBehavior.SingleRow) > 0)
            {
                if (ResultCommandText.EndsWith(";"))
                    ResultCommandText = ResultCommandText.Substring(0, ResultCommandText.Length - 1);
                ResultCommandText += " limit 1;";
                
            }
            
            
            
            return ResultCommandText;
            
        }


        private void ExecuteCommand()
        {
            // Check the connection state first.
            CheckConnectionState();

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


            if (parse == null)
            {
                Connector.Query(this);

                // Check for errors and/or notifications and do the Right Thing.
                connector.CheckErrorsAndNotifications();
            }
            else
            {
                try
                {

                    BindParameters();

                    connector.Execute(new NpgsqlExecute(bind.PortalName, 0));

                    // Check for errors and/or notifications and do the Right Thing.
                    connector.CheckErrorsAndNotifications();
                }
                finally
                {
                    // As per documentation:
                    // "[...] When an error is detected while processing any extended-query message,
                    // the backend issues ErrorResponse, then reads and discards messages until a
                    // Sync is reached, then issues ReadyForQuery and returns to normal message processing.[...]"
                    // So, send a sync command if we get any problems.

                    connector.Sync();
                }
            }
        }
        
        
         
        
    }
}