如何从sqlite存储和检索blob

本文介绍了如何从sqlite存储和检索blob的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经在c ++，python和现在(也许)在C#中使用了sqlite.在所有这些中，我都不知道如何在表中插入Blob.如何在sqlite中存储和检索Blob?

I have used sqlite in c++, python and now (perhaps) in C#. In all of these i have no idea how to insert a blob into a table. How do i store and retrieve a blob in sqlite?

推荐答案

在C#中，您可以按照以下方法进行操作:

Here's how you can do it in C#:

class Program { static void Main(string[] args) { if (File.Exists("test.db3")) { File.Delete("test.db3"); } using (var connection = new SQLiteConnection("Data Source=test.db3;Version=3")) using (var command = new SQLiteCommand("CREATE TABLE PHOTOS(ID INTEGER PRIMARY KEY AUTOINCREMENT, PHOTO BLOB)", connection)) { connection.Open(); command.ExecuteNonQuery(); byte[] photo = new byte[] { 1, 2, 3, 4, 5 }; command.CommandText = "INSERT INTO PHOTOS (PHOTO) VALUES (@photo)"; command.Parameters.Add("@photo", DbType.Binary, 20).Value = photo; command.ExecuteNonQuery(); command.CommandText = "SELECT PHOTO FROM PHOTOS WHERE ID = 1"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { byte[] buffer = GetBytes(reader); } } } } static byte[] GetBytes(SQLiteDataReader reader) { 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(0, fieldOffset, buffer, 0, buffer.Length)) > 0) { stream.Write(buffer, 0, (int)bytesRead); fieldOffset += bytesRead; } return stream.ToArray(); } } }