Npqsql存储过程返回多个REFCURSOR

Question

从调用存储过程时遇到一些问题。NET/C#使用Npgsql。我有时会遇到不同的错误，从已经在进行的操作到现在可用的行，再到超出范围的列，必须在0和0之间。然而，这是我从NUnit看到的主要堆栈跟踪。

System.InvalidOperationException : An operation is already in progress.
   at Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState)
   at Npgsql.NpgsqlConnector.ExecuteInternalCommand(SimpleFrontendMessage message, Boolean withTimeout)
   at Npgsql.NpgsqlConnector.Rollback()
   at Npgsql.NpgsqlTransaction.Rollback()
   at Omega.Data.DAO.PatientShapeDao.GetShapeData(Int64 shapeId) in c:UsersjkratzProjectsomegaOmegadataDAOPatientShapeDao.cs:line 62
   at Omega.Tests.DAO.PostgresDaoTests.TestGetShapeData() in c:UsersjkratzProjectsomegaOmega.TestsDAOPostgresDaoTests.cs:line 26

我在Postgres中有以下存储过程：

CREATE OR REPLACE FUNCTION public.getpatientshapedata(p_shapeid bigint)
  RETURNS SETOF refcursor AS
$BODY$
    DECLARE
        v_shapecursor REFCURSOR;
        v_shapefilecursor REFCURSOR;
    BEGIN
      OPEN v_shapecursor FOR
        select * from patientshape where id = p_shapeid;
      RETURN NEXT v_shapecursor;
      OPEN v_shapefilecursor FOR
        select * from patientshapefile where patientshapeid = p_shapeid;
      RETURN NEXT v_shapefilecursor;
    END;
$BODY$
  LANGUAGE plpgsql

它现在返回两个refcursor。

我的应用程序中有以下Dao对象。

public class PatientShapeDao
    {
        private NpgsqlConnection _dbHandle;
        private readonly string _connectionString;
        public PatientShapeDao()
        {
            OmegaConnectionString connectionString = Transform.getInstance.LoadOmegaConnection();
            _connectionString = string.Format("Server={0};User Id={1};Password={2};Database={3}", 
                connectionString.Host, connectionString.UserId, connectionString.Password, connectionString.Database);
        }
        public void GetShapeData(long shapeId)
        {
            NpgsqlTransaction transaction;
            // open connection
            _dbHandle = new NpgsqlConnection(_connectionString);
            _dbHandle.Open();
            // begin transaction
            transaction = _dbHandle.BeginTransaction();
            try
            {
                // call stored procedure
                NpgsqlCommand command = new NpgsqlCommand("getpatientshapedata", _dbHandle);
                command.Parameters.Add(new NpgsqlParameter("p_shapeid", shapeId));
                command.CommandType = CommandType.StoredProcedure;
                NpgsqlDataReader dataReader = command.ExecuteReader();
                // read result sets
                while (dataReader.Read())
                {
                    Console.WriteLine("{0}t{1}", dataReader[0], dataReader[1]);
                }
                dataReader.NextResult();
                // read result sets
                while (dataReader.Read())
                {
                    Console.WriteLine("{0}t{1}", dataReader[0], dataReader[1]);
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
            }
            finally
            {
                _dbHandle.Close();
            }
        }
    }

Answer 1

您的堆栈跟踪显示异常是从Rollback抛出的，这意味着您的代码中有一些早期的异常，但您尚未发布。

无论如何，您需要将NpgsqlCommand和NpgsqlDataReader放入using中，以便在到达catch块之前对它们进行正确的处理。