Question

请教SQLite数据库读写BLOB字段

Answer 1

// 连接字符串 data source=d:\test.db3;password=1234
SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();
connStr.DataSource = @"d:\test.db3";
connStr.Password = "1234";
conn = new SQLiteConnection(connStr.ToString());
conn.Open();

// 插入数据
string SQL = "INSERT INTO test(k) VALUES(:k)";
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = SQL;

// 取图标，准备插入到数据库BLOB字段
FileStream picStream = File.OpenRead(@"d:\1.bmp");
byte[] bArray = new byte[picStream.Length];
picStream.Read(bArray, 0, bArray.Length);
picStream.Close();

cmd.Parameters.Add("k", DbType.Binary).Value = bArray; // BLOB
cmd.ExecuteNonQuery();

//取出数据
cmd.CommandText = "SELECT k FROM test";
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MemoryStream streamImage = new MemoryStream(reader["k"] as byte[]);
pictureBox1.Image = Image.FromStream(streamImage);
streamImage.Close(); // 关闭流
}
reader.close();

conn.close();