code to retrieve binary field from Sql Server

[Guest]

I have some code to read the contents of a varbinary(max) field in SQL Server
2005. It is working ok I just wanted to know if anyone had any comments on
it's efficiency.

Code to read from the field

using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
if (reader.Read())
{
MemoryStream stream = new MemoryStream();
int bufferSize = 32768;
byte[] outbyte = new byte[bufferSize];
long startIndex = 0;
int columnIndex = 4;
int bufferIndex = 0;
int bytesReceived;
BinaryWriter bw = new BinaryWriter(stream);


while ((bytesReceived =
Convert.ToInt32(reader.GetBytes(columnIndex, startIndex, outbyte,
bufferIndex, bufferSize))) > 0)
{
bw.Write(outbyte, 0, bytesReceived);
bw.Flush();
startIndex += bufferSize;
}

cv.CVContent = stream;

return cv;

}



*********************************************

Code to write into the field

using (SqlCommand cmd = new SqlCommand())
{
int bufferSize = 32768;
Byte[] binaryDataBuffer = new Byte[bufferSize];
Byte[] binaryData = new Byte[inputStream.Length];
int bytesRead;

BinaryReader br = new BinaryReader(inputStream);
MemoryStream ms = new MemoryStream();
BinaryWriter bw = new BinaryWriter(ms);

while ((bytesRead = br.Read(binaryDataBuffer, 0,
bufferSize)) > 0)
{
bw.Write(binaryDataBuffer, 0, bytesRead);
}

binaryData = ms.ToArray();

cmd.Connection = gwenv.DatabaseConnection;
cmd.Parameters.Add("CVContent", SqlDbType.VarBinary).Value =
binaryData;

 

[Guest]

You can use the

SqlBytes bytes = reader.GetSqlBytes(1);

method, which vastly cuts down on the amount of code.

Then, for example:
Bitmap productImage = new Bitmap(bytes.Stream);

Here is the documentation page:

http://msdn2.microsoft.com/en-us/library/a1904w6t.aspx

Peter