PostgreSQL接口编程一：OLEDB－－PGNP驱动

51qsx

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