How do I store and retrieve a blob from sqlite?

by

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

More Related Contents:

  1. SQLite how to refer to column name by a query
  2. How do I unlock a SQLite database?
  3. “Insert if not exists” statement in SQLite
  4. How do I make an UPDATE while joining tables on SQLite?
  5. SQLite Concurrent Access
  6. System.Data.SQLite Close() not releasing database file
  7. INSERT IF NOT EXISTS ELSE UPDATE?
  8. Sqlite LIMIT / OFFSET query
  9. Set start value for AUTOINCREMENT in SQLite
  10. How to use ROW_NUMBER in sqlite
  11. Android – Pulling SQlite database android device
  12. ALTER COLUMN in sqlite
  13. SQLite error ‘attempt to write a readonly database’ during insert?
  14. Efficient paging in SQLite with millions of records
  15. SQLite add Primary Key
  16. Encrypted cookies in Chrome
  17. How delete table inner join with other table in Sqlite?
  18. ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite
  19. How to get a list of column names
  20. sqlite – How to get INSERT OR IGNORE to work
  21. Creating stored procedure and SQLite?
  22. How Scalable is SQLite? [closed]
  23. How do DATETIME values work in SQLite?
  24. change sqlite file size after “DELETE FROM table”
  25. Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created
  26. How to set unique constraint over multiple columns when any one can be null?
  27. How to display blob value using x’abc’ binary string literal syntax?
  28. sql query – how to apply limit within group by
  29. Unable to load DLL sqlite3 in Universal Windows App running on Mobile
  30. Creating stored procedure in SQLite

Leave a Comment