1介绍
@H_502_2@PGNP Native Provider是以OLEDB接口访问Postgresql数据库的驱动程序。以下简称PGNP，Postgresql数据库以下简称pg。
@H_502_2@PGNP是pg数据库的OLEDB接口驱动程序，他介于微软OLEDB和ADO.NET之OLEDB与Postgresql libpq库接口之间，实现了大多数OLEDB接口，并使用pg的libpq访问pg数据库。PGNP可以为.NET和NATIVE的32/64位应用程序提供支持访问pg。
@H_502_2@这是一个商业软件，Business license要390$。
@H_502_2@从http://pgoledb.com/可以下载使用版PGNP-1.3.0.2251.exe
@H_502_2@2安装
@H_502_2@点击PGNP-1.3.0.2251.exe开始安装，按下一步即可完成。
@H_502_2@3用psql连到postgresql，psql中命令\i sql_script_file_name即可
@H_502_2@sql_script_file_name文件中内容如下：
@H_502_2@--建模式、表、插入记录、建函数
@H_502_2@-- Create schema for PGNP samples
@H_502_2@-- DROP SCHEMA pgnp_samples;
@H_502_2@CREATE SCHEMA pgnp_samples AUTHORIZATION postgres;
@H_502_2@GRANT ALL ON SCHEMA pgnp_samples TO postgres;
@H_502_2@SET search_path='pgnp_samples';
@H_502_2@--删除photo类型字段
@H_502_2@-- DROP TABLE pgnp_samples.contact;
@H_502_2@CREATE TABLE contact
@H_502_2@(
@H_502_2@ contact_id bigint not null,
@H_502_2@ fname character varying(64),
@H_502_2@ lname character varying(64),
@H_502_2@ revenue double precision,
@H_502_2@-- photo lo,
@H_502_2@ created_date timestamp without time zone NOT NULL DEFAULT now(),
@H_502_2@ modified_date timestamp without time zone NOT NULL DEFAULT now(),
@H_502_2@ CONSTRAINT pk_contact_id PRIMARY KEY (contact_id)
@H_502_2@);
@H_502_2@INSERT INTO contact(contact_id,fname,lname,revenue) VALUES (1,'James','Smith',20000.0),(2,'Sue','McMartin',35000.0);
@H_502_2@-- DROP TABLE pgnp_samples."group";
@H_502_2@CREATE TABLE "group"
@H_502_2@(
@H_502_2@ group_id bigint not null,
@H_502_2@ group_name character varying(128),
@H_502_2@ region uuid,
@H_502_2@ CONSTRAINT pk_group_id PRIMARY KEY (group_id)
@H_502_2@);
@H_502_2@INSERT INTO "group"(group_id,group_name,region) VALUES (1,'EMEA','00000000000000000000000000000001'),'NA','00000000000000000000000000000002');
@H_502_2@-- DROP FUNCTION pgnp_samples.ContactsLike(character varying(64));
@H_502_2@CREATE OR REPLACE FUNCTION ContactsLike(character varying(64)) RETURNS bigint AS 'SELECT count(1) FROM contact WHERE lname ~~* $1;' LANGUAGE sql;
@H_502_2@--as后边加空格
@H_502_2@DROP FUNCTION pgnp_samples.sptest2(integer);
@H_502_2@CREATE OR REPLACE FUNCTION pgnp_samples.sptest2(IN integer)
@H_502_2@ RETURNS TABLE(f1 integer,f2 text) AS
@H_502_2@$BODY$
@H_502_2@ SELECT $1,CAST($1 AS text) || ' is text'
@H_502_2@ UNION ALL
@H_502_2@ SELECT $1*2,CAST($1 AS text) || ' is text too'
@H_502_2@$BODY$
@H_502_2@ LANGUAGE 'sql';
@H_502_2@
@H_502_2@-- DROP FUNCTION pgnp_samples.GetMultipleResults();
@H_502_2@CREATE OR REPLACE FUNCTION GetMultipleResults() RETURNS SETOF refcursor AS
@H_502_2@'DECLARE refContact refcursor; refGroup refcursor;
@H_502_2@BEGIN
@H_502_2@ OPEN refContact FOR SELECT * FROM contact; RETURN NEXT refContact;
@H_502_2@ OPEN refGroup FOR SELECT * FROM "group"; RETURN NEXT refGroup;
@H_502_2@ RETURN;
@H_502_2@END;' LANGUAGE plpgsql;
@H_502_2@-- DROP TABLE arrays
@H_502_2@CREATE TABLE arrays
@H_502_2@(
@H_502_2@ id serial NOT NULL,
@H_502_2@ test1d character varying(15)[],
@H_502_2@ test2d numeric(7,3)[][],
@H_502_2@ test3d integer[][][],
@H_502_2@ CONSTRAINT pk_arrays_id PRIMARY KEY (id)
@H_502_2@);
@H_502_2@INSERT INTO arrays(test1d,test2d,test3d)
@H_502_2@VALUES('{"New York",Paris,Bejing}','{{3000.146,17.101,0.000,0.667},{3.14159,2.71,-1.778,128.3}}',
@H_502_2@'{{3,16,9,22,15,100},{20,8,21,14,2,{7,25,13,1,19,{24,12,5,18,6,{11,4,17,10,23,100}}')
@H_502_2@4. C#代码如下：
@H_502_2@ public partial class Program
@H_502_2@ {
@H_502_2@ static readonly string connStr = "Provider=PGNP.1;Data Source=localhost;Initial Catalog=postgres;User ID=postgres;Password=12345;Extended Properties=\"NESTED_TRANS=ON;SEARCH_PATH=pgnp_samples,public;PORT=5432\"";
@H_502_2@ static int Main(string[] args)
@H_502_2@ {
@H_502_2@ int error_count = 0;
@H_502_2@ error_count += Get_Arrays();
@H_502_2@ if (error_count > 0)
@H_502_2@ ConsoleWriteError("Errors count: " + error_count,"");
@H_502_2@ else
@H_502_2@ ConsoleWriteSuccess("All samples ran successfully!");
@H_502_2@ return error_count;
@H_502_2@ }
@H_502_2@ static void ConsoleWriteSampleHeader(String sample_hdr)
@H_502_2@ {
@H_502_2@ Console.ForegroundColor = ConsoleColor.Blue;
@H_502_2@
@H_502_2@ Console.WriteLine(sample_hdr);
@H_502_2@ }
@H_502_2@ static void ConsoleWriteMessage(String msg)
@H_502_2@ {
@H_502_2@ ConsoleWriteMessage(msg," ");
@H_502_2@ }
@H_502_2@ static void ConsoleWriteMessage(String msg,String blank)
@H_502_2@ {
@H_502_2@ Console.ForegroundColor = ConsoleColor.Gray;
@H_502_2@ Console.WriteLine(blank + msg);
@H_502_2@ }
@H_502_2@ static void ConsoleWriteError(String error,String optionalStmt)
@H_502_2@ {
@H_502_2@ Console.ForegroundColor = ConsoleColor.Red;
@H_502_2@ Console.WriteLine("** " + error);
@H_502_2@ if (optionalStmt.Length > 0)
@H_502_2@ {
@H_502_2@ Console.ForegroundColor = ConsoleColor.Gray;
@H_502_2@ Console.WriteLine(" Last stmt: " + optionalStmt);
@H_502_2@ }
@H_502_2@ }
@H_502_2@ static void ConsoleWriteSuccess(String success_msg)
@H_502_2@ {
@H_502_2@ Console.ForegroundColor = ConsoleColor.Green;
@H_502_2@ Console.WriteLine(success_msg);
@H_502_2@ }
@H_502_2@
@H_502_2@ static public int Get_Arrays()
@H_502_2@ {
@H_502_2@ String lastStmt = "";
@H_502_2@ try
@H_502_2@ {
@H_502_2@ OleDbConnection conn = new OleDbConnection(connStr);
@H_502_2@ conn.Open();
@H_502_2@ // Read records from pgnp_samples.contact table.
@H_502_2@ ConsoleWriteSampleHeader("Reading array elements from database.");
@H_502_2@ OleDbCommand cmd = conn.CreateCommand();
@H_502_2@ cmd.CommandText = lastStmt = "SELECT test1d,test3d FROM arrays";
@H_502_2@ OleDbDataReader dr = cmd.ExecuteReader();
@H_502_2@ while (dr.Read())
@H_502_2@ {
@H_502_2@ for (int fieldIndex = 0; fieldIndex < 3; fieldIndex++)
@H_502_2@ {
@H_502_2@ if (dr.IsDBNull(fieldIndex))
@H_502_2@ {
@H_502_2@ ConsoleWriteMessage(String.Format("{0}: [NULL]",dr.GetName(fieldIndex)));
@H_502_2@ continue;
@H_502_2@ }
@H_502_2@ PrintArrayElements(dr.GetName(fieldIndex),dr.GetValue(fieldIndex) as Array);
@H_502_2@ }
@H_502_2@ }
@H_502_2@ }
@H_502_2@ catch (Exception ex)
@H_502_2@ {
@H_502_2@ ConsoleWriteError(ex.Message,lastStmt);
@H_502_2@ return 1;
@H_502_2@ }
@H_502_2@ return 0;
@H_502_2@ }
@H_502_2@ static public void PrintArrayElements(string fieldName,Array field)
@H_502_2@ {
@H_502_2@ ConsoleWriteMessage(fieldName + ":"," ");
@H_502_2@ switch (field.Rank)
@H_502_2@ {
@H_502_2@ case 1:
@H_502_2@ for (int i = field.GetLowerBound(0); i < field.GetLowerBound(0) + field.GetLength(0); i++)
@H_502_2@ {
@H_502_2@ object x = field.GetValue(i);
@H_502_2@ ConsoleWriteMessage(String.Format("{0}>{1}",i,x));
@H_502_2@ }
@H_502_2@ break;
@H_502_2@ case 2:
@H_502_2@ for (int j = field.GetLowerBound(1); j < field.GetLowerBound(1) + field.GetLength(1); j++)
@H_502_2@ {
@H_502_2@ for (int i = field.GetLowerBound(0); i < field.GetLowerBound(0) + field.GetLength(0); i++)
@H_502_2@ {
@H_502_2@ object x = field.GetValue(i,j);
@H_502_2@ ConsoleWriteMessage(String.Format("({0},{1}) > {2}",j,x));
@H_502_2@ }
@H_502_2@ }
@H_502_2@ break;
@H_502_2@ case 3:
@H_502_2@ for (int k = field.GetLowerBound(2); k < field.GetLowerBound(2) + field.GetLength(2); k++)
@H_502_2@ {
@H_502_2@ for (int j = field.GetLowerBound(1); j < field.GetLowerBound(1) + field.GetLength(1); j++)
@H_502_2@ {
@H_502_2@ for (int i = field.GetLowerBound(0); i < field.GetLowerBound(0) + field.GetLength(0); i++)
@H_502_2@ {
@H_502_2@ object x = field.GetValue(i,k);
@H_502_2@ ConsoleWriteMessage(String.Format("({0},{1},{2}) > {3}",k,x));
@H_502_2@ }
@H_502_2@ }
@H_502_2@ }
@H_502_2@ break;
@H_502_2@ }
@H_502_2@ }
@H_502_2@ }
@H_502_2@5.执行结果如下：
@H_502_2@
