[CSharpTips]C#读取SQLite数据库中文乱码

C#读取SQLite数据库中文乱码

C#在读取C++写入数据的Sqlite数据库中的Text内容时，会出现乱码，因为C++默认编码格式为GB2312，而Sqlite编码格式为UTF-8，存入时不统一就会出现乱码

using System;

using System.Data;

using System.Data.SQLite;

using System.Text;
namespace SQLiteSamples

{

class Program

{

//数据库连接

SQLiteConnection m_dbConnection;
static void Main(string[] args)

{

Program p = new Program();

//sql中使用hex(Text)将Text转换为16进制字符串

DataTable table = p.GetData("select hex(Name) from highscores");

var name = table.Rows[0][0].ToString();

byte[] byteresult = p.HexStringToByteArrart(name);

//读取C++写入的sqlite数据库中的Text，将byte数组解析为GB2312编码字符串

//var result = Encoding.GetEncoding("GB2312").GetString(byteresult);

//将byte数组解析为UTF-8编码字符串

var result = Encoding.GetEncoding("UTF-8").GetString(byteresult);

Console.WriteLine(result);

Console.ReadLine();
}

public Program()

{

createNewDatabase();

connectToDatabase();

createTable();

fillTable();

printHighscores();

}
//创建一个空的数据库

void createNewDatabase()

{

SQLiteConnection.CreateFile("MyDatabase.sqlite");

}
//创建一个连接到指定数据库

void connectToDatabase()

{

m_dbCOnnection= new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");

m_dbConnection.Open();

}
//在指定数据库中创建一个table

void createTable()

{

string sql = "create table highscores (name varchar(20), score int)";

SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

command.ExecuteNonQuery();

}
//插入一些数据

void fillTable()

{

string sql = "insert into highscores (name, score) values ('张三', 3000)";

SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('李四', 6000)";

command = new SQLiteCommand(sql, m_dbConnection);

command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('王五', 9001)";

command = new SQLiteCommand(sql, m_dbConnection);

command.ExecuteNonQuery();

}
//使用sql查询语句，并显示结果

void printHighscores()

{

string sql = "select * from highscores order by score desc";

SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

SQLiteDataReader reader = command.ExecuteReader();

while (reader.Read())

Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);

//Console.ReadLine();

}

///

/// 将查询结果填充至DataTable

///

///

///

DataTable GetData(string sql)

{

DataTable dt = new DataTable();

lock (m_dbConnection)

{

SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, m_dbConnection);

adapter.Fill(dt);

return dt;

}

}

///

/// 将16进制字符串转换为byte数组

///

///

///

byte[] HexStringToByteArrart(string input)

{

input = input.Replace(" ", "").Trim().ToUpper();

byte[] buffer = new byte[input.Length / 2];

for (int i = 0; i
{

buffer[i / 2] = (byte)Convert.ToByte(input.Substring(i, 2), 16);

}

return buffer;

}

}

}

　　

[CSharpTips]C#读取SQLite数据库中文乱码的相关教程结束。