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

阳光穿透心脏的1/2处 2022-02-17 00:45 148阅读 0赞

### 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);
                }
            }
        }
    }

执行结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpeWF6aGVuMjAxMQ_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpeWF6aGVuMjAxMQ_size_16_color_FFFFFF_t_70]: /images/20220217/480ad40542cb45d586baf7570961e6d9.png