How do I set encoding in an NpgsqlConnection

I have a PostgreSQL database, which uses character encoding WIN1252.

When querying the database, some records will produce an error when trying to read the data, because it is trying to convert it to UTF8. This happens on some foreign names containing certain non-Latin characters.

The error is:

ERROR: 22P05: character with byte sequence 0x81 in encoding "WIN1252" has no equivalent in encoding "UTF8"

It happens when I call Read() on the NpgsqlDataReader.

My connection is defined as:

new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=xyz;User Id=****;Password=****;");

What can I do to read this data using C#?

Solution

I've managed to solve the problem. There is no way of setting the property in the connection string or any of the properties of the NpgsqlConnection or NpgsqlCommand.

However, I was able to set the value of client_encoding in a query. So directly after opening the connection I first executed the (non)query:

set client_encoding = 'WIN1252'

After that, any subsequent command on the same connection used the proper encoding and returned the results without complaints.