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);
}
}
}
}
执行结果