Showing posts with label attempting. Show all posts
Showing posts with label attempting. Show all posts

Wednesday, March 21, 2012

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

Files and file extensions

Hello.

I am attempting to document the various files that are incorporated into a reporting services project and need a more official explanation or defintion of the particular file and it's purpose. I understand what most of the files are and what they serve but would prefer to document it using Microsoft's official explanation so that we can decide which files may require that we source control them.

I have tried searching MSDN for 'file extentions',' file types', and typing in the individual .xxx extensions in to see if there is a documented definition for those files but seemed to get results that do not give me an official definition, didn't come close or were entirely not related.

.sln, .suo, .rds, .rdl, .rdl.data, .rptproj, .rptproj.vspscc, etc

Any links to the official explantion or definition of the files that make up a reporting services report project and their function/importance to the project would be appreciated.

Thanks.

tryin to help u.

http://filext.com/detaillist.php?extdetail=SLN

msdn2.microsoft.com/en-us/df8st53z(VS.80).aspx

msdn2.microsoft.com/en-us/bb165909(VS.80).aspx

filext.com/detaillist.php?extdetail=SUO

i think filext.com can work for u..