C# 和 PostgreSQL

C# and PostgreSQL

提问by Yevhen

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

任何人都可以向我展示一个使用从 PLSQL 返回的游标到 C# 代码的工作示例吗？

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

我找到了许多示例，展示了如何dataSet用返回的数据填充 a ，但我找不到如何使用带有 a 的游标DataReader，因此结果我有{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);

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

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

tr.Commit();

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

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

采纳答案by Yevhen

I have got some answers on my question.

我对我的问题有一些答案。

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>.

问题：我有一个存储的 PLSQL 过程，它返回refCursor. 我必须使用 a 获取返回的数据DataReader，但是当我添加参数时，db 返回<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();

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

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

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

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

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>.

但现在我有另一个问题。当我将另一个输出参数传递给我时CommandText，我的结果中有两个字段。其中一个是0{my first output param}，另一个是<unnamed portal>。

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

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

回答by ken

For your reference:

供你参考：

/// <summary>
/// Get data from the returning refcursor of postgresql function
/// </summary>
/// <param name="FunctionName">Function name of postgresql</param>
/// <param name="Parameters">parameters to pass to the postgresql function</param>
/// <param name="ErrorOccured">out bool parameter to check if it occured error</param>
/// <returns></returns>
public List<DataTable> GetRefCursorData(string FunctionName, List<object> Parameters, out bool ErrorOccured)
{ 
    string connectstring = ""; //your connectstring here
    List<DataTable >  dtRtn =new List<DataTable>();
    NpgsqlConnection connection = null;
    NpgsqlTransaction transaction = null;
    NpgsqlCommand command = null;            
    try
    {
        connection = new NpgsqlConnection(connectstring);
        transaction = connection.BeginTransaction();
        command = new NpgsqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = FunctionName;
        command.Transaction = transaction;
        //
        if (Parameters != null)
        {
            foreach (object item in Parameters)
            {
                NpgsqlParameter parameter = new NpgsqlParameter();
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = item;
                command.Parameters.Add(parameter);
            }
        }
        //
        NpgsqlDataReader dr = command.ExecuteReader();
        while (dr.Read())
        {
            DataTable dt = new DataTable();
            command = new NpgsqlCommand("FETCH ALL IN " + "\"" + dr[0].ToString() + "\"", Connection); //use plpgsql fetch command to get data back
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
            da.Fill(dt);
            dtRtn.Add(dt); //all the data will save in the List<DataTable> ,no matter the connection is closed or returned multiple refcursors
        }                
        ErrorOccured = false;
        transaction.Commit();
    }
    catch
    { 
        //error handling ...
        ErrorOccured = true;
        if (transaction != null) transaction.Rollback();
    }
    finally
    {
        if (connection != null) connection.Close();
    }
    return dtRtn;
}

回答by Yevhen

I have solved the problem with Outparameters by using two commands in the same transaction.

我Out通过在同一个事务中使用两个命令解决了参数问题。

In the first command, I read the out parameter and then execute the next command.

在第一个命令中，我读取了 out 参数，然后执行了下一个命令。

The second command looks like:

第二个命令看起来像：

var cmd2 = new NpgsqlCommand("FETCH ALL FROM \"list\"", (NpgsqlConnection) Connection)

Where listthe name of cursor created inside the stored procedure. As a result I get data selected from the db.

其中list在存储过程中创建的游标名称。结果我从数据库中选择了数据。

回答by ALOToverflow

First of all, here is some documentation that could be useful: Npgsql doc

首先，这里有一些可能有用的文档：Npgsql doc

In this documentation you'll find a NpgsqlDataAdapter. This object also has a Fill()method (inherited from DbDataAdapter). This method can take a DataSetand a cursor. It will fillthe DataSetwith the data returned by your cursor.

在本文档中，您将找到一个NpgsqlDataAdapter. 这个对象还有一个Fill()方法（继承自DbDataAdapter）。这个方法可以带一个DataSet和一个游标。这将填补在DataSet与光标返回的数据。

You can't actually give a DataReaderto this method, but you can give a DataTable, I think you can manage to do something with this.

你实际上不能给DataReader这个方法一个，但是你可以给一个DataTable，我认为你可以设法用这个做点什么。