NickName:Asfand Iqbal Ask DateTime:2013-04-15T23:15:08

Retrieve data from PostgreSQL Database using ADO.Net “System.Data.Odbc” (VB.Net)

I have been asked to do some research on PostgreSQL and after running the following piece of code system returns two entries (Unnamed Portal 1 and Unnamed Portal 2) when we read DBDataReader object, however I would like to read all the recods from the these two portals and then load my business object with the information as each portal has more than 5 records in tables.

After exploring various pages on the internet I came across that there is a Third Party library ("NpgSQL" > NpgsqlDataReader ) offers this functionality and it is free. I have plugged their library into my solution and it works as per expections. However, I don't want to use this third party software and just wondering if there is a way I can read records from these two portals then it would be great.

Please note that you can give us a solution in c# as I am familiar with that. Thanks.

Code Snippet that I am currently using to retrieve data

Dim oConnection As DbConnection = DbProviderFactories.GetFactory(CONST_PROVIDER_ODBC).CreateConnection
    oConnection.ConnectionString = "Valid connection string goes here"
    oConnection.Open()

    ' Start a transaction as it is required to work with cursors in PostgreSQL
    Dim tran As DbTransaction = oConnection.BeginTransaction

    ' Define a command to call stored procedure show_cities_multiple
    Dim command As DbCommand = DbProviderFactories.GetFactory("System.Data.Odbc").CreateCommand
    command.CommandText = "SELECT MyTestFunction()"
    command.CommandType = CommandType.StoredProcedure
    command.Connection = oConnection
    command.Transaction = tran

    ' Execute the stored procedure and obtain the first result set
    Dim dr As DbDataReader = command.ExecuteReader()

    ' Output the rows of the first result set
    While dr.Read()

    'This is where system show Unnamed Portal 1 or Unnamed portal 2 at the second time.
        MsgBox(String.Format("{0}", dr(0)))
    End While

    tran.Commit()

-- Function to retrieve data.

CREATE OR REPLACE FUNCTION MyTestFunction() RETURNS SETOF refcursor AS $$
DECLARE
  ref1 refcursor;           -- Declare cursor variables
  ref2 refcursor;                             
BEGIN
  OPEN ref1 FOR SELECT * FROM MyTable1;
  RETURN NEXT ref1;                                                                              -- Return the cursor to the caller

  OPEN ref2 FOR SELECT * FROM MyTable2;
  RETURN NEXT ref2;                                                                              -- Return the cursor to the caller
END;
$$ LANGUAGE plpgsql;

Environment I am currently working on is following:-

32 Bit Machine.
Visual Studio 2010 + SP1
ODBC Prodiver: PostgreSQL Unicode 9.01.02.00
ADO.Net (System.Data.Odbc)

Certainly, I am looking forward to hear any feedback and your help would be much appreciated.

Copyright Notice：Content Author:「Asfand Iqbal」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/16018791/retrieve-data-from-postgresql-database-using-ado-net-system-data-odbc-vb-net