C# .net SQLite存取GBK格式字符串_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);


    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

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

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);
                }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

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);
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14