Streaming data from a NVarchar(Max) column using c#

Question

I want to put the contents of some files into the database to be read by a seperate process. This is a two step thing as the files will be uploaded to a java server but then processed by a seperate c# application that runs periodically.

I was planning on using a nvarchar(Max) column to represent the data but I can't see how to read from that sort of column in a sensible way. I don't want to use SqlDataReader.GetString as that will force me to hold all the data in memory at once. The files aren't massive but that just seems like a stupid thing to do - it'll give me it as a single string which will then need splitting up into lines, so the whole approach would be totally backwards.

I was assuming I'd just be able to use a normal stream reader but calling GetStream fails saying it doesn't work for this type of column.

Any ideas? Is it just going to be easier for the database to pretend this isn't really text and store it as bytes so I can stream it?

Answer 1

I wrote this extension method some time ago:

public static class DataExtensions
{
    public static Stream GetStream(this IDataRecord record, int ordinal)
    {
        return new DbBinaryFieldStream(record, ordinal);
    }

    public static Stream GetStream(this IDataRecord record, string name)
    {
        int i = record.GetOrdinal(name);
        return record.GetStream(i);
    }

    private class DbBinaryFieldStream : Stream
    {
        private readonly IDataRecord _record;
        private readonly int _fieldIndex;
        private long _position;
        private long _length = -1;

        public DbBinaryFieldStream(IDataRecord record, int fieldIndex)
        {
            _record = record;
            _fieldIndex = fieldIndex;
        }

        public override bool CanRead
        {
            get { return true; }
        }

        public override bool CanSeek
        {
            get { return true; }
        }

        public override bool CanWrite
        {
            get { return false; }
        }

        public override void Flush()
        {
            throw new NotSupportedException();
        }

        public override long Length
        {
            get
            {
                if (_length < 0)
                {
                    _length = _record.GetBytes(_fieldIndex, 0, null, 0, 0);
                }
                return _length;
            }
        }

        public override long Position
        {
            get
            {
                return _position;
            }
            set
            {
                _position = value;
            }
        }

        public override int Read(byte[] buffer, int offset, int count)
        {
            long nRead = _record.GetBytes(_fieldIndex, _position, buffer, offset, count);
            _position += nRead;
            return (int)nRead;
        }

        public override long Seek(long offset, SeekOrigin origin)
        {
            long newPosition = _position;
            switch (origin)
            {
                case SeekOrigin.Begin:
                    newPosition = offset;
                    break;
                case SeekOrigin.Current:
                    newPosition = _position + offset;
                    break;
                case SeekOrigin.End:
                    newPosition = this.Length - offset;
                    break;
                default:
                    break;
            }
            if (newPosition < 0)
                throw new ArgumentOutOfRangeException("offset");
            _position = newPosition;
            return _position;
        }

        public override void SetLength(long value)
        {
            throw new NotSupportedException();
        }

        public override void Write(byte[] buffer, int offset, int count)
        {
            throw new NotSupportedException();
        }
    }
}

It's designed for a BLOB, but it works for a NVARCHAR(max) as well (at least on SQL Server).

You can use it like this:

    using (var dataReader = command.ExecuteReader())
    {
        dataReader.Read();
        using (var stream = dataReader.GetStream("Text"))
        using (var streamReader = new StreamReader(stream))
        {
            // read the text using the StreamReader...
        }
    }