使用 Npgsql for Postgresql 的 C# 查询显示重复的结果和丢失的表数据

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

提问by Ashraf Abusada

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.

我正在检查 PostgreSQL 作为 SQLServer 的潜在替代品，我在 PostgreSQL 公共模式的测试数据库中创建了一个测试表，并将两行数据添加到测试表中。

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.

现在的问题是，当使用 NpgSQL.dll 从 C#.net 运行简单查询时，我得到了重复的结果，但并未显示所有表数据。

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;
            }
        }
    }
}

采纳答案by Steve

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.

重复内容的问题是由循环 usingWhile(dRead.Read())和循环使用表的 DataRows 引起的foreach。这有效地循环了您的数据两次。

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

如果要使用 aDataReader循环记录，则不需要 DataRow 和 DataTable，而是使用FieldCountDataReader的属性和当前记录的 DataReader 索引器。

// 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

相反，如果要循环遍历 DataTable 及其行，则需要使用 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());
    }
}

回答by smiech

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());
    }
}