在C＃中的oraclecommand中传递参数 [英] Passing parameter in oraclecommand in C#

问题描述

我有这个问题。

请仔细研究这段代码并提供协助。

谢谢



我尝试了什么：

 尝试 
 {
  string  query =   SELECT uetc.team_codes，LPAD（papf.employee_number，5,0）staff_id，papf.full_name full_name，papf.email_address，LPAD（papf1.employee_number，5,0）supervisor_id，papf1.full_name supervisor_name，papf1.email_address，hl .location_code，SUBSTR（hl.location_code，1,3）branch_code，SUBSTR（hl.location_code，6）branch_name，hl.loc_information15 region_name，hl.loc_information14 zone_name FROM per_all_people_f papf，per_all_assignments_f paaf，per_grades pg，per_jobs pj，hr_locations_all hl， hr_all_organization_units haou，per_all_people_f papf1，per_all_assignments_f paaf1，per_grades pg1，per_jobs pj1，hr_locations_所有hl1，hr_all_organization_units haou1，ub_emp_team_codes_new uetc WHERE papf.person_id = paaf.person_id AND papf.current_employee_flag ='Y'和SYDRATE BETWEEN papf.effective_start_date和papf.effective_end_date AND paaf.primary_flag ='Y'AND papf.employee_number< 7000 AND papf.employee_number<> 00000 AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.grade_id = pg.grade_id（+）AND paaf.job_id = pj.job_id（+）AND paaf.location_id = hl.location_id（+）AND paaf.organization_id = haou .organization_id（+）AND paaf.supervisor_id = papf1.person_id（+）AND papf1.person_id = paaf1.person_id（+）AND papf1.current_employee_flag（+）='Y'和SYDRATE之间的papf1.effective_start_date（+）和papf1。 effective_end_date（+）AND paaf1.primary_flag（+）='Y'和SYSDATE BETWEEN paaf1.effective_start_date（+）AND paaf1.effective_end_date（+）AND paaf1.grade_id = pg1.grade_id（+）AND paaf1.job_id = pj1.job_id （+）AND paaf1.location_id = hl1.location_id（+）AND paaf1.organization_id = haou1.organization_id（+）AND papf.person_id = uetc.person_id（+）AND uetc.person_id（+）= papf.person_id AND papf。 employee_number = @staff_id; 
 OracleCommand OraC =  new  OracleCommand（query，OracleConn）; 
 O raC.CommandType = CommandType.Text; 
 OraC.Parameters.Add（ new  OracleParameter（  @staff_id，OracleDbType.Varchar2））。Value = staffid; 
  //   OraC.Parameters.Add（new OracleParameter（SESSION_ID，OracleDbType.Varchar2））。 = SessionID;  
  //   OraC.Parameters.Add（new OracleParameter（V_RESULT， OracleDbType.Varchar2,32767））。Direction = ParameterDirection.Output;  
 
 
  //   OracleDataReader dr = OraC.ExecuteNonQuery（）;  
 OracleDataReader dr = OraC.ExecuteReader（）; 
  while （dr.Read（））
 {
 SupervisorEmail = dr [   EMAIL_ADDRESS_1]。ToString（）; 
 StaffEmail = dr [  EMAIL_ADDRESS]。ToString（）; 
  string  id = dr [  STAFF_ID ]的ToString（）; 
} 
 
} 
  catch （例外情况）
 {
 
 ex.Message.ToString（）; 
} 
 最后 
 {
 OracleConn.Close（）; 
} 

解决方案

第一个问题是你的编写方式与针对SQL Server的查询编写方式相同哪个不行。不久前我写了一篇关于这个的帖子。请随意查看： .Net Oracle Queries

im having issues with this.
kindly look into this code and assist.
thanks

What I have tried:

try
                {
                    string query = "SELECT uetc.team_codes, LPAD (papf.employee_number, 5, 0) staff_id,papf.full_name full_name, papf.email_address,LPAD (papf1.employee_number, 5, 0) supervisor_id,papf1.full_name supervisor_name, papf1.email_address, hl.location_code, SUBSTR (hl.location_code, 1, 3) branch_code, SUBSTR (hl.location_code, 6) branch_name, hl.loc_information15 region_name, hl.loc_information14 zone_name  FROM per_all_people_f papf, per_all_assignments_f paaf, per_grades pg, per_jobs pj, hr_locations_all hl, hr_all_organization_units haou, per_all_people_f papf1, per_all_assignments_f paaf1, per_grades pg1, per_jobs pj1, hr_locations_all hl1,  hr_all_organization_units haou1, ub_emp_team_codes_new uetc  WHERE papf.person_id = paaf.person_id AND papf.current_employee_flag = 'Y' AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND paaf.primary_flag = 'Y'  AND papf.employee_number < 7000 AND papf.employee_number <> 00000 AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date  AND paaf.grade_id = pg.grade_id(+) AND paaf.job_id = pj.job_id(+)  AND paaf.location_id = hl.location_id(+)  AND paaf.organization_id = haou.organization_id(+) AND paaf.supervisor_id = papf1.person_id(+)  AND papf1.person_id = paaf1.person_id(+) AND papf1.current_employee_flag(+) = 'Y' AND SYSDATE BETWEEN papf1.effective_start_date(+) AND papf1.effective_end_date(+) AND paaf1.primary_flag(+) = 'Y' AND SYSDATE BETWEEN paaf1.effective_start_date(+) AND paaf1.effective_end_date(+) AND paaf1.grade_id = pg1.grade_id(+) AND paaf1.job_id = pj1.job_id(+)  AND paaf1.location_id = hl1.location_id(+) AND paaf1.organization_id = haou1.organization_id(+) AND papf.person_id = uetc.person_id(+) AND uetc.person_id(+) = papf.person_id  AND papf.employee_number = @staff_id";
                    OracleCommand OraC = new OracleCommand(query, OracleConn);
                    OraC.CommandType = CommandType.Text;
                    OraC.Parameters.Add(new OracleParameter("@staff_id", OracleDbType.Varchar2)).Value = staffid;
                    //OraC.Parameters.Add(new OracleParameter("SESSION_ID", OracleDbType.Varchar2)).Value = SessionID;
                    //OraC.Parameters.Add(new OracleParameter("V_RESULT", OracleDbType.Varchar2, 32767)).Direction = ParameterDirection.Output;


                    //OracleDataReader dr = OraC.ExecuteNonQuery();
                    OracleDataReader dr = OraC.ExecuteReader();
                    while (dr.Read())
                    {
                        SupervisorEmail = dr["EMAIL_ADDRESS_1"].ToString();
                        StaffEmail = dr["EMAIL_ADDRESS"].ToString();
                        string id = dr["STAFF_ID"].ToString();
                    }

                }
                catch (Exception ex)
                {

                    ex.Message.ToString();
                }
                finally
                {
                    OracleConn.Close();
                } 

解决方案

The first problem is that you are writing the same way you would write it for queries against a SQL Server which won't work. I wrote a post about this a little while ago. Feel free to have a look: .Net Oracle Queries

这篇关于在C＃中的oraclecommand中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！