Retrieving Binary/Blob files from Microsoft Dynamics Nav wit

John777

I am working with a MS Dynamics Nav Database that have a file attachment tables. The files are stored in MS SQL. I am able to pull the files to my desktop with a custom asp.net application that I have built, but when I open the files, they are corrupted. These are PDFs files that are located in the "image" file type column of the database and I have tried to download over 20 files. All of them varies in size and seem to download successfully.
The reason why I suspect these are PDFs files is because the column right next to the binary columns give me the name of the file as in PDF format. I have also tried to renaming the file after I download to different image formats but without any luck when I tried to open it. This is not my first project to retrieve binary files, from MS SQL database. If anyone work on getting files off the Nav database before, please help me. The sample code below I wrote to retrieve files using LINQ to SQL when I give it a specific ID in the browser. Please advice me if you know any sort of compression or encryption in the binary files itself and how to grab the file successfully to read it. Thanks

protected void getFileFromID(string queryid)
    {
        string Filename = string.Empty;


        byte[] bytes;

        try
        {
            DataClassesFilesDataContext dcontext = new DataClassesFilesDataContext();

            var myfile = (from file in dcontext.Comptroller_File_Attachments
                          where file.No_ == queryid
                          select file).First();

            if (myfile.Table_ID.ToString().Length > 0 && myfile.Attachment != null)
            {
                Filename = myfile.FileName.ToString();

                bytes = myfile.Attachment.ToArray();
                Response.Clear();                   
                Response.ContentType = "application/octet-stream";                 
                Response.AddHeader("Content-Disposition", "attachment; filename=" + Filename);
                Response.BinaryWrite(bytes);               
                Response.End();
            }

            else
            {
                Response.Write("no file exist");
            }
        }

        catch (Exception e)
        {
            Response.Write(e);

        }
    }

thegunzo

I have done this, here is a part of the webpage I created.

query = @SELECT [Link ID], , [File Name], [Content Type], [Object] FROM [ + databaseName +
@].dbo.[ + linkTableName + @] WHERE [Link ID] =' + linkId + @';

sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = query;
sqlDataReader = sqlCommand.ExecuteReader();

if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
fileName = sqlDataReader.GetValue(2).ToString();
contentType = sqlDataReader.GetValue(3).ToString();

_ImageBytes = (Byte[])(sqlDataReader.GetValue(4));
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "startUpScriptBlock", "alert('No document found with " + linkId + " calling: " + query +"');", true);
sqlConnection.Close();
return;
}
{
Page.Response.Clear();
Page.Response.Expires = 0;
Page.Response.Buffer = false;
Page.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Page.Response.AddHeader("Content-Length", _ImageBytes.Length.ToString());
Page.Response.ContentType = ContentType;
Page.Response.BinaryWrite(_ImageBytes);
Page.Response.End();
}

mdPartnerNL

so no option to do this thru NAV?

thegunzo

Sure there is.

You can easilly insert and select entries from SQL database with a field type Image. In NAV you handle the field as a byte array.

You can also download the file from the web page into NAV with HttpWebRequest.

Everything is possible with DotNet

John777

Sorry. Thanks for the answer. But I figured out the problem. The way that I found to decompress these files are to remove the first 4 bytes in the array of bytes of the BLOB file and then use deflatestream to retrieve the file.

mdPartnerNL

ok, good, so the blob is created in the NAV client? and now you get the data in c#

and for this you need to remove 4 bytes. Is it possible to show a codesnippet?

John777

Code snippet is below and required .net >= 4.0. You can re-write it to your liking or optimize it. I am using LINQ-SQL but you can change the filename to a public variable in a separate class file to avoid using tuple and re-run of the function. Basically, it is taking in a fileID with a query parameter at the end of the URL. Here it is:

//get bytes and remove first 4 bytes from bytes array
  protected Tuple<byte[], string> getBytesfromFile(string queryID)
  {

      byte[] MyFilebytes = null;
      string filename = string.Empty;

        try
        {
            DataClassesFilesDataContext dcontext = new DataClassesFilesDataContext();

            var myfile = (from file in dcontext.Comptroller_File_Attachments
                          where file.No_ == queryID
                          select file).First();

            if (myfile.Table_ID.ToString().Length > 0 && myfile.Attachment != null)
            {

                MyFilebytes = myfile.Attachment.ToArray().Skip(4).ToArray();
               filename = myfile.FileName.ToString();
            }

            else
                Response.Write("no byte to return");

        }
      catch 
     {
         Response.Write("no byte");
     }

        return Tuple.Create(MyFilebytes, filename);
  }

    //after getting the remaining bytes (after removing 4 first byte) deflate the byte and then store it in a memory steam and get the result back.
  protected void getFile()
  {
      try
      {
          string Filename = string.Empty;
          byte[] myfile = getBytesfromFile(getQueryID()).Item1;

          byte[] result;

          using (Stream input = new DeflateStream(new MemoryStream(myfile),
                                        CompressionMode.Decompress))
          {
              using (MemoryStream output = new MemoryStream())
              {
                  input.CopyTo(output);
                  result = output.ToArray();
              }
          }

          Filename = getBytesfromFile(getQueryID()).Item2;


          Response.Clear();
          Response.ContentType = "application/octet-stream";
          Response.AddHeader("Content-Disposition", "attachment; filename=" + Filename);
          Response.BinaryWrite(result);
          Response.End();
      }

      catch (Exception e)
      {
          Response.Write(e);
      }

  }
    //pass in file id
    protected string getQueryID()
    {

        QueryID.QueryStringID = Request.QueryString["fileid"];
        return QueryID.QueryStringID;
    }

mdPartnerNL

Thanks for sharing, really good info.

Juhl

Have you disabled compression on the field in NAV?