问题描述

谁能给我看一个使用从 PLSQL 返回的游标到 C# 代码的工作示例吗?

Can anyone show me a working example of using a cursor returned from PLSQL to C# code?

我找到了许多示例，展示了如何用返回的数据填充 dataSet，但我找不到如何将光标与 DataReader 一起使用，因此我得到了{未命名的门户}.

I found many examples showing how to fill a dataSet with returned data, but I cannot find how to use that cursor with a DataReader, so as a result I have {unnamed portal}.

NpgsqlTransaction tr = (NpgsqlTransaction) Connection.BeginTransaction();
NpgsqlCommand cursCmd = new NpgsqlCommand("someStoredProcedure(:inRadius)", (NpgsqlConnection) Connection);
cursCmd.Transaction = tr;
NpgsqlParameter rf = new NpgsqlParameter("ref", NpgsqlTypes.NpgsqlDbType.Refcursor);
rf.Direction = ParameterDirection.InputOutput;
cursCmd.Parameters.Add(rf);

我必须添加这个才能正确使用 NpgsqlDataReader myReader;:

I have to add this to use NpgsqlDataReader myReader; correctly:

tr.Commit();

当我在sql命令之后写fetch时，它可以工作，但它不适合.

When I wrote fetch after the sql command, it works but it is not suitable.

推荐答案

我的问题得到了一些答案.

I have got some answers on my question.

问题:我有一个存储的 PLSQL 过程，它返回 refCursor.我必须使用 DataReader 获取返回的数据，但是当我添加参数时，db 返回 <unnamed portal>.

Problem: I have a stored PLSQL procedure which returns refCursor. I have to get the returned data with a DataReader, but wwhen I added parameters, the db returned <unnamed portal>.

要遍历所有返回的数据，我必须这样编写代码:

To walk through all returned data I have to write my code like so:

NpgsqlTransaction tr = (NpgsqlTransaction) Connection.BeginTransaction();
NpgsqlCommand cursCmd = new NpgsqlCommand("someStoredProcedure", (NpgsqlConnection) Connection);
cursCmd.Transaction = tr;
NpgsqlParameter rf = new NpgsqlParameter("ref", NpgsqlTypes.NpgsqlDbType.Refcursor);
rf.Direction = ParameterDirection.InputOutput;
cursCmd.Parameters.Add(rf);

NpgsqlParameter param2 = new NpgsqlParameter("param1", NpgsqlTypes.Int32);
rf.Direction = ParameterDirection.Input;
cursCmd.Parameters.Add(param2);
NpgsqlDataReader r = cmd.ExecuteReader();

while (r.Read())
{
    ; // r.GetValue(0);
}
r.NextResult();
while(r.Read())
{
    ;
}

tr.Commit();

请注意，您不要像 func(:param1) 那样在 sql 中编写参数.

Notice that you don't write your parameters in sql like func(:param1).

如果您的函数中有参数，请只将函数名称分配给 CommandText 属性，并像往常一样将参数添加到 NpgsqlCommand.Parameters 集合中.Npgsql 将负责正确绑定您的参数.

If you have parameters in your function, assign only the function name to the CommandText property and add parameters to the NpgsqlCommand.Parameters collection as usual. Npgsql will take care of binding your parameters correctly.

但现在我遇到了另一个问题.当我将另一个输出参数传递给我的 CommandText 时，我的结果中有两个字段.其中一个是0{我的第一个输出参数}，另一个是<unnamed portal>.

But now I have another problem. When I pass another output parameter to my CommandText, I have two fields in my result. One of them is 0{my first output param} and the other is <unnamed portal>.

在Oracle中，我可以直接将RefCursor参数转换为DataReader，但是在postgresql中，我不能.

In Oracle, I can directly convert a RefCursor parameter to a DataReader, but in postgresql, I cannot.

这篇关于C# 和 PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！