如何从sqlite存储和检索blob [英] How do i store and retrieve a blob from sqlite

问题描述

我已经在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();
        }
    }
}

这篇关于如何从sqlite存储和检索blob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！