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();
}
}
转载于:https://www.cnblogs.com/sekon/p/4669926.html
