using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Data;
using VBNET = Microsoft.VisualBasic;
using gmRTL.Core;
using Microsoft.VisualBasic.CompilerServices;
using System.Linq;
using System.Collections.Generic;

namespace FMStocks_DB
{
   public class DBHelper
   {
      private const string m_modName = "FMStocks_DB.DBHelper";
      string m_DBServer = "";
      string m_ConnectionString = "";
      private void IObjectConstruct_Construct(object pCtorObj)
      {
         // eval construction string and find out what the admin wants us to do
         string[] arrParams = Array.Empty<string>();
         int x = 0;
         string strHints = "";

         try
         {
            gmRTL.Core.GlobalException.Clear();
            strHints = ((object)((dynamic)pCtorObj).ConstructString).ToStr();
            arrParams = strHints.Split(",");
            for(x = arrParams.GetLowerBound(0); x <= arrParams.GetUpperBound(0); x += 1)
            {
               switch(Helpers.GetKey(arrParams[x]).UCase())
               {
               case "CONNECTIONSTRING":
                  m_ConnectionString = Helpers.GetValue(arrParams[x]);
                  break;
               case "SERVER":
                  m_DBServer = Helpers.GetValue(arrParams[x]);
                  break;
               }
            }

            return;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            Helpers.RaiseError(m_modName + "(IObjectConstruct Interface)","Construct(" + strHints + ")","",0,true);
         }
      }
      public string GetConnectionString()
      {
         string GetConnectionString = "";

         try
         {
            gmRTL.Core.GlobalException.Clear();

            if (m_ConnectionString != "")
            {
               GetConnectionString = m_ConnectionString;
            }
            else
            {
               // assume that we want to use a SQL Server connection through OLDEB
               if (m_DBServer != "")
               {
                  GetConnectionString = "MultipleActiveResultSets=True;Data Source=" + m_DBServer + ";User Id=stocks_login;Password=password;Connect Timeout=3;network library=dbmssocn;";
               }
               else
               {
                  // GetConnectionString = "MultipleActiveResultSets=True;Data Source=localhost;User Id=stocks_login;Password=password;Connect Timeout=3;network library=dbmssocn;"
                  GetConnectionString = "MultipleActiveResultSets=True;Data Source=(local);User Id=stocks_login;Password=password;Connect Timeout=3;network library=dbmssocn;";
               }
            }

            return GetConnectionString;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            Helpers.RaiseError(m_modName,"GetConnectionString()","",0,true);
         }
         return GetConnectionString;
      }
      public gmRTL.DataLib.SqlClient.Recordset RunSPReturnRS(string strSP,object params_migname,string strConn)
      {
         gmRTL.DataLib.SqlClient.Recordset RunSPReturnRS = default;

         //  Create the ADO objects
         gmRTL.DataLib.SqlClient.Recordset rs = null;
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            rs = new gmRTL.DataLib.SqlClient.Recordset();
            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects  & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSP;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            collectParams(cmd,params_migname);

            //  Execute the query for readonly
            rs.CursorLocation = gmRTL.DataLib.CursorLocationEnum.adUseClient;
            rs.Open(cmd, null, gmRTL.DataLib.CursorTypeEnum.adOpenStatic, gmRTL.DataLib.LockTypeEnum.adLockReadOnly);

            //  Disconnect the recordset
            cmd.Connection = null;
            cmd = null;
            rs.ActiveConnection = null;

            //  Return the resultant recordset
            RunSPReturnRS = rs;
            return RunSPReturnRS;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            rs = null;
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSPReturnRS(" + strSP + ", ...)","",0,false);
         }
         return RunSPReturnRS;
      }
      //  RunSQLReturnRS differs from RunSPReturnRS only on the line that
      //  sets the cmd.CommandType = adCmdText vs. adCmdStoredProc
      public gmRTL.DataLib.SqlClient.Recordset RunSQLReturnRS(string strSQL,object params_migname,string strConn)
      {
         gmRTL.DataLib.SqlClient.Recordset RunSQLReturnRS = default;

         //  Set up Command and Connection objects
         gmRTL.DataLib.SqlClient.Recordset rs = null;
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            rs = new gmRTL.DataLib.SqlClient.Recordset();
            cmd = new System.Data.SqlClient.SqlCommand();

            // Run the procedure
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSQL;
            cmd.CommandType = System.Data.CommandType.Text;

            collectParams(cmd,params_migname);

            rs.CursorLocation = gmRTL.DataLib.CursorLocationEnum.adUseClient;
            rs.Open(cmd, null, gmRTL.DataLib.CursorTypeEnum.adOpenStatic, gmRTL.DataLib.LockTypeEnum.adLockReadOnly);

            //  Disconnect the recordsets and cleanup
            cmd.Connection = null;
            cmd = null;
            rs.ActiveConnection = null;

            RunSQLReturnRS = rs;
            return RunSQLReturnRS;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            rs = null;
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSQLReturnRS(" + strSQL.Left(10) + ", ...)","",0,false);
         }
         return RunSQLReturnRS;
      }
      public gmRTL.DataLib.SqlClient.Recordset RunSPReturnRS_RW(string strSP,object params_migname,string strConn)
      {
         gmRTL.DataLib.SqlClient.Recordset RunSPReturnRS_RW = default;

         //  Create the ADO objects
         gmRTL.DataLib.SqlClient.Recordset rs = null;
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            rs = new gmRTL.DataLib.SqlClient.Recordset();
            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSP;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            collectParams(cmd,params_migname);
            rs.CursorLocation = gmRTL.DataLib.CursorLocationEnum.adUseClient;

            //  Execute the query as an updatable recordset and stay connected
            rs.Open(cmd, null, gmRTL.DataLib.CursorTypeEnum.adOpenDynamic, gmRTL.DataLib.LockTypeEnum.adLockBatchOptimistic);
            cmd = null;

            //  Return and quit
            RunSPReturnRS_RW = rs;
            return RunSPReturnRS_RW;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            rs = null;
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSPReturnRS_RW(" + strSP + ", ...)","",0,false);
         }
         return RunSPReturnRS_RW;
      }
      public gmRTL.DataLib.SqlClient.Recordset RunSQLReturnRS_RW(string strSQL,object params_migname,string strConn)
      {
         gmRTL.DataLib.SqlClient.Recordset RunSQLReturnRS_RW = default;

         //  Create the ADO objects
         gmRTL.DataLib.SqlClient.Recordset rs = null;
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            rs = new gmRTL.DataLib.SqlClient.Recordset();
            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSQL;
            cmd.CommandType = System.Data.CommandType.Text;
            collectParams(cmd,params_migname);
            rs.CursorLocation = gmRTL.DataLib.CursorLocationEnum.adUseClient;

            //  Execute the query as an updatable recordset and stay connected
            rs.Open(cmd, null, gmRTL.DataLib.CursorTypeEnum.adOpenDynamic, gmRTL.DataLib.LockTypeEnum.adLockBatchOptimistic);
            cmd = null;

            //  Return and quit
            RunSQLReturnRS_RW = rs;
            return RunSQLReturnRS_RW;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            rs = null;
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSQLReturnRS_RW(" + strSQL + ", ...)","",0,false);
         }
         return RunSQLReturnRS_RW;
      }
      public object RunSP(string strSP,object params_migname,string strConn)
      {
         object RunSP = null;

         //  Create the ADO objects
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSP;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            collectParams(cmd,params_migname);

            //  Execute the query without returning a recordset
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();

            //  Disconnect the command and clean up
            cmd.Connection = null;
            cmd = null;

            return RunSP;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSP(" + strSP + ", ...)","",0,false);
         }
         return RunSP;
      }
      public object RunSQL(string strSQL,object params_migname,string strConn)
      {
         object RunSQL = null;

         //  Create the ADO objects
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSQL;
            cmd.CommandType = System.Data.CommandType.Text;
            collectParams(cmd,params_migname);

            //  Execute the query without returning a recordset
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();

            //  Cleanup
            cmd.Connection = null;
            cmd = null;

            return RunSQL;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSQL(" + strSQL.Left(10) + ", ...)","",0,false);
         }
         return RunSQL;
      }
      public int RunSPReturnInteger(string strSP,object params_migname,string strConn)
      {
         int RunSPReturnInteger = 0;

         //  Create the ADO objects
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSP;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            collectParams(cmd,params_migname);

            //  Assume the last parameter is outgoing
            cmd.Parameters.Add(gmRTL.DataLib.SqlClient.SqlCommandExtensions.CreateParameter(cmd,"@retval",System.Data.SqlDbType.Int,System.Data.ParameterDirection.Output,4,null));

            //  Execute without a resulting recordset and pull out the "return value" parameter
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            RunSPReturnInteger = cmd.Parameters["@retval"].Value.ToInt();

            //  Disconnect the command, and clean up
            cmd.Connection = null;
            cmd = null;

            return RunSPReturnInteger;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSPReturnInteger(" + strSP + ", ...)","",0,false);
         }
         return RunSPReturnInteger;
      }
      public int RunSQLReturnInteger(string strSQL,object params_migname,string strConn)
      {
         int RunSQLReturnInteger = 0;

         //  Create the ADO objects
         System.Data.SqlClient.SqlCommand cmd = null;
         try
         {
            gmRTL.Core.GlobalException.Clear();

            cmd = new System.Data.SqlClient.SqlCommand();

            //  Init the ADO objects & the stored proc parameters
            if (strConn == "")
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(GetConnectionString());
            }
            else
            {
               cmd.Connection = new System.Data.SqlClient.SqlConnection(strConn);
            }
            cmd.CommandText = strSQL;
            cmd.CommandType = System.Data.CommandType.Text;

            //  Assume the first parameter is outgoing
            cmd.Parameters.Add(gmRTL.DataLib.SqlClient.SqlCommandExtensions.CreateParameter(cmd,"@retval",System.Data.SqlDbType.Int,System.Data.ParameterDirection.Output,4,null));

            collectParams(cmd,params_migname);

            // 
            //  Execute without a resulting recordset and pull out the "return value" parameter
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            RunSQLReturnInteger = cmd.Parameters["@retval"].Value.ToInt();

            //  Disconnect the command, and clean up
            cmd.Connection = null;
            cmd = null;

            return RunSQLReturnInteger;

         }
         catch(System.Exception exc)
         {
            gmRTL.Core.GlobalException.Initialize(exc);
            cmd = null;
            Helpers.RaiseError(m_modName,"RunSQLReturnInteger(" + strSQL.Left(10) + ", ...)","",0,false);
         }
         return RunSQLReturnInteger;
      }
      private void collectParams(System.Data.SqlClient.SqlCommand cmd,object argparams)
      {
         object params_migname = null;
         object v = null;
         int i = 0;
         int l = 0;
         int u = 0;
         // if argparams is empty

         if (!(argparams is Array))
         {
            return;
         }

         params_migname = new object[((object[])argparams).GetUpperBound(0)+1][];
         Array.Copy((object[])argparams,(System.Array)params_migname, ((object[])argparams).GetUpperBound(0)+1);
         for(i = ((System.Array)(params_migname)).GetLowerBound(0); i <= ((System.Array)(params_migname)).GetUpperBound(0); i += 1)
         {
            l = ((System.Array)(((object[])(params_migname))[i])).GetLowerBound(0);
            u = ((System.Array)(((object[])(params_migname))[i])).GetUpperBound(0);
            //  Check for nulls.
            if (u - l == 3)
            {
               if (gmRTL.Core.Utils.VarType(((object[][])(params_migname))[i][3]) == gmRTL.Core.Enums.VariantType.String)
               {
                  v = (((object[][])(params_migname))[i][3].ToStr() == "" ? DBNull.Value : ((object[][])(params_migname))[i][3]);
               }
               else
               {
                  v = ((object[][])(params_migname))[i][3];
               }
               cmd.Parameters.Add(gmRTL.DataLib.SqlClient.SqlCommandExtensions.CreateParameter(cmd,((object[][])(params_migname))[i][0].ToStr(),((System.Data.SqlDbType)(((object[][])(params_migname))[i][1])),System.Data.ParameterDirection.Input,((object[][])(params_migname))[i][2].ToInt(),v));
            }
            else
            {
               Helpers.RaiseError(m_modName,"collectParams(...): incorrect # of parameters","",0,true);
            }
         }
      }
      public DBHelper() : base()
      {
         Class_Initialize();
      }
      private void Class_Initialize()
      {
         m_DBServer = Helpers.GetComputerName();
         //  to simplify the demo, I will assume the SQL server is accessible through the local machine name
      }
   }
}
