Reading records from the database using NpgSql and Postgres 9.4

Question

I am usng Postgres 9.4 and NpgSql 2.2.5 for my .Net application.Postgres is new to me and whenevr I try to execute the postgres function in the database,it is throwing me an error

Here is my C# code

public List<District> DistrictReadAll()
      {
          try
          {
              List<District> districts = new List<District>();
              using (var conn = new NpgsqlConnection(this.RealEsateDB))
              {
                  conn.Open();
                  using (var cmd = conn.CreateCommand())
                  {
                      cmd.CommandText = "district_read_all";
                      cmd.CommandType = System.Data.CommandType.StoredProcedure;                      
                      NpgsqlDataReader rdr = cmd.ExecuteReader();
                      while (rdr.Read())
                      {
                          District objDist = new District();
                          objDist.DistrictId = (int)rdr["districtid"];
                          objDist.Name = rdr["name"].ToString();
                          districts.Add(objDist);
                      }
                      return districts;
                  }
              }
          }
          catch ( Exception ex)
          {

              throw;
          }
      }

Postgres function

create or replace function district_read_all()
returns refcursor  
as
$$
declare
    ref1 refcursor ;
begin
    open ref1 for 
    select districtid,name from district order by name asc;
return  ref1;
end
$$ language plpgsql;

public.district table definition

CREATE TABLE district
(
  districtid serial NOT NULL,
  name character varying(250) NOT NULL,
  activeflag boolean DEFAULT true,
  CONSTRAINT pk_district_districtid PRIMARY KEY (districtid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE district
  OWNER TO postgres;

And the C# error stacktrace

Npgsql.NpgsqlException was caught
  HResult=-2147467259
  Message=ERROR: 34000: cursor "<unnamed portal 1>" does not exist
  Source=Npgsql
  ErrorCode=-2147467259
  BaseMessage=cursor "<unnamed portal 1>" does not exist
  Code=34000
  ColumnName=""
  ConstraintName=""
  DataTypeName=""
  Detail=""
  ErrorSql=SELECT * FROM district_read_all()
  File=src\backend\commands\portalcmds.c
  Hint=""
  Line=168
  Position=""
  Routine=PerformPortalFetch
  SchemaName=""
  Severity=ERROR
  TableName=""
  Where=""
  StackTrace:
       at Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()
       at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
       at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
       at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
       at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
       at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
       at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb)
       at Npgsql.NpgsqlCommand.ExecuteReader()
       at CarRental.DataAccess.DistrictAccess.DistrictReadAll() in c:\DotNetApplications\CarRentalGit\RJ.CarRental\CarRental.DataAccess\DistrictAccess.cs:line 58
  InnerException:

This is a very simple thing ,if I use Sql Server

Can anyone let me know what am doing wrong here?

user1322772 · Accepted Answer · 2015-07-25 21:37:16Z

2

You have to wrap and execute the query within a NpgsqlTransaction for it to work - see the Using refcursors section of the manual.

using (var conn = new NpgsqlConnection(this.RealEsateDB))
{
    conn.Open();

    using (NpgsqlTransaction t = conn.BeginTransaction()) 
    {
        using (var cmd = conn.CreateCommand())
        {
            ...
        }

        t.Commit();
    }
}

answered Jul 25, 2015 at 21:37

user1322772

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reading records from the database using NpgSql and Postgres 9.4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related