Accentuation problem in postgresql with npgsql

Question

I have a database postgresql raised with encoding SQL_ASCII and template0. When I try to select lines with accents like NAY gives error, but if the records have no accent works normally.

Mistakes:

Error Parsing column 6 (sempresa=0 - String)}

Message = Unable to convert bytes [C3] to index 0 of the code page specified for Unicode.

I’m using npgsql and Dapper

<packages>
  <package id="Dapper" version="1.50.2" targetFramework="net45" />
  <package id="Npgsql" version="3.1.7" targetFramework="net45" />
</packages>

I tried to:

Pass by client encoding in connection string. I tried several encodings.

var sqlBuilder = new NpgsqlConnectionStringBuilder
{
    Host = host,
    Database = database,
    Username = user,
    Password = password,
    Pooling = false,
    ClientEncoding = "SQL_ASCII" 
    //Tentei "UNICODE", "utf8", "win-1252"
};

I also tried running a command before select to change the encoding. I tried several encodings:

 // Tentei: SQL_ASCII, win-1252, unicode
 connection.Execute("set client_encoding = 'SQL_ASCII'");

 var data = connection.Query<T>(strSQL);

Recreating the database with another encoding is not an option as the application will run in multiple banks already in production.

I’ve looked for other questions on the network, but nothing worked in my case.

Answer 1

After I contacted the NPG-SQL maintainers, version 3.1.8 added support for the attribute ClientEncoding, what solved the problem.

 var sqlBuilder = new NpgsqlConnectionStringBuilder
                {
                    Host = strLocal,
                    Database = strNome,
                    Username = strUser,
                    Password = strSenha,
                    Pooling = false,
                    Encoding = "windows-1252",
                    ClientEncoding = "sql-ascii"
                };

 string strConexao = sqlBuilder.ConnectionString;

However, it is recommended not to use the Encoding sql-ascii for new databases. My case was a case of legacy software, and there was no possibility to exchange the database Encoding.