"Paul Pleasant" <PaulPleas***@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.ConnectionStrings.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