What is the most efficient way to read many bytes from SQL Server using SqlDataReader (C#)

Question

Hi everybody! What is the most efficient way to read bytes (8-16 K) from SQL Server using SqlDataReader. It seems I know 2 ways:

byte[] buffer = new byte[4096];
MemoryStream stream = new MemoryStream();
long l, dataOffset = 0;
while ((l = reader.GetBytes(columnIndex, dataOffset, buffer, 0, buffer.Length)) > 0)
{
    stream.Write(buffer, 0, buffer.Length);
    dataOffset += l;
}

and

reader.GetSqlBinary(columnIndex).Value

The data type is IMAGE

Answer 1

GetSqlBinary will load the whole data into memory while your first approach will read it in chunks which will take less memory especially if you need to only process the binary in parts. But once again it depends on what you are going to do with the binary and how it will be processed.

Answer 2

For that blob size, I would go with GetSqlBinary. Below I've also included a Base64 encode example; something like this:

using (SqlConnection con = new SqlConnection("...")) {
    con.Open();
    using (SqlCommand cmd = con.CreateCommand()) {
        cmd.CommandText = "SELECT TOP 1 * FROM product WHERE DATALENGTH(picture)>0";
        using (SqlDataReader reader = cmd.ExecuteReader()) {
            reader.Read();

            byte[] dataBinary = reader.GetSqlBinary(reader.GetOrdinal("picture")).Value;
            string dataBase64 = System.Convert.ToBase64String(dataBinary, Base64FormattingOptions.InsertLineBreaks);

            //TODO: use dataBase64 
        }
    }
}