예제 #1
0
        public bool ExecuteQuery(string strSql)
        {
            bool flag = false;

            try
            {
                conn.BeginTrans();

                ADODB.Command cmd = new ADODB.Command();
                cmd.ActiveConnection = conn;
                cmd.CommandTimeout   = 90;
                cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
                cmd.CommandText      = strSql;

                object objRecordsAffected = Type.Missing;
                object objParams          = Type.Missing;
                cmd.Execute(out objRecordsAffected, ref objParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);

                conn.CommitTrans();

                flag = true;
            }
            catch (Exception ex)
            {
                flag = false;

                conn.RollbackTrans();

                //PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
            }

            return(flag);
        }
예제 #2
0
        /// <summary>
        /// Delete Row from access table
        /// </summary>
        /// <returns></returns>
        public bool QueryDeleteOrUpdate(string queryString)
        {
            if (con == null || con.State != 1)
            {
                this.OpenConnection();
            }
            if (con.State != 1)
            {
                return(false);
            }

            //Assign the connection to the command
            cmd = new CommandClass();
            cmd.ActiveConnection = con;
            cmd.CommandType      = CommandTypeEnum.adCmdText;

            //delete row
            cmd.CommandText = queryString;

            //Execute the command
            object nRecordsAffected = Type.Missing;
            object oParams          = Type.Missing;

            cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);

            cmd = null;
            return(true);
        }
예제 #3
0
        private void _InitSqlLst()
        {
            //
            if (queryLst != null)
            {
                return;
            }

            if (con == null)
            {
                throw new InvalidOperationException();
            }

            var    querylst = new List <string>();
            string s;
            var    cat = new ADOX.Catalog();
            var    cmd = new ADODB.Command();

            queryLst = new List <Query>();


            //アクションクエリ
            var rs = con.OpenSchema(ADODB.SchemaEnum.adSchemaProcedures);

            while (!rs.EOF)
            {
                s = rs.Fields["PROCEDURE_NAME"].Value.ToString();
                rs.MoveNext();
                if (!s.StartsWith("~"))
                {
                    querylst.Add(s);
                }
            }

            cat.ActiveConnection = con;
            foreach (var q in querylst)
            {
                //
                cmd = (ADODB.Command)cat.Procedures[q].Command;
                var sql = cmd.CommandText.ToUpper();
                //var name = cmd.Name;

                var name = q;

                if (!(sql.StartsWith("UPDATE") || sql.StartsWith("INSERT")))
                {
                    return;
                }

                var query = new Query(name, sql);
                queryLst.Add(query);

                //if (sql.StartsWith("UPDATE"))
                //    updateSqlLst.Add(sql);

                //if (sql.StartsWith("INSERT"))
                //    insertSqlLst.Add(sql);
            }
        }
예제 #4
0
        private void DisplayResults()
        {
            //SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
            ADODB.Connection conn = new ADODB.Connection();
            ADODB.Command    cmd  = new ADODB.Command();
            cmd.CommandText = "SELECT RCount = COUNT(*) " +
                              "FROM [common].[SQLDataValidation] " +
                              "WHERE UPPER(Severity) = 'HIGH' " +
                              "AND SchemaName = '" + CompanyCode + "'";
            cmd.CommandTimeout = 10000;

            if (conn.State == 0)
            {
                if (connPassword.Trim() == "")
                {
                    conn.Open();
                }
                else
                {
                    conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
                              (int)ADODB.ConnectModeEnum.adModeUnknown);
                }
            }
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

            DataTable dataTable = new DataTable();

            cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
            cmd.ActiveConnection = conn;
            ADODB.Recordset recordSet = null;
            object          objRecAff;

            try
            {
                recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
            }
            catch
            {
                throw;
            }

            for (int i = 0; i < recordSet.RecordCount; i++)
            {
                if (Convert.ToInt32(recordSet.Fields["RCount"].Value) == 0)
                {
                    // Posting Enabled
                    PostingEnabledDisabled(true, false);
                }
                else
                {
                    // Posting Disabled
                    PostingEnabledDisabled(false, false);
                }
            }

            File.Delete(fileString);

            Application.Exit();
        }
예제 #5
0
파일: CGD2060C.cs 프로젝트: dmgfhc/NGHB
        public static bool Gf_ExecSql(ADODB.Connection Conn, string sQuery)
        {
            bool returnValue = false;

            //Dim iCount As Integer
            object[,] OutParam = new object[3, 5];

            ADODB.Command adoCmd;

            try
            {
                //Db Connection Check
                if (GeneralCommon.M_CN1.State == 0)
                {
                    if (GeneralCommon.GF_DbConnect() == false)
                    {
                        return(returnValue);
                    }
                }

                Cursor.Current = Cursors.WaitCursor;

                //Ado Setting
                GeneralCommon.M_CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
                adoCmd = new ADODB.Command();

                Conn.BeginTrans();

                adoCmd.ActiveConnection = GeneralCommon.M_CN1;
                adoCmd.CommandText      = "UPDATE  GP_USTRESULT SET  UST_LOC= '',UST_REMARTS   = '' WHERE  PLATE_NO  = '05201206040102'";

                object null_object  = "";
                object null_object2 = "";
                adoCmd.Execute(out null_object, ref null_object2, -1);
                Conn.CommitTrans();
                adoCmd         = null;
                Cursor.Current = Cursors.Default;

                if (GeneralCommon.M_CN1.State != 0)
                {
                    GeneralCommon.M_CN1.Close();
                }
            }
            catch (Exception ex)
            {
                adoCmd = null;
                Conn.RollbackTrans();
                returnValue    = false;
                Cursor.Current = Cursors.Default;
                if (GeneralCommon.M_CN1.State != 0)
                {
                    GeneralCommon.M_CN1.Close();
                }
                GeneralCommon.Gp_MsgBoxDisplay((string)("Gf_ExecProcedure Error : " + ex.Message), "", "");
            }

            return(returnValue);
        }
예제 #6
0
        public int ExecuteSql(CommandTypeEnum eCmdType, SqlFrag frag)
        {
            ADODB.Command sqlCmd         = GenerateCommand(eCmdType, frag);
            object        obRecsAffected = 0;

            sqlCmd.Execute(out obRecsAffected);

            return((int)obRecsAffected);
        }
예제 #7
0
        public Recordset CreateRecordset(SqlFrag frag, CommandTypeEnum eCmdType = CommandTypeEnum.adCmdText,
                                         CursorLocationEnum eCursorLoc          = CursorLocationEnum.adUseClient, CursorTypeEnum eCursorType = CursorTypeEnum.adOpenDynamic,
                                         LockTypeEnum eLockType = LockTypeEnum.adLockOptimistic, ExecuteOptionEnum eExecOptions              = ExecuteOptionEnum.adOptionUnspecified)
        {
            ADODB.Command SqlCmd   = GenerateCommand(eCmdType, frag);
            Recordset     rRecords = new Recordset();

            rRecords.CursorLocation = eCursorLoc;

            rRecords.Open(SqlCmd, Type.Missing, eCursorType, eLockType, (int)eExecOptions);
            return(rRecords);
        }
예제 #8
0
        // Call the GISPKG_CCB_ESILOCATION.ValidateESILocation stored procedure to validate
        // if the ESI Location is being used by a Premise record on a different Service Point.
        private bool ValidateDuplicate(string esiLocation, int g3eFID)
        {
            bool returnValue = true;

            try
            {
                ADODB.Command cmd = new ADODB.Command();

                cmd.CommandText = "{call GISPKG_CCB_ESILOCATION.ValidateESILocation(?,?,?,?,?)}";
                cmd.CommandType = CommandTypeEnum.adCmdText;

                ADODB.Parameter param = cmd.CreateParameter("ESILocation", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 10, esiLocation);
                cmd.Parameters.Append(param);
                param = cmd.CreateParameter("G3EFID", DataTypeEnum.adBigInt, ParameterDirectionEnum.adParamInput, 10, g3eFID);
                cmd.Parameters.Append(param);
                param = cmd.CreateParameter("ExistingFID", DataTypeEnum.adBigInt, ParameterDirectionEnum.adParamOutput, 10);
                cmd.Parameters.Append(param);
                param = cmd.CreateParameter("JobID", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamOutput, 30);
                cmd.Parameters.Append(param);
                param = cmd.CreateParameter("Status", DataTypeEnum.adSingle, ParameterDirectionEnum.adParamOutput, 1);
                cmd.Parameters.Append(param);

                int recordsAffected = 0;

                Recordset spRS = m_DataContext.ExecuteCommand(cmd, out recordsAffected);

                if (!Convert.ToBoolean(cmd.Parameters["Status"].Value))
                {
                    string job = string.Empty;

                    if (cmd.Parameters["JobID"].Value.ToString().Length > 0)
                    {
                        job = " in Job " + cmd.Parameters["JobID"].Value;
                    }
                    if (m_InteractiveMode)
                    {
                        MessageBox.Show(ERROR_DUPLICATE_ESILOCATION + cmd.Parameters["ExistingFID"].Value + job, "G/Technology", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                    returnValue = false;
                }
            }
            catch (Exception ex)
            {
                if (m_InteractiveMode)
                {
                    MessageBox.Show("Error in fiESILocationUpdate:ValidateDuplicate - " + ex.Message, "G/Technology", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
                returnValue = false;
            }

            return(returnValue);
        }
예제 #9
0
 /// <summary>
 /// Method to execute sql query and return the result record set
 /// </summary>
 /// <param name="sqlString"></param>
 /// <returns></returns>
 private void ExecuteCommand(string sqlString)
 {
     try
     {
         int           outRecords = 0;
         ADODB.Command command    = new ADODB.Command();
         command.CommandText = sqlString;
         m_rsDesignArea      = m_gtdataContext.ExecuteCommand(command, out outRecords);
     }
     catch (Exception)
     {
         throw;
     }
 }
예제 #10
0
 /// <summary>
 /// Method to execute sql query and return the result record set
 /// </summary>
 /// <param name="sqlString"></param>
 /// <returns></returns>
 private Recordset GetRecordSet(string sqlString)
 {
     try
     {
         int           outRecords = 0;
         ADODB.Command command    = new ADODB.Command();
         command.CommandText = sqlString;
         ADODB.Recordset results = DataContext.ExecuteCommand(command, out outRecords);
         return(results);
     }
     catch (Exception)
     {
         throw;
     }
 }
예제 #11
0
 private int ExecuteCommand(string sql)
 {
     ADODB.Recordset results = null;
     try
     {
         int           outRecords = 0;
         ADODB.Command command    = new ADODB.Command();
         command.CommandText = sql;
         results             = m_gTDataContext.ExecuteCommand(command, out outRecords);
         return(outRecords);
     }
     catch
     {
         throw;
     }
 }
예제 #12
0
        private void GetExchequerVersion(out string ExVersion, ConnectionStringBuilder connObj)
        {
            ExVersion = "";
            //SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
            ADODB.Connection conn = new ADODB.Connection();
            ADODB.Command    cmd  = new ADODB.Command();

            if (conn.State == 0)
            {
                if (connObj.DecryptedPassword.Trim() == "")
                {
                    conn.Open();
                }
                else
                {
                    conn.Open(ExchequerCommonSQLConnection, "", connObj.DecryptedPassword.Trim(),
                              (int)ConnectModeEnum.adModeUnknown);
                }
            }
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

            try
            {
                cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
                cmd.CommandText      = "SELECT name, value FROM fn_listextendedproperty(default, default, default, default, default, default, default); ";
                cmd.ActiveConnection = conn;
                ADODB.Recordset recordSet = null;
                object          objRecAff;
                recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);

                if (recordSet.RecordCount > 0)
                {
                    ExVersion = "Exchequer " + recordSet.Fields["value"].Value;
                }

                if (conn.State == 1)
                {
                    conn.Close();
                }
            }
            catch
            {
                throw;
            }
        }
예제 #13
0
        /// <summary>
        /// Insert data to table and ignored column tb_ID; True = Success, False = Fail
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="table_name"></param>
        /// <returns></returns>
        public bool InsertDataToTable <T> (T t, string table_name, string ignore_column_name)
        {
            if (con == null || con.State != 1)
            {
                this.OpenConnection();
            }
            if (con.State != 1)
            {
                return(false);
            }

            //Get properties of T
            Type itemType   = typeof(T);
            var  properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);

            //get field and value
            string s1 = "", s2 = "";

            foreach (var p in properties)
            {
                if (!p.Name.ToLower().Equals(ignore_column_name.ToLower()))
                {
                    s1 += string.Format("[{0}],", p.Name);
                    s2 += string.Format("'{0}',", p.GetValue(t, null));
                }
            }
            s1 = s1.Substring(0, s1.Length - 1);
            s2 = s2.Substring(0, s2.Length - 1);

            //Assign the connection to the command
            cmd = new CommandClass();
            cmd.ActiveConnection = con;
            cmd.CommandType      = CommandTypeEnum.adCmdText;

            cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", table_name, s1, s2);

            //Execute the command
            object nRecordsAffected = Type.Missing;
            object oParams          = Type.Missing;

            cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);

            cmd = null;
            return(true);
        }
예제 #14
0
        public void Execute()
        {
            short     fNo = 0;
            Recordset connectivityComponentRs = null;
            string    feederId       = string.Empty;
            string    subStationCode = string.Empty;

            try
            {
                IGTComponent component = Components.GetComponent(11);
                connectivityComponentRs = component.Recordset;
                if (connectivityComponentRs.RecordCount > 0)
                {
                    connectivityComponentRs.MoveFirst();
                    fNo = Convert.ToInt16(connectivityComponentRs.Fields["G3E_FNO"].Value);
                }
                if (fNo == 16)                  // If affected feature is substation breaker then do nothing
                {
                    return;
                }
                feederId = Convert.ToString(connectivityComponentRs.Fields["FEEDER_1_ID"].Value);
                ADODB.Command command    = null;
                int           outRecords = 0;
                string        sqlString  = string.Format("select distinct ssta_c SSCODE from CONNECTIVITY_N where g3e_fno = 16 and feeder_1_id = '{0}' ", feederId);

                command             = new ADODB.Command();
                command.CommandText = sqlString;
                ADODB.Recordset results = DataContext.ExecuteCommand(command, out outRecords);
                if (results.RecordCount > 0)
                {
                    results.MoveFirst();
                    subStationCode = Convert.ToString(results.Fields["SSCODE"].Value);
                }

                connectivityComponentRs.Fields["SSTA_C"].Value = subStationCode;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error during Set Substation Code execution. " + ex.Message, "G/Technology");
            }
        }
예제 #15
0
        public DataTable GetData(string strSql, string strSqlData)
        {
            DataTable dt = new DataTable();

            try
            {
                conn.BeginTrans();

                ADODB.Command cmd = new ADODB.Command();
                cmd.ActiveConnection = conn;
                cmd.CommandTimeout   = 90;
                cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
                cmd.CommandText      = strSql;

                object objRecordsAffected = Type.Missing;
                object objParams          = Type.Missing;
                cmd.Execute(out objRecordsAffected, ref objParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);

                ADODB.Recordset rs = new ADODB.Recordset();
                rs.Open(strSqlData, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, -1);
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.Fill(dt, rs);

                adapter.Dispose();
                rs.Close();

                conn.CommitTrans();
            }
            catch (Exception ex)
            {
                conn.RollbackTrans();

                //PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
            }

            return(dt);
        }
예제 #16
0
 protected internal ADOCommand(ADOConnection connection, ADODB.Command command) : base(connection)
 {
     _command           = command;
     _useOrdinalBinding = true;
 }
예제 #17
0
        private void DisplayResults()
        {
            lvSummaryResults.Items.Clear();

            //SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
            ADODB.Connection conn = new ADODB.Connection();
            ADODB.Command    cmd  = new ADODB.Command();
            cmd.CommandText = "SELECT [IntegrityErrorCode] " +
                              ", [SummaryDescription] = CONVERT(VARCHAR(100), count(IntegrityErrorCode)) + ' ' + [IntegritySummaryDescription] " +
                              ", [Severity] " +
                              ", [SchemaName] " +
                              ", [TableName] " +
                              "FROM [common].[SQLDataValidation] " +
                              "GROUP BY SchemaName, Severity, IntegrityErrorCode, IntegritySummaryDescription, TableName " +
                              "ORDER BY SchemaName, Severity, IntegrityErrorCode";
            cmd.CommandTimeout = 10000;

            if (conn.State == 0)
            {
                if (connPassword.Trim() == "")
                {
                    conn.Open();
                }
                else
                {
                    conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
                              (int)ADODB.ConnectModeEnum.adModeUnknown);
                }
            }
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

            cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
            cmd.ActiveConnection = conn;
            ADODB.Recordset recordSet = null;
            object          objRecAff;

            try
            {
                recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
            }
            catch
            {
                throw;
            }

            for (int i = 0; i < recordSet.RecordCount; i++)
            {
                ListViewItem lvItem = new ListViewItem(recordSet.Fields[0].Value);

                lvItem.SubItems.Add(recordSet.Fields[1].Value);
                lvItem.SubItems.Add(recordSet.Fields[2].Value);
                lvItem.SubItems.Add(recordSet.Fields[3].Value);
                lvItem.SubItems.Add(recordSet.Fields[4].Value);

                lvSummaryResults.Items.Add(lvItem);

                recordSet.MoveNext();
            }

            if (conn.State == 1)
            {
                conn.Close();
            }

            // Set INI file based on results of each company
            foreach (ListViewItem lvItem in lvCompanies.CheckedItems)
            {
                tsCheckStatus.Text = "Updating INI file: " + lvItem.SubItems[1].Text.TrimEnd();
                Thread.Sleep(1000);
                Application.DoEvents();

                //SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
                conn = new ADODB.Connection();

                Command command = new ADODB.Command();
                command.CommandText = "SELECT RCount = COUNT(*) " +
                                      "FROM [common].[SQLDataValidation] " +
                                      "WHERE UPPER(Severity) = 'HIGH' " +
                                      "AND SchemaName = '" + lvItem.SubItems[1].Text.TrimEnd() + "'";

                command.CommandTimeout = 10000;

                if (conn.State == 0)
                {
                    if (connPassword.Trim() == "")
                    {
                        conn.Open();
                    }
                    else
                    {
                        conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
                                  (int)ADODB.ConnectModeEnum.adModeUnknown);
                    }
                }
                conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

                command.ActiveConnection = conn;
                ADODB.Recordset rs = null;
                Object          objAff;

                try
                {
                    rs = (ADODB.Recordset)command.Execute(out objAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
                }
                catch
                {
                    throw;
                }

                {
                    if (Convert.ToInt32(rs.Fields["RCount"].Value) == 0)
                    {
                        // Posting Enabled
                        tsCheckStatus.Text = "Posting Enabled: " + lvItem.SubItems[1].Text.TrimEnd();
                        Thread.Sleep(1000);
                        Application.DoEvents();
                        PostingEnabledDisabled(true, lvItem.SubItems[1].Text.TrimEnd());
                        lvItem.SubItems[4].Text = "Enabled";
                    }
                    else
                    {
                        // Posting Disabled
                        tsCheckStatus.Text = "Posting Disabled: " + lvItem.SubItems[1].Text.TrimEnd();
                        Thread.Sleep(1000);
                        Application.DoEvents();
                        PostingEnabledDisabled(false, lvItem.SubItems[1].Text.TrimEnd());
                        lvItem.SubItems[4].Text = "Disabled";
                    }
                }
                if (conn.State == 1)
                {
                    conn.Close();
                }
            }
            tsCheckStatus.Text = "Check Complete";
            Thread.Sleep(1000);
            Application.DoEvents();
        }
예제 #18
0
        private void lvSummaryResults_DoubleClick(object sender, EventArgs e)
        {
            lvDetailedResults.Items.Clear();

            //SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
            ADODB.Connection conn = new ADODB.Connection();
            ADODB.Command    cmd  = new ADODB.Command();
            cmd.CommandText = "SELECT [IntegrityErrorMessage] " +
                              ", [SchemaName] " +
                              ", [TableName] " +
                              ", [PositionID] " +
                              "FROM [common].[SQLDataValidation] " +
                              "WHERE IntegrityErrorCode = '" + lvSummaryResults.SelectedItems[0].Text + "' " +
                              "AND SchemaName = '" + lvSummaryResults.SelectedItems[0].SubItems[3].Text + "' " +
                              "ORDER BY PositionID";
            cmd.CommandTimeout = 10000;

            if (conn.State == 0)
            {
                if (connPassword.Trim() == "")
                {
                    conn.Open();
                }
                else
                {
                    conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
                              (int)ADODB.ConnectModeEnum.adModeUnknown);
                }
            }
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

            cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
            cmd.ActiveConnection = conn;
            ADODB.Recordset recordSet = null;
            object          objRecAff;

            try
            {
                recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
            }
            catch
            {
                throw;
            }

            for (int i = 0; i < recordSet.RecordCount; i++)
            {
                ListViewItem lvItem = new ListViewItem(recordSet.Fields[0].Value);

                lvItem.SubItems.Add(recordSet.Fields[1].Value);
                lvItem.SubItems.Add(recordSet.Fields[2].Value);
                lvItem.SubItems.Add(recordSet.Fields[3].Value.ToString());

                lvDetailedResults.Items.Add(lvItem);

                recordSet.MoveNext();
            }

            if (conn.State == 1)
            {
                conn.Close();
            }

            lvDetailedResults.Visible  = true;
            btnReturnToSummary.Visible = true;

            lvSummaryResults.Visible = false;
        }
예제 #19
0
        /// <summary>
        /// Inserts a record into the DEIS_TRANSACTION table.
        /// </summary>
        /// <returns>Boolean indicating status</returns>
        private bool InsertTransaction()
        {
            bool returnValue = false;

            try
            {
                ADODB.Command cmd = new ADODB.Command();

                cmd.CommandText = "insert into deis_transaction (tran_code,tran_date,bank_fln,wr_no,serv_area,yard,user_id,conn_sec,existing_bank,wiring_config_code," +
                                  "locn,ins_trf1_company_no,ins_trf1_tsn,ins_trf1_kva_size,ins_trf1_phs_code,ins_trf1_type_code,ins_trf1_mount_code,ins_trf1_kind_code," +
                                  "ins_trf1_pri_volt_code,ins_trf1_sec_volt_code,ins_trf2_company_no,ins_trf2_tsn,ins_trf2_kva_size,ins_trf2_phs_code,ins_trf2_type_code," +
                                  "ins_trf2_mount_code,ins_trf2_kind_code,ins_trf2_pri_volt_code,ins_trf2_sec_volt_code,ins_trf3_company_no,ins_trf3_tsn,ins_trf3_kva_size," +
                                  "ins_trf3_phs_code,ins_trf3_type_code,ins_trf3_mount_code,ins_trf3_kind_code,ins_trf3_pri_volt_code,ins_trf3_sec_volt_code,rem_trf1_company_no," +
                                  "rem_trf1_tsn,rem_trf1_kva_size,rem_trf1_phs_code,rem_trf1_type_code,rem_trf1_mount_code,rem_trf1_kind_code,rem_trf1_pri_volt_code," +
                                  "rem_trf1_sec_volt_code,rem_trf2_company_no,rem_trf2_tsn,rem_trf2_kva_size,rem_trf2_phs_code,rem_trf2_type_code,rem_trf2_mount_code," +
                                  "rem_trf2_kind_code,rem_trf2_pri_volt_code,rem_trf2_sec_volt_code,rem_trf3_company_no,rem_trf3_tsn,rem_trf3_kva_size,rem_trf3_phs_code," +
                                  "rem_trf3_type_code,rem_trf3_mount_code,rem_trf3_kind_code,rem_trf3_pri_volt_code,rem_trf3_sec_volt_code) " +
                                  "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) " +
                                  "returning tran_no into ?";
                cmd.CommandType = CommandTypeEnum.adCmdText;

                // Input parameters
                ADODB.Parameter param = cmd.CreateParameter("TranCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 1, TransactionDEIS.TransactionCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("TranDate", DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.TransactionDate.Replace('T', ' '));
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("BankFLN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.BankFLN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("WrNo", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.WrNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("ServArea", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.ServiceArea);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("Yard", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.Yard);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("UserID", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.UserID);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("SecConn", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.SecondaryConnection);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("ExistingBank", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.ExistingBank);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("WiringConfigCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.WiringConfigCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("Location", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.Location);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1CompanyNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1TSN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.InsTrf1KvaSize);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1PhaseCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1TypeCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1MountCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1KindCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1PriVoltCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf1SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf1SecVoltCode);
                cmd.Parameters.Append(param);


                param = cmd.CreateParameter("InsTrf2CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2CompanyNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2TSN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.InsTrf2KvaSize);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2PhaseCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2TypeCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2MountCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2KindCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2PriVoltCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf2SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf2SecVoltCode);
                cmd.Parameters.Append(param);


                param = cmd.CreateParameter("InsTrf3CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3CompanyNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3TSN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.InsTrf3KvaSize);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3PhaseCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3TypeCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3MountCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3KindCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3PriVoltCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("InsTrf3SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.InsTrf3SecVoltCode);
                cmd.Parameters.Append(param);


                param = cmd.CreateParameter("RemTrf1CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1CompanyNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1TSN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.RemTrf1KvaSize);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1PhaseCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1TypeCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1MountCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1KindCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1PriVoltCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf1SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf1SecVoltCode);
                cmd.Parameters.Append(param);


                param = cmd.CreateParameter("RemTrf2CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2CompanyNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2TSN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.RemTrf2KvaSize);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2PhaseCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2TypeCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2MountCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2KindCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2PriVoltCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf2SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf2SecVoltCode);
                cmd.Parameters.Append(param);


                param = cmd.CreateParameter("RemTrf3CompanyNumber", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3CompanyNumber);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3TSN", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3TSN);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3KvaSize", DataTypeEnum.adDouble, ParameterDirectionEnum.adParamInput, 10, TransactionDEIS.RemTrf3KvaSize);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3PhaseCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3PhaseCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3TypeCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3TypeCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3MountCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3MountCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3KindCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3KindCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3PriVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3PriVoltCode);
                cmd.Parameters.Append(param);

                param = cmd.CreateParameter("RemTrf3SecVoltCode", DataTypeEnum.adVarChar, ParameterDirectionEnum.adParamInput, 30, TransactionDEIS.RemTrf3SecVoltCode);
                cmd.Parameters.Append(param);

                // Output parameter
                param = cmd.CreateParameter("TranNo", DataTypeEnum.adBigInt, ParameterDirectionEnum.adParamOutput, 10);
                cmd.Parameters.Append(param);

                int recordsAffected = 0;

                Recordset spRS = m_Application.DataContext.ExecuteCommand(cmd, out recordsAffected);

                m_Application.DataContext.Execute("commit", out recordsAffected, (int)CommandTypeEnum.adCmdText);

                if (!Convert.IsDBNull(cmd.Parameters["TranNo"].Value))
                {
                    TransactionDEIS.TransactionNumber = Convert.ToInt32(cmd.Parameters["TranNo"].Value);
                }

                returnValue = true;
            }
            catch (Exception ex)
            {
                TransactionDEIS.TransactionMessage = "FAILED - Error inserting record into " + TransactionDEIS.TABLE_DEIS_TRANSACTION + ": " + ex.Message;
                returnValue = false;
            }

            return(returnValue);
        }
예제 #20
0
        public void RefreshServerList()
        {
            ADODB.Connection cn  = new ADODB.Connection();
            ADODB.Recordset  rs  = new ADODB.Recordset();
            ADODB.Command    cmd = new ADODB.Command();
            Excel.Workbook   wb  = Globals.ThisAddIn.Application.ActiveWorkbook;
            Excel.Worksheet  ws;
            string           ldapQry = Properties.Settings.Default.Rdg_LdapQry;

            try
            {
                cn.Open("Provider=ADsDSOObject;");
                ldapQry = ldapQry.Replace("[Rdg.LdapPath]", Properties.Settings.Default.Rdg_LdapPath);
                cmd.ActiveConnection = cn;
                object recs;
                rs = cn.Execute(ldapQry, out recs, 0);

                //bool sheetExists;
                //For Each ws In wb.Sheets
                //    If My.Settings.Rdg_SheetName = ws.Name Then
                //        sheetExists = True
                //        ws.Activate()
                //    End If
                //Next ws

                //If sheetExists = False Then
                //    ws = wb.ActiveSheet
                //    Dim answer As Integer
                //    string msg = "The sheet named '" & My.Settings.Rdg_SheetName & "' does not exist."
                //    msg = msg & vbCrLf & "Would you like to use the current sheet?"
                //    answer = MsgBox(msg, vbYesNo + vbQuestion, "Sheet Not Found")
                //  'MessageBox.Show(msg, "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                //    If answer = vbYes Then
                //        ws = wb.ActiveSheet
                //        My.Settings.Rdg_SheetName = wb.ActiveSheet.Name
                //    Else
                //        Exit Try
                //    End If
                //Else
                //ws = wb.Worksheets[Properties.Settings.Default.Rdg_SheetName];
                ws = wb.ActiveSheet;
                //End If

                ws.Activate();
                Ribbon.ClearSheetContents();
                for (int i = 0; i <= rs.Fields.Count - 1; i++)
                {
                    ws.Cells[1, i + 1].Value = rs.Fields[i].Name;
                }
                ws.Range[ws.Cells[1, 1], ws.Cells[1, rs.Fields.Count]].Font.Bold = true;
                ws.Range["A2"].CopyFromRecordset(rs);

                Ribbon.FormatTableFromRange();
                Excel.ListObject tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;
                Ribbon.UpdateZeroStringToNull(tbl);
                Ribbon.FormatDateColumns();

                //'create server type column from the first 2 characters of the server name
                //'If (My.Settings.Rdg_ServerGroup = "ServerType" Then
                //'    tbl.ListColumns.Add(3).Name = My.Settings.Rdg_ServerGroup
                //'    tbl.ListColumns(My.Settings.Rdg_ServerGroup).DataBodyRange.FormulaR1C1 = "=UPPER(IFERROR(IF(SEARCH(""-"", [@Name]) > 0, LEFT([@Name], 2), """"), ""(NONE)""))"
                //'    Globals.ThisAddIn.Application.Columns.AutoFit()
                //'End If

                Ribbon.ribbonref.InvalidateRibbon(); //'reset dropdown lists
                Ribbon.ActivateTab();
            }
            catch (Exception ex)
            {
                ErrorHandler.DisplayMessage(ex);
            }
        }
예제 #21
0
        private void _InitSqlLst()
        {
            //
            if (queryLst != null)
                return;

            if (con == null)
                throw new InvalidOperationException();

            var querylst = new List<string>();
            string s;
            var cat = new ADOX.Catalog();
            var cmd = new ADODB.Command();
            queryLst = new List<Query>();

            //アクションクエリ
            var rs = con.OpenSchema(ADODB.SchemaEnum.adSchemaProcedures);
            while (!rs.EOF)
            {
                s = rs.Fields["PROCEDURE_NAME"].Value.ToString();
                rs.MoveNext();
                if (!s.StartsWith("~"))
                    querylst.Add(s);
            }

            cat.ActiveConnection = con;
            foreach (var q in querylst)
            {
                //
                cmd = (ADODB.Command)cat.Procedures[q].Command;
                var sql = cmd.CommandText.ToUpper();
                //var name = cmd.Name;

                var name = q;

                if (!(sql.StartsWith("UPDATE") || sql.StartsWith("INSERT")))
                    return;

                var query = new Query(name, sql);
                queryLst.Add(query);

                //if (sql.StartsWith("UPDATE"))
                //    updateSqlLst.Add(sql);

                //if (sql.StartsWith("INSERT"))
                //    insertSqlLst.Add(sql);
            }
        }
예제 #22
0
        private void InitializeCustomerSettings(ADODB.Connection mycn)
        {
            var myInsert =
               @"INSERT INTO tblCustomerSettings
                            ( CustomerID,
                              BillingMethodID,
                              MobileCarrierID
                            )
                            SELECT
                            tblCustomers.CustomerID,
                            0 AS bmID,
                            0 AS mcID
                            FROM tblCustomers;";

               var myCmd = new ADODB.Command {ActiveConnection = mycn, CommandText = myInsert};
               myCmd.CommandType = CommandTypeEnum.adCmdText;

               object dummy = Type.Missing;
               ADODB.Recordset rs = myCmd.Execute(out dummy, ref dummy,0);
        }
예제 #23
0
        private void UpdateDBVersion(ADODB.Connection mycn)
        {
            var myUpdate = @"Update tblCaptions Set mdbVersion = '1.01.09'";
               var myCmd = new ADODB.Command { ActiveConnection = mycn, CommandText = myUpdate };
               myCmd.CommandType = CommandTypeEnum.adCmdText;

               object dummy = Type.Missing;
               ADODB.Recordset rs = myCmd.Execute(out dummy, ref dummy, 0);
        }
예제 #24
0
        /// <summary>
        ///
        /// </summary>
        public void RefreshServerList()
        {
            ADODB.Connection cn  = new ADODB.Connection();
            ADODB.Recordset  rs  = new ADODB.Recordset();
            ADODB.Command    cmd = new ADODB.Command();
            Excel.Workbook   wb  = Globals.ThisAddIn.Application.ActiveWorkbook;
            Excel.Worksheet  ws;
            Excel.ListObject tbl;
            int    iCols   = 0;
            string msg     = String.Empty;
            string ldapQry = Properties.Settings.Default.Rdg_LdapQry;

            try
            {
                cn.Open("Provider=ADsDSOObject;");
                ldapQry              = ldapQry.Replace("[Rdg.LdapPath]", Properties.Settings.Default.Rdg_LdapPath);
                cmd.CommandText      = ldapQry;
                cmd.ActiveConnection = cn;
                object objRecAff     = null;
                object objParameters = null;
                rs = cmd.Execute(out objRecAff, ref objParameters, (int)ADODB.CommandTypeEnum.adCmdText);

                bool sheetExists;
                //For Each ws In wb.Sheets
                //    If My.Settings.Rdg_SheetName = ws.Name Then
                //        sheetExists = True
                //        ws.Activate()
                //    End If
                //Next ws

                //If sheetExists = False Then
                //    ws = wb.ActiveSheet
                //    Dim answer As Integer
                //    msg = "The sheet named '" & My.Settings.Rdg_SheetName & "' does not exist."
                //    msg = msg & vbCrLf & "Would you like to use the current sheet?"
                //    answer = MsgBox(msg, vbYesNo + vbQuestion, "Sheet Not Found")
                //    'MessageBox.Show(msg, "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                //    If answer = vbYes Then
                //        ws = wb.ActiveSheet
                //        My.Settings.Rdg_SheetName = wb.ActiveSheet.Name
                //    Else
                //        Exit Try
                //    End If
                //Else
                //    ws = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(My.Settings.Rdg_SheetName)
                //End If

                //Globals.ThisAddIn.Application.Sheets(My.Settings.Rdg_SheetName).Activate
                //Ribbon.ClearSheetContents()
                //For iCols = 0 To rs.Fields.Count - 1
                //    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
                //Next
                //ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True
                //ws.Range("A2").CopyFromRecordset(rs)

                //Ribbon.CreateTableFromRange()
                //Ribbon.UpdateBlankCells()
                //Ribbon.FormatDateColumns()

                //'create server type column from the first 2 characters of the server name
                //'If My.Settings.Rdg_ServerGroup = "ServerType" Then
                //'    tbl.ListColumns.Add(3).Name = My.Settings.Rdg_ServerGroup
                //'    tbl.ListColumns(My.Settings.Rdg_ServerGroup).DataBodyRange.FormulaR1C1 = "=UPPER(IFERROR(IF(SEARCH(""-"", [@Name]) > 0, LEFT([@Name], 2), """"), ""(NONE)""))"
                //'    Globals.ThisAddIn.Application.Columns.AutoFit()
                //'End If

                //Ribbon.InvalidateRibbon() 'reset dropdown lists
                //Ribbon.ActivateTab()
            }
            catch (Exception ex)
            {
                ErrorHandler.DisplayMessage(ex);
            }
        }
예제 #25
0
        private void CheckCompany()
        {
            this.UseWaitCursor = true;

            clsCoreChecks CoreChecks = new clsCoreChecks();
            clsTransactionHeaderChecks         TransactionHeaderChecks         = new clsTransactionHeaderChecks();
            clsTransactionLineChecks           TransactionLineChecks           = new clsTransactionLineChecks();
            clsTransactionLineJobCostingChecks TransactionLineJobCostingChecks = new clsTransactionLineJobCostingChecks();
            clsTransactionLineStockCheck       TransactionLineStockChecks      = new clsTransactionLineStockCheck();
            clsHistoryChecks       HistoryChecks       = new clsHistoryChecks();
            clsHistoryCalculations HistoryCalculations = new clsHistoryCalculations();

            //SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
            ADODB.Connection conn = new ADODB.Connection();

            ADODB.Command cmd = new ADODB.Command();
            cmd.CommandText = "IF EXISTS (SELECT * FROM sys.tables WHERE name LIKE 'SQLDataValidation%') DROP TABLE common.SQLDataValidation; " +
                              "CREATE TABLE common.SQLDataValidation(IntegrityErrorNo varchar(50), " +
                              "IntegrityErrorCode varchar(50), " +
                              "Severity varchar(50), " +
                              "IntegrityErrorMessage varchar(max), " +
                              "IntegritySummaryDescription varchar(max), " +
                              "SchemaName varchar(10), " +
                              "TableName varchar(100), " +
                              "PositionId int);";

            cmd.CommandTimeout = 10000;

            if (conn.State == 0)
            {
                if (connPassword.Trim() == "")
                {
                    conn.Open();
                }
                else
                {
                    conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
                              (int)ConnectModeEnum.adModeUnknown);
                }
            }
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

            try
            {
                Object recAff;
                cmd.ActiveConnection = conn;
                cmd.CommandType      = ADODB.CommandTypeEnum.adCmdText;
                cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
                Console.WriteLine("Table Created Successfully...");

                if (conn.State == 1)
                {
                    conn.Close();
                }
            }
            catch
            {
                throw;
            }

            // Clear Old Results for Company
            cmd                = new ADODB.Command();
            cmd.CommandText    = "DELETE common.SQLDataValidation WHERE SchemaName = '" + CompanyCode + "'";
            cmd.CommandTimeout = 10000;

            if (conn.State == 0)
            {
                if (connPassword.Trim() == "")
                {
                    conn.Open();
                }
                else
                {
                    conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
                              (int)ConnectModeEnum.adModeUnknown);
                }
            }

            try
            {
                cmd.ActiveConnection = conn;

                Object recAff;
                cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
                cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
                Console.WriteLine("Old Results Deleted Successfully...");

                if (conn.State == 1)
                {
                    conn.Close();
                }
            }
            catch
            {
                throw;
            }

            tsProgressBar.Value   = 0;
            tsProgressBar.Maximum = 26;

            string Company = CompanyCode;

            IncrementToolbar();
            tsProgressBar.Refresh();

            lblStatus.Text = "Checking Currencies";

            // Run Core Checks
            // Check for inverted currencies if system either Euro or Multi-Currency
            if (MultiCurrency == true)
            {
                CoreChecks.CheckInvertedCurrencies(ExchequerCommonSQLConnection, Company, connPassword);
            }
            IncrementToolbar();

            lblStatus.Text = "Checking Transaction Headers";

            // Run Transaction Header Checks
            // Check for Transaction Headers with Zero Folio Number
            TransactionHeaderChecks.TransactionZeroFolioNum(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check if Transaction Header Trader Codes Exist
            TransactionHeaderChecks.TransactionTraderCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Control GL codes exist if they are set
            TransactionHeaderChecks.TransactionCheckControlGLCodesExistIfSet(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Control GL Codes are not Headers
            TransactionHeaderChecks.TransactionCheckControlGLNotHeaders(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Currency Codes exist
            TransactionHeaderChecks.TransactionCheckCurrencyCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            lblStatus.Text = "Checking Transaction Lines";

            // Run Transaction Line Checks
            // Check Transaction Header exists for non RUN transaction lines
            TransactionLineChecks.TransactionLineExistsWithNoTransactionHeader(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check if Transaction Lines exist with invalid GL Code
            TransactionLineChecks.TransactionLineExistWithInvalidGLCode(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check if Transaction Lines exist with Header GL Code
            TransactionLineChecks.TransactionLineExistWithHeaderGLCode(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check if Transction Lines exist with invalid Trader Code
            TransactionLineChecks.TransactionLineExistInvalidTraderCode(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check if Transaction Line Trader Code is same as Transaction Header Trader Code
            TransactionLineChecks.TransactionLineTraderCodeMatchesHeader(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Transaction Line Currency Codes Exist
            TransactionLineChecks.TransactionLineCurrencyCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Transaction Line VAT Code Exists
            TransactionLineChecks.TransactionLineVATCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Transaction Line Inclusive VAT Code Exists
            TransactionLineChecks.TransactionLineInclusiveVATCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            // Check Transaction Line Period Matches Header Period
            TransactionLineChecks.TransactionLinePeriodMatchesHeaderPeriod(ExchequerCommonSQLConnection, Company, connPassword);
            IncrementToolbar();

            //// Check History Currency Codes Exist
            //HistoryChecks.HistoryCheckCurrencyCodesExist(ExchequerCommonSQLConnection, Company);
            //IncrementToolbar();

            //// Check History General Ledger Codes Exist
            //HistoryChecks.HistoryCheckGLCodesExist(ExchequerCommonSQLConnection, Company);
            //IncrementToolbar();

            // Check if Cost Centres/Departments are used
            if (CCDept == true)
            {
                lblStatus.Text = "Checking Cost Centres and Departments";

                // Check Transaction Line Cost Centre Codes Exist
                TransactionLineChecks.TransactionLineCostCentresExist(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                // Check Transaction Line Department Codes Exist
                TransactionLineChecks.TransactionLineDepartmentsExist(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                //// Check History Cost Centre Codes Exist
                //HistoryChecks.HistoryCheckCostCentreCodesExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();

                //// Check History Department Codes Exist
                //HistoryChecks.HistoryCheckDepartmentCodesExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();
            }
            else
            {
                IncrementToolbar();
                IncrementToolbar();
            }

            // Check if Job Costing enabled
            if (JobCosting == true)
            {
                lblStatus.Text = "Checking Job Costing";

                // Check Employee Codes on Header exist
                TransactionHeaderChecks.TransactionCheckEmployeeCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                // Check Transaction Line Job Exists
                TransactionLineJobCostingChecks.TransactionLineJobExist(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                // Check Transaction Line Job check not Contract
                TransactionLineJobCostingChecks.TransactionLineCheckJobNotContract(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                // Check Transaction Line Analysis Code Exists
                TransactionLineJobCostingChecks.TransactionLineCheckAnalysisCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                //// Check Employee Currency History Codes Exist
                //HistoryChecks.HistoryCheckEmployeeCurrencyCodesExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();

                //// Check Employee History Codes Exist
                //HistoryChecks.HistoryCheckEmployeeCodesExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();

                //// Check Job Currency History Codes Exist
                //HistoryChecks.HistoryCheckJobCurrencyExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();

                //// Check Job History Codes Exist
                //HistoryChecks.HistoryCheckJobExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();

                //// Check Analysis Id History Codes Exist
                //HistoryChecks.HistoryCheckAnalysisIdExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();
            }
            else
            {
                IncrementToolbar();
                IncrementToolbar();
                IncrementToolbar();
                IncrementToolbar();
            }

            // Check if Stock enabled
            if (StockModule == true)
            {
                lblStatus.Text = "Checking Stock Records";

                // Check Transaction Line Stock Code Exists
                TransactionLineStockChecks.TransactionLineStockCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                // Check Transaction Line Stock Code not a Group
                TransactionLineStockChecks.TransactionLineStockCodeNotGroup(ExchequerCommonSQLConnection, Company, connPassword);
                IncrementToolbar();

                //// Check Stock History Stock Codes Exists
                //HistoryChecks.HistoryCheckStockExist(ExchequerCommonSQLConnection, Company);
                //IncrementToolbar();

                // Check if Locations is enabled
                if (Locations == true)
                {
                    lblStatus.Text = "Checking Locations";

                    // Check if Transaction Line Location Codes exist
                    TransactionLineStockChecks.TransactionLineLocationCodeDoesNotExist(ExchequerCommonSQLConnection, Company, connPassword);
                    IncrementToolbar();

                    //// Check Stock History Location Codes Exists
                    //HistoryChecks.HistoryCheckLocationExist(ExchequerCommonSQLConnection, Company);
                    //IncrementToolbar();

                    //// Check Stock History Stock Location Codes Exists
                    //HistoryChecks.HistoryCheckStockLocationExist(ExchequerCommonSQLConnection, Company);
                    //IncrementToolbar();
                }
                else
                {
                    IncrementToolbar();
                }
            }
            else
            {
                IncrementToolbar();
                IncrementToolbar();
                IncrementToolbar();
            }

            lblStatus.Text = "Checking Profit and Loss Brought Forward";

            // Check Profit and Loss Brought Forward
            HistoryCalculations.ProfitAndLossBroughtForward(ExchequerCommonSQLConnection, Company, CommitmentAccounting, connPassword);
            IncrementToolbar();

            this.UseWaitCursor = false;
            lblStatus.Text     = "Checking Completed";

            tsProgressBar.Value = tsProgressBar.Maximum;
            Thread.Sleep(100);
            Application.DoEvents();

            // Display Summary Results in UI
            DisplayResults();
        }
예제 #26
0
 protected override SQLCommand InternalCreateCommand()
 {
     ADODB.Command command = new ADODB.Command();
     command.ActiveConnection = _connection;
     return(new ADOCommand(this, command));
 }