using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;

namespace com.cloudsoft.utils
{

    /// <summary>
    ///  Class contains methods for performing database queries
    /// </summary>
    public class DBHelper
    {

        private Boolean _pbUppercaseNodes = false;
        private String _pzDateFormat = "dd-MMM-yyyy HH:mm:ss";

        private String pzSystem = "";
        private String pzSchema = "";
        private XMLHelper _request = null;
        private Boolean lbAutoCommit = true;
        private DbConnection oConnection = null;
        private ArrayList _aParser = null;
        private DBParams _Command = null;

        public DBHelper(XMLHelper oRequest)
        {
            lbAutoCommit = (oRequest._con == null);
            _request = oRequest;
            pzSystem = oRequest.getString("system");
            oConnection = oRequest._con;

        }
        
        /// <summary>
        /// Creates, opens, and returns a connection to the database using details specified in the registry
        /// with the name stored in the local pzSystem variable.
        /// </summary>
        /// <param name="bIsRecordSet">If true then PLSQLRSet=1 is added to the connection string.</param>
        /// <param name="bDbConnection">Whether to create an DbConnection or a standard DbConnection.</param>
        /// <returns>An DbConnection.</returns>
        private DbConnection getConnection()
        {

            DbConnection cn;

            string zConnString = "Server=localhost,1435;Database=AlexisPTSTest;User Id=gpspl;Password=yW2S6KXdY8;"; 
            //String zConnString = ConfigurationManager.ConnectionStrings[pzSystem].ConnectionString;
            cn = new SqlConnection(zConnString);

            try
            {
                cn.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cn;

        }


        //For File Loader ends here

        #region Public Methods

        /// <summary>Creates a new instance of DBHelper for a given system, 
        /// defaulting to the default data provider</summary>
        /// <param name="zSystem">The system to connect to</param>
        /// <remarks>
        /// A registry entry should be set up for the given system under Hkey/LocalMachine/Software/GlaxoWellcome/system
        /// The system should have a minimum of the following:
        /// <list type="bullet">
        /// <item><description>DBUser</description></item>
        /// <item><description>DBPassword</description></item>
        /// <item><description>DBSource</description></item>
        /// </list>
        ///</remarks>
        public DBHelper(String zSystem)
        {
            pzSystem = zSystem.ToUpper();
        }
        public DBHelper(String zSystem, Boolean bUppercaseNodes) : this(zSystem)
        {
            _pbUppercaseNodes = bUppercaseNodes;
        }
        public DBHelper(String zSystem, Boolean bUppercaseNodes, String zDateFormat) : this(zSystem, bUppercaseNodes)
        {
            _pzDateFormat = zDateFormat;
        }

        
    /// <summary>
    /// Creates Parameters to pass to the passed in Command type
    /// </summary>
    /// <param name="zCommandText">Can be either a SQL Command or a stored procedure depending on the type</param>
    /// <param name="tCommandType">The type of Command to create parameters for</param>
    /// <returns>The Parameters Object</returns>
    public DBParams createSPParams(String zSQL)
    {
        return createSPParams(zSQL, "");
    }
    public DBParams createSPParams(String zSQL, XMLHelper oNode)
    {
        _request = oNode;
        zSQL = parseSQL(zSQL);
        if (_Command != null && _Command.Command != null)
            _Command.Command.Connection.Close();

        DbCommand oCommand = getConnection().CreateCommand();
        oCommand.CommandText = zSQL;
        _Command = new DBParams(oCommand);
        addParserParams(oNode);

        return _Command;
    }
    public DBParams createSPParams(String zSQL, String zXPath)
    {
        if (_request != null)
            zSQL = parseSQL(zSQL);
        if (_Command != null && _Command.Command != null)
            _Command.Command.Connection.Close();

        DbCommand oCommand = getConnection().CreateCommand();
        oCommand.CommandText = zSQL; 
        _Command = new DBParams(oCommand);
        if (zXPath.Equals(""))
            addParserParams();
        else
            addParserParams(zXPath);
        return _Command;
    }
        
    public String parseSQL(String zSQL)
    {
        _aParser = new ArrayList();
        return parseSQL(zSQL, false);
    }

        public String parseSQL(String zSQL, Boolean bConditional)
    {
        try
        {
        //Make sure square brackets are even
        int iEven = 0;
        for(int i = 0, n = zSQL.Length ; i < n ; i++)
        {
            char c = zSQL[i];
            if (c == '[') iEven++;
            if (c == ']') iEven--;
        }
        if (iEven != 0)
        {
            throw new Exception("Square brackets must be matched");
        }

        // Find optional clauses
        Regex regex = new Regex("\\[([^\\[\\]]+|\\[([^\\[\\]]+|\\[([^\\[\\]]+)*\\])*\\])*\\]");
        Match matcher = regex.Match(zSQL);
        int iCurr = 0;
        StringBuilder sb = new StringBuilder();
        while (matcher.Success)
        {
            sb.Append(replaceSQLVars(zSQL.Substring(iCurr, matcher.Index - iCurr), bConditional));
            //iCurr = matcher.start() + matcher.end();
            iCurr = matcher.Index + matcher.Length;
            String match = matcher.Groups[0].Value;
            match = match.Substring(1, match.Length - 2);
            if (matcher.Groups[2].Value != null)
                sb.Append(parseSQL(match,true));
            else
                sb.Append(replaceSQLVars(match, true));
            matcher = matcher.NextMatch();
        }

            sb.Append(replaceSQLVars(zSQL.Substring(iCurr), bConditional));
            return sb.ToString();
        }
        catch (Exception ex)
        {
            return "";
        }
    }

    private String replaceSQLVars(String zSQL, Boolean bConditional)
    {
        try
        {
            // Find variables to replace
            Regex regex = new Regex("[vilcbotdf]?:[@a-z_0-9./]*");
            Match matcher = regex.Match(zSQL);
            StringBuilder sb = new StringBuilder();
            int iCurr = 0;
            while (matcher.Success)
            {
                // Get the replace value
                String match = matcher.Groups[0].Value;
                String zMatch = match.Substring(match.IndexOf(':')+1);
                char zMatchType = match[0];

                // If this is a conditional replace return nothing as soon as a variable is empty
                //if (bConditional && _request.getString(zMatch) == "" && zMatchType != 'o')
                if (bConditional && _request.getString(zMatch).Equals(""))
                    return "";
                String zTextBefore = zSQL.Substring(iCurr, matcher.Index - iCurr);
                String zReplace = "";
                switch (zMatchType)
                {
                    case ':':
                    case 'o': zReplace = _request.getString(zMatch);
                        //if (zReplace == "") zReplace = "=";
                        if (zReplace.Equals("soundex"))
                        {
                            String[] arrWords = zTextBefore.Split(' ');
                            zReplace += "(" + arrWords[arrWords.Length-1] + ") = ";
                            zTextBefore = "";
                            for (int i = 0; i < arrWords.Length - 1; i++)
                                zTextBefore += arrWords[i] + " ";
                        }
                        break;
                    case 'i':
                    case 'f':
                    case 't':
                    case 'd':
                    case 'b':
                        zReplace = $"@{zMatch}";
                        _aParser.Add(match);
                        break;
                    default:
                        zReplace = $"@{zMatch}"; ;
                        String zOp = _request.getString("op_" + zMatch);
                        if (zOp.Equals("soundex"))
                            zReplace = "soundex('" + zReplace + "')";
                        else if (zOp.Equals("like"))
                            match = "l:" + zMatch;
                        _aParser.Add(match);
                        break;
                }
                sb.Append(zTextBefore);
                iCurr = matcher.Index +  matcher.Length;
                sb.Append(zReplace);
                matcher = matcher.NextMatch();
            }
            sb.Append(zSQL.Substring(iCurr));
            return sb.ToString();
        }
        catch (Exception ex)
        {
            return "";
        }
    }
    private void addParserParams()
    {
        addParserParams(_request);
    }
    private void addParserParams(String zXPath)
    {
        foreach (XMLHelper oNode in _request.getNodes(zXPath))
        {
            addParserParams(oNode);
            //_Command.addBatch();
        }
    }
    private void addParserParams(XMLHelper oNode)
    {
        if (_aParser is null)
            return;

        for (int i=0; i < _aParser.Count; i++)
        {
            // Get the replace value
            String match = _aParser[i] as string;
            String zMatch = match.Substring(match.IndexOf(':')+1);
            char zMatchType = match[0];
            Object oParam = null;
            String zValue = (zMatch.Equals("xml_data")) ? oNode.getNode(zMatch).getFirstChild().getXML() : oNode.getString(zMatch);
            DbType iType = DbType.String;
            switch (zMatchType)
            {
                case 'f': oParam = oNode.getDouble(zMatch);
                    iType = DbType.Decimal;
                    break;
                case 'i': oParam = oNode.getInt(zMatch);
                    iType = DbType.Int32;
                    break;
                case 't': oParam = oNode.getSQLDateTime(zMatch);
                    iType = DbType.DateTime;
                    break;
                case 'd': oParam = oNode.getSQLDate(zMatch);
                    iType = DbType.Date;
                    break;
                case 'b': oParam = (oNode.getBoolean(zMatch)) ? "Y" : "N";
                    zValue = (String) oParam;
                    iType = DbType.Boolean;
                    break;
                case 'c': oParam = zValue;
                    iType = DbType.String;
                    break;
                case 'l': oParam = "%" + zValue + "%";
                    zValue = (String) oParam;
                    break;
                default: oParam = zValue;
                    break;
            }
            if (zValue.Length == 0)
                oParam = null;

            _Command.set($"@{zMatch}", oParam, iType);
        }

    }
        
        /// <summary>
        /// Legacy method - now superseded by createDBSPParams
        /// </summary>
        public DBParams createSPParams(String zCommandText, CommandType tCommandType)
        {
            DbCommand oCommand = getConnection().CreateCommand();
            oCommand.CommandType = tCommandType;
            oCommand.CommandText = zCommandText;
            return new DBParams(oCommand);
        }


        /// <summary>
        /// Executes a store procedure optionally returning output parameters
        /// This is not used for returning recordsets as XML, but for transactional stored procedures
        /// </summary>
        /// <param name="oParams">DBParams Parameters</param>
        /// <returns>An XMLHelper with any output parameters</returns>
        public XMLHelper execute(DBParams oParams)
        {
            //return execute(oParams, false);
            return execute(oParams, false);
        }

        /// <summary>
        /// Executes a SQL Query
        /// This is not used for returning recordsets as XML, but for transactional stored procedures
        /// </summary>
        /// <param name="zSQL">DBParams Parameters</param>
        /// <returns>An XMLHelper with any output parameters</returns>
        public XMLHelper execute(String zSQL)
        {
            DBParams oParams = createSPParams(zSQL, CommandType.Text);
            return execute(oParams, false);
        }


        /// <summary>
        /// Executes a store procedure optionally returning output parameters
        /// This is not used for returning recordsets as XML, but for transactional stored procedures
        /// </summary>
        /// <param name="oParams">DBParams Parameters</param>
        /// <returns>An XMLHelper with any output parameters</returns>
        public XMLHelper execute(DBParams oParams, Boolean bKeepOpen)
        {
            return executeODAC(oParams, bKeepOpen);
        }

        #endregion 

        #region ODAC Specific Private Methods

        /// <summary>
        /// Executes a store procedure optionally returning output parameters
        /// This is not used for returning recordsets as XML, but for transactional stored procedures
        /// </summary>
        /// <param name="oParams">DBParams Parameters</param>
        /// <param name="bKeepOpen">Flag to determine if the connection is kept open or not</param>
        /// <returns>An XMLHelper with any output parameters</returns>
        private XMLHelper executeODAC(DBParams oParams, Boolean bKeepOpen)
        {
            DateTime dStart = DateTime.Now;
            XMLHelper oResponse = new XMLHelper("response");
            oResponse.appendNode("rows", Convert.ToString(oParams.ExecuteNonQuery()));
            foreach (DbParameter oParam in oParams.ODACParameters())
            {
                if (oParam.Direction == System.Data.ParameterDirection.Output)
                {
                    oResponse.appendNode(oParam.ParameterName.Replace(":", ""), oParam.Value.ToString());
                }   
            }
            TimeSpan tDiff = DateTime.Now.Subtract(dStart);
            oResponse.appendAttribute("duration", tDiff.TotalMilliseconds.ToString());
            if (!bKeepOpen)
            {
                oParams.CloseConnection();
                oParams.CloseCommand();
            }
            return oResponse;
        }



        /// <summary>
        /// Creates Parameters to pass to a Stored Procedure. Also calls getConnection to get an
        /// DbConnection, and appends the required RefCursor.
        /// </summary>
        /// <param name="zCommandText">The stored procedure to be called</param>
        /// <returns>The Parameters Object</returns>
        /// <example>
        /// DBHelper oDB = new DBHelper("uki");
        /// DBParams oCommand = oDB.createOracleSPParams("pkg_orgcusts.RetrieveTargetOrgsByBrand");
        /// </example>
        private DBParams createODACSPParams(String zCommandText, CommandType tCommandType, Boolean bindByName)
        {
            DbCommand oCommand = getConnection().CreateCommand();
            oCommand.CommandType = tCommandType;
            
            switch (tCommandType)
            {
                case CommandType.Text:
                    {
                        oCommand.CommandText = zCommandText;
                        break;
                    }
                case CommandType.StoredProcedure:
                case CommandType.TableDirect:
                    {
                        oCommand.CommandText = pzSchema + "." + zCommandText;
                        break;
                    }
            }

            return new DBParams(oCommand);
        }


        #endregion

        public void closeConnection(DBParams oCommand)
        {
            oCommand.CloseConnection();
        }

        #region Overrides for Get Dom

        public XMLHelper getDOM(IDataReader oRS)
        {
            return getDOM(oRS, 0, 2000);
        }
        /// <summary>
        /// Gets a DOM of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <returns>An XMLHelper representation of the recordset in the format response/rows/row</returns>
        public XMLHelper getDOM(IDataReader oRS, int iStartRow, int iMaxRows)
        {
            return appendToDOM(null, null, null, null, oRS, "rows", "row", iStartRow, iMaxRows, true, RefCurPosition.First, false, true, false);
        }
        /// <summary>
        /// Gets a DOM of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <returns>An XMLHelper representation of the recordset in the format response/rows/row</returns>
        public XMLHelper getDOM(String zSQL)
        {
            _Command = createSPParams(zSQL);
            return getDOM(_Command, "rows", "row");
        }
        /// <summary>
        /// Gets a DOM of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <returns>An XMLHelper representation of the recordset in the format response/rows/row</returns>
        public XMLHelper getDOM(DBParams oCommand, String zRowsetName, String zRowName)
        {
            return getDOM(oCommand, 0, 0, false, RefCurPosition.First, zRowsetName, zRowName, false, true, null, null, null, null, false);
        }
        /// <summary>
        /// Gets a DOM of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <returns>An XMLHelper representation of the recordset in the format response/rows/row</returns>
        public XMLHelper getDOM(DBParams oCommand, Boolean withRefCur)
        {
            
            RefCurPosition refCurPosition = RefCurPosition.None;
            if (withRefCur)
                refCurPosition = RefCurPosition.First;

            return getDOM(oCommand, 0, 0, false, refCurPosition, "rows", "row", false, true, null, null, null, null, false);

        }

        /// <summary>
        /// Gets a XML DOM of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <param name="iStartRow">The start row number to retrieve</param>
        /// <param name="iMaxRows">The maximum number of rows</param>
        /// <param name="bShowTotal">Determines whether the total Rowcount is shown.</param>
        /// <param name="zRowsetName">The Rowset node name.</param>
        /// <param name="zRowName">The Row node name</param>
        /// <param name="bShowMetadata">Determines whether to create meta data info</param>
        /// <returns>An XMLHelper representation of the recordset</returns>
        public XMLHelper getDOM(DBParams oCommand, int iStartRow, int iMaxRows, Boolean bShowTotal, DBHelper.RefCurPosition refCurPosition,
            String zRowsetName, String zRowName, Boolean bShowMetadata, Boolean bCloseConnection, 
            XMLHelper oInputDOM, String zNode, String zKey, String zGrouping, Boolean CheckRowName)
        {

            //Time the processing...
            DateTime dStart = DateTime.Now;

            //Execute the database Command...
            XMLHelper oResponse = appendToDOM(oInputDOM, zNode, zKey, zGrouping, oCommand, zRowsetName, zRowName, iStartRow, iMaxRows, bShowTotal, refCurPosition, bShowMetadata, false, CheckRowName);
            
            //Add the execution time to the ouptut...
            TimeSpan tDiff = DateTime.Now.Subtract(dStart);
            oResponse.appendAttribute("duration", tDiff.TotalMilliseconds.ToString());
            
            //Close the connection if required...
            if (bCloseConnection)
            {
                oCommand.CloseConnection();
            }

            return oResponse;

        }

        #endregion

        #region Overrides for Append To DOM

        /// <summary>
        /// Executes a Command and adds the recordset to an existing DOM
        /// </summary>
        /// <param name="oInputDom">Dom to append to</param>
        /// <param name="zNode">Xpath to the node to append information to</param>
        /// <param name="zKey">Key to match</param>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <param name="zRowsetName">The Rowset node name</param>
        /// <param name="zRowName">The row Node Name</param>
        public void appendToDOM(XMLHelper oInputDom, String zNode, String zKey, DBParams oCommand, String zRowsetName, String zRowName)
        {
            appendToDOM(oInputDom, zNode, zKey, null, oCommand, zRowsetName, zRowName, 0, 9999999,
                false, (oCommand.Command.CommandType == CommandType.Text ? RefCurPosition.None : RefCurPosition.First), false, false, false); 
            //appendToDOM(oInputDom, zNode, zKey, oCommand, zRowsetName, zRowName, 0, 9999999, false, false);
        }

        /// <summary>
        ///  Executes a Command and adds the recordset to an existing DOM
        /// </summary>
        /// <param name="oInputDom">Dom to append to</param>
        /// <param name="zNode">Xpath to the node to append information to</param>
        /// <param name="zKey">Key to match</param>
        /// <param name="oParams">Parameters to pass to the stored procedure</param>
        /// <param name="zRowsetName">The Rowset node name</param>
        /// <param name="zRowName">The row Node Name</param>
        /// <param name="iStartRow">The start row number</param>
        /// <param name="iMaxRows">The maximun number of rows to fetch</param>
        /// <param name="bShowTotal">Determines whether totals are shown</param>
        /// <param name="bShowMetadata">Determines whether metadata is to be shown</param>
        /// 
        public void appendToDOM(XMLHelper oInputDom, String zNode, String zKey, DBParams oParams, String zRowsetName, String zRowName, int iStartRow, int iMaxRows, Boolean bShowTotal, Boolean bShowMetadata)
        {
            appendToDOM(oInputDom, zNode, zKey, null, oParams, zRowsetName, zRowName, iStartRow, iMaxRows, 
                        bShowTotal, RefCurPosition.None, false, false, false);
        }
        
        /// <summary>
        /// Executes a Command and adds the recordset to an existing DOM
        /// JF 12/2007 - Generalised to take an IDbCommand, allowing it to be used with either an
        ///              DbCommand or DbCommand
        /// </summary>
        /// <param name="oInputDom">Dom to append to</param>
        /// <param name="zNode">Xpath to the node to append information to</param>
        /// <param name="zKey">Key to match</param>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <param name="zRowsetName">The Rowset node name</param>
        /// <param name="zRowName">The row Node Name</param>
        /// <param name="iStartRow">The start row number</param>
        /// <param name="iMaxRows">The maximun number of rows to fetch</param>
        /// <param name="bShowTotal">Determines whether totals are shown</param>
        /// <param name="bShowMetadata">Determines whether metadata is to be shown</param>
        /// <param name="bCloseConnection">Whether to close the Commands connection when finished exection</param>
        /// <param name="CheckRowName">This is an override to force a check of the row name against any xpath which 
        /// may have been defined in the column name from the procedure/package. It's function is to avoid the creation
        /// of a dom structure such as rows/row/row if the xpath in the procedure reads row/@value. If the first component
        /// of the xpath in the procedure matches the rowname specified in the call to this method, then it will assume
        /// that you don't want to append a new child with the same name. It will still allow the creation of 
        /// rows/row/childrow if the procedure reads childrow/@value.</param>
        public XMLHelper appendToDOM(XMLHelper oInputDom, String zNode, String zKey, String zGrouping, DBParams oParams, 
            String zRowsetName, String zRowName, int iStartRow, int iMaxRows, 
            Boolean bShowTotal, DBHelper.RefCurPosition refCurPosition, Boolean bShowMetadata, Boolean bCloseConnection,
            Boolean CheckRowName)
        {


            #region Execute the database Command

            //Execute the Command and obtain the output recordset...
            DbCommand oCommand = oParams.AsDbCommand();

            //oCommand.Prepare();

            CommandBehavior cb = CommandBehavior.Default;
            if (bCloseConnection)
            {
                cb = CommandBehavior.CloseConnection;
            }
            IDataReader oRS = oCommand.ExecuteReader(cb);
            
            #endregion

            //Return the response (the input dom may also have been appended to inline)...
            return appendToDOM(oInputDom, zNode, zKey, zGrouping, oRS, zRowsetName, zRowName, iStartRow, iMaxRows, bShowTotal, refCurPosition, bShowMetadata, bCloseConnection, CheckRowName);
            
        }

        public XMLHelper appendToDOM(XMLHelper oInputDom, String zNode, String zKey, String zGrouping, IDataReader oRS,
            String zRowsetName, String zRowName, int iStartRow, int iMaxRows,
            Boolean bShowTotal, DBHelper.RefCurPosition refCurPosition, Boolean bShowMetadata, Boolean bCloseConnection,
            Boolean CheckRowName)
        {

            //Establish output vars...
            XMLHelper oResponse = new XMLHelper("response");
            //XMLHelper oResponse = (oInputDom == null || oInputDom.getName() == "request") ? new XMLHelper("response") : oInputDom;

            XMLHelper oInRowGroup = null;
            XMLHelper oOutRowGroup = null;

            XMLHelper oRow = null;

            String zNodeValue;

            Int32 lFields = oRS.FieldCount;

            String[] vValue = null;
            Int32 lGroupCols = 0;
            String zGroupNode = null;
            if (zGrouping != "" && zGrouping != null)
            {
                vValue = zGrouping.Split(":".ToCharArray());
                lGroupCols = Convert.ToInt32(vValue[0]);
                zGroupNode = vValue[1];
            }

            //Determine whether to create a grouping node...
            if (zRowsetName != "" && zRowsetName != null)
            {
                //Append a grouping node to the response to hold child nodes...
                oOutRowGroup = oResponse.appendNode(zRowsetName);
            }
            else
            {
                //Appending child nodes directly to the response root, rather than a grouping node...
                oOutRowGroup = oResponse;
            }

            // Create a collection of keys in the DOM
            Dictionary<String, XMLHelper> oColKeys = new Dictionary<String, XMLHelper>();
            if (zNode != "" && zNode != null)
            {

                XMLHelper[] oNodes = oInputDom.getNodes(zNode);
                for (int i = 0; i < oNodes.Length; i++)
                {

                    XMLHelper oNode = oNodes[i];
                    if (zRowsetName != "" && zRowsetName != null)
                    {
                        oInRowGroup = oNode.appendNode(zRowsetName);
                    }
                    else
                    {
                        oInRowGroup = oNode;
                    }

                    if (zKey != "" && zKey != null)
                        oColKeys.Add(oNodes[i].getValue(zKey), oInRowGroup);

                }

            }

            //If you are required to append "Metadata", then 
            //append column names and types...
            if (bShowMetadata)
            {
                XMLHelper oMeta = oResponse.appendNode("meta_data");
                XMLHelper oCols = oMeta.appendNode("columns");
                for (int i = 0; i < oRS.FieldCount; i++)
                {
                    XMLHelper oNode = oCols.appendNode("column");
                    oNode.appendAttribute("name", oRS.GetName(i).ToLower());
                    oNode.appendAttribute("type", oRS.GetDataTypeName(i));
                }
            }

            #region Deal with paging

            // Set the start row and max rows. Unless maxrows has been explicitly 
            //defined then default to a safe value (2000 in this case)...
            int iRow = iStartRow;
            if (iMaxRows == 0)
                iMaxRows = Int32.MaxValue;
            //iMaxRows = 2000;

            //If you are "paging", then a startrow will be provided, so 
            //advance to the startrow of this page...
            if (iStartRow > 0)
            {
                for (int i = 0; i < iStartRow; i++)
                    oRS.Read();
            }

            #endregion

            String zCurrRowKey = null;
            String zPrevRowKey = null;
            Boolean bFirst = true;
            XMLHelper oTempNode = null;
            Int32 lField = 0;

            //  Loop through the recordset and construct the XML DOM
            while (oRS.Read() && iRow < (iMaxRows + iStartRow))
            {

                if (iRow >= iStartRow)
                {

                    bFirst = true;

                    // Get the "Key" for this row. Used to determine whether this should be a new grouping.
                    zCurrRowKey = null;
                    for (lField = 0; lField <= lGroupCols - 1; lField++)
                    {
                        zCurrRowKey += (string)oRS[lField].ToString() + "-";
                    }

                    if (!string.IsNullOrEmpty(zKey))
                    {
                        oInRowGroup = oColKeys[oRS[0].ToString()];
                    }

                    //If you aren't grouping, or if this key is new...
                    if (lGroupCols == 0 || zCurrRowKey != zPrevRowKey)
                    {
                        //Create a new node...
                        oRow = new XMLHelper(zRowName);
                        if (oInRowGroup != null)
                        {
                            oRow = oInRowGroup.appendNode(oRow);
                        }
                        oTempNode = oRow;
                    }

                    lField = 0;
                    for (int i = 0; i < oRS.FieldCount; i++)
                    {
                        //DataRow oField = oRS.[i];
                        if (lField >= lGroupCols || zCurrRowKey != zPrevRowKey)
                        {
                            //vValue = oField.Value;
                            zNodeValue = "";

                            //  Get the data from the field
                            if (oRS.GetFieldType(i).Name =="DateTime")
                            {
                                if (oRS.IsDBNull(i))
                                    zNodeValue = "";
                                else
                                    zNodeValue = oRS.GetDateTime(i).ToString(_pzDateFormat);
                            }
                            else
                            {
                                if (oRS.IsDBNull(i))
                                    zNodeValue = "";
                                else
                                    zNodeValue = oRS[i].ToString();
                            }

                            String zNodeName = oRS.GetName(i).ToLower().Replace("\"", "");
                            if (_pbUppercaseNodes)
                                zNodeName = zNodeName.ToUpper();
                            //This is an override to check the row name during an append.
                            //See procedure definition comment for an explanation...
                            if (CheckRowName && zNodeName.Split("/".ToCharArray()).Length > 1)
                            {
                                if (zRowName == zNodeName.Split("/".ToCharArray())[0])
                                    zNodeName = zNodeName.Replace(zRowName + "/", "");
                            }

                            //  Add the field to the current row
                            if (bFirst & !string.IsNullOrEmpty(zKey))
                            {
                                //Do Nothing...
                            }
                            else if (lGroupCols == lField & lGroupCols > 0)
                            {
                                oRow = oRow.appendNode(zGroupNode);
                                oRow.appendXPathNode(zNodeName, zNodeValue, false);
                            }
                            else
                            {
                                //if this is a value node rather than an attribute, 
                                //and if it is a match to the node name...
                                if (CheckRowName &&
                                    (zNodeName == zRowName) &&
                                    !zNodeName.Contains("@")
                                    )
                                {
                                    //Just set the node value...
                                    oRow.setValue(zNodeValue);
                                }
                                else
                                {
                                    if (zNodeName == "xml-data" || zNodeName.EndsWith("_xml"))
                                        oRow.appendNode(new XMLHelper("<" + zNodeName + ">" + zNodeValue + "</" + zNodeName + ">"));
                                    else
                                        oRow.appendXPathNode(zNodeName, zNodeValue, false);
                                }
                            }

                        }
                        bFirst = false;
                        lField = lField + 1;
                    }

                    oRow = oTempNode;

                    //If you are dom merging, append to the inrowgroup...
                    //if (oInRowGroup != null)
                    //    oInRowGroup.appendNode(oRow);

                    //Always append to the outrowgroup...
                    oOutRowGroup.appendNode(oRow);

                    zPrevRowKey = zCurrRowKey;

                }

                iRow++;

            }

            //  If total is to be shown move to the end of the recordset
            if (bShowTotal)
            {

                //Need to read the remaining rows in the recordset to count rows...
                bool additionalRowsCounted = false;
                while (oRS.Read() && iRow < 2000)
                {
                    additionalRowsCounted = true;
                    iRow++;
                }

                //if ((iStartRow == 0) && (additionalRowsCounted))
                //    iRow++;

                //if (iStartRow > 0)
                //    iRow--;
                if (additionalRowsCounted)
                    iRow++;

                //Append the rowcount to the returned results...
                oResponse.appendAttribute("total_rows", Convert.ToString(iRow));
                //oResponse.appendNode("total_rows", Convert.ToString(iRow));

            }

            //Always close the recordset...
            oRS.Close();

            //Return the response (the input dom may also have been appended to inline)...
            return oResponse;

        }
        
        public XMLHelper getTables()
        {
            XMLHelper oResponse = null;
            try
            {
                DataTable dt = getConnection().GetSchema("Tables");
                oResponse = getDOM(dt);
            }
            catch (Exception ex)
            {
            }
            return oResponse;
        }
        public XMLHelper getColumns(String zTable)
        {
            XMLHelper oResponse = null;
            try
            {

                DbCommand cmd = getConnection().CreateCommand();
                cmd.CommandText = "select * from " + zTable;
                IDataReader rs = cmd.ExecuteReader();
                oResponse = getDOM(rs.GetSchemaTable());
            }
            catch (Exception ex)
            {
            }
            return oResponse;
        }
        public XMLHelper getDOM(DataTable dt)
        {
            XMLHelper oResponse = new XMLHelper("response");
            XMLHelper oRows = oResponse.appendNode("rows");
            foreach (DataRow dr in dt.Rows)
            {
                XMLHelper oRow = oRows.appendNode("row");
                foreach (DataColumn dc in dt.Columns)
                {
                    oRow.appendNode(dc.ColumnName.ToLower(), dr[dc].ToString());
                }
            }
            return oResponse;
        }
        #endregion

        #region Overrides for Get XML
        
        public enum RefCurPosition
        {
            First,
            Last,
            None
        }

        /// <summary>
        /// Gets an XML String of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <returns>An XML string representation of the recordset in the format response/rows/row</returns>
        public String getXML(DBParams oCommand)
        {
            return getDOM(oCommand, true).getXML();
        }

        public String getXML(DBParams oCommand, Boolean withRefCur)
        {
            return getDOM(oCommand, withRefCur).getXML();
        }

        /// <summary>
        /// Gets an XML String of a recordset
        /// </summary>
        /// <param name="oCommand">Parameters to pass to the stored procedure</param>
        /// <param name="iStartRow">The start row number to retrieve</param>
        /// <param name="iMaxRows">The maximum number of rows</param>
        /// <param name="bShowTotal">Determines whether the total Rowcount is shown.</param>
        /// <param name="zRowsetName">The Rowset node name.</param>
        /// <param name="zRowName">The Row node name</param>
        /// <param name="bShowMetadata">Determines whether to create meta data info</param>
        /// <returns>An XML string representation of the recordset</returns>
        public String getXML(DBParams oCommand, int iStartRow, int iMaxRows, Boolean bShowTotal, String zRowsetName, String zRowName, Boolean bShowMetadata)
        {
            return getXML(oCommand, iStartRow, iMaxRows, bShowTotal, zRowsetName, zRowName, bShowMetadata, true);
        }
        public XMLHelper getXMLHelper(DBParams oCommand, int iStartRow, int iMaxRows, Boolean bShowTotal, String zRowsetName, String zRowName, Boolean bShowMetadata)
        {
            return getXMLHelper(oCommand, iStartRow, iMaxRows, bShowTotal, zRowsetName, zRowName, bShowMetadata, true);
        }

        public String getXML(DBParams oCommand, int iStartRow, int iMaxRows, Boolean bShowTotal, String zRowsetName, String zRowName, Boolean bShowMetadata, Boolean bCloseConnection)
        {
            return getXML(oCommand, iStartRow, iMaxRows, bShowTotal, RefCurPosition.First, zRowsetName, zRowName, bShowMetadata, bCloseConnection, null, null, null, null, false).getXML();
        }
        public XMLHelper getXMLHelper(DBParams oCommand, int iStartRow, int iMaxRows, Boolean bShowTotal, String zRowsetName, String zRowName, Boolean bShowMetadata, Boolean bCloseConnection)
        {
            return getXML(oCommand, iStartRow, iMaxRows, bShowTotal, RefCurPosition.First, zRowsetName, zRowName, bShowMetadata, bCloseConnection, null, null, null, null, false);
        }

        public XMLHelper getXML(DBParams oCommand, int iStartRow, int iMaxRows, Boolean bShowTotal, DBHelper.RefCurPosition refCurPosition, String zRowsetName, String zRowName, Boolean bShowMetadata, Boolean bCloseConnection, XMLHelper oInputDOM, String zNode, String zKey, String zGrouping, Boolean CheckRowName)
        {
            return getDOM(oCommand, iStartRow, iMaxRows, bShowTotal, refCurPosition, zRowsetName, zRowName, bShowMetadata, bCloseConnection, oInputDOM, zNode, zKey, zGrouping, CheckRowName);
        }

        /// <summary>
        /// High performance version of append to dom.
        /// Will always add columns as attributes.
        /// </summary>
        public String getXML(DBParams oParams, String zRowsetName, String zRowName, Boolean bCloseConnection)
        {
            return getXML(oParams, zRowsetName, zRowName, bCloseConnection, null, null, null);
        }

        public String getXML(DBParams oParams, String zRowsetName, String zRowName, Boolean bCloseConnection, Int32? StartRow, Int32? MaxRows, Boolean? ShowTotal)
        {
            return getXMLStringBuilder(oParams, zRowsetName, zRowName, bCloseConnection, StartRow, MaxRows, ShowTotal).ToString();
        }

        public XMLHelper getXMLHelper(DBParams oParams, String zRowsetName, String zRowName, Boolean bCloseConnection, Int32? StartRow, Int32? MaxRows, Boolean? ShowTotal)
        {
            return new XMLHelper(getXMLStringBuilder(oParams, zRowsetName, zRowName, bCloseConnection, StartRow, MaxRows, ShowTotal));
        }

        /// <summary>
        /// High performance version of append to dom.
        /// Will always add columns as attributes.
        /// </summary>
        private StringBuilder getXMLStringBuilder(DBParams oParams, String zRowsetName, String zRowName, Boolean bCloseConnection, Int32? StartRow, Int32? MaxRows, Boolean? ShowTotal)
        {

            //Execute the procedure...
            //System.Data.IDbCommand oCommand = oParams.AsIDbCommand();
            DbCommand oCommand = oParams.Command;
            IDataReader oRS = null;
            if (bCloseConnection)
            {
                oRS = oCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            else
            {
                oRS = oCommand.ExecuteReader();
            }
            
            //Get the number of columns...
            Int32 FieldCount = oRS.FieldCount;

            //Get the column names (in advance of the loop, so it doesn't 
            //need to read the rowset every time through...
            Dictionary<Int32, String> oColumnNames = new Dictionary<Int32, String>();
            String ColName = null;
            for (int i = 0; i < FieldCount; i++)
            {
                ColName = oRS.GetName(i).ToLower();
                if (ColName.StartsWith("@"))
                {
                    ColName = ColName.Substring(1);
                }
                oColumnNames.Add(i, ColName);
            }

            StringWriter sw = new StringWriter();
            XmlTextWriter xtw = new XmlTextWriter(sw);

            //Append a placeholder for the rows...
            xtw.WriteStartElement("response");

            if (zRowsetName != null)
                xtw.WriteStartElement(zRowsetName);

            //Paging setup...
            if (StartRow == null) StartRow = 0;
            if (MaxRows == null) MaxRows = 0;
            if (MaxRows == 0) MaxRows = int.MaxValue;
            
            //Loop through every row in the rs, adding rows and cols appropriately...
            Int32 rowCount = 0;
            Boolean WriteRow = true;
            while (oRS.Read())
            {

                //If you are at the start row...
                if (rowCount >= StartRow && WriteRow)
                {

                    //Add a new row...
                    xtw.WriteStartElement(zRowName);
                    
                    for (int i = 0; i < FieldCount; i++)
                    {
                        //  Add the field to the current row
                        xtw.WriteAttributeString(oColumnNames[i].ToString(), oRS[i].ToString());
                    }

                    xtw.WriteEndElement();

                    //If you have reached the max number of rows you want to paint, then stop painting...
                    if ((rowCount - StartRow) >= (MaxRows-1))
                        WriteRow = false;

                }

                rowCount++;

            }

            if (zRowsetName != null)
            {
                xtw.WriteElementString("total_rows", rowCount.ToString());
                xtw.WriteEndElement();
            }

            xtw.WriteElementString("total_rows", rowCount.ToString());
            xtw.WriteEndElement();

            //Close the rs...
            oRS.Close();
            
            //Return the built dom...
            return sw.GetStringBuilder();

        }

        public StringBuilder getCSV(DBParams oParams, String zRowsetName, String zRowName, 
            Boolean bCloseConnection, Int32? StartRow, Int32? MaxRows, Boolean? ShowTotal,
            out long rowCount)
        {

            //Execute the procedure...
            //System.Data.IDbCommand oCommand = oParams.AsIDbCommand();
            DbCommand oCommand = oParams.Command;
            DbDataReader oRS = null;
            if (bCloseConnection)
            {
                oRS = oCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            else
            {
                oRS = oCommand.ExecuteReader();
            }

            //Get the number of columns...
            Int32 FieldCount = oRS.FieldCount;

            StringWriter sw = new StringWriter();

            //Get the column names (in advance of the loop, so it doesn't 
            //need to read the rowset every time through...
            Dictionary<Int32, String> oColumnNames = new Dictionary<Int32, String>();
            String ColName = null;
            for (int i = 0; i < FieldCount; i++)
            {
                ColName = oRS.GetName(i).ToLower();
                if (ColName.StartsWith("@"))
                {
                    ColName = ColName.Substring(1);
                }
                oColumnNames.Add(i, ColName);

                sw.Write(ColName);
                if (i < (FieldCount - 1))
                    sw.Write(",");

            }

            sw.Write(Environment.NewLine);


            //Paging setup...
            if (StartRow == null) StartRow = 0;
            if (MaxRows == null) MaxRows = 0;
            if (MaxRows == 0) MaxRows = int.MaxValue;

            Type type;

            //Loop through every row in the rs, adding rows and cols appropriately...
            rowCount = 0;
            Boolean WriteRow = true;
            while (oRS.Read())
            {

                //If you are at the start row...
                if (rowCount >= StartRow && WriteRow)
                {

                    for (int i = 0; i < FieldCount; i++)
                    {
                        
                        type = oRS[i].GetType();
                        if ((type == typeof(String)))
                            sw.Write("\"");
                        else if (oColumnNames[i] == "ean_code")  //always output ean codes as text to avoid conversion by excel to exponents
                            sw.Write("=\"");

                        sw.Write(oRS[i].ToString());

                        if (type == typeof(String))
                            sw.Write("\"");
                        else if (oColumnNames[i] == "ean_code")  //always output ean codes as text to avoid conversion by excel to exponents
                            sw.Write("\"");

                        if (i < (FieldCount-1))
                            sw.Write(",");

                    }

                    sw.Write(Environment.NewLine);

                    //If you have reached the max number of rows you want to paint, then stop painting...
                    if ((rowCount - StartRow) >= (MaxRows - 1))
                        WriteRow = false;

                }

                rowCount++;

            }

            //Close the rs...
            oRS.Close();

            //Return the built csv...
            return sw.GetStringBuilder();

        }


        #endregion

    }
}