title: Retrieving a BLOB with System.Data.SQLite and C#
body: <p>I'm newbie in <em>SQLITE</em> and I'm currently using <strong>System.Data.SQLite.dll</strong> version <strong>1.0.89.0</strong> in a <strong>C#</strong> project.
  My database contains a simple table <strong>'files'</strong> with following columns:</p>
  
  <ul>
  <li>[id] <strong>VARCHAR(50) NOT NULL</strong></li>
  <li>[chunk] <strong>INTEGER NOT NULL</strong></li>
  <li>[content] <strong>BLOB NOT NULL</strong></li>
  <li>[size] <strong>INTEGER NOT NULL</strong></li>
  <li>[date_ins] <strong>DATETIME NOT NULL</strong></li>
  </ul>
  
  <p><strong>PRIMARY KEY</strong>(id,chunk)</p>
  
  <p>I created a class (<strong>OfflineStorage</strong>) to add and retrieve files in this table as <em>BLOBS</em>.
  <strong>Save</strong> method works ok, but <strong>Load</strong> generate an <strong>InvalidCastException</strong> on <strong>GetStream</strong> extended method.</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class OfflineStorage
  {
  
      private static string l_strConnectionTemplate = "Data Source={0};Version=3;Password=\"{1}\";";
  
      private string l_strConnection;
      private int SQLITE_MAX_BLOB_LENGTH;
  
      private string l_strCreateTable = @"CREATE TABLE IF NOT EXISTS [files]" +
                                         "( " +
                                         "     [id] VARCHAR(50) NOT NULL, " +
                                         "     [chunk] INTEGER NOT NULL, " +
                                         "     [content] BLOB NOT NULL, " +
                                         "     [size] INTEGER NOT NULL, " +
                                         "     [date_ins] DATETIME NOT NULL, " +
                                         "     PRIMARY KEY(id,chunk) " +
                                         ")";
  
      private string l_strSelectQuery = @"SELECT chunk, content, size FROM files WHERE id = @id ORDER BY chunk";
      private string l_strUpdateQuery = @"UPDATE files SET content = content || @new_content, size = size + @size WHERE id = @id AND chunk = @chunk";
      private string l_strInsertQuery = @"INSERT INTO files(id, chunk, content, size, date_ins) VALUES(@id, @chunk, @new_content, @size, DATETIME('now'))";
  
      public OfflineStorage(string strFilename, string strPassword = "")
      {
          SQLiteConnection l_objConnection = null;
          if (!File.Exists(strFilename))
          {
              l_strConnection = string.Format(l_strConnectionTemplate, strFilename, "");
              SQLiteConnection.CreateFile(strFilename);
              l_objConnection = new SQLiteConnection(l_strConnection);
              l_objConnection.SetPassword(strPassword);
              l_objConnection.Close();
          }
  
          l_strConnection = string.Format(l_strConnectionTemplate, strFilename, strPassword);
          l_objConnection = getConnection();
          using (SQLiteCommand l_objCommand = new SQLiteCommand(l_strCreateTable, l_objConnection))
          {
              l_objCommand.ExecuteNonQuery();
          }
          SQLITE_MAX_BLOB_LENGTH = 1000000;
  
          CloseConnection(l_objConnection);
      }
  
      private SQLiteConnection getConnection()
      {
          SQLiteConnection l_objConnection = null;
  
          try
          {
              l_objConnection = new SQLiteConnection(l_strConnection);
              l_objConnection.Open();
              return l_objConnection;
          }
          catch (Exception ex)
          {
              CloseConnection(l_objConnection);
  
              throw new OfflineStorageException("Local Service open db error.", ex);
          }
      }
  
      private void CloseConnection(SQLiteConnection objConnection)
      {
          if (objConnection != null)
          {
              objConnection.Close();
              objConnection = null;
          }
      }
  
      public long Load(string strID, Stream objStream)
      {
          if (!objStream.CanWrite)
              throw new NotSupportedException("Stream not writable.");
  
          SQLiteConnection l_objConnection = getConnection();
  
          // Columns Identifier (name of file)
          SQLiteParameter l_objID = new SQLiteParameter("@id", DbType.String);
          l_objID.Value = strID;
  
          SQLiteCommand l_objCommand = new SQLiteCommand(l_strSelectQuery, l_objConnection);
          l_objCommand.Parameters.Add(l_objID);
  
          // Load File Records
          SQLiteDataReader l_objReader;
          try
          {
              l_objReader = l_objCommand.ExecuteReader();
          }
          catch (Exception ex)
          {
              CloseConnection(l_objConnection);
              throw new OfflineStorageException("SQLite exception.", ex);
          }
  
          long l_lFileLength = 0;     // Complete file length
          int  l_iDBChunk = -1;       // Current chunk on database
          int  l_iChunk = 0;          // Current 'sub chunk'
          long l_lChunkLength = -1;   // Current 'sub chunk' length
  
          try
          {
              // For each record of current file selected by identifier
              while (l_objReader.Read())
              {
                  l_iDBChunk = l_objReader.GetInt32(0);       // Chunk ID
                  l_lChunkLength = l_objReader.GetInt64(2);   // Chunk size 
                  Trace.Assert(l_iChunk == l_iDBChunk);       // Compare expected Chunck with Database ID Chunk
                  l_lFileLength += l_objReader.GetStream(objStream, 1, l_lChunkLength); // Load chunk
                  l_iChunk++;
              }
          }
          catch (Exception ex)
          {
              string l_strMessage = string.Format("SQLite exception on file {0}, DB chunk {1}: \n{2}", strID, l_iDBChunk, ex.Message);
              throw new OfflineStorageException(l_strMessage, ex);
          }
          finally
          {
              l_objReader.Close();
              l_objCommand.Dispose();
              CloseConnection(l_objConnection);
          }
  
          if (l_iChunk &lt; 1)
          {
              string l_strMessage = string.Format("File {0} not readed on db.", strID);
              throw new OfflineStorageException(l_strMessage);
          }
  
          return l_lFileLength;
      }
  
      public void Save(string strID, Stream objStream, bool bOverwrite = false)
      {
          const int CHUNK_SIZE = 8 * 1024;
  
          if (!objStream.CanRead)
              throw new NotSupportedException("Stream not readable.");
          long l_lOldPosition = objStream.Position;
  
          SQLiteConnection l_objConnection = getConnection();
          byte[] lar_byBuffer = new byte[CHUNK_SIZE];
  
          SQLiteParameter l_objID = new SQLiteParameter("@id", DbType.String);
          l_objID.Value = strID;
          SQLiteParameter l_objContent = new SQLiteParameter("@new_content", DbType.Binary);
          l_objContent.Value = lar_byBuffer;
          SQLiteParameter l_objChunk = new SQLiteParameter("@chunk", DbType.Int32);
          SQLiteParameter l_objSize = new SQLiteParameter("@size", DbType.Int32);
  
          SQLiteCommand l_objCommand = new SQLiteCommand(l_strInsertQuery, l_objConnection);
          l_objCommand.Parameters.Add(l_objID);
          l_objCommand.Parameters.Add(l_objContent);
          l_objCommand.Parameters.Add(l_objChunk);
          l_objCommand.Parameters.Add(l_objSize);
  
          int  l_iReturn, l_lBytesRead;
          int  l_iChunk = 0;          // Current 'sub chunk'
          int  l_iDBChunk = 0;        // Current chunk on database
          long l_lDBChunkLength = 0;  // Current length of chunk
          l_objChunk.Value = l_iDBChunk;
  
          //Transaction
          using (SQLiteTransaction l_objTransaction = l_objConnection.BeginTransaction())
          {
              // Read File from stream
              while ((l_lBytesRead = objStream.Read(lar_byBuffer, 0, lar_byBuffer.Length)) &gt; 0)
              {
                  // Check for next Chunk
                  if ((l_lDBChunkLength + l_lBytesRead) &gt;= SQLITE_MAX_BLOB_LENGTH)
                  {
                      l_objCommand.CommandText = l_strInsertQuery;
                      l_iChunk = 0;       // reset 'sub chunk' counter
                      l_lDBChunkLength = 0; // reset chunk size
                      l_iDBChunk++;       // increase chunk ID
                      l_objChunk.Value = l_iDBChunk;
                  }
  
                  l_lDBChunkLength += l_lBytesRead;   // Increase length of chunk
                  l_objContent.Size = l_lBytesRead;   // Length of Content field
                  l_objSize.Value = l_lBytesRead;     // Chunk lenght (write on 'size' column)
  
                  #region WRITE
                  try
                  {
                      l_iReturn = l_objCommand.ExecuteNonQuery();
                      if (l_iChunk == 0)
                      {
                          l_objCommand.CommandText = l_strUpdateQuery;
                      }
                  }
                  catch (Exception ex)
                  {
                      l_objTransaction.Rollback();
                      CloseConnection(l_objConnection);
                      string l_strMessage = string.Format("SQLite exception on file {0}, DB chunk {1}, chunk {2}: \n{3}", strID, l_iDBChunk, l_iChunk, ex.Message);
                      throw new OfflineStorageException(l_strMessage, ex);
                  }
  
                  if (l_iReturn != 1)
                  {
                      l_objTransaction.Rollback();
                      CloseConnection(l_objConnection);
                      string l_strMessage = string.Format("DB chunk {1}, chunk {2} of file {0} not inserted on db.", strID, l_iDBChunk, l_iChunk);
                      throw new OfflineStorageException(l_strMessage);
                  }
                  #endregion WRITE
  
                  l_iChunk++;
              }
  
              l_objTransaction.Commit();
          }
          l_objCommand.Dispose();
          CloseConnection(l_objConnection);
          objStream.Position = l_lOldPosition;
      }
  }
  </code></pre>
  
  <p>DB Data Reader Extended class:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public static class DbDataReaderExtension
  {
      public static long GetStream(this DbDataReader objReader, System.IO.Stream objStream, int iIndex = 0, long lFileLength = -1)
      {
          const int CHUNK_SIZE = 7 * 1024;
  
          byte[] lar_byBuffer = new byte[CHUNK_SIZE]; // Buffer
          long l_lBytesRead;          // Bytes readed from SQLite database column
          long l_lFieldOffset = 0;    // Field offset on database column
          long l_lBytesRemainig = lFileLength;
  
          while ((l_lBytesRead = objReader.GetBytes(iIndex, l_lFieldOffset, lar_byBuffer, 0, lar_byBuffer.Length)) &gt; 0)
          {
              l_lFieldOffset += l_lBytesRead; // prepare next offset
  
              if (l_lBytesRemainig &gt; 0)       // check if a FileLength was set
              {
                  l_lBytesRemainig -= l_lBytesRead; // remove readed bytes
                  if (l_lBytesRemainig &lt; 0) // Cut last bytes not valid if file is bigger than column size
                      l_lBytesRead += l_lBytesRemainig;
              }
  
              // write only valid bytes 
              objStream.Write(lar_byBuffer, 0, (int)l_lBytesRead);
          }
  
          return lFileLength &lt; 0 ? l_lFieldOffset : lFileLength;
      }
  }
  </code></pre>
  
  <p>I found this exception is generated because ReadBytes method (SQLiteDataReader) call VerifyType</p>
  
  <pre class="lang-cs prettyprint-override"><code>private TypeAffinity VerifyType(int i, DbType typ)
  {
    CheckClosed();
    CheckValidRow();
  
    TypeAffinity affinity = GetSQLiteType(i).Affinity;
  
    switch (affinity)
    {
      ...        
      case TypeAffinity.Text:
        if (typ == DbType.SByte) return affinity;
        if (typ == DbType.String) return affinity;
        if (typ == DbType.SByte) return affinity;
        if (typ == DbType.Guid) return affinity;
        if (typ == DbType.DateTime) return affinity;
        if (typ == DbType.Decimal) return affinity;
        break;
      case TypeAffinity.Blob:
        ...
    }
  
    throw new InvalidCastException();
  }
  </code></pre>
  
  <p>In this function isn't expected that typ is equal to <strong><em>DbType.Binary</em></strong> and <em>affinity</em> is equal to  <strong><em>TypeAffinity.Text</em></strong>.<br/>
  Can anyone help me to understand this problem?<br/>
  Thank you</p>