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);
            }
        }
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页