0

I am trying to export employee images from a database, save them onto my computer, then upload them to Salesforce in each employee's account. My problem is that there must be an issue with the way that I am saving the files from the database. When I go to upload to Salesforce the picture file pulled from the database, it tells me "your file cannot be uploaded at this time. please try again in a few minutes". However, when I email an individual picture to myself, download it, then upload that picture, it works. (I tried to do this en masse; it did not work). I have come to the conclusion that the issue might be with the code. The photo is stored as varbinary(max) and saved as both .png and .jpeg types, and extracted from the database using a variation of MSDN's code:

   SqlConnection connection = new SqlConnection(connectionString);
   SqlCommand command = new SqlCommand(queryString, connection);

   connection.Open();
   SqlDataReader myReader = command.ExecuteReader(CommandBehavior.SequentialAccess);

   while (myReader.Read())
   {
      filePath = @"C:\Users\npredey\Documents\Photos_from_database\" + myReader["first_name"] + '_' + myReader["last_name"];
      FileStream fs = new FileStream(filePath + ".gif", FileMode.OpenOrCreate, FileAccess.Write); // creates a new file for output

      bw = new BinaryWriter(fs);

      startIndex = 0;
      retval = myReader.GetBytes(3, startIndex, outbyte, 0, bufferSize);

      while (retval == bufferSize)
      {
         bw.Write(outbyte);
         bw.Flush();

         startIndex += bufferSize;
         retval = myReader.GetBytes(3, startIndex, outbyte, 0, bufferSize);
      }

      bw.Write(outbyte, 0, (int)retval - 1);
      bw.Flush();

      bw.Close();
      fs.Close();
   }
   myReader.Close();
   connection.Close();

Could there be an issue with how the file is saved, where it would not be able to be uploaded? I have saved the file with all three acceptable extensions (.gif, .jpg, .png) and none of them have worked.

Nick
  • 823
  • 2
  • 10
  • 22
  • "The photo is stored as varbinary(max)" - but using what format? – enkryptor Jun 20 '16 at 15:05
  • @enkryptor edited. – Nick Jun 20 '16 at 15:06
  • 1
    Looks like you are storing your image data in a `Filestream` column, well done :) If you have the server option `Enable FILESTREAM for file I/O streaming access` enabled then you can access the data directly through Windows Explorer. That might save you a headache. – Chris Pickford Jun 20 '16 at 15:10
  • What do you mean "both .png and .jpeg"? Do you have two records per picture in your database? – enkryptor Jun 20 '16 at 15:13
  • @enkryptor no I mean that there are separate pictures (only one) that has either a `.jpeg` or `.png` – Nick Jun 20 '16 at 15:26
  • And you don't know, which is which? Could you show the query? – enkryptor Jun 20 '16 at 15:27
  • @enkryptor I do know which is which. The query is `"SELECT e.emp_id, e.first_name, e.last_name, ep.photo FROM HT_HOST.dbo.emp e JOIN HT_HOST.dbo.emp_photo ep ON e.emp_id = ep.emp_id";`, where `ep.photo` is the column containing the photo. – Nick Jun 20 '16 at 15:30
  • 1
    This one can be related - http://stackoverflow.com/questions/2579373/saving-any-file-to-in-the-database-just-convert-it-to-a-byte-array – enkryptor Jun 20 '16 at 15:31

1 Answers1

1

On last write you shouldn't substract 1 from retval.

retval contains the numbers of bytes read. The third parameters of BinaryWriter.Write is the numbers of bytes to write.

As you want to write all bytes you should only pass retval instead of retval - 1, which would keep the last byte from beign written.

Shago
  • 605
  • 4
  • 12