current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

Can any one show me working example of using cursor returned from PLSQL to c# code? I found many examples showing how to fill dataSet with returned data but I cannot find how to use that cursor with DataReader, 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);

what i have to write here to use NpgsqlDataReader myReader; correctly. 
tr.Commit();
when I wrote 'fetch' after sql command it works but it is not suitable.

thnx in advance

share|improve this question
    
I think it is rather specific question –  tga Jan 11 '10 at 21:09

4 Answers 4

up vote 1 down vote accepted

I have got some answers on my question.

Problem: I have stored PLSQL procedure which returns refCursor. I have to get returned data with datareader. But When I added parameters db returned

To walk through all returned data I have to write my code 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();

you should notice that you haven'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 just another output parameter to my commandtext. As a result I have to fields one of them is 0{my first output param} another one is In oracle i can directly convert RefCursor parameter to datareader but in postgresql i cannot.

Thank you for attention

share|improve this answer
    
+1 exactly what I was looking for. –  AlexKuznetsov Oct 25 '13 at 18:53
up vote 1 down vote

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;
    }
share|improve this answer
    
This answer will be more useful when you provide a sample call of your function. For example, how should we initialize List<object> Parameters? –  AlexKuznetsov Oct 25 '13 at 18:53
up vote 0 down vote

First of all, here is some documentations that could be usefull : 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.

share|improve this answer
up vote 0 down vote

Problem with Out parameter I have solved using two commands in the same transaction

from the first command i read out parameter and the execute 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 a get data selected from db

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.