C# .net SQLite存取GBK格式字符串

前一阵接手了一个项目，是一个读取Excel的数据，然后导出到SQLite数据库中。听起来是很简单，可是仍旧遇到了不少问题。其实困扰我好久的问题就是如何存GBK编码的字符串到SQLite中。

跟我对接的Android那边需要读这个db文件，链接数据库的时候用的GBK编码，之前的导出软件是用的Delphi写的。

所以我这边必须用C#存GBK。一开始我尝试了跟多次在代码中先转UTF-8到GBK格式，再存进数据库，不管怎么转，只要存字符串的话存进去的都是UTF-8，然后我就寻找原因，想起来我用的是System.Data.SQLite.dll这个库，还好是开源的，于是下载源码看了看。发现代码里把传进来的编码格式先转换成UTF-8。所以无论外层怎么改，只要调用这个库进行增改操作都会存入的UTF-8。所以可以改SQLite的源码再编译成dll应该就可以。改完之后，编译的时候发现少了点，查阅资料也很少有比较详细的能解决的方案，所以不得已这个方案给pass掉了。

然后回想起来之前存数据为什么可以存GBK，想到了是不是可以用C的库，于是看了一下sqlite3的源码，并没有在底层进行转码。所以此方法可行。首先下载sqlite3.dll到项目生成目录，比如Debug或者Release目录下，然后需要动态加载C库了。新建一个类Sqlite.cs，

 class SQLite    {        public const int SQLITE_OK = 0;/* Successful result */        public const int SQLITE_ERROR = 1;/* SQL error or missing database */        public const int SQLITE_INTERNAL = 2;/* An internal logic error in SQLite */        public const int SQLITE_PERM = 3;/* Access permission denied */        public const int SQLITE_ABORT = 4;/* Callback routine requested an abort */        public const int SQLITE_BUSY = 5;/* The database file is locked */        public const int SQLITE_LOCKED = 6;/* A table in the database is locked */        public const int SQLITE_NOMEM = 7;/* A malloc() failed */        public const int SQLITE_READONLY = 8;/* Attempt to write a readonly database */        public const int SQLITE_INTERRUPT = 9;/* Operation terminated by sqlite_interrupt() */        public const int SQLITE_IOERR = 10;/* Some kind of disk I/O error occurred */        public const int SQLITE_CORRUPT = 11;/* The database disk image is malformed */        public const int SQLITE_NOTFOUND = 12;/* (Internal Only) Table or record not found */        public const int SQLITE_FULL = 13;/* Insertion failed because database is full */        public const int SQLITE_CANTOPEN = 14;/* Unable to open the database file */        public const int SQLITE_PROTOCOL = 15;/* Database lock protocol error */        public const int SQLITE_EMPTY = 16;/* (Internal Only) Database table is empty */        public const int SQLITE_SCHEMA = 17;/* The database schema changed */        public const int SQLITE_TOOBIG = 18;/* Too much data for one row of a table */        public const int SQLITE_CONSTRAINT = 19;/* Abort due to contraint violation */        public const int SQLITE_MISMATCH = 20;/* Data type mismatch */        public const int SQLITE_MISUSE = 21;/* Library used incorrectly */        public const int SQLITE_NOLFS = 22;/* Uses OS features not supported on host */        public const int SQLITE_AUTH = 23;/* Authorization denied */        public const int SQLITE_ROW = 100;/* sqlite_step() has another row ready */        public const int SQLITE_DONE = 101;/* sqlite_step() has finished executing */        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_open(byte[] filename, out IntPtr db);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_close", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_close(IntPtr db);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_prepare_v2(IntPtr db, string zSql, int nByte, out　IntPtr ppStmpt, IntPtr pzTail);        //SQLITE_API int sqlite3_get_table(        //sqlite3 *db,                /* The database on which the SQL executes */        // const char *zSql,           /* The SQL to be executed */        // char ***pazResult,          /* Write the result table here */        // int *pnRow,                 /* Write the number of rows in the result here */        // int *pnColumn,              /* Write the number of columns of result here */        // char **pzErrMsg             /* Write error messages here */        //)               //不建议使用        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_get_table", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_get_table(IntPtr db, string zSql, ref string[] pazResult, ref IntPtr pnRow, ref IntPtr pnColumn, ref string[] pzErrmsg);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_prepare16(IntPtr db, string zSql, int nByte, out　IntPtr ppStmpt, IntPtr pzTail);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare16_v2", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_prepare16_v2(IntPtr db, string zSql, int nByte, out　IntPtr ppStmpt, IntPtr pzTail);        [DllImport("    ", EntryPoint = "sqlite3_prepare", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_prepare(IntPtr db, string zSql, int nByte, out　IntPtr ppStmpt, IntPtr pzTail);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_text16", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_bind_text16(IntPtr stmHandle, int n, byte[] zSql, int nByte, IntPtr pzTail);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_text", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_bind_text(IntPtr stmHandle, int n, byte[] zSql, int nByte, IntPtr pzTail);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_key", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_key(IntPtr db, string pKey, int nKey);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_rekey", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_rekey(IntPtr db, string pKey, int nKey);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_step", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_step(IntPtr stmHandle);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_finalize(IntPtr stmHandle);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg", CallingConvention = CallingConvention.Cdecl)]        public static extern string sqlite3_errmsg(IntPtr db);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_column_count(IntPtr stmHandle);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name", CallingConvention = CallingConvention.Cdecl)]        public static extern string sqlite3_column_origin_name(IntPtr stmHandle, int iCol);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_column_type(IntPtr stmHandle, int iCol);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_column_int(IntPtr stmHandle, int iCol);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text", CallingConvention = CallingConvention.Cdecl)]        public static extern string sqlite3_column_text(IntPtr stmHandle, int iCol);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double", CallingConvention = CallingConvention.Cdecl)]        public static extern double sqlite3_column_double(IntPtr stmHandle, int iCol);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_bytes", CallingConvention = CallingConvention.Cdecl)]        public static extern int sqlite3_column_bytes(IntPtr stmHandle, int iCol);        [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_blob", CallingConvention = CallingConvention.Cdecl)]        public static extern IntPtr sqlite3_column_blob(IntPtr stmHandle, int iCol);        //int sqlite3_column_bytes(sqlite3_stmt*, int iCol);        //const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);        //int sqlite3_column_bytes(sqlite3_stmt*, int iCol);        //int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);        //double sqlite3_column_double(sqlite3_stmt*, int iCol);        //int sqlite3_column_int(sqlite3_stmt*, int iCol);        //sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);        //const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);        //const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);        //int sqlite3_column_type(sqlite3_stmt*, int iCol);        //sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);    }

然后我们就可以在程序中调用这个库指定字符串编码存入了。

string err;byte[] arrDbFileName = Encoding.UTF8.GetBytes(dbFileName);            if (SQLite.sqlite3_open(arrDbFileName, out db) != SQLite.SQLITE_OK)            {                err = SQLite.sqlite3_errmsg(db);            }            if (SQLite.sqlite3_key(db, strDbpwd, strDbpwd.Length) != SQLite.SQLITE_OK)//输入密码            {                err = SQLite.sqlite3_errmsg(db);            }            if (SQLite.sqlite3_rekey(db, "", 0) != SQLite.SQLITE_OK)//解密            {                err = SQLite.sqlite3_errmsg(db);            } string query = "INSERT INTO tablename(index1,index2) VALUES(?,?)";            if (SQLite.sqlite3_prepare_v2(db, query, query.Length, out　stmHandle, IntPtr.Zero) != SQLite.SQLITE_OK)//准备语句            {                err = SQLite.sqlite3_errmsg(db);             }             if (SQLite.SQLITE_OK != SQLite.sqlite3_bind_text(stmHandle, 1, Encoding.GetEncoding(936).GetBytes(str1.ToString().Trim()), -1, IntPtr.Zero))            {                err = SQLite.sqlite3_errmsg(db);            }            if (SQLite.SQLITE_OK != SQLite.sqlite3_bind_text(stmHandle, 2, Encoding.GetEncoding(936).GetBytes(str2.ToString().Trim()), -1, IntPtr.Zero))            {                err = SQLite.sqlite3_errmsg(db);            }             if (SQLite.sqlite3_step(stmHandle) != SQLite.SQLITE_DONE)//执行            {                err = SQLite.sqlite3_errmsg(db);            }             if (SQLite.sqlite3_finalize(stmHandle) != SQLite.SQLITE_OK)//销毁准备语句                {                    err = SQLite.sqlite3_errmsg(db);                }                if (SQLite.sqlite3_close(stmHandle) != SQLite.SQLITE_OK)//关闭数据库                {                    err = SQLite.sqlite3_errmsg(db);                }

sqlite3也支持 事务操作 ，没有相应的方法，只需执行sqli语句就可以

executeSql("begin transaction");//开始事务executeSql("commit transaction");//执行事务private void executeSql(string sqlStr)        {            if (SQLite.SQLITE_OK != SQLite.sqlite3_prepare_v2(db, sqlStr, sqlStr.Length, out stmHandle, IntPtr.Zero))            {                err = SQLite.sqlite3_errmsg(db);            }            if (SQLite.sqlite3_step(stmHandle) != SQLite.SQLITE_DONE)            {                err = SQLite.sqlite3_errmsg(db);            }        }