如何从 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(); } } }