C# Query using Npgsql for Postgresql shows duplicate results and missing table data

Question

I am checking out PostgreSQL as potential substitute to SQLServer, I created a test table in a test database in PostgreSQL public schema and added two rows of data to the test table.

Now the problem is when running simple query from C#.net using NpgSQL.dll I get duplicate results, and not all table data is shown.

Here is the code I used:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Npgsql;


namespace PlayingWithPostgres
{
    class Program
    {
        static void Main(string[] args)
        {
            // creating the connection string (Server, Port, User id, password, database)
            string conStr = "Server=127.0.0.1; Port=5432; User Id=postgres; Password=Sada1973; Database=CarsTestDB;";
            NpgsqlConnection conn = new NpgsqlConnection(conStr);
            string comStr = "Select * FROM \"CarsTable\";";
            NpgsqlCommand com = new NpgsqlCommand(comStr, conn);
            NpgsqlDataAdapter ad = new NpgsqlDataAdapter(com);
            DataTable dt = new DataTable();
            Console.WriteLine("Conection to server established successfuly \n");
            // check if connection is open or not
            if(conn != null && conn.State == ConnectionState.Open)
            {
                Console.WriteLine("Connection Open");
                conn.Close();
            }
            else
            {
                conn.Open();
            }

            // Fill data table with data and start reading
            ad.Fill(dt);
            NpgsqlDataReader dRead = com.ExecuteReader();

            try
            {
                Console.WriteLine("Contents of table in database: \n");
                while (dRead.Read())
                {
                    foreach(DataRow row in dt.Rows)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            Console.Write("{0} \t \n", row[i].ToString());
                        }
                    }
                }
            }
            catch (NpgsqlException ne)
            {
                Console.WriteLine("Problem connecting to server, Error details {0}", ne.ToString());
            }
            finally
            {
                Console.WriteLine("Closing connections");
                dRead.Close();
                dRead = null;
                conn.Close();
                conn = null;
                com.Dispose();
                com = null;
            }
        }
    }
}

Answer 1

The problem of duplicate content is caused by the loop using While(dRead.Read()) and the loop over the DataRows of the table using the foreach. This effectively loops two times on your data.

If you want to use a DataReader to loop over the records then you don't need a DataRow and a DataTable, but use the FieldCount property of the DataReader and the DataReader indexer for the current record.

// Not needed
// ad.Fill(dt);
NpgsqlDataReader dRead = com.ExecuteReader();

while (dRead.Read())
{
   for(int i = 0; i < dRead.FieldCount; i++)
       Console.Write("{0} \t \n", dRead[i].ToString());
}

instead if you want to loop over a DataTable and its rows you need to loop using the Columns.Count

ad.Fill(dt);
// Not needed
// NpgsqlDataReader dRead = com.ExecuteReader();
foreach(DataRow row in dt.Rows)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        Console.Write("{0} \t \n", row[i].ToString());
    }
}

Answer 2

In your initial approach for every row you are displaying its columns up to column with index = row count. I believe that is not what you wanted to do. You should display every column per row like:

Instead of

foreach(DataRow row in dt.Rows)
{
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        Console.Write("{0} \t \n", row[i].ToString());
    }
}

Use:

foreach(DataRow row in dt.Rows)
{
    for (int i = 0; i < row.ItemArray.Length; i++)
    {
        Console.Write("{0} \t \n", row.ItemArray[i].ToString());
    }
}