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

 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.执行结果如下：