C# and PostgreSQL

Question

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

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);

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

tr.Commit();

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

I think it is rather specific question

Yevhen
– Yevhen

2010-01-11 21:09:17 +00:00
Commented Jan 11, 2010 at 21:09 — Yevhen
– Yevhen, Commented Jan 11, 2010 at 21:09

CarenRose · Accepted Answer · 2020-01-03 02:37:47Z

4

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;
}

edited Jan 3, 2020 at 2:37

CarenRose

1,3661 gold badge14 silver badges26 bronze badges

answered May 3, 2012 at 13:38

ken

412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A-K Over a year ago

This answer will be more useful when you provide a sample call of your function. For example, how should we initialize List<object> Parameters?

AHMAD SUMRAIZ Over a year ago

There is error "A command is already in progress:" on this line execution da.Fill(dt);

CarenRose · Accepted Answer · 2020-01-03 00:21:53Z

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

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

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.

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

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

CarenRose · Accepted Answer · 2020-01-03 02:58:26Z

0

First of all, here is some documentation that could be useful: 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 DataSet and a cursor. It will fill the DataSet with the data returned by your cursor.

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

edited Jan 3, 2020 at 2:58

CarenRose

1,3661 gold badge14 silver badges26 bronze badges

answered Jan 13, 2010 at 14:22

ALOToverflow

2,6995 gold badges37 silver badges70 bronze badges

Comments

CarenRose · Accepted Answer · 2020-01-03 05:52:34Z

0

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

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

The second command looks like:

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

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

edited Jan 3, 2020 at 5:52

CarenRose

1,3661 gold badge14 silver badges26 bronze badges

answered Jan 15, 2010 at 22:58

Yevhen

1,9752 gold badges14 silver badges27 bronze badges

Collectives™ on Stack Overflow

C# and PostgreSQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related