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

Question

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.

If you're using C# I strongly recommend that you use nPgSQL. It's much better supported than psqlODBC, and is no more "third party" than psqlODBC its self is. They're both projects outside the PostgreSQL core that're maintained by different teams. BTW, thanks for the good, detailed question. — Craig Ringer
– Craig Ringer, Commented Apr 15, 2013 at 23:49
Thank you Craig for your input. I must confess a mistake as I am already using npgSQL library in my solution and mistakenly wrote "PostgreSQL Unicode" in this question. — Asfand Iqbal
– Asfand Iqbal, Commented Apr 16, 2013 at 8:29
Thank you Craig for your input. <br> I have managed to implement npgSQL library in my solution and it seems to work fine. Had to make some changes in my Data Access Layer (DAL) as you may have noticed that we are using the DBProviderFactories to establish connectivity of two different databases (SQL Server & PostgreSQL). <br> With regards to your appreciation towards the detailed question, I think it helps other developers to understand the development environment and nature of the question. This may lead to drastically/completely change the process of how to deal/resolve issue. — Asfand Iqbal
– Asfand Iqbal, Commented Apr 16, 2013 at 8:35
Hi Craig, After reading your profile information, it is evident that you are the most an appropriate person to ask about how can we deal records paging at database level in PostGreSQL? Here what I mean by records paging is assume we have 500 records in the database against the customer and I only want to get 50 records at a time.User will have a navigation control on the Form to move backward and forward. I have already developed a solution in SQL Server however not sure how we can do it in PostgreSQL. Your help will be much appreciated. Thanks — Asfand Iqbal
– Asfand Iqbal, Commented Apr 16, 2013 at 8:41
@AsfandIbqal It's best to post a new question for a new problem. Link back to this one if you don't want to repeat all the background. BTW I haven't posted my suggestion as an answer to this one because it's still quite possible someone will have an answer that uses ODBC / ADO.NET like you asked. Also I'm really not the most appropriate person to ask, I haven't done tons with webapps - and pagination is a pain at the best of times. — Craig Ringer
– Craig Ringer, Commented Apr 16, 2013 at 9:53

Craig Ringer · Accepted Answer · 2013-04-16 10:47:37Z

1

psqlODBC is less used and receives less maintenance than nPgSQL.

Both are "third party" in the sense that they're maintained outside the core PostgreSQL code. So are other tools considered quite important for Pg, like PgBouncer, pgbarman, repmgr, PgAgent-III, Slony-I, Bucardo, Skytools/Londiste and more.

In general I would recommend using nPgSQL over psqlODBC when workinging .NET. nPgSQL supports ADO.NET and Entity Framework so you shouldn't have to change tons to use it directly.

answered Apr 16, 2013 at 10:47

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related