Npgsql

初始化
NpgsqlConnection()

// Connect to a PostgreSQL database
          NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres; " + 
             "Password=pwd;Database=postgres;");
          conn.Open();

NpgsqlCommand(string cmdText)

查詢返回單行

          // 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);

ExecuteScalar()
執行查詢，並在由該查詢返回的結果集返回第一行的第一列。額外的列或行被忽略。

查詢

// 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]);

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的創建自動遞增的主鍵

方法1:

CREATE TABLE test_b 
( 
  id serial PRIMARY KEY, 
  name character varying(128) 
); 

方法2:

CREATE TABLE test_c 
( 
  id integer PRIMARY KEY, 
  name character varying(128) 
);  

CREATE SEQUENCE test_c_id_seq 
    START WITH 1 
    INCREMENT BY 1 
    NO MINVALUE 
    NO MAXVALUE 
    CACHE 1;

alter table test_c alter column id set default nextval('test_c_id_seq');