使用 C# 流式传输 SQL Server 2005 图像字段？
在广泛检查了这个问题的答案后，我仍然找不到令人满意的解决方案。所以就这样吧。
我需要在 SQL Server 2005 表的列中存储可能大量的数据。我绝对需要以流式传输方式工作，以便：
- 写入时，数据以块的形式发送到数据库。是否有一些内置方法可以使用 System.Data.SqlClient 中的类从 C# 客户端代码实现此目的？或者我是否需要诉诸使用 ADODB.Net Stream 对象？不确定如何混合这两个概念（特别是在参与当前的 SqlTransaction 方面）。
或者，是否有一种方法可以让 T-SQL 存储过程将数据追加到现有列。使用这种方法，存储过程将从客户端代码中多次调用，这将实现流式传输要求。
- 读取时，应该以流式传输方式一次读取一个数据块，
或者，是否有一种方法可以实现 T-。 SQL 存储过程提供对图像字段内容的顺序甚至随机访问？
After having checked extensively for an answer to this question, I still cannot find a satisfying solution. So here goes.
I need to store possibly large amount of data in a column of an SQL Server 2005 table. I absolutely need to work in a streaming fashion, so that :
- When writing, the data is sent in chunks to the database. Is there some builtin way to achieve this from C# client-code using the classes in System.Data.SqlClient? Or do I need to resort to using ADODB.Net Stream object ? Not sure how to mix the two concepts (especially with regards to participating in the current SqlTransaction.
Alternatively, is there a way for a T-SQL stored procedure to append data to an existing column. With this approach, the stored procedure would be called multiple times from the client code and this would achieve the streaming requirement.
- When reading, the data should be read one chunk at a time, in a streaming fashion.
Alternatively, is there a way for a T-SQL stored procedure to provide sequential or even random access to the contents of an image field?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
其实办法是有的，只是有点痛而已。
SQL Server 2005支持更新部分列：
http://technet.microsoft.com/en-us /library/ms177523(SQL.90).aspx
您可以使用子字符串读取列的一部分（是的，甚至是二进制 - 它会很乐意返回一个字节数组）。
这里需要注意的是，这只是一个更新 - 因此您必须首先在 varbinary(max) 字段中创建一个值，然后更新它。除此之外，绝对可以像从 SQL Server 流式传输数据一样进行处理。我用流类包装了检索/更新功能，以使我的生活更轻松。
希望这有帮助。
Actually, there is a way, it just hurts a bit.
SQL Server 2005 supports updating part of a column:
http://technet.microsoft.com/en-us/library/ms177523(SQL.90).aspx
And you can do a read of part of a column with substring (yes, even binary - it'll cheerfully return an array of bytes).
The caveats here are that it's only an update - so you'd have to create a value in the varbinary(max) field first, then update it. Other than it, it's absolutely possible to deal with as though you're streaming data to/from SQL Server. I wrapped the retrieval / update functionality with a stream class to make my life easier.
Hope this helps.
好吧，回答我自己的问题。
事实是......实际上没有办法做我想做的事。来自纯客户端代码或来自 T-SQL 中的服务器端存储过程。在我们切换到 SQL Server 2008 之前，我们将不得不寻找另一个解决方案。
然而，实际上有一种方法可以模拟这种行为，从而实现流媒体需求。解决方案在于客户端和服务器代码之间的协作。
例如，服务器数据库应该将要流式传输的全部内容作为片段表中的一组记录公开。每个记录代表要流式传输的整个内容的一个块。而在客户端中，按顺序读取流，然后将每个块发送到数据库以填充一条记录。
通过适当的簿记，也可以以流方式读取存储的数据。
顺便说一句，这就是 Microsoft BizTalk Server 正在做的事情，我也是这么发现的。
Well, answering my own question.
The truth is... there is actually no way to do what I want. Either from pure client code or from server side stored procedures in T-SQL. Until we switch to SQL Server 2008, we will have to find another soluton.
However, there is actually a way to simulate this behavior, so that the streaming requirement is achieved. The solution lies in a collaboration between client and server code.
The server database should, for instance, expose the entire contents to be streamed as a set of records in a fragments table. Each record, representing a chunk of the entire contents to be streamed. Whereas in the client, the stream is read in sequence, and each chunk is then sent to the database to fill one record.
With suitable bookkeeping, the reading of the stored data can also be done in a streaming fashion.
Incidentally, that's what Microsoft BizTalk Server is doing, and that's how I find out.