PostgreSQL and C# - Working with Result Sets - Npgsql .NET Data Provider

Npgsql .NET Data Provider allows you to process result sets returned by a SELECT statement (query) a PostgreSQL function (stored procedure).

• How to Return a Result Set from a Stored Procedure in PostgreSQL

Quite often a result set contains just a single row and column, for example, when you obtain the result of SELECT COUNT(*) FROM … or last generated ID using SELECT LASTVAL();

Consider a PostgreSQL query returning a single row result set with one column:

   -- Query always return 1 row and 1 column (if the table exists, and there are no other system errors)
   SELECT COUNT(*) FROM cities;

You still can use NpgsqlDataReader class, and perform ExecuteReader and then Read to read the row, but you can also use ExecuteScalar method to reduce code:

   using System;
   using Npgsql;
 
   class Sample
   {
       static void Main(string[] args)
       {
          // Connect to a PostgreSQL database
          NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres; " + 
             "Password=pwd;Database=postgres;");
          conn.Open();
 
          // Define a query returning a single row result set
          NpgsqlCommand command = new NpgsqlCommand("SELECT COUNT(*) FROM cities", conn);
 
          // Execute the query and obtain the value of the first column of the first row
          Int64 count = (Int64)command.ExecuteScalar();
 
         Console.Write("{0}\n", count);
         conn.Close();
     }
   }

Note. If the query returns multiple rows and columns, ExecuteScalar method returns the value of the first column in the first row.

Consider a PostgreSQL query returning a result set with 2 columns: city and state:

   -- Query returning 2 columns
   SELECT city, name FROM cities;

The following sample C# code executes the query and processes the result set:

   using System;
   using Npgsql;
 
   class Sample
   {
     static void Main(string[] args)
     {
         // Connect to a PostgreSQL database
         NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres; " + 
             "Password=pwd;Database=postgres;");
         conn.Open();
 
         // Define a query
         NpgsqlCommand command = new NpgsqlCommand("SELECT city, state FROM cities", conn);
 
         // Execute the query and obtain a result set
         NpgsqlDataReader dr = command.ExecuteReader();
 
         // Output rows
         while (dr.Read())
           Console.Write("{0}\t{1} \n", dr[0], dr[1]);
 
         conn.Close();
     }
   }

Output:

Consider a PostgreSQL function returning a single result set (stored procedure in terms of relational databases):

   -- Procedure that returns a single result set (cursor) 
   CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
    DECLARE
      ref refcursor;                                                     -- Declare a cursor variable
    BEGIN
      OPEN ref FOR SELECT city, state FROM cities;   -- Open a cursor
      RETURN ref;                                                       -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;

The stored procedure (function in terms of PostgreSQL) returns a result set with 2 columns: city and state.

The following sample C# code executes the stored procedure and processes the result set:

   using System;
   using System.Data;
   using Npgsql; 
 
   class Sample
   {
     static void Main(string[] args)
     {
         // Connect to a PostgreSQL database
         NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres; " + 
             "Password=pwd;Database=postgres;");
         conn.Open();
 
         // Start a transaction as it is required to work with result sets (cursors) in PostgreSQL
         NpgsqlTransaction tran = conn.BeginTransaction();
 
         // Define a command to call show_cities() procedure
         NpgsqlCommand command = new NpgsqlCommand("show_cities", conn);
         command.CommandType = CommandType.StoredProcedure;
 
         // Execute the procedure and obtain a result set
         NpgsqlDataReader dr = command.ExecuteReader();
 
         // Output rows 
         while (dr.Read())
            Console.Write("{0} \t {1} \n", dr[0], dr[1]);
 
         tran.Commit();  
         conn.Close();   
       }
     }

Output:

Important Note

PostgreSQL requires to start a transaction explicitly to work with result sets. The result sets are available until the end of transaction, and by default PostgreSQL works in auto-commit mode, so it drops all results set after the procedure call is completed, so they become unavailable to the caller.

Consider a procedure that returns multiple result sets to the caller:

    -- Procedure that returns multiple result sets (cursors)
   CREATE OR REPLACE FUNCTION show_cities_multiple() RETURNS SETOF refcursor AS $$
    DECLARE
      ref1 refcursor;           -- Declare cursor variables
      ref2 refcursor;                             
    BEGIN
      OPEN ref1 FOR SELECT city, state FROM cities WHERE state = 'CA';   -- Open the first cursor
      RETURN NEXT ref1;                                                                              -- Return the cursor to the caller
 
      OPEN ref2 FOR SELECT city, state FROM cities WHERE state = 'TX';   -- Open the second cursor
      RETURN NEXT ref2;                                                                              -- Return the cursor to the caller
    END;
    $$ LANGUAGE plpgsql;

The stored procedure returns 2 result sets. You can process the first result the same way as if the procedure returned a single result set, and then use NextResult(); method of the DataReader to switch to the next result set and so on:

   using System;
   using System.Data;
   using Npgsql;
 
   class Sample
   {
      static void Main(string[] args)
     {
         // Connect to PostgreSQL
         NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres; " + 
             "Password=pwd;Database=postgres;");
         conn.Open();
 
         // Start a transaction as it is required to work with cursors in PostgreSQL
         NpgsqlTransaction tran = conn.BeginTransaction();
 
         // Define a command to call stored procedure show_cities_multiple
         NpgsqlCommand command = new NpgsqlCommand("show_cities_multiple", conn);
         command.CommandType = CommandType.StoredProcedure;
 
         // Execute the stored procedure and obtain the first result set
         NpgsqlDataReader dr = command.ExecuteReader();
 
         // Output the rows of the first result set
         while (dr.Read())
             Console.Write("{0}\t{1} \n", dr[0], dr[1]);
 
         // Switch to the second result set
         dr.NextResult();
 
         // Output the rows of the second result set
         while (dr.Read())
            Console.Write("{0}\t{1} \n", dr[0], dr[1]);
 
         tran.Commit();
         conn.Close();
     }
   }

• PostgreSQL Articles and Reference
• How to Return a Result Set from a Stored Procedure in PostgreSQL
• PostgreSQL and C# - Npgsql .NET Data Provider - Getting Started
• Npgsql - User's Manual