/* Copyright GBASE. 2009  All rights reserved.                 */
/*                                                                   */
/*This sample program is owned by International Business Machines    */
/*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
/*and licensed, not sold.                                            */
/*                                                                   */
/*You may copy, modify, and distribute this sample program in any    */
/*form without payment to IBM,  for any purpose including developing,*/
/*using, marketing or distributing programs that include or are      */
/*derivative works of the sample program. Licenses to IBM patents    */
/*are expressly excluded from this license.                          */
/*The sample program is provided to you on an "AS IS" basis, without */
/*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
/*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
/*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
/*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
/*limitation of implied warranties, so the above limitations or      */
/*exclusions may not apply to you.  IBM shall not be liable for any  */
/*damages you suffer as a result of using, modifying or distributing */
/*the sample program or its derivatives.                             */
/*                                                                   */
/*Each copy of any portion of this sample program or any derivative  */
/*work,  must include the above copyright notice and disclaimer of   */
/*warranty.                                                          */
/*                                                                   */

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using GBS.Data.GBasedbt;
using System.Xml;

namespace StoredProc_INOUT
{
    class Program
    {
        #region data members

        private IfxConnection               _connection;
        private StreamWriter                _outStream;
        private IfxCommand                  _command;

        #endregion

        #region internal functions 

        /// <summary>
        /// Constructor
        /// </summary>
        public Program()
        {
            _connection = null;
            _outStream = new StreamWriter(Console.OpenStandardOutput());
            _outStream.AutoFlush = true;
        }

        /// <summary>
        /// Property to set the output stream for the application
        /// </summary>
        public StreamWriter OutStream
        {
            get { return _outStream; }
            set { _outStream = value; }
        }

        /// <summary>
        /// Establishes connection with the server
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns>bool</returns>
        public bool EstablishConnection(String connectionString)
        {
            try
            {
                _connection = new IfxConnection(connectionString);
                _connection.Open();
                _command = new IfxCommand();
                _command.Connection = _connection;
            }
            catch (Exception excep)
            {
                WriteLine(String.Format("Connection Error: {0}", excep.Message));
                return false;
            }

            return true;
        }

        /// <summary>
        /// Clean up
        /// </summary>
        public void CleanUp()
        {
            if (_connection != null)
                _connection.Close();

            _outStream.Close();
        }

        /// <summary>
        /// Write status messages to file/console
        /// </summary>
        /// <param name="logMessage"></param>
        public void WriteLine(string logMessage)
        {
            _outStream.WriteLine(logMessage);
        }

        #endregion

        #region Stored Procedure Create/Delete/Execute

        /// <summary>
        /// Delete existing procedure with the same name if present
        /// </summary>
        private void DeleteProcedures()
        {
            try
            {
                _command.CommandText = "drop procedure multiplyvalues";
                _command.ExecuteNonQuery();
               
            }
            catch(Exception excep)
            {
                WriteLine(String.Format("The stored procedures do not exist.: {0}", excep.Message));
            }
        }

        /// <summary>
        /// Create a procedure with INOUT and OUT params
        /// </summary>
        /// <returns>bool</returns>
        public bool CreateStoredProcedures()
        {
            try
            {
                _command.CommandText = "create procedure multiplyvalues(INOUT value1 int, INOUT value2 int, OUT outbigintparam bigint) \n" +
                " returning int; \n" +
                " define rvar1 int; \n" +
                " let rvar1 = value1 * value2; \n" +
                "  let value1 = 100; \n" +
                "  let value2 = 200; \n" +
                "  let outbigintparam = 1234567890; \n" +
                "  return rvar1; \n" +                          
                "end procedure; ";

                _command.ExecuteNonQuery();
              
            }
            catch(Exception excep)
            {
                WriteLine(String.Format("The stored procedures cannot be created: {0}", excep.Message));
                return false;
            }
            return true;
        }

        /// <summary>
        /// Execute the procedure.
        /// </summary>
        /// <returns>bool</returns>
        public bool ExecuteStoredProcedures()
        {
            IfxParameter param1, param2, param3, param4;

            _command.CommandText = "{? = call multiplyvalues(?,?,?)};";  
            param4 = new IfxParameter("retValparam", IfxType.Integer);
            param1 = new IfxParameter("intparam1", IfxType.Integer);
            param2 = new IfxParameter("intparam2", IfxType.Integer);
            param3 = new IfxParameter("bigintparam", IfxType.BigInt);

            IfxParameter paramX = _command.Parameters.Add("@retValparam", IfxType.Integer);
            paramX.Direction = ParameterDirection.ReturnValue;
            paramX = _command.Parameters.Add("@intparam1", IfxType.Integer);
            paramX.Direction = ParameterDirection.InputOutput;
            paramX.Value = 5;
            paramX = _command.Parameters.Add("@intparam2", IfxType.Integer);
            paramX.Direction = ParameterDirection.InputOutput;
            paramX.Value = 6;
            paramX = _command.Parameters.Add("@bigintparam", IfxType.BigInt);
            paramX.Direction = ParameterDirection.Output;

            WriteLine("***** Input values before executing the procedure *****");
            WriteLine(String.Format("INOUT Param Value1: {0}", _command.Parameters["@intparam1"].Value));
            WriteLine(String.Format("INOUT Param Value2: {0}", _command.Parameters["@intparam2"].Value));

            try
            {
                _command.ExecuteNonQuery();
            }
            catch (Exception excep)
            {
                WriteLine(String.Format("Error executing procedure: {0}", excep.Message));
                return false;
            }

            WriteLine("***** Results after execution of the procedure *****");
            WriteLine(String.Format("Return value: {0}", _command.Parameters["@retValparam"].Value));
            WriteLine(String.Format("INOUT Param Value1: {0}", _command.Parameters["@intparam1"].Value));
            WriteLine(String.Format("INOUT Param Value2: {0}", _command.Parameters["@intparam2"].Value));
            WriteLine(String.Format("OUT bigint param: {0}", _command.Parameters["@bigintparam"].Value));
            return true;
        }

        #endregion


        /// <summary>
        /// The demo app needs to be invoked as  EXE -conn "connection string" -log "log filename"
        /// Eg: StoredProc.exe //This will pick the connection string from ConnInfo.xml
        /// OR
        /// StoredProc.exe -conn "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;"
        /// OR
        /// StoredProc.exe -conn "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;" -log log.txt
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            bool bResult = false;
            String connString = String.Empty;
            String logFile = String.Empty;

            Program prg = new Program();
            
            if (args.Length == 0)
            {
                try
                {
                    XmlReaderSettings settings = new XmlReaderSettings();
                    settings.ConformanceLevel = ConformanceLevel.Auto;
                    using (XmlReader reader = XmlReader.Create("ConnInfo.xml", settings))
                    {
                        try
                        {
                            connString = reader.ReadElementString("ConnectionString");
                            logFile = reader.ReadElementString("LogFile");
                        }
                        catch (Exception)
                        {
                            //Ignore this error
                        }
                    }
                }
                catch (System.Exception excep)
                {
                    Console.WriteLine(excep.Message);
                    Console.WriteLine("Problem reading ConnInfo.xml file, either use");
                    Console.WriteLine("StoredProc.exe //This will pick the connection string from ConnInfo.xml");
                    Console.WriteLine("StoredProc.exe -conn \"DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;\" OR");
                    Console.WriteLine("StoredProc.exe -conn \"DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;\" -log log.txt");
                }
            }

            if (args.Length > 1)
               connString = args[1];

            if (args.Length == 4)
              logFile = args[3];
            
            if(logFile.Length != 0)
              prg.OutStream = new StreamWriter(logFile, false);

            if(connString.Length != 0)
                bResult = prg.EstablishConnection(connString);
            else
                prg.WriteLine("*****    Connection Failed!!!     *****");

            if (bResult == true)
            {
                prg.WriteLine("*****    Connection successful     *****");
                bResult = prg.CreateStoredProcedures();
            }

            if (bResult == true)
            {
                prg.WriteLine("*****    Stored Procedure creation successful     *****");
                bResult = prg.ExecuteStoredProcedures();
            }

            prg.DeleteProcedures();

            if (bResult == false)
            {
                prg.WriteLine("There is an ERROR in the execution!");
            }
            else
            {
                prg.WriteLine("*****    Demo app for Stored Procedure with INOUT params has successfully executed!  *****");
            }

            if (logFile.Length != 0)
            {
                Console.WriteLine("Execution Complete!");
                Console.WriteLine("Refer to results in the log file: {0}", logFile);
            }

            prg.CleanUp();

        }
    }
}
