tga tga - 3 months ago 121
SQL Question

C# and PostgreSQL

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

tga tga
Answer

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