What is the most efficient way to read a large number of bytes from SQL Server using SqlDataReader (C #)

Question

What is the most efficient way to read bytes (8-16 K) from SQL Server using SqlDataReader. I seem to know two 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 

Data Type - IMAGE

Answer 1

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

Answer 2

For this blob size, I would go with GetSqlBinary. Below I also included an example of Base64 encoding; something like that:

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