In a previous article, the process of extracting and storing Dynamics GP Document Attach files using BCP was demonstrated. This article will delve further into this topic, exploring how to achieve the same outcome using .NET.
While exporting file attachments from SQL using .NET is straightforward, it requires a series of data access commands to read data from the VarBinary field and write it to a file through a stream. This complexity results in significantly more lines of code compared to the simple single-line BCP statement.
An MSDN forum post provides a basic code sample comprising nine lines, implying a simple solution. However, in practice, a valuable .NET application will necessitate additional code for a data access layer, parameter handling, multiple record processing, error handling, and other essential components.
To begin, an ExecuteScalar data access method is defined, responsible for returning a byte array.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
| 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, a method is created to select the BinaryBlob field value from the coAttachmentItems table. This method utilizes a FileStream to save the file to the disk.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| 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, a method retrieves the attachment list and invokes the SaveAttachment method for each file. This method allows for additional parameters and logic, such as specifying the maximum number of files, filtering by file extension, filtering by GP record type, or filtering by customer, vendor, and so on.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
| 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 demonstrates simplicity and effectiveness, successfully writing out the two sample attachments: a text file and a JPG image.
Therefore, this approach proves suitable for .NET applications, integrations, or reporting platforms capable of utilizing .NET.
However, it’s important to note that this sample writes the attachment to disk as a file, which may not be ideal for dynamically generated reports. Exploring the possibility of retrieving an in-memory representation of the image file and embedding it directly into the report could be beneficial. Further investigation is required to determine the feasibility of this approach.
This concludes the exploration of the second method. Future endeavors may involve investigating the use of VBA in GP Report Writer to extract image files from Doc Attach and embed them directly into a report, time permitting.