Hacky Sql Compact Workaround

Question

So, I'm trying to use ADO.NET to stream a file data stored in an image column in a SQL Compact database.

To do this, I wrote a DataReaderStream class that takes a data reader, opened for sequential access, and represents it as a stream, redirecting calls to Read(...) on the stream to IDataReader.GetBytes(...).

One "weird" aspect of IDataReader.GetBytes(...), when compared to the Stream class, is that GetBytes requires the client to increment an offset and pass that in each time it's called. It does this even though access is sequential, and it's not possible to read "backwards" in the data reader stream.

The SqlCeDataReader implementation of IDataReader enforces this by incrementing an internal counter that identifies the total number of bytes it has returned. If you pass in a number either less than or greater than that number, the method will throw an InvalidOperationException.

The problem with this, however, is that there is a bug in the SqlCeDataReader implementation that causes it to set the internal counter to the wrong value. This results in subsequent calls to Read on my stream throwing exceptions when they shouldn't be.

I found some infomation about the bug on this MSDN thread.

I was able to come up with a disgusting, horribly hacky workaround, that basically uses reflection to update the field in the class to the correct value.

The code looks like this:

    public override int Read(byte[] buffer, int offset, int count)
    {
        m_length  = m_length ?? m_dr.GetBytes(0, 0, null, offset, count);

        if (m_fieldOffSet < m_length)
        {
            var bytesRead = m_dr.GetBytes(0, m_fieldOffSet, buffer, offset, count);
            m_fieldOffSet += bytesRead;

            if (m_dr is SqlCeDataReader)
            {
                //BEGIN HACK
                //This is a horrible HACK.
                    m_field = m_field ?? typeof (SqlCeDataReader).GetField("sequentialUnitsRead", BindingFlags.NonPublic | BindingFlags.Instance);
                    var length = (long)(m_field.GetValue(m_dr));
                    if (length != m_fieldOffSet)
                    {   
                        m_field.SetValue(m_dr, m_fieldOffSet);
                    }
                //END HACK
            }

            return (int) bytesRead;
        }
        else
        {
            return 0;
        }
    }

For obvious reasons, I would prefer to not use this.

However, I do not want to buffer the entire contents of the blob in memory either.

Does any one know of a way I can get streaming data out of a SQL Compact database without having to resort to such horrible code?

Answer 1

I contacted Microsoft (through the SQL Compact Blog) and they confirmed the bug, and suggested I use OLEDB as a workaround. So, I'll try that and see if that works for me.

Answer 2

Actually, I decided to fix the problem by just not storing blobs in the database to begin with.

This eliminates the problem (I can stream data from a file), and also fixes some issues I might have run into with Sql Compact's 4 GB size limit.