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 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,

CONSTRAINT pk_group_ID PRIMARY KEY (group_ID)

);

INSERT INTO "group"(group_ID,group_name,region) VALUES (1,'EMEA','00000000000000000000000000000001'),'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,100},{20,8,21,14,2,{7,25,13,1,19,{24,12,5,18,6,{11,4,17,10,23,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,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}",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,k);

ConsoleWriteMessage(String.Format("({0},{1},{2}) > {3}",k,x));

}

}

}

break;

}

}

}

5.执行结果如下：

总结

以上是内存溢出为你收集整理的PostgreSQL接口编程一：OLEDB－－PGNP驱动全部内容，希望文章能够帮你解决PostgreSQL接口编程一：OLEDB－－PGNP驱动所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。