C # query using Npgsql for Postgresql shows duplicate results and missing table data
I am testing PostgreSQL as a potential replacement for SQLServer, I created a test table in a test database in the PostgreSQL public schema and added two data tables to the test table.
Now the problem is doing a simple query from C # .net using NpgSQL.dll. I am getting duplicate results, not all table data.
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;
}
}
}
}
source to share
The duplicate content issue is caused by a loop using While(dRead.Read())
and a loop over the table's DataRows using foreach
. This effectively repeats two times over your data.
If you want to use DataReader
to loop through records, you don't need DataRow and DataTable, but use property FieldCount
for DataReader and DataReader for 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 the DataTable and its rows, you need to loop with 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());
}
}
source to share
In your original approach, for each row, you are showing your columns up to the column with index = row count. I believe this is not what you wanted to do. You have to display each column per row, for example:
Instead
foreach(DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Console.Write("{0} \t \n", row[i].ToString());
}
}
Using:
foreach(DataRow row in dt.Rows)
{
for (int i = 0; i < row.ItemArray.Length; i++)
{
Console.Write("{0} \t \n", row.ItemArray[i].ToString());
}
}
source to share