Thread: PostgreSQL and C#

Le Mardi 9 Avril 2002 15:13, Daniel Morgan a écrit :
> I am interested in Platform Invoking in PostgreSQL using C#.
> Unfortunately, I cannot find any information about the various
> structures that are used in libpq to do this.  Like, what is PGConn
> structure defined as?

Dave Page, pgAdmin2 author (http://pgadmin.postgresql.org), is interested in
C# and (might be) working on this issue. Maybe you can contact him on
pgadmin-hackers@postgresql.org mailing list.

Best regards,
Jean-Michel POURE

> I really want to use C# and .NET to query PostgreSQL 7.2 databases?

Hi Daniel,

The way I have done it is by using ODBC. You will need to download:
odbc.net from the microsoft site (URL below) and the postgresql odbc driver
(URL below).

Some of the .net CLR database orientated classes are specific to
Microsoft's SQL Server so you can't use them. odbc.net provides a suite of
classes such as OdbcConnection, OdbcCommand and OdbcDataReader; which, so
far, have been really easy to use (see mini example below).


> Do I need to get the source the PostgreSQL database itself?

Absolutely not! I highly recommend you using odbc.net to connect to
postgresql, it is simple to use (and learn), fast and has, so far, been bug
free.

postgresql odbc site:
http://odbc.postgresql.org

odbc.net is here (watch out for the line wrap)

http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml

Mini Example:

string query = "SELECT * FROM testtable";
OdbcConnection odbcCon = new OdbcConnection("DSN=PostgreSQL");
OdbcCommand odbcCom = new OdbcCommand(query, odbcCon);
odbcCon.Open();
OdbcDataReader odbcReader =
odbcCom.ExecuteReader(CommandBehavior.CloseConnection);
Then you can start 'reading'.

Good luck,

..matthew

This email just got returned to me as a failed delivery so I am resending
it, apologies if anyone gets it twice.  Matthew

> I really want to use C# and .NET to query PostgreSQL 7.2 databases?

Hi Daniel,

The way I have done it is by using ODBC. You will need to download:
odbc.net from the microsoft site (URL below) and the postgresql odbc driver
(URL below).

Some of the .net CLR database orientated classes are specific to
Microsoft's SQL Server so you can't use them. odbc.net provides a suite of
classes such as OdbcConnection, OdbcCommand and OdbcDataReader; which, so
far, have been really easy to use (see mini example below).


> Do I need to get the source the PostgreSQL database itself?

Absolutely not! I highly recommend you using odbc.net to connect to
postgresql, it is simple to use (and learn), fast and has, so far, been bug
free.

postgresql odbc site:
http://odbc.postgresql.org

odbc.net is here:

http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml

Mini Example:

string query = "SELECT * FROM testtable";
OdbcConnection odbcCon = new OdbcConnection("DSN=PostgreSQL");
OdbcCommand odbcCom = new OdbcCommand(query, odbcCon);
odbcCon.Open();
OdbcDataReader odbcReader =
odbcCom.ExecuteReader(CommandBehavior.CloseConnection);
Then you can start 'reading'.

Good luck,

..matthew

Thanks for the help.  However, I'm more interested in a direct approach
from C# to the PostgreSQL client library.  Also, this must be able to
work under Windows, Linux, etc...

I discovered that pq.dll is the PostgreSQL client library under Windows
while libpq.so is library under Linux.  This was the cause of my
confusion because I couldn't find libpq.so under Windows.  Here is
snippet I used to pinvoke into pq.dll on cgwin.  Now, I can help work on
creating a provider for System.Data in Mono C#.  Work has already
started on System.Data classes by various people, including Rodrigo
Moya, the maintainer of Gnome-DB.

using System;
using System.Runtime.InteropServices;

namespace ConsoleApplication1
{
    /// <summary>
    /// Summary description for Class1.
    /// </summary>
    class Class1
    {
        [DllImport("pq.dll")]
        public static extern IntPtr PQconnectdb(String
conninfo);
        // PGconn *PQconnectdb(const char *conninfo)

        [DllImport("pq.dll")]
        public static extern void PQfinish(IntPtr conn);
        // void PQfinish(PGconn *conn)

        [DllImport("pq.dll")]
        public static extern IntPtr PQexec(IntPtr conn,
            String query);
        // PGresult *PQexec(PGconn *conn,    const char
*query);

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main(string[] args)
        {
            IntPtr dbconn;
            String sConnInfo;
            String sQuery;

            sConnInfo = "host=localhost dbname=test
user=danmorg password=viewsonic";
            sQuery = "insert into sometable " +
                     "(tid,tdesc) " +
                     "values('dan','morgan') ";

            dbconn = PQconnectdb(sConnInfo);

            if(dbconn == IntPtr.Zero)
                Console.WriteLine("dbconn is null");
            else
            {
                Console.WriteLine("assuming successful
connection");
                PQexec(dbconn,sQuery);
                PQfinish(dbconn);
            }
        }
    }
}

-----Original Message-----
From: Matthew Stanfield [mailto:matthew@propertyknowledge.com]
Sent: Tuesday, April 09, 2002 1:39 PM
To: Daniel Morgan
Cc: PostgreSQL General Mailing List
Subject: Re: [GENERAL] PostgreSQL and C#

This email just got returned to me as a failed delivery so I am
resending
it, apologies if anyone gets it twice.  Matthew

> I really want to use C# and .NET to query PostgreSQL 7.2 databases?

Hi Daniel,

The way I have done it is by using ODBC. You will need to download:
odbc.net from the microsoft site (URL below) and the postgresql odbc
driver
(URL below).

Some of the .net CLR database orientated classes are specific to
Microsoft's SQL Server so you can't use them. odbc.net provides a suite
of
classes such as OdbcConnection, OdbcCommand and OdbcDataReader; which,
so
far, have been really easy to use (see mini example below).


> Do I need to get the source the PostgreSQL database itself?

Absolutely not! I highly recommend you using odbc.net to connect to
postgresql, it is simple to use (and learn), fast and has, so far, been
bug
free.

postgresql odbc site:
http://odbc.postgresql.org

odbc.net is here:
http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.as
p?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml

Mini Example:

string query = "SELECT * FROM testtable";
OdbcConnection odbcCon = new OdbcConnection("DSN=PostgreSQL");
OdbcCommand odbcCom = new OdbcCommand(query, odbcCon);
odbcCon.Open();
OdbcDataReader odbcReader =
odbcCom.ExecuteReader(CommandBehavior.CloseConnection);
Then you can start 'reading'.

Good luck,

..matthew

Thanks for the help.  However, I'm more interested in a direct approach
from C# to the PostgreSQL client library.  Also, this must be able to
work under Windows, Linux, etc...
 
 

 

http://archives.postgresql.org

-- 
-------------------------------------------------------------------------
Medi Montaseri                               medi@CyberShell.com
Unix Distributed Systems Engineer            HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------

We are developing an application for leather industry using PostgreSQL with
PHP scripting. The program will be run from a central server and buyers and
sellers will login using internet connection and update/exchange information
through the system.

Some companies do not want to be connected to the internet throughout the
day. They want to use the system from a local server during the day and
update the data to the central server (and get the updated data from the
server) once or twice a day.

Can someone please advise me, how this can be accomplished? That is, to make
the two databases hand shake and exchange the changes effected since they
last met.

Best regards,


Siva,
Chennai, Tamil Nadu

I think this is a logic question they haven't worked through. Let's say
company A and B are using the on-line server and company C is not. All
three start the day the same way with company A offering 500 shoes for
sale. Company B likes the shoes and buys 400 of them. The database on
the central server now says there are 100 shoes left. But Company C,
which also likes the shoes, thinks there are 500 since they haven't
updated their version of the database. They put in an order for 200
shoes and only find out later they have been shipped 100, with 100 back
ordered.

This is just a simple example with three companies. Multiply that by
hundreds of transactions and hundreds of companies and you get a real
nightmare.

The solution is pretty simple. The companies who don't want to be
connected all day only connect when they want to conduct a transaction.
The data is updated the moment they log on, they conduct their purchase
and then they log off.
Frank Hilliard
http://frankhilliard.com/


Ma Siva Kumar wrote:

>We are developing an application for leather industry using PostgreSQL with
>PHP scripting. The program will be run from a central server and buyers and
>sellers will login using internet connection and update/exchange information
>through the system.
>
>Some companies do not want to be connected to the internet throughout the
>day. They want to use the system from a local server during the day and
>update the data to the central server (and get the updated data from the
>server) once or twice a day.
>
>Can someone please advise me, how this can be accomplished? That is, to make
>the two databases hand shake and exchange the changes effected since they
>last met.
>
>Best regards,
>
>
>Siva,
>Chennai, Tamil Nadu
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>

Fortunately, this particular situation involves simpler relations. It is more
like this.

Buying companies place orders to their supplier companies. Supplier companies
maintain the status of orders in the system. Buyer companies can login and
get information about the order status.

Thus, most of the update of information comes from the internal working of
supplier companies and the relationship is strictly one to one (what one
buyer buys from a supplier does not affect any one else in the system).

If everyone uses the central server to update the transactions that is ideal.
However, atleast in the initial stages, supplier companies may like to
conduct their day to day operations on the local server (due to connection
speeds, costs etc) and update the transactions to the central server several
times a day.

The information flow from the buyer is in the form of new orders, approval of
samples, payment information etc.

Hope now it is clearer. Even though there will be hundreds of companies in
the system, each one will have a strictly one to one relationship with
others.

What is the best possible way to do this using Postgres and PHP?

Best regards,


Siva,
Chennai, Tamil Nadu.



> I think this is a logic question they haven't worked through. Let's say
> company A and B are using the on-line server and company C is not. All
> three start the day the same way with company A offering 500 shoes for
> sale. Company B likes the shoes and buys 400 of them. The database on
> the central server now says there are 100 shoes left. But Company C,
> which also likes the shoes, thinks there are 500 since they haven't
> updated their version of the database. They put in an order for 200
> shoes and only find out later they have been shipped 100, with 100 back
> ordered.
>
> This is just a simple example with three companies. Multiply that by
> hundreds of transactions and hundreds of companies and you get a real
> nightmare.
>
> The solution is pretty simple. The companies who don't want to be
> connected all day only connect when they want to conduct a transaction.
> The data is updated the moment they log on, they conduct their purchase
> and then they log off.
> Frank Hilliard
> http://frankhilliard.com/
>
> Ma Siva Kumar wrote:
> >We are developing an application for leather industry using PostgreSQL
> > with PHP scripting. The program will be run from a central server and
> > buyers and sellers will login using internet connection and
> > update/exchange information through the system.
> >
> >Some companies do not want to be connected to the internet throughout the
> >day. They want to use the system from a local server during the day and
> >update the data to the central server (and get the updated data from the
> >server) once or twice a day.
> >
> >Can someone please advise me, how this can be accomplished? That is, to
> > make the two databases hand shake and exchange the changes effected since
> > they last met.
> >
> >Best regards,
> >
> >
> >Siva,
> >Chennai, Tamil Nadu
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Sun, 14 Apr 2002 07:29:12 +0530
Ma Siva Kumar <ma_sivakumar@yahoo.com> wrote:

> Fortunately, this particular situation involves simpler relations. It is more
> like this.
>
> Buying companies place orders to their supplier companies. Supplier companies
> maintain the status of orders in the system. Buyer companies can login and
> get information about the order status.
>
> Thus, most of the update of information comes from the internal working of
> supplier companies and the relationship is strictly one to one (what one
> buyer buys from a supplier does not affect any one else in the system).
>
> If everyone uses the central server to update the transactions that is ideal.
> However, atleast in the initial stages, supplier companies may like to
> conduct their day to day operations on the local server (due to connection
> speeds, costs etc) and update the transactions to the central server several
> times a day.
>
> The information flow from the buyer is in the form of new orders, approval of
> samples, payment information etc.
>
> Hope now it is clearer. Even though there will be hundreds of companies in
> the system, each one will have a strictly one to one relationship with
> others.
>
> What is the best possible way to do this using Postgres and PHP?
>
> Best regards,
>
>
> Siva,
> Chennai, Tamil Nadu.
>
<SNIP>

You might want to consider an email based system.

Your system will send email (which can be done via dial-up POP).
Send formated messages back and forth (something like EDI) using
maybe XML or a custom format.

I've done this before for customers who are paranoid about being on the
internet all of the time.  Its not hard.

While this is not really much help for PHP (as it web based), it is
just an idea.

GB