如何从sqlite存储和检索blob

我在c ++,python和现在(也许)在C#中使用过sqlite.在所有这些中,我不知道如何将blob插入表中.如何在sqlite中存储和检索blob？

1> Darin Dimitr..：

---

以下是如何在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();
        }
    }
}

---

好的,所以三年后......但是这段代码中的`actualRead`有什么意义呢？为什么不直接使用`stream.Write(buffer,0,bytesRead)`？

---

我从http://sqlite.phxsoftware.com/下载了System.Data.SQLite.dll并将其添加到项目引用中.没有必要安装任何东西.

2> dicroce..：

---

您需要使用sqlite的预处理语句接口.基本上,我们的想法是你为blob准备一个带占位符的语句,然后使用其中一个绑定调用来"绑定"你的数据......

SQLite准备语句

3> cdavidyoung..：

---

我最终使用这种插入blob的方法:

   protected Boolean updateByteArrayInTable(String table, String value, byte[] byteArray, String expr)
   {
      try
      {
         SQLiteCommand mycommand = new SQLiteCommand(connection);
         mycommand.CommandText = "update " + table + " set " + value + "=@image" + " where " + expr;
         SQLiteParameter parameter = new SQLiteParameter("@image", System.Data.DbType.Binary);
         parameter.Value = byteArray;
         mycommand.Parameters.Add(parameter);

         int rowsUpdated = mycommand.ExecuteNonQuery();
         return (rowsUpdated>0);
      }
      catch (Exception)
      {
         return false;
      }
   }

为了阅读它,代码是:

   protected DataTable executeQuery(String command)
   {
      DataTable dt = new DataTable();
      try
      {
         SQLiteCommand mycommand = new SQLiteCommand(connection);
         mycommand.CommandText = command;
         SQLiteDataReader reader = mycommand.ExecuteReader();
         dt.Load(reader);
         reader.Close();
         return dt;
      }
      catch (Exception)
      {
         return null;
      }
   }

   protected DataTable getAllWhere(String table, String sort, String expr)
   {
      String cmd = "select * from " + table;
      if (sort != null)
         cmd += " order by " + sort;
      if (expr != null)
         cmd += " where " + expr;
      DataTable dt = executeQuery(cmd);
      return dt;
   }

   public DataRow getImage(long rowId) {
      String where = KEY_ROWID_IMAGE + " = " + Convert.ToString(rowId);
      DataTable dt = getAllWhere(DATABASE_TABLE_IMAGES, null, where);
      DataRow dr = null;
      if (dt.Rows.Count > 0) // should be just 1 row
         dr = dt.Rows[0];
      return dr;
   }

   public byte[] getImage(DataRow dr) {
      try
      {
         object image = dr[KEY_IMAGE];
         if (!Convert.IsDBNull(image))
            return (byte[])image;
         else
            return null;
      } catch(Exception) {
         return null;
      }
   }

   DataRow dri = getImage(rowId);
   byte[] image = getImage(dri);

---

为getImage中的字节数组+1。救命！

4> breez..：

---

这对我来说很好（C＃）：

byte[] iconBytes = null;
using (var dbConnection = new SQLiteConnection(DataSource))
{
    dbConnection.Open();
    using (var transaction = dbConnection.BeginTransaction())
    {
        using (var command = new SQLiteCommand(dbConnection))
        {
            command.CommandText = "SELECT icon FROM my_table";

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader["icon"] != null && !Convert.IsDBNull(reader["icon"]))
                    {
                        iconBytes = (byte[]) reader["icon"];
                    }
                }
            }
        }
        transaction.Commit();
    }
}

无需分块。只是强制转换为字节数组。