Import and Export file from BLOB storage Account in D365 F&O using X++
Import:
/// <summary>
/// MKInventQualityOrderLineService
/// </summary>
using Microsoft.Azure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.WindowsAzure.Storage.File;
using System.Net;
/// <summary>
/// MKInventQualityOrderLineService
/// </summary>
class MKInventQualityOrderLineService extends SysOperationServiceBase
{
#File
#define.delimiterField(',')
InventParameters inventParameters;
/// <summary>
/// processMultipleFiles
/// </summary>
/// <param name = "_contract">MKInventQualityOrderLineContract</param>
public void processMultipleFiles(MKInventQualityOrderLineContract _contract)
{
#OCCRetryCount
System.IO.MemoryStream memoryStream;
System.String storageAccountName;
System.String keyValue;
CloudStorageAccount storageAccount;
CloudFileClient fileClient;
CloudFileShare fileShare;
CloudFileDirectory fileDirectoryRoot;
CloudFileDirectory fileDirectory;
CloudFile file;
TextStreamIo textStreamIo;
inventParameters = InventParameters::find();
storageAccountName = inventParameters.MKStorageAccountName;
keyValue = inventParameters.MKAccountKeyValue;
var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(storageAccountName, keyValue);
storageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);
fileClient = storageAccount.CreateCloudFileClient();
fileShare = fileClient.GetShareReference(inventParameters.MKFileShare);
if (fileShare.Exists(null, null))
{
fileDirectoryRoot = fileShare.GetRootDirectoryReference();
fileDirectory = fileDirectoryRoot.GetDirectoryReference(inventParameters.MKLineSourcePath);
if (fileDirectory.Exists(null, null))
{
// Get a reference to the root directory for the share.
CloudFileDirectory rootDirectory = fileShare.GetRootDirectoryReference();
// Get a reference to the directory we created previously.
CloudFileDirectory readFolder = rootDirectory.GetDirectoryReference(inventParameters.MKLineSourcePath);
Microsoft.WindowsAzure.Storage.File.FileRequestOptions fileRequestOptions;
var files = readFolder.ListFilesAndDirectories(fileRequestOptions, null);
var enumerator = files.getEnumerator();
while (enumerator.moveNext())
{
File= enumerator.Current as CloudFile;
if (file.Exists(null, null))
{
try
{
memoryStream = new System.IO.MemoryStream();
file.DownloadToStream(memoryStream, null, null, null);
this.importFromBlob(memoryStream, File.Name) ;
textStreamIo = TextStreamIo::constructForRead(memoryStream);
DMFDefinitionGroupExecution definitionGroupExecution;
select firstonly TargetStatus from definitionGroupExecution
order by RecId desc
where definitionGroupExecution.DEFINITIONGROUP == inventParameters.MKLineDefinationGroupName;
//upload
if(definitionGroupExecution.TargetStatus == DMFBatchJobStatus::Finished)
{
this.writeFile(File.Name, inventParameters.MKLineDestinationPath, memoryStream);
}
else
{
this.writeFile(File.Name, inventParameters.MKLineErrorPath, memoryStream);
}
this.deleteFile(File.Name, inventParameters.MKLineSourcePath);
InventQualityOrderTable qualityOrder;
Args args = new Args();
while select qualityOrder
where qualityOrder.OrderStatus == InventTestOrderStatus::Open
{
args.record(qualityOrder) ;
InventQualityOrderValidate validate = InventQualityOrderValidate::newQualityOrderTable(qualityOrder);
validate.run();
}
}
catch
{
continue;
}
}
}
}
}
}
/// <summary>
/// deleteFile meathod
/// </summary>
/// <param name = "_fileName">Filename</param>
/// <param name = "_deleteLocation">str</param>
public void deleteFile(Filename _fileName, str _deleteLocation = 'Outbound')
{
str connectStr = strFmt('DefaultEndpointsProtocol=https;AccountName=%1;AccountKey=%2;EndpointSuffix=core.windows.net', inventParameters.MKStorageAccountName, inventParameters.MKAccountKeyValue);
str deleteLocationPath = _deleteLocation;
str exceptionMessage;
try
{
CloudStorageAccount storageAccount = CloudStorageAccount::Parse(connectStr);
CloudFileClient fileClient = storageAccount.CreateCloudFileClient();
//Processed is a folder that I have in my Azure file share
CloudFileShare fileShare;
CloudFile tempCloudFile;
//Folder in Azure file share
fileShare = fileClient.GetShareReference(inventParameters.MKFileShare);
if (fileShare.Exists(null, null) == false)
{
throw error (strFmt("@BAE:Filesharewasnotfound", inventParameters.MKFileShare));
}
if (fileShare.Exists(null, null))
{
// Get a reference to the root directory for the share.
CloudFileDirectory rootDirectory = fileShare.GetRootDirectoryReference();
// Get a reference to the directory we created previously.
CloudFileDirectory sampleDir = rootDirectory.GetDirectoryReference(deleteLocationPath);
tempCloudFile = sampleDir.GetFileReference(_fileName);
if (tempCloudFile.Exists(null, null) == true)
{
tempCloudFile.Delete(null, null, null);
}
}
}
catch (Exception::CLRError)
{
System.Exception interopException = CLRInterop::getLastException();
if (interopException != null)
{
if (interopException is System.Reflection.TargetInvocationException)
{
System.Exception innerException = interopException.InnerException;
if (innerException != null)
{
exceptionMessage = innerException.Message;
}
}
else
{
exceptionMessage = interopException.Message;
}
throw error (exceptionMessage);
}
}
}
/// <summary>
/// writeFile
/// </summary>
/// <param name = "_fileName">Filename</param>
/// <param name = "_writeLocation">str</param>
/// <param name = "_stream">System.IO.Stream</param>
/// <param name = "overwrite">boolean</param>
public void writeFile(Filename _fileName = 'dummyTestFile', str _writeLocation = 'Outbound', System.IO.Stream _stream = null,boolean overwrite=false)
{
str connectStr = strFmt('DefaultEndpointsProtocol=https;AccountName=%1;AccountKey=%2;EndpointSuffix=core.windows.net', inventParameters.MKStorageAccountName, inventParameters.MKAccountKeyValue);
str writeLocationPath = _writeLocation;
;
System.IO.Stream memoryStream;
str exceptionMessage;
try
{
CloudStorageAccount storageAccount = CloudStorageAccount::Parse(connectStr);
CloudFileClient fileClient = storageAccount.CreateCloudFileClient();
//Processed is a folder that I have in my Azure file share
CloudFileShare fileShare;
CloudFile tempCloudFile;
if (_stream)
{
memoryStream = _stream;
}
fileShare = fileClient.GetShareReference(inventParameters.MKFileShare);
if (fileShare.Exists(null, null) == false)
{
throw error (strFmt("@MK:Filesharewasnotfound", inventParameters.MKFileShare));
}
if (fileShare.Exists(null, null))
{
// Get a reference to the root directory for the share.
CloudFileDirectory rootDirectory = fileShare.GetRootDirectoryReference();
// Get a reference to the directory we created previously.
CloudFileDirectory sampleDir = rootDirectory.GetDirectoryReference(writeLocationPath);
//tempCloudFile = sampleDir.GetFileReference(strFmt("%1_%2.txt", _fileName, timeNow()));
tempCloudFile = sampleDir.GetFileReference(strFmt('%1', _fileName));
if(overwrite)
{
memoryStream.Position = 0; // have to reset position, else you will get an empty file
tempCloudFile.UploadFromStream(memoryStream, null, null, null);
}
else
{
if (tempCloudFile.Exists(null, null) == false)
{
memoryStream.Position = 0; // have to reset position, else you will get an empty file
tempCloudFile.UploadFromStream(memoryStream, null, null, null);
}
}
if (tempCloudFile.Exists(null, null) == false)
{
memoryStream.Position = 0; // have to reset position, else you will get an empty file
tempCloudFile.UploadFromStream(memoryStream, null, null, null);
}
memoryStream.Flush();
memoryStream = null;
}
}
catch (Exception::CLRError)
{
System.Exception interopException = CLRInterop::getLastException();
if (interopException != null)
{
if (interopException is System.Reflection.TargetInvocationException)
{
System.Exception innerException = interopException.InnerException;
if (innerException != null)
{
exceptionMessage = innerException.Message;
}
}
else
{
exceptionMessage = interopException.Message;
}
throw error (exceptionMessage);
}
}
}
/// <summary>
/// DMFDefinitionGroupEntity
/// </summary>
/// <param name = "_uploadedStatement">SharedServiceUnitFileID</param>
/// <param name = "definitionGroup">DMFDefinitionGroup</param>
/// <returns>filePath</returns>
public DMFLocalFilePath applyTransforms(SharedServiceUnitFileID _uploadedStatement, DMFDefinitionGroup definitionGroup)
{
DMFDefinitionGroupEntity definitionGroupEntity = DMFDefinitionGroupEntity::find(inventParameters.MKLineDefinationGroupName, 'Quality order line results');// //this.findDMFDefinitionGroupEntity(definitionGroup);
DMFExecutionId executionId = DMFUtil::setupNewExecution(definitionGroup.DefinitionGroupName);
DMFDefinitionGroupExecution execution = DMFDefinitionGroupExecution::find(
definitionGroup.DefinitionGroupName,
definitionGroupEntity.Entity,
executionId,
true);
execution.IsTransformed = NoYes::No;
DMFLocalFilePath filePath = execution.applyTransforms(_uploadedStatement);
DMFExecution e = DMFExecution::find(executionId, true);
e.delete();
return filePath;
}
/// <summary>
/// importFromBlob meathod
/// </summary>
/// <param name = "_memory">System.IO.Stream</param>
/// <param name = "_fileName">str</param>
public void importFromBlob(System.IO.Stream _memory, str _fileName)
{
SharedServiceUnitFileID fileId;
DMFDefinitionGroup definitionGroup;
DMFDefinitionGroupEntity definitionGroupEntity;
DMFExecutionId executionId;
DMFDefinitionGroupExecution execution;
// Should be used to get file into FileUploadTemporaryStorageResult object
FileUploadTemporaryStorageResult result
= File::SendFileToTempStore_GetResult(_memory, _fileName);
if (result && result.getUploadStatus())
{
fileId = result.getFileId();
definitionGroup = this.findDefinitionGroup(_fileName);
this.applyTransforms(fileId, definitionGroup);
definitionGroupEntity = DMFDefinitionGroupEntity::find(inventParameters.BAELineDefinationGroupName, 'Quality order line results');// this.findDMFDefinitionGroupEntity(definitionGroup);
executionId = DMFUtil::setupNewExecution(definitionGroup.DefinitionGroupName);
// Find execution
execution = DMFDefinitionGroupExecution::find(
definitionGroup.DefinitionGroupName,
definitionGroupEntity.Entity,
executionId,
true
);
execution.FilePath = fileId;
execution.IsTransformed = NoYes::Yes;
execution.IsSelected = NoYes::Yes;
execution.ExecuteTargetStep = NoYes::Yes;
execution.update();
setPrefix(strFmt("@SYS73667", _fileName));
// Import the file via quick import DMF
DMFQuickImportExport::doPGImport(definitionGroup.DefinitionGroupName, executionId, true);
// Deletes file
result.deleteResult();
}
}
/// <summary>
/// DMFDefinitionGroup
/// </summary>
/// <param name = "_fileName">Filename</param>
/// <returns>definitionGroup</returns>
public DMFDefinitionGroup findDefinitionGroup(Filename _fileName)
{
DMFDefinitionGroup definitionGroup;
select firstonly definitionGroup
where definitionGroup.DefinitionGroupName == inventParameters.BAELineDefinationGroupName;
return definitionGroup;
}
}
Export:
/// <summary>
/// MKQualityOrdersLineExportService
/// </summary>
using Microsoft.Azure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.WindowsAzure.Storage.File;
using System.Net;
/// <summary>
/// MKQualityOrdersLineExportService
/// </summary>
class MKQualityOrdersLineExportService extends SysOperationServiceBase
{
#File
#define.delimiterField(',')
InventParameters inventParameters;
/// <summary>
/// writeFile meathod
/// </summary>
/// <param name = "_fileName">Filename</param>
/// <param name = "_writeLocation">str</param>
/// <param name = "_stream">System.IO.Stream</param>
/// <param name = "overwrite">boolean</param>
public void writeFile(Filename _fileName = 'dummyTestFile', str _writeLocation = 'Outbound', System.IO.Stream _stream = null,boolean overwrite=false)
{
str connectStr = strFmt('DefaultEndpointsProtocol=https;AccountName=%1;AccountKey=%2;EndpointSuffix=core.windows.net',
inventParameters.MKStorageAccountName, inventParameters.MKAccountKeyValue);
str writeLocationPath = _writeLocation;
;
System.IO.Stream memoryStream;
str exceptionMessage;
try
{
CloudStorageAccount storageAccount = CloudStorageAccount::Parse(connectStr);
CloudFileClient fileClient = storageAccount.CreateCloudFileClient();
//Processed is a folder that I have in my Azure file share
CloudFileShare fileShare;
CloudFile tempCloudFile;
if (_stream)
{
memoryStream = _stream;
}
fileShare = fileClient.GetShareReference(inventParameters.MKFileShare);
if (fileShare.Exists(null, null) == false)
{
throw error (strFmt("@MK:Filesharewasnotfound", inventParameters.MKFileShare));
}
if (fileShare.Exists(null, null))
{
// Get a reference to the root directory for the share.
CloudFileDirectory rootDirectory = fileShare.GetRootDirectoryReference();
// Get a reference to the directory we created previously.
CloudFileDirectory sampleDir = rootDirectory.GetDirectoryReference(writeLocationPath);
//tempCloudFile = sampleDir.GetFileReference(strFmt("%1_%2.txt", _fileName, timeNow()));
tempCloudFile = sampleDir.GetFileReference(strFmt('%1', _fileName));
if(overwrite)
{
memoryStream.Position = 0; // have to reset position, else you will get an empty file
tempCloudFile.UploadFromStream(memoryStream, null, null, null);
}
else
{
if (tempCloudFile.Exists(null, null) == false)
{
memoryStream.Position = 0; // have to reset position, else you will get an empty file
tempCloudFile.UploadFromStream(memoryStream, null, null, null);
}
}
if (tempCloudFile.Exists(null, null) == false)
{
memoryStream.Position = 0; // have to reset position, else you will get an empty file
tempCloudFile.UploadFromStream(memoryStream, null, null, null);
}
memoryStream.Flush();
memoryStream = null;
}
}
catch (Exception::CLRError)
{
System.Exception interopException = CLRInterop::getLastException();
if (interopException != null)
{
if (interopException is System.Reflection.TargetInvocationException)
{
System.Exception innerException = interopException.InnerException;
if (innerException != null)
{
exceptionMessage = innerException.Message;
}
}
else
{
exceptionMessage = interopException.Message;
}
throw error (exceptionMessage);
}
}
}
/// <summary>
/// processMultipleFiles meathod
/// </summary>
/// <param name = "_contract">MKQualityOrdersLineExportContract</param>
public void processMultipleFiles(MKQualityOrdersLineExportContract _contract)
{
#DMF
SharedServiceUnitFileID fileId;
inventParameters = inventParameters::find();
DMFDefinitionGroupName definitionGroupName = InventParameters.MKExportLineDefinitionGroupName; //"lineexport";
try
{
EntityName entityName = DMFEntity::findFirstByTableId(tableNum(InventQualityOrderLineEntity)).EntityName;
// Start:Optional if you want to filter data while exporting
Query query = new Query(DMFUtil::getDefaultQueryForEntityV3(entityName));
QueryBuildDataSource qbds = query.dataSourceTable(tableNum(InventQualityOrderLineEntity));
// End
DMFEntityExporter exporter = new DMFEntityExporter();
fileId = exporter.exportToFile(entityName,
definitionGroupName,
'', //Optional: ExecutionID
'CSV', //Optional::SourceName
#FieldGroupName_AllFields, //Optional field selection
query.pack(), //Optional: Filtered Query
curExt() //Optional: DataAReaId
);
if (fileId != '')
{
str downloadUrl = DMFDataPopulation::getAzureBlobReadUrl(str2Guid(fileId));
System.Uri uri = new System.Uri(downloadUrl);
str fileExt;
if (uri != null)
{
fileExt = System.IO.Path::GetExtension(uri.LocalPath);
}
Filename filename = strFmt('QualityOrderLineExport%1',fileExt);
System.IO.Stream stream = File::UseFileFromURL(downloadUrl);
File::SendFileToUser(stream, filename);
DMFDefinitionGroupExecution definitionGroupExecution;
select firstonly TargetStatus from definitionGroupExecution
order by RecId desc
where definitionGroupExecution.DEFINITIONGROUP == inventParameters.MKExportLineDefinitionGroupName;
//upload
if(definitionGroupExecution.TargetStatus == DMFBatchJobStatus::Finished)
{
this.writeFile(filename, inventParameters.MKExportLineDestinationPath, stream);
}
else
{
this.writeFile(filename, inventParameters.MKExportLineErrorPath, stream);
}
}
else
{
throw error("@MK:DMFexecutionfailedanddetailswerewrittentotheexecutionlog");
}
}
catch
{
error("@MK:Erroroccurredwhileexporting");
}
}
}