How can get correct Column Name from Database with Npgsql?

Question

This is my table in database:

CREATE TABLE public.test
(
  id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
  hc character varying(30),
  "Hc" character varying(30),
  "HC" character varying(30),
  f character varying(30),
  "F" character varying(30),
  f1 character varying(30),
  te numeric(2,2),
  CONSTRAINT test_pkey PRIMARY KEY (id)
)

If i get a Table Definition by Npgsql from vb.net:

select * from test where null = null

Result: some colums had changed name: Ex: Hc => Hc1,HC => HC2

How can get correct Column Name from Database with Npgsql?

Answer 1

It seems that you (directly or indirectly) use DbDataAdapter.Fill which renames columns as follows if necessary:

If the DbDataAdapter encounters duplicate columns while populating a DataTable, it generates names for the subsequent columns, using the pattern "columnname1", "columnname2", "columnname3", and so on.

Apparently, this deduplication process treats column names in a case-insensitive way which is why the columns also get renamed in your example. There is no way to turn off this behavior directly (see AdapterUtil.cs,2402).

A work around would be to use an additional SqlCommand and use SqlDataReader.GetName to obtain the exact column names and then change the columns of the DataTable accordingly. This could be done as follows:

Dim query = "select * from test where null = null"

' setup connection
Dim connection As New NpgsqlConnection(
    String.Format("Server={0};Port={1};Database={2};User Id={3};Password={4};",
                  host, port, database, user, password))
connection.Open()

' fill data table from query
Dim table As New DataTable
Using adapter = New NpgsqlDataAdapter(query, connection)
    adapter.Fill(table)
End Using

' correct column names
Using command = New NpgsqlCommand(query, connection)
    Using reader = command.ExecuteReader()
        For i = 0 To table.Columns.Count - 1
            table.Columns(i).ColumnName = reader.GetName(i)
        Next
    End Using
End Using

' display table in DataGridView
view.DataSource = table