Sqlite3 .net操作以及编码示例

 
Sqlite3内部采用UTF8存储，但是为转成GB2312就必须调用编码函数，太麻烦了，于是今天琢磨出一种让sqlite3内部存储汉字采用GB2312的方法，将字段设为BLOB，然后保存汉字的GB2312编码的字节数组就OK了。但是把词库的所有字段从TEXT转换成BLOB,结果查询时用where word = ’hello’竟然无法查询出结果,随手改成where word like ’hello’竟然可以,估计是BLOB无法用"="查询。 

using System; using System.Text; using System.Data; using Finisar.SQLite; namespace SqliteConvert { /// <summary> /// Class1 的摘要说明。 /// </summary> class Class1 { /// <summary> /// 应用程序的主入口点。 /// </summary> [STAThread] static void Main(string[] args) { // // TODO: 在此处添加代码以启动应用程序 // //打开以前的库 SQLiteConnection sqliteConn = new SQLiteConnection(@"Data Source=d:projectDrEye.db;New=False;Compress=True;Version=3;"); sqliteConn.Open(); SQLiteDataAdapter sqliteDa = new SQLiteDataAdapter("select * from DrEye",sqliteConn); DataSet sqliteDs = new DataSet(); sqliteDa.Fill(sqliteDs); //创建新库 SQLiteConnection sqliteConnNew = new SQLiteConnection(@"Data Source=d:projectDrEye.new1.db;New=True;Compress=True;Version=3"); sqliteConnNew.Open(); //建表 SQLiteCommand sqliteCmdNew = sqliteConnNew.CreateCommand(); sqliteCmdNew.CommandText = "CREATE table DrEye (word TEXT Primary Key,explanation BLOB,InflectedForm BLOB)"; sqliteCmdNew.ExecuteNonQuery(); //插入数据 SQLiteDataAdapter sqliteDaNew = new SQLiteDataAdapter("select * from DrEye", sqliteConnNew); SQLiteCommandBuilder sqliteCbNew = new SQLiteCommandBuilder(sqliteDaNew); DataSet sqliteDsNew = new DataSet(); sqliteDaNew.Fill(sqliteDsNew); Console.WriteLine(sqliteDs.Tables[0].Rows.Count); Console.WriteLine(sqliteDsNew.Tables[0].Rows.Count); foreach(DataRow dr in sqliteDs.Tables[0].Rows) { DataRow sqliteDrNew = sqliteDsNew.Tables[0].NewRow(); sqliteDrNew[0] = dr[0]; sqliteDrNew[1] = EncodingConvert(dr[1].ToString()); if (dr[2].ToString() != "") { sqliteDrNew[2] = EncodingConvert(dr[2].ToString()); } sqliteDsNew.Tables[0].Rows.Add(sqliteDrNew); break; } sqliteDaNew.Update(sqliteDsNew); Console.WriteLine("Game over!"); Console.ReadLine(); } //将字符串转换成字节 static byte[] EncodingConvert(string utf8String) { byte[] ansiCode; ansiCode = Encoding.Default.GetBytes(utf8String); return ansiCode; } } }