Change encoding of a list of objects

c# character-encoding dapper mysql sql-server

Question

I have a program which using Dapper read in data from SQL Server and then save the data into another MySQL database. What it is doing is:

  1. Read in data from SQL server as a list of objects e.g. List<AKindOfObject> = read in data from SQL Server
  2. Open a connection to MySQL, save the List<AKindOfObject>

The problem is some property of AKindOfObject is saved as Chinese in original database. When the data is transferred to the MySQL database, those Chinese characters are shown as "?".

Is there any way to change the encoding of List<AKindOfObject> to gb2312 in C# code?

Expert Answer

I suspect what is happening here is simply: your database has non-unicode columns, and you are trying to store unicode data. Unrepresentable characters will indeed be greeked. The correct fix there is: make sure your column is unicode. Messing with column encodings could work, but damn that's a lot of work compared to just using unicode.


Dapper passes things "as is" to ADO.NET; what the provider does is up to the provider. There is a way to tell it to choose between ANSI and Unicode when sending data into the database, though - via DbString, i.e.

conn.Execute(sql, new {
    id, name,
    desc = new DbString { IsAnsi = true, Value = desc }
});

This also allows you to control the length etc. However, we are not controlling the encoding here; the encoding is usually a property of the database itself, or of the provider. If MySQL has some bespoke ways of controlling that in ADO.NET, I'm "all ears", but first: you need to get it working in raw ADO.NET.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why