最近做AX 4.0 到 2012 的升级，需要将AX 4.0 中员工照片迁移到2012相应的位置，有多种思路可以实现该意图，本文仅摘录两种思路中的关键技术细节如下：
思路1：将存储在4.0数据库里的员工照片导出为物理文件，然后将这些物理文件重新上传到2012.
AX 4.0 导出员工照片的代码：
static void Job227(Args _args)
{
CompanyImage m_companyImage;
Image m_image;
str m_path;
EmplTable m_EmplTable;
;
while select m_EmplTable
{
//员工照片保存在CompanyImage表的Image字段，然后通过dataAreaId、EmplTable.TableId、EmplTable.RecId与员工表记录关联
m_companyImage = CompanyImage::find(m_EmplTable.dataAreaId,m_EmplTable.TableId,m_EmplTable.RecId);
if(m_companyImage)
{
m_image = new Image();
m_image.setData(m_companyImage.Image);
m_path = "E:Photo" + m_EmplTable.EmplId + ".jpg";
if(WinAPI::fileExists(m_path))
{
m_image.saveImage(m_path);
}
else
{
try
{
m_image.saveImage(m_path);
}
catch(Exception::Error)
{
info(m_EmplTable.EmplId +" Export error!");
}
}
}
}
}
这段代码执行完后，所有员工照片都以“工号.jpg”为文件名保存到了“D:Photo”文件夹
AX 2012 导入员工照片的代码：
static void ww1207_importPersonImage(Args _args)
{
BinData binData = new BinData();
str extention, path, nameOfFile;
HcmWorker m_HcmWorker;
str imageFilePathName;
container imageContainer;
HcmPersonImage personImage;
;
//AX 2012将员工档案表更名为HcmWorker等多张表
while select m_HcmWorker
{
imageFilePathName = "D:Photo" + m_HcmWorker.PersonnelNumber + ".jpg";
if (imageFilePathname && WinAPI::fileExists(imageFilePathName))
{
binData.loadFile(imageFilePathName);
imageContainer = binData.getData();
ttsbegin;
//AX 2012将员工照片保存在HcmPersonImage表的Image字段，通过HcmPersonImage.Person与HcmWorker表关联
personImage = HcmPersonImage::findByPerson(m_HcmWorker.Person, true);
if(!personImage.RecId)
{
personImage.Person = m_HcmWorker.Person;
personImage.Image = imageContainer;
personImage.insert();
}
else
{
personImage.Image = imageContainer;
personImage.update();
}
ttscommit;
}
else
{
info(m_HcmWorker.PersonnelNumber + " no photo files.");
}
}
}
思路2：在AX 2012中调用T-SQL读取AX 4.0数据库相应字段，转换格式后存入 AX 2012。
static void ww1207_ImportEmplImageFromAX40(Args _args)
{
System.Data.SqlClient.SqlConnection con;
System.Data.SqlClient.SqlCommand com;
System.Data.SqlClient.SqlDataAdapter da;
System.Data.SqlClient.SqlDataReader dr;
System.Data.DataSet ds;
Integer inter;
System.IO.MemoryStream stream;
int i;
Binary bny;
str sSQL="SELECT EmplTable.EmplId,EmplTable.RecId, CompanyImage.Image "+
"FROM EmplTable,CompanyImage "+
"WHERE EmplTable.RecId = CompanyImage.RefRecId";
System.Byte[] content;
int len;
HcmWorker m_HcmWorker;
container imageContainer;
HcmPersonImage personImage;
HcmPersonnelNumberId m_HcmPersonnelNumberId;
;
try
{
con = new System.Data.SqlClient.SqlConnection(strfmt("Data Source=AX4.0_Database_ServerName; Initial Catalog=AX4.0_Database_DatabaseName; Integrated Security=true"));
com = new System.Data.SqlClient.SqlCommand(sSQL,con);
con.Open();
dr = com.ExecuteReader();
while(dr.Read())
{
//EmplTable.EmplId
m_HcmPersonnelNumberId = dr.get_Item(0);
//CompanyImage.Image
content = dr.get_Item(2);
len = content.get_Length();
stream = new System.IO.MemoryStream(content,true);
stream.Write(content,7,len-7);
bny = Binary::constructFromMemoryStream(stream);
imageContainer = bny.getContainer();
m_HcmWorker = HcmWorker::findByPersonnelNumber(m_HcmPersonnelNumberId);
//如果AX 2012员工表存在和4.0相同工号的员工记录
if(m_HcmWorker.RecId)
{
ttsbegin;
//AX 2012将员工照片保存在HcmPersonImage表的Image字段，通过HcmPersonImage.Person与HcmWorker表关联
personImage = HcmPersonImage::findByPerson(m_HcmWorker.Person, true);
if(!personImage.RecId)
{
personImage.Person = m_HcmWorker.Person;
personImage.Image = imageContainer;
personImage.insert();
}
else
{
personImage.Image = imageContainer;
personImage.update();
}
ttscommit;
}
}
con.Close();
dr.Dispose();
}
Catch(Exception::Error)
{
info("error in Error Class");
dr.Dispose();
}
Catch(Exception::CLRError)
{
info(CLRInterop::getLastException().ToString());
con.Close();
dr.Dispose();
}
CodeAccessPermission::revertAssert();
}
其中对4.0中Image字段的处理是该段代码的关键：也就是要将获取的Image字段的二进制数值截掉7个字节长度，至于原因不再赘述，有兴趣者可以参考下面的转帖内容。
Q:
Dear all,
I am trying to display images from a SSRS report by reading a Ax 4.0 database without any luck.
search from the forum give me 2 options by using
1. remove first 7 characters from fields Binary. ( substring (image, 7, datalength(image)).
2. follow MSDN http://msdn.microsoft.com/en-us/library/ms156342.aspx by using
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!ImageFieldName.Value),105))
** I also try other length because we are using Chinese_taiwan collation.
both seems not working. Does anyone has any sucessful experiences? Thanks in advance.
pan
A:
I have experience retrieving AX 4.0 graphic images from SQL, though I have never used them in a report.
The encoding of Binary data into an SQL Image field changed during an AX 4.0 Service Pack, and I can no longer remember which one. So, depending on the AX version in place when the image was saved, the format may vary.
If you view the SQL Image field using SQL Server Management Studio (for example), the following structure exists for SP2+.
0x 07 2B L1 L2 L3 L4 FF D8 ..
In this case, the 2B in the 2nd byte indicates the newer format (SP2+). Immediately following that are 4 bytes that represent the length (L1-L4 in little endian) of the stored Binary data, starting immediately after that (FF D8 in this case, which is how all JPG files start). The length represented by L1-L4 will be 7 less than the DATALENGTH(..) of your raw Image field. I’m not sure if the first byte being 07 is a coincidence or not.
The other format I have seen looks like this.
0x XX FD YY L1 L2 L3 L4 FF D8 ..
In this case, the FD in the 2nd byte indicates the older format. Skipping one byte, the length is again found as L1-L4, and the Binary data starts one byte later (seen here as FF D8 again, for JPG as an example).
it appears to me that there’s an extra byte at the end of the Binary data that is not actually part of the image. Perhaps it’s a checksum or single byte terminator used by AX, but it does not belong in your graphic Binary data. For example, in one of my stored images, the DATALENGTH(image) is 8330 or 0x208A, and the data shows 0x83 0x20 0x00 0x00 or 0x2083, a difference of 7. Skipping the first 6 bytes (it started 0x 07 2B so it’s the new format), that leaves an extra at the end.
Be careful of your substring usage. In .NET, substring is 0 based, meaning that substring(.., 7) in fact skips a full 7 characters (NOT bytes), Substring(“abc”, 1, 1) returns “b” not “a”. And if you’re using Unicode string variables, then Substring(.., 7) could in fact skip 14 bytes.
You probably want to skip 6 bytes and start at the 7th position, discarding the last byte. You can sanity check your code by saving a JPG image for testing and validating during debugging that your Binary result starts with FF D8 and ends with FF D9, since all JPG files start and end this way. You can also code your logic to check the 2nd byte position for the 2B or FD indicator and adjust the starting position of the Binary data accordingly, in case you have a mixture of old and new Image data.
Good luck.
原帖地址：https://community.dynamics.com/product/ax/f/33/p/55226/109394.aspx
转载请注明：ww12345678 的部落格 | AX Helper » How to read AX 4.0 db image from SSRS 2005/2008