Esempio n. 1
0
        private void CreateTables(bool onlycurruser)
        {
            try
            {
                string createTableSql = $"CREATE TABLE {OracleDbContext.OldTable} AS SELECT * FROM v$sql";
                //Context.DB.ExecSqlStatement(createTableSql);
                OracleDbHelper.ExecuteSql(createTableSql);
                this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Create data table succeed.\r\n")));

                string getDateSql = "SELECT to_char(SYSDATE,'yyyy/MM/dd hh24:mi:ss') AS currdate FROM dual";
                //DataSet ds = Context.DB.ExecuteDataSet(getDateSql);
                //if (ds != null) { this.StartOnText = Convert.ToString(ds.Tables[0].Rows[0]["currdate"]); }
                string currdate = OracleDbHelper.GetSingle(getDateSql)?.ToString();
                if (!string.IsNullOrEmpty(currdate))
                {
                    this.StartOnText = currdate;
                }
                if (onlycurruser)
                {
                    this.IsCurrUser = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
Esempio n. 2
0
        private void DropTables(string host, string port, string service, string userid, string password)
        {
            try
            {
                Runtimes.ConnectionString = $"Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = {host})(PORT = {port}))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orclbak))); Persist Security Info=True;User ID={userid};Password={password};";
                OracleDbContext.Initialize(host, port, service, userid, password);
                //DataTable dt = OracleDbContext.GetDomainSystemTable();
                //int count = OracleDbHelper.ExecuteSql("select * from CORE.DOMAINSYSTEM where SYSTEMKEY = :p0", "cis");
                //DataTable dt1 = OracleDbHelper.ExecuteDataTable("select * from CORE.DOMAINSYSTEM where SYSTEMKEY = :key", new OracleParameter("@key", "emr"));
                StringBuilder           sb        = new StringBuilder();
                IList <OracleParameter> paras     = new List <OracleParameter>();
                IList <string>          uniqueIds = new List <string> {
                    "c5331527bcc249909742c1f961df3e0d,672a1eb4bedd414c9baa36051f63d120", "5d09f9f161d04202895d18233d56886b,8d531688e23b4d96964f852921925e16", "e32db5191dec4c91a5a176d33a98236d,28273471d69644518225fcc4ce2a86f1"
                };
                sb.AppendLine("BEGIN");
                for (int i = 0; i < 3; i++)
                {
                    sb.AppendLine($"update DATA.bjcasignitem set SignStatusCodeId=:p{i * 3},RowVersion=:p{i * 3 + 1} where UniqueId in (SELECT CAST(column_value AS VARCHAR2(128)) ItemId FROM TABLE(poor.fnIDInString(:p{i * 3 + 2},',')));");
                    paras.Add(new OracleParameter($"@p{i * 3}", OracleDbType.Int32, i, ParameterDirection.Input));
                    paras.Add(new OracleParameter($"@p{i * 3 + 1}", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                    paras.Add(new OracleParameter($"@p{i * 3 + 2}", OracleDbType.Varchar2, uniqueIds[i], ParameterDirection.Input));
                }
                sb.Append("END;");
                int ret = OracleDbHelper.ExecuteSql(sb.ToString(), paras.ToArray());

                sb = new StringBuilder();
                sb.AppendLine("BEGIN");
                sb.AppendLine($"UPDATE POOR.ORDERREQUEST s set S.ISDELETED=1 where s.encounterid =:EncounterId and s.ISDELETED=0;");
                sb.AppendLine($"UPDATE POOR.PERFORMREQUEST s SET S.ISDELETED = 1 where s.ENCOUNTERID =:EncounterId and s.ISDELETED = 0;");
                sb.AppendLine($"UPDATE POOR.PERFORMREQUESTBILL s SET S.ISDELETED= 1 where s.ENCOUNTERID =:EncounterId and s.ISDELETED=0;");
                sb.AppendLine($"UPDATE FIAB.OTHERBILLING s SET S.ISDELETED= 1 where s.ENCOUNTERID =:EncounterId and s.ISDELETED=0;");
                sb.AppendLine("COMMIT;");
                sb.AppendLine("END;");
                paras.Add(new OracleParameter($"@EncounterId", OracleDbType.Int32, 11961, ParameterDirection.Input));
                ret = OracleDbHelper.ExecuteSql(sb.ToString(), paras.ToArray());

                string         sql = "SELECT table_name FROM all_tables WHERE table_name = :p0";
                IList <string> tns = new List <string> {
                    OracleDbContext.OldTable, OracleDbContext.NewTable
                };
                foreach (var tablename in tns)
                {
                    if (!string.IsNullOrEmpty(OracleDbHelper.GetSingle(sql, new OracleParameter("@p0", tablename))?.ToString()))
                    {
                        OracleDbHelper.ExecuteSql($"DROP TABLE {tablename} PURGE"); //删除Table不进入Recycle
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
        }
Esempio n. 3
0
        private bool ProcessData(string tablename, string module, out string sql)
        {
            try
            {
                string createTableSql = $"CREATE TABLE {OracleDbContext.NewTable} AS SELECT * FROM v$sql";
                //Context.DB.ExecSqlStatement(createTableSql);
                OracleDbHelper.ExecuteSql(createTableSql);

                //string sqlbase = string.Format(@"SELECT a.SQL_ID, a.parsing_schema_name AS SCHEMA, a.module AS MODULE, a.sql_text AS SQL_TEXT
                //, a.sql_fulltext AS SQL_FULLTEXT
                //, a.parse_calls - nvl(b.parse_calls, 0) AS PARSE_CALLS
                //, a.buffer_gets - nvl(b.buffer_gets, 0) AS BUFFER_GETS
                //, a.disk_reads - nvl(b.disk_reads, 0) AS DISK_READS
                //, a.executions - nvl(b.executions, 0) AS EXECUTIONS
                //, round((a.cpu_time - nvl(b.cpu_time, 0)) / 1000000, 2) AS CPU_TIME
                //, round((a.cpu_time - nvl(b.cpu_time, 0)) / ((a.executions - nvl(b.executions, 0)) * 1000000), 2) AS CPU_TIME_PER_EXE
                //, round((a.elapsed_time - nvl(b.elapsed_time, 0)) / ((a.executions - nvl(b.executions, 0)) * 1000000), 2) AS ELAPSED_TIME_PER_EXE
                //, to_date(a.FIRST_LOAD_TIME, 'yyyy-MM-dd hh24:mi:ss') AS FIRST_LOAD_TIME, a.LAST_ACTIVE_TIME
                //FROM {0} a LEFT JOIN {1} b ON a.hash_value = b.hash_value AND a.address = b.address
                //WHERE ( to_date(a.FIRST_LOAD_TIME, 'yyyy/MM/dd hh24:mi:ss') > to_date('{2}', 'yyyy/MM/dd hh24:mi:ss') OR a.LAST_ACTIVE_TIME > to_date('{2}', 'yyyy/MM/dd hh24:mi:ss') )
                //AND (a.executions - nvl(b.executions, 0)) > 0 <CRITERIA>
                //ORDER BY a.LAST_ACTIVE_TIME DESC, a.FIRST_LOAD_TIME DESC", OracleDbContext.NewTable, OracleDbContext.OldTable, this.StartOnText);

                string sqlbase = string.Format(@"SELECT a.SQL_ID, a.parsing_schema_name AS SCHEMA, a.module AS MODULE, a.sql_text AS SQL_TEXT 
                , a.sql_fulltext AS SQL_FULLTEXT 
                , a.parse_calls AS PARSE_CALLS 
                , a.buffer_gets AS BUFFER_GETS 
                , a.disk_reads AS DISK_READS 
                , decode(a.executions,0,1,a.executions) AS EXECUTIONS 
                , round(a.cpu_time / 1000000, 2) AS CPU_TIME 
                , round(a.cpu_time / (decode(a.executions,0,1,a.executions) * 1000000), 2) AS CPU_TIME_PER_EXE 
                , round(a.elapsed_time / (decode(a.executions,0,1,a.executions) * 1000000), 2) AS ELAPSED_TIME_PER_EXE 
                , to_date(a.FIRST_LOAD_TIME, 'yyyy-MM-dd hh24:mi:ss') AS FIRST_LOAD_TIME, a.LAST_ACTIVE_TIME 
                FROM v$sql a 
                WHERE ( to_date(a.FIRST_LOAD_TIME, 'yyyy/MM/dd hh24:mi:ss') > to_date('{0}', 'yyyy/MM/dd hh24:mi:ss') OR a.LAST_ACTIVE_TIME > to_date('{0}', 'yyyy/MM/dd hh24:mi:ss') )
                <CRITERIA> 
                ORDER BY a.LAST_ACTIVE_TIME DESC, a.FIRST_LOAD_TIME DESC", this.StartOnText);

                string criteria  = "";
                string criteria2 = "";
                if (!string.IsNullOrWhiteSpace(module))
                {
                    IList <string> ms      = module.Split(';').Select(a => string.Format("'{0}'", a.ToUpper())).ToList();
                    string         modules = string.Join(",", ms);
                    criteria2 = string.Format("AND UPPER(a.module) IN ({0})", modules);
                }

                criteria = criteria2;
                if (this.IsCurrUser)
                {
                    string user    = "";
                    string usersql = "SELECT user FROM dual";
                    //DataSet ds = Context.DB.ExecuteDataSet(usersql);
                    //if ((ds != null) && (ds.Tables[0].Rows.Count > 0))
                    //    user = Convert.ToString(ds.Tables[0].Rows[0]["user"]);
                    user = OracleDbHelper.GetSingle(usersql).ToString();

                    criteria = string.Format("AND a.parsing_schema_name = '{0}'", user);
                    if (!string.IsNullOrWhiteSpace(criteria2))
                    {
                        criteria = string.Format("{0} {1}", criteria, criteria2);
                    }
                }

                sqlbase = sqlbase.Replace("<CRITERIA>", criteria);
                if (this.IsSaveOracle && !string.IsNullOrWhiteSpace(tablename))
                {
                    this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Checking table:" + tablename + " ...\r\n")));
                    string sql2 = "SELECT table_name FROM all_tables WHERE table_name = :p0";
                    if (!string.IsNullOrEmpty(OracleDbHelper.GetSingle(sql2, new OracleParameter("@p0", tablename))?.ToString()))
                    {
                        this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Drop table:" + tablename + " ...\r\n")));
                        OracleDbHelper.ExecuteSql($"DROP TABLE {tablename} PURGE");
                    }

                    this.Dispatcher.BeginInvoke(new Action(() => this.tb_log.AppendText("Creating table:" + tablename + " ...\r\n")));
                    string createTable = $"CREATE TABLE {tablename} AS {sqlbase}";
                    //Context.DB.ExecSqlStatement(createTable);
                    OracleDbHelper.ExecuteSql(createTable);
                }

                sql = string.Format("SELECT ROWNUM, t.* FROM ({0}) t WHERE ROWNUM <= 500", sqlbase);
                return(true);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常", MessageBoxButton.OK, MessageBoxImage.Error);
                sql = string.Empty;
                return(false);
            }
        }