In a prior post, I showed how to use BCP to extract and save Dynamics GP Document Attach files. I decided to explore this further and use .NET to extract the GP Doc Attach files.
It is very easy to export the file attachments from SQL using .NET, but because you need some data access commands to read the data from the VarBinary field and separate write the data to a file using a stream, it's quite a few more lines than the very simple single-line BCP statement.
This MSDN forum post has the basic code sample, which is 9 lines. So in theory you could have something that simple. But in reality any .NET application of value is going to have a fair amount of additional code for a data access layer and other plumbing, allowing for parameters, processing multiple records, error handling, etc.
To start, I have an ExecuteScalar data access method that returns a byte array.
public static byte[]
ExecuteScalarBinary(string
database, CommandType commandType, string commandText, SqlParameter[] sqlParameters)
{
byte[] scalarResult;
SqlConnection gpConn = new SqlConnection();
try
{
gpConn =
Connection(database);
SqlCommand gpCommand = new SqlCommand(commandText);
gpCommand.Connection
= gpConn;
gpCommand.CommandType = commandType;
if ((commandType == CommandType.StoredProcedure) || (commandType ==
CommandType.Text))
{
if (sqlParameters != null)
{
foreach (SqlParameter sqlParameter in sqlParameters)
{
gpCommand.Parameters.Add(sqlParameter);
}
}
}
object result = gpCommand.ExecuteScalar();
if (result != null)
{
scalarResult = (byte[])result;
}
else
{
scalarResult = null;
}
return scalarResult;
}
catch (Exception ex)
{
throw ex;
}
finally
{
gpConn.Close();
}
}
Next, I have a method that selects the BinaryBlob field value from the coAttachmentItems table and uses a FileStream to save the file to disk.
public static bool
SaveAttachment(string
database, string attachmentID, string fullFilePath)
{
try
{
string sqlCommand = "SELECT BinaryBlob FROM coAttachmentItems WHERE
Attachment_ID = @Attachment_ID";
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Attachment_ID", System.Data.SqlDbType.VarChar, 37);
sqlParameters[0].Value = attachmentID;
byte[] binary =
ExecuteScalarBinary(database, CommandType.Text, sqlCommand, sqlParameters);
FileStream fs = new FileStream(fullFilePath, FileMode.Create);
fs.Write(binary, 0,
binary.Length);
fs.Close();
return true;
}
catch (Exception ex)
{
throw ex;
}
}
Finally, I have a method that retrieves the list of attachments and calls the SaveAttachment method for each file. This is where you could have additional parameters and logic, like maximum number of files, filter by file extension, filter by GP record type, of filter by customer, vendor, etc.
public bool ExportAttachments(string database, int maxFiles,
string directory)
{
try
{
DataTable fileList = new DataTable();
bool success = DataAccess.GetAttachmentList("TWO", ref fileList);
int fileCount = fileList.Rows.Count;
if (fileCount < maxFiles)
{
maxFiles =
fileCount;
}
string attachmentID = string.Empty;
string fileName = string.Empty;
string fullPath = string.Empty;
for (int loop = 1; loop <= maxFiles; loop++)
{
attachmentID =
fileList.Rows[loop - 1]["Attachment_ID"].ToString().Trim();
fileName =
fileList.Rows[loop - 1]["fileName"].ToString().Trim();
fullPath = Path.Combine(directory, fileName);
success = DataAccess.SaveAttachment(database,
attachmentID, fullPath);
}
return true;
}
catch (Exception ex)
{
throw ex;
}
}
This prototype code is relatively simple and works well, writing out my two sample attachments, a text file and a JPG.
So, if you have a .NET application or integration, or perhaps even a reporting platform that can utilize .NET, you could use this approach.
One consideration is that this sample writes the attachment to disk as a file. This would probably be less than ideal for a dynamically generated report, so I would look into whether it is possible to retrieve an in-memory representation of the image file and embed it in the report. I've never done anything like that, so I don't know if that is possible.
Anyway, that's a second method. If I have time, I'll see if I can use VBA in GP Report Writer to extract image files from Doc Attach and embed them on a report.
Steve Endow is a Microsoft MVP
for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.
He is the owner of Precipio Services, which provides Dynamics GP
integrations, customizations, and automation solutions.
Did something similar for when I replaced the GP Quotes with my own SSRS solution and needed to get the attachments and add them to the emailed quote and posted it on Dyndevelopers but your approach is more interesting.
ReplyDelete