CODEWITHSUNDEEP
c#asp.netarrayssqlcommand
Adam
Adam

Reputation: 2031

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

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

Upvotes: 0

Views: 608

Answers (1)

Steve
Steve

Reputation: 216323

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

Upvotes: 2

Related Questions