Devart Forums

Posted: **Mon 12 Oct 2009 19:41**

Hello, I have this problem.
I'm using Postgres with stored procedures. My stored procedure receives a refcursor to return a dataset. I was using Npgsql, a free Postgres provider, but decided to try devart's provider. I build a class hierarchy so i can change providers at wish. When using Npgsql, the code is as follows:

**********************************************************

Code: Select all

public override DataSet ExecuteStoredProcedure(string query, List parameters)
        {

            bool local = false;
            DataSet dataSet = new DataSet();
            OpenConnection();
            if (Transaction==null)
            {
                local = true;
                Transaction = connection.BeginTransaction();
            }
            command = new NpgsqlCommand(query, connection as NpgsqlConnection) { CommandType = CommandType.StoredProcedure };

            foreach (var dbParameter in GenerateParams(parameters))
            {

                command.Parameters.Add(dbParameter);
            }
            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command as NpgsqlCommand);

            try
            {
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.Fill(dataSet);

            }
            finally
            {
                if (local)
                {
                    Transaction.Commit();
                    ClearTransaction();
                }
            }
            return dataSet;
        }

**********************************************************

GenerateParams is a method I define in each descending class, I have three 4 classes, BaseDataAcces, SqlDataAccess, PostgresDataAccess, PostgresDevartDataAccess.
In the class PostgresDataAccess, GenerateParams is defined as follow:

**********************************************************

Code: Select all

public override List GenerateParams(List parameters)
        {
            List parametros = new List();
            foreach (DataBase.Parameter parameter in parameters)
            {
                NpgsqlParameter param = new NpgsqlParameter
                {
                    ParameterName = (parameter.ParameterName),
                    Value = parameter.ParameterValue
                };
                switch (parameter.ParameterType)
                {
                    case ParameterType.BigInt:
                        param.NpgsqlDbType = NpgsqlDbType.Bigint;
                        break;
                    case ParameterType.VarChar:
                        param.NpgsqlDbType = NpgsqlDbType.Text;
                        break;
                    case ParameterType.DateTime:
                        param.NpgsqlDbType = NpgsqlDbType.Timestamp;
                        break;
                    case ParameterType.Binary:
                        param.NpgsqlDbType = NpgsqlDbType.Bytea;
                        break;
                    case ParameterType.Boolean:
                        param.NpgsqlDbType = NpgsqlDbType.Boolean;
                        break;
                    case ParameterType.Text:
                        param.NpgsqlDbType = NpgsqlDbType.Text;
                        break;
                    case ParameterType.RefCursor:
                        [b]param.NpgsqlDbType = NpgsqlDbType.Refcursor;[/b]
                        break;
                }
                parametros.Add(param);
            }
            return parametros;
        }

**********************************************************

My problem is this. When defining GenerateParams in PostgresDevartDataAccess, I find there is no DbType for refcursor:

**********************************************************
......................................
break;
case ParameterType.Text:
param.DbType = DbType.String;
break;
case ParameterType.RefCursor:
param.DbType = new ...........
break;
}
parametros.Add(param);
......................................

**********************************************************
That is a problem, 'cause I can't get the results from the stored procedure. How can I achieve this, without changing the code?
Is there a refcursor type, somewhere in devart classes?

Here is a sample of the kind of functions I have in Postgres:

Code: Select all

CREATE OR REPLACE FUNCTION dbo.select_detalleslote(data1 refcursor, data2 refcursor, data3 refcursor, data4 refcursor, data5 refcursor, _lotecodebar text)
  RETURNS SETOF refcursor AS
$BODY$


      BEGIN
      OPEN data1  FOR SELECT * FROM dbo.Lote WHERE LoteCODEBAR=_lotecodebar ;
      RETURN NEXT data1 ;
      OPEN data2 FOR SELECT  dbo.LoteAsociado.FechaAsociado, dbo.Usuario.UserName
      FROM dbo.LoteAsociado INNER JOIN dbo.Usuario
      ON dbo.LoteAsociado.AsociadorID = dbo.Usuario.userID
      WHERE LoteCODEBAR=_lotecodebar ;
      RETURN NEXT data2;
      OPEN data3 FOR SELECT * FROM dbo.LoteDevuelto WHERE LoteCODEBAR = _lotecodebar ;
      RETURN NEXT data3;
      OPEN data4 FOR SELECT CODEBAR FROM dbo.Modelo WHERE LoteCODEBAR = _lotecodebar AND Procesado=TRUE;
      RETURN NEXT data4;
      OPEN data5 FOR SELECT FormadorID, RegistradorID, FechaRegistro, LoteCODEBAR FROM dbo.LoteRegistrado
      WHERE LoteCODEBAR = _lotecodebar ;
      RETURN NEXT data5;


      END;
      $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION dbo.select_detalleslote(refcursor, refcursor, refcursor, refcursor, refcursor, text) OWNER TO "NombreUsuario";

Thanks in advance.

Posted: **Wed 14 Oct 2009 11:26**

The type of refcursor parameter doesn't matter. Default PgSqlType is VarChar. The value of this parameter should be cast to PgSqlCursor. For more information, please refer to our documentation (the Devart.Data.PostgreSql.PgSqlCursor class).
Our mapping tables are here: http://www.devart.com/forums/viewtopic.php?t=15942 .

Devart Forums

How to use refcursor parameters?

How to use refcursor parameters?