code to retrieve binary field from Sql Server

[=?Utf-8?B?c2NvdHRybQ==?=]

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;
--
Scott

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

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


--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"scottrm" wrote:

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;
--
Scott