SQLite操作

            string  dbPath="Data Source=D:\\test.db3";
            using (SQLiteConnection cnn = new SQLiteConnection(dbPath))
            {
                cnn.Open();   //无库则自动建

                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    byte[] buffer = null;

                    //建表
                    string sql = "create table test(id int,file blob);";
                    cmd.CommandText = sql;

                    //将文件转二进制数组存入Blob字段
                    string file = @"d:\help.jpg";
                    buffer = FileHelper.FileToBytebuffer(file);
                    cmd.CommandText = "insert into test values('11',@data)";
                    SQLiteParameter para = new SQLiteParameter("@data", DbType.Binary);
                    para.Value = buffer;
                    cmd.Parameters.Add(para);
                    cmd.ExecuteNonQuery();

                    //读取记录，将将Blob字段的转存为文件
                    cmd.CommandText = "SELECT * FROM test";// WHERE ID = '12'";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string f = reader["id"].ToString();  //以列名取值
                           buffer =Helpers.Get_SQLite_Blob_Bytes(reader,1); //以列号取值
                            FileHelper.BytebufferToFile(buffer, "d:\\"+f+".jpg");
                        }
                    }                   

                    //更新Blob字段
                    Byte[] m_byte = FileHelper.FileToBytebuffer("d:\\pic1.jpg");
                    cmd.CommandText = "UPDATE test set file=@file WHERE id=12";
                    SQLiteParameter param_m = new SQLiteParameter("@file", DbType.Binary, m_byte.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, m_byte);
                    cmd.Parameters.Add(param_m); //很多参数阿，注意DBType.Binary 
                    cmd.ExecuteNonQuery();

                    //删除记录
                    cmd.CommandText = "delete from test where id=12";
                    cmd.ExecuteNonQuery();

                    //判断表是否存在，不在存则新建
                    cmd.CommandText = @"create table if not exists tes (ID integer primary key autoincrement not null, tojid varchar(200),timestamp timestamp,remark,data text,type int)";
                    cmd.ExecuteNonQuery();

                }
            }     

 

            /// <summary>
            /// 读取SQLite表二进制字段，返回二进制数组
            /// </summary>
            /// <param name="reader">SQLiteDataReader</param>
            /// <param name="columnnumber">字段序号</param>
            /// <returns>byte[]</returns>
            public static byte[] Get_SQLite_Blob_Bytes(SQLiteDataReader reader, int columnnumber)
            {
                const int CHUNK_SIZE = 2 * 1024;
                byte[] buffer = new byte[CHUNK_SIZE];
                long bytesRead;
                long fieldOffset = 0; using (MemoryStream stream = new MemoryStream())
                {
                    while ((bytesRead = reader.GetBytes(columnnumber, fieldOffset, buffer, 0, buffer.Length)) > 0)
                    {
                        stream.Write(buffer, 0, (int)bytesRead);
                        fieldOffset += bytesRead;
                    } return stream.ToArray();
                }
            }