Tutorial :C# and PostgreSQL



Question:

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


Solution:1

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


Solution:2

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


Solution:3

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


Solution:4

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »