gpt4 book ai didi

c# - 使用来自 Oracle 存储过程的 refcursor 值填充 C# 数据表

转载 作者:太空宇宙 更新时间:2023-11-03 15:57:06 25 4
gpt4 key购买 nike

我想用存储过程“spValidateDBA”中的 refcursor 参数 UserRole 的值填充数据表,但它每次都给我这个错误:

Column 'UserRole' does not belong to table.

C# 代码:-

string sConnectionString = "Data Source=XE;User ID=sys;Password=system;DBA PRIVILEGE=sysdba";
OracleConnection myConnection = new OracleConnection(sConnectionString);
OracleCommand myCommand = new OracleCommand("spValidateDBA", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "spValidateDBA";
myCommand.Parameters.Add("UserId", OracleDbType.Varchar2, 50);
myCommand.Parameters["UserId"].Value = txtUsrId.Text.ToString().ToUpper();
myCommand.Parameters.Add("UserRole",OracleDbType.RefCursor, 50).Direction = ParameterDirection.Output;
myCommand.Parameters.Add("UserIdOut", OracleDbType.Varchar2, 50).Direction = ParameterDirection.Output;
var rolechk = false;
string checkrole = "DBA";
myConnection.Open();
myCommand.ExecuteReader();
// Create the OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter(myCommand);
DataTable dt = new DataTable();
da.Fill(dt); // Trying to populate a DataTable with refcursor UserRole.
if (myCommand.Parameters["UserIdOut"].Value.ToString().ToUpper() == txtUsrId.Text.ToString().ToUpper())
{
CustomMsgbox.Show("1", "DB Utilities Tool", "OK", "Cancel");
foreach (DataRow dr in dt.Rows)
{
if (dr["UserRole"].ToString().ToUpper().Equals(checkrole)==true)//getting the error "Column 'UserRole' does not belong to table." here
{

CustomMsgbox.Show("\tLogin Successful..!!\t" + Environment.NewLine + "Welcome to DB Utilities Tool", "DB Utilities Tool", "OK", "Cancel");
DBA dba = new DBA();
dba.Show();
this.Hide();
rolechk = true;
break;

}
}

if (!rolechk)
{

CustomMsgbox.Show("Insufficient privileges", "DB Utilities Tool", "OK", "Cancel");
myConnection.Close();
}


else
CustomMsgbox.Show("Please enter correct User ID/Password", "DB Utilities Tool", "OK", "Cancel");

}

存储过程 spValidateDBA

create or replace PROCEDURE spValidateDBA(
UserId IN VARCHAR2,
UserRole OUT SYS_REFCURSOR,
UserIdOut OUT VARCHAR2)
AS
BEGIN
select USERNAME into UserIdOut from DBA_USERS DU where DU.USERNAME=UserId;
OPEN UserRole FOR
select GRANTED_ROLE from DBA_USERS DU,DBA_ROLE_PRIVS DRP where DU.USERNAME=UserId AND DU.USERNAME=DRP.GRANTEE;
END spValidateDBA;

最佳答案

您在游标查询中仅检索一列 GRANTED_ROLE:

OPEN UserRole FOR select GRANTED_ROLE from DBA_USERS DU, .......

但是您正在尝试从游标中获取 UserRole 列。

if (dr["UserRole"].ToString().ToUp.........

那里没有任何名为 UserRole 的列,您只能获取 GRANTED_ROLE

关于c# - 使用来自 Oracle 存储过程的 refcursor 值填充 C# 数据表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23179350/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com