postgres npgsql with c# - trying to write special characters to an ascii encoded database

I am using th开发者_运维技巧e open source library npgsql http://pgfoundry.org/projects/npgsql/ to manipulate third party data on a postgres database. The database is encoded SQL_ASCII.

If I run a query using a standard c# string like

INSERT INTO CITIES(cityname) values("Köln")

the value that shows up in the database looks more like: пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ

e.g. scrambled. I've spent many hours trying to use different string encodings etc with no success

SQL_ASCII means you turn off the encoding functionality in the database. Thus it's up to you to make sure all your clients use the same encoding. I believe npgsql will always use UTF8, and that this cannot be changed.

So there is no "the value that shows up in the database" - it will be depending on what encoding the client that talks to the database is in.

The best way to fix it is to set an actual encoding in the database, and set it to something useful - such as UTF8 or LATIN1.