work around the text field in wrong encoding in sqlite3 database

元々日本語で書こうと思いますが、昨日ツイッターで呟いてみたと自分の無力に感じます
ですから英語で書きます

The encoding of text field in sqlite3 database is utf-8.
But it seems some programs work on sqlite3 database with the text in the encoding other than utf-8.

To retrieve data from such database, using sqlite3 native api is not much challenge,
Using sqlite3_column_text could get the raw data regardless the encoding,
then using the retrieved string as the origin encoding is OK.

But there is a problem when working such database with sqlite.net.
Because sqlite.net regards fields with text type as utf-8 encoded strings,
so it retrieved the string and simply stored in System.String typed variable.
I have tried many way to fix the string but failed.
Finally I found it's because some chars not in the range of utf-8 encoding are unrecoverable destroyed.

Then, it must be some way to work around such situation.
And this is what I found:

Take the database created by tsk(天則観) as example.
to get the string stored in p1name field and p2name field,
make the SQL statement like this:

select cast(p1name as blob), cast(p2name as blob) from trackrecord123

Then, use SQLiteDataReader.GetBytes instead of SQLiteDataReader.GetString to retrieve the raw data.
like this:

SQLiteDataReader sqldr = sqlcmd.ExecuteReader();

while(sqldr.Read()){

	for(int i = 0; i < 2; ++i)

	{

		int len = (int) sqldr.GetBytes(i, 0, null, 0, 0);

		byte[] s = new byte[len];

		len = (int) sqldr.GetBytes(i, 0, s, 0, len);

		string ss = Encoding.GetEncoding("shift-jis").GetString(s, 0, len);

		Console.WriteLine("{0}", ss);

	}

}

sqldr.Close();

The code becomes longer then using GetString, anyway the problem is solved.

なお問題があれば、ツイッターでsorayukinoyumeへ気軽にどうぞ