Npgsql获取自定义函数(function)返回的数据集

1. 建表

 create table city (
  Id  int, 
  Name varchar(20)
 );

2.插入数据

 insert into city values(1,'BeiJing'),(2,'NewYork'),(3,'Hong kong'),(4,'ShaingHai');

3.自定义函数

create or replace function getCity() returns setof city as 
$$
begin
return query select * from city;
end;
$$
language plpgsql;

4.Npgsql获取数据集

using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;

namespace CurveTest
{
    public class City {
        public   int Id { get; set; }
        public string Name { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=localhost;Port=5433;User Id=postgres;Password=123456;Database=mydb;";
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            try
            {
                conn.Open();
                NpgsqlCommand cmd = new NpgsqlCommand("getCity", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                List<City> cities = new List<City>();
                using (NpgsqlDataReader rd = cmd.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        City city = new City();
                        if (ReadRecord(city, rd))
                        {
                            cities.Add(city);
                        }
                        else
                        {
                            Console.WriteLine("read record failed");
                        }
                    }
                }
                PrintCityInfo(cities);
            }
            finally
            {
                conn.Close();
            }
            Console.ReadLine();
        }

        static bool ReadRecord(object obj, NpgsqlDataReader reader)
        {
            try
            {
                PropertyInfo[] propertyInfos = obj.GetType().GetProperties();
                foreach (var info in propertyInfos)
                {
                    var value = ReadRecordColumn(reader, info.Name);
                    if (value != null)
                    {
                        info.SetValue(obj, value);
                    }
                }
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return false;
            }
        }

        static object ReadRecordColumn(NpgsqlDataReader reader, string name)
        {
            try
            {
                var ordinal = reader.GetOrdinal(name);
                if (reader.IsDBNull(ordinal))
                {
                    return null;
                }
                return reader.GetValue(ordinal);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return null;
            }
        }
        //打印信息
        static void PrintCityInfo(List<City> cities)
        {
            if (cities == null || cities.Count == 0)
                return;
            Console.WriteLine("Id  Name");
            foreach (var item in cities)
            {
                Console.WriteLine(" {0} {1}", item.Id, item.Name);
            }
        }
    }
}

    执行结果