Differences

This shows you the differences between two versions of the page.

postgresql:npgsql_cs_result_sets [December 28, 2011 6:13 am]
sqlines
postgresql:npgsql_cs_result_sets [December 28, 2011 6:28 am] (current)
sqlines
Line 4: Line 4:
  * [[/postgresql/how-to/return_result_set_from_stored_procedure|How to Return a Result Set from a Stored Procedure in PostgreSQL]]   * [[/postgresql/how-to/return_result_set_from_stored_procedure|How to Return a Result Set from a Stored Procedure in PostgreSQL]]
 +
 +===== Query - Working with a Single Row Result Set in C# =====
 +
 +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:
 +
 +<code sql>
 +   -- Query always return 1 row and 1 column (if the table exists, and there are no other system errors)
 +   SELECT COUNT(*) FROM cities;
 +</code>
 +
 +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:
 +
 +<code csharp>
 +   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();
 +     }
 +   }
 +</code>
 +
 +**Note**. If the query returns multiple rows and columns, //ExecuteScalar// method returns the value of the first column in the first row.
===== Query - Working with a Result Set in C# ===== ===== Query - Working with a Result Set in C# =====
Line 53: Line 93:
| Houston | TX | | Houston | TX |
| St.Louis | MO | | St.Louis | MO |
 +
===== Stored Procedure - Working with a Single Result Set in C# ===== ===== Stored Procedure - Working with a Single Result Set in C# =====