Read a SqlCommand column (multiple rows) into a single C# byte[] array

Question

Given a SQL table Document with data:

ID  Content     ByteLength  Sequence
1   Part1...    945669      1
1   Part2...    945669      2
1   Part3...    945669      3

...

2   Part1...    45234       1
2   Part2...    45234       2

Where:

Document.Content = Up to 32KB of data
Document.ByteLength = Total data in bytes for the Document ID
Document.Sequence = Order of content in the Document ID

How can I read all of Document.Content into a single byte array byte[] Content?

using (var command = new SqlCommand("SELECT Content FROM Document WHERE ID=1 ORDER BY Sequence", connection))
    using (var reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            // What goes here?

            // if we just want one row:
            //var fileBytes = (byte[])reader.GetValue(0); // read the file data from the selected row (first column in above query)
        }
    }

You can write in append mode to a binary file. What is the datatype of the Content field? — Steve
– Steve, Commented Jun 14, 2017 at 16:56
@Steve Currently varchar(max). It mostly holds RTF and txt content. — Adam
– Adam, Commented Jun 14, 2017 at 16:57
File.AppendAllText inside the loop and when you have finished the loop ReadAllText — Steve
– Steve, Commented Jun 14, 2017 at 17:00

Steve · Accepted Answer · 2017-06-14 17:06:58Z

2

Given the fact that this Content field contains text data, you can simply use a StringBuilder to add data while you read the content field

using (var command = new SqlCommand("SELECT Content FROM Document WHERE ID=1 ORDER BY Sequence", connection))
using (var reader = command.ExecuteReader())
{
    // Set a large enough initial capacity
    StringBuilder sb = new StringBuilder(32767);
    while(reader.Read())
    {
        sb.Append(reader.GetString(0));
    }
}

Now, at the loop exit, all the content is in the StringBuilder buffer and you can get it back in a byte array with

byte[] buffer = System.Text.Encoding.UTF8.GetBytes(sb.ToString());

answered Jun 14, 2017 at 17:06

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex K. Over a year ago

There is a (byte[]) cast from the reader in the current code so I think it must var/binary

Steve Over a year ago

The OP stated in the comment above that Content is a varchar(max) field

Collectives™ on Stack Overflow

Read a SqlCommand column (multiple rows) into a single C# byte[] array

1 Answer 1

2 Comments

Hot Network Questions