使用c＃从NVarchar（最大）列中流式传输数据， [英] Streaming data from a NVarchar(Max) column using c#

问题描述

我想将一些文件的内容放入数据库，以便由独立的进程读取。这是一个两步骤的事情，因为文件将被上传到一个java服务器，然后由一个单独的c＃应用程序定期运行处理。

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.

我计划使用 nvarchar（Max）列来表示数据，但我不能看到如何以合理的方式从这种列中读取。我不想使用 SqlDataReader.GetString ，因为这将迫使我立即保存所有的数据在内存中。文件不是很大，但只是看起来像一个愚蠢的事情 - 它会给我一个单一的字符串，然后需要拆分成行，所以整个方法将完全倒退。

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.

我假设我只能使用一个普通的流阅读器，但调用 GetStream 失败，说它不工作

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?

推荐答案

我之前写了这个扩展方法：

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

它是为BLOB设计的，但它也适用于NVARCHAR（max）（至少在SQL Server上）。

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

你可以这样使用它：

    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...
        }
    }

这篇关于使用c＃从NVarchar（最大）列中流式传输数据，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！