Files corrupt when retrieving BLOB data

I am attempting (for the last several days) to save binary files to a SQL
Server 2005 database and then retrieve the files later. Have tried the new
varbinary(max) and image data types and several different techniques with no
luck. I can save 'most' file types and get something useful (will open,
compile, run etc.) but when I try to save and retrieve a .NET assembly
nothing works. The retrieved file is always the correct size but never
exactly the same as the file saved to the database.
The diff tool in SourceSafe shows a difference in the files but I cannot
visibly see the difference. Any help would be appreciated. This is do or die
for the project I am working on.
Code samples are below:
Writing the file:
using (TransactionScope oTranScope = new TransactionScope())
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
try
{
SqlCommand cmd = new SqlCommand("AddWorkZone", cn);
cmd.CommandType = CommandType.StoredProcedure;
System.IO.FileStream fs = new System.IO.FileStream(filepath,
System.IO.FileMode.Open, System.IO.FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] b = br.ReadBytes((int)fs.Length);
fs.Close();
SqlParameter p1 = new SqlParameter("@.WorkZoneName",
SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter("@.ShortDescription",
SqlDbType.VarChar);
SqlParameter p3 = new SqlParameter("@.LongDescription",
SqlDbType.VarChar);
SqlParameter p4 = new SqlParameter("@.Author", SqlDbType.VarChar);
SqlParameter p5 = new SqlParameter("@.AssemblyPath",
SqlDbType.VarChar);
SqlParameter p6 = new SqlParameter("@.SortOrder", SqlDbType.Int);
SqlParameter p7 = new SqlParameter("@.BinaryFile",
SqlDbType.Image, b.Length,
ParameterDirection.Input, false, 0, 0, null,
DataRowVersion.Current, b);
SqlParameter p8 = new SqlParameter("@.WZID", SqlDbType.Int);
p8.Direction = ParameterDirection.Output;
p1.Value = myWorkZone.WorkZoneName;
p2.Value = myWorkZone.ShortDescription;
p3.Value = myWorkZone.LongDescription;
p4.Value = myWorkZone.Author;
p5.Value = fName;
p6.Value = 0;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
cmd.Parameters.Add(p7);
cmd.Parameters.Add(p8);
cn.Open();
cmd.ExecuteNonQuery();
//We need the ID for later use.
WorkZoneID = (int)p8.Value;
}
Reading the file:
//No file exists so we must get a new one or abort.
SqlConnection cn = new
SqlConnection(currentConnectionStrings.GetConnectionString(LF.ConnectionStri
ngs.DatabaseTypes.LightningConfig));
cn.Open();
SqlCommand Cmd = new SqlCommand("SELECT BinaryFile FROM WorkZones WHERE
ID = " + myWorkZone.ID.ToString(), cn);
SqlDataReader Reader =
Cmd.ExecuteReader(CommandBehavior.SequentialAccess);
byte[] filebyte = new byte[0];
if (Reader.HasRows)
{
long startIndex = 0;
long retval;
int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
BinaryWriter bw;
while (Reader.Read())
{
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate,
FileAccess.Write);
bw = new BinaryWriter(fs);
startIndex = 0;
retval = Reader.GetBytes(0, startIndex, outByte, 0, bufferSize);
while (retval == bufferSize)
{
bw.Write(outByte);
bw.Flush();
startIndex += bufferSize;
retval = Reader.GetBytes(0, startIndex, outByte, 0,
bufferSize);
}
bw.Write(outByte, 0, (int)retval - 1);
bw.Flush();
bw.Close();
fs.Close();
www.webtechone.com> nothing works. The retrieved file is always the correct size but never
> exactly the same as the file saved to the database.
It looks to me like the following statement is intentionally dropping the
last byte:
bw.Write(outByte, 0, (int)retval - 1);
I should think the code should be:
if(retval > 0)
{
bw.Write(outByte, 0, (int)retval);
}
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Pleasant" <PaulPleasant@.discussions.microsoft.com> wrote in message
news:4748FE17-5358-4CA0-8D8F-19DC9267B44F@.microsoft.com...
>I am attempting (for the last several days) to save binary files to a SQL
> Server 2005 database and then retrieve the files later. Have tried the new
> varbinary(max) and image data types and several different techniques with
> no
> luck. I can save 'most' file types and get something useful (will open,
> compile, run etc.) but when I try to save and retrieve a .NET assembly
> nothing works. The retrieved file is always the correct size but never
> exactly the same as the file saved to the database.
> The diff tool in SourceSafe shows a difference in the files but I cannot
> visibly see the difference. Any help would be appreciated. This is do or
> die
> for the project I am working on.
> Code samples are below:
> Writing the file:
> using (TransactionScope oTranScope = new TransactionScope())
> {
> using (SqlConnection cn = new SqlConnection(connectionString))
> {
> try
> {
> SqlCommand cmd = new SqlCommand("AddWorkZone", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> System.IO.FileStream fs = new System.IO.FileStream(filepath,
> System.IO.FileMode.Open, System.IO.FileAccess.Read);
> BinaryReader br = new BinaryReader(fs);
> Byte[] b = br.ReadBytes((int)fs.Length);
> fs.Close();
> SqlParameter p1 = new SqlParameter("@.WorkZoneName",
> SqlDbType.VarChar);
> SqlParameter p2 = new SqlParameter("@.ShortDescription",
> SqlDbType.VarChar);
> SqlParameter p3 = new SqlParameter("@.LongDescription",
> SqlDbType.VarChar);
> SqlParameter p4 = new SqlParameter("@.Author",
> SqlDbType.VarChar);
> SqlParameter p5 = new SqlParameter("@.AssemblyPath",
> SqlDbType.VarChar);
> SqlParameter p6 = new SqlParameter("@.SortOrder",
> SqlDbType.Int);
> SqlParameter p7 = new SqlParameter("@.BinaryFile",
> SqlDbType.Image, b.Length,
> ParameterDirection.Input, false, 0, 0, null,
> DataRowVersion.Current, b);
> SqlParameter p8 = new SqlParameter("@.WZID", SqlDbType.Int);
> p8.Direction = ParameterDirection.Output;
> p1.Value = myWorkZone.WorkZoneName;
> p2.Value = myWorkZone.ShortDescription;
> p3.Value = myWorkZone.LongDescription;
> p4.Value = myWorkZone.Author;
> p5.Value = fName;
> p6.Value = 0;
> cmd.Parameters.Add(p1);
> cmd.Parameters.Add(p2);
> cmd.Parameters.Add(p3);
> cmd.Parameters.Add(p4);
> cmd.Parameters.Add(p5);
> cmd.Parameters.Add(p6);
> cmd.Parameters.Add(p7);
> cmd.Parameters.Add(p8);
> cn.Open();
> cmd.ExecuteNonQuery();
> //We need the ID for later use.
> WorkZoneID = (int)p8.Value;
> }
> Reading the file:
> //No file exists so we must get a new one or abort.
> SqlConnection cn = new
> SqlConnection(currentConnectionStrings.GetConnectionString(LF.ConnectionSt
rings.DatabaseTypes.LightningConfig));
> cn.Open();
> SqlCommand Cmd = new SqlCommand("SELECT BinaryFile FROM WorkZones WHERE
> ID = " + myWorkZone.ID.ToString(), cn);
> SqlDataReader Reader =
> Cmd.ExecuteReader(CommandBehavior.SequentialAccess);
> byte[] filebyte = new byte[0];
> if (Reader.HasRows)
> {
> long startIndex = 0;
> long retval;
> int bufferSize = 100;
> byte[] outByte = new byte[bufferSize];
> BinaryWriter bw;
> while (Reader.Read())
> {
> FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate,
> FileAccess.Write);
> bw = new BinaryWriter(fs);
> startIndex = 0;
> retval = Reader.GetBytes(0, startIndex, outByte, 0,
> bufferSize);
> while (retval == bufferSize)
> {
> bw.Write(outByte);
> bw.Flush();
> startIndex += bufferSize;
> retval = Reader.GetBytes(0, startIndex, outByte, 0,
> bufferSize);
> }
> bw.Write(outByte, 0, (int)retval - 1);
> bw.Flush();
> bw.Close();
> fs.Close();
>
> --
> www.webtechone.com|||Good grief!
I copied this code from:
http://msdn.microsoft.com/library/d...romdatabase.asp
That's what I get for copying code and then not debugging it myself.
Thanks a bunch!
--
www.webtechone.com
"Dan Guzman" wrote:

> It looks to me like the following statement is intentionally dropping the
> last byte:
> bw.Write(outByte, 0, (int)retval - 1);
> I should think the code should be:
> if(retval > 0)
> {
> bw.Write(outByte, 0, (int)retval);
> }
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Pleasant" <PaulPleasant@.discussions.microsoft.com> wrote in message
> news:4748FE17-5358-4CA0-8D8F-19DC9267B44F@.microsoft.com...
>
>