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

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 = n开发者_运维百科ew 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

---

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.

---

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