Debugging Test/UAT Data from Development VM in D365FO

 JIT access to TEST/UAT

Just-in-time access which is required for various troubleshooting efforts, running unplanned queries, or data upgrade problem solving.

Go to LCS and select the environment for which you wanted to connect the database. In my case, I am using TEST.

Enter the firewall by adding the IP address of the Dev VM from where the database will be connected.

Upon clicking Confirm, there will be a message which says about the expiry time of this firewall. Usually 8 hours.

The next step is accessing the database and the mode needed.

In the LCS page, under Database Accounts section, 4 options will be displayed for the accessing reason. As we are troubleshooting, we need write access and thus selecting ‘Troubleshooting tuning for AX”.

Select the reason and enter the details.

Click ‘Request Access’ and refresh the page. User will be presented with the login details .

Note down the details of SQL server, database name , user name and password.

These are needed when we connect the database from dev VM.

Refer Microsoft link for further information

Connect to Test Database

Now with the above credentials, connect to the TEST database and create a new login which will be used for debugging.

Use SQL Server authentication login(not Windows) to connect the database

Execute the below query which creates the new user used for debugging .

CREATE USER [axdevdebugadmin] WITH PASSWORD=N'Pass@word1', DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_owner', 'axdevdebugadmin'
GO

Web.Config changes:

Connect to the VM and navigate to the folder having ‘AOS Service’ -> WebRoot and select web.config file.

Take a copy of this file as we are going to modify some of the details in the file to connect the dev VM to TEST database.

Stop the following services :

IIS

World wide publishing

MS Dynamics 365 Batch

MS Dynamics 365 DIXF

Close the VS

Any active connection to local SQL db.

Right click on ‘Web.config’ file and open in Notepad. Make changes to the following tags.

<add key="DataAccess.Database" value="LCS JIT database name" />
<add key="DataAccess.DbServer" value="LCS JIT database server />
<add key="DataAccess.SqlUser" value="LCS JIT user name" />
<add key="DataAccess.SqlPwd" value="LCS JIT password" />
<add key="DataAccess.AxAdminSqlPwd" value="pass@word1" />
<add key="DataAccess.AxAdminSqlUser" value="axdevdebugadmin" />

Start the IIS and World wide publishing service.

Connect the Dev VM URL . You can check if it is accessing the test database by navigating to

System Administration -> Inquiries -> Database -> Database Information

Now the system is ready to start the debugging.

Tips and precautions while doing the process

1. Make sure the VM and the higher environment are in the same version .
2. Latest code in the VM where the debugging is triggered. Remove unwanted pending changes and do a buikd+sync in the VM before initiating the connection.
3. It is always recommended to have the code base same in both the environments.
4. Do not trigger database sync after connecting to TEST/UAT database .
5. Take a copy of the web.config file before making the changes.
6. Revert the changes once the debugging is finished. If left with the same web.config file , the URL will be inaccessible after 8 hours. Please note the JIT is alive only for 8 hours.
7. If the database is refreshed within 8 hours, we need to generate a new JIT access.

Difference between datetime x++

In this post I am going to show the difference between 2  utc datetime and it will return the Days,Hours,Minute,Seconds

 utcDatetime PreparedOn ;
 utcDatetime DispensedOn ;
 str diffTime ;
 int time;

if(DispensedOn !=utcdatetimenull() && PreparedOn!=utcdatetimenull())
 {
     time           = DateTimeUtil :: getDifference(DispensedOn,PreparedOn);
     diffTime    = strFmt('%1d:%2h:%3m:%4S',(time div 86400),((time Mod 86400) div 3600),((time Mod 3600) div 60),((time Mod 3600) Mod 60));
  }

Reference :https://msaxcrm.blogspot.com/2018/09/difference-between-2-utc-datetime-ax.html

Upload and download file from Blob Storage using x++

 

Upload and download file from Blob Storage using x++

By using below code we connect to Blob storage. Below operations also we can do it.

•     File List
•     Read the file content
•     Move the file between folder.
•     Upload File
•     Delete File

using Microsoft.WindowsAzure.Storage;
Using Microsoft.WindowsAzure.Storage.Blob;
using System.IO;
class TestAzureBlob 
{
    public static void main(Args _args)
    {
        TestAzureBlob       testAzureBlob = new TestAzureBlob();
        CloudBlobContainer  blobContainer;

        // Connecting container
        blobContainer = testAzureBlob.connectToAzureBlob();

        // Get's the file List
        testAzureBlob.getFilesList(blobContainer);// Way 1
        testAzureBlob.readTheFiles(blobContainer);// Way 2

        // Read the data in file
        testAzureBlob.readFileValue(blobContainer);

        // upload the file
        testAzureBlob.UploadFileToBlob(blobContainer);

        // move the file
        testAzureBlob.moveTheFileFromOneFolderToAnotherFolder(blobContainer);

        // delete the file
        testAzureBlob.deleteFileFromFolder(blobContainer);
    }
}

    Connect to Blob:

    public CloudBlobContainer connectToAzureBlob()
    {
        CloudBlobClient 	cloudBlobClient;
        CloudBlobContainer	cloudBlobContainer;
        CloudStorageAccount cloudStorageAccount;
  
        cloudStorageAccount = CloudStorageAccount::Parse("Azure Blob Connection String");
        // Example :
        // ("DefaultEndpointsProtocol = https;
        // AccountName = 'AccountName';AccountKey = 'AccountKey';EndpointSuffix=core.windows.net");
        cloudBlobClient 	= cloudStorageAccount.CreateCloudBlobClient();
        cloudBlobContainer 	= cloudBlobClient.GetContainerReference("mycontainer");
  
        Info(cloudBlobContainer.Name);

        return cloudBlobContainer;
    }

    Get the files List:

    Way 1:

     public void getFilesList(CloudBlobContainer _cloudBlobContainer)
    {
        // Directory of blob container
        CloudBlobDirectory  cloudBlobDirectory;
        container           fileCon;

        // Folder Path
        cloudBlobDirectory = _cloudBlobContainer.GetDirectoryReference("My Folder");

        System.Collections.IEnumerable lstEnumarable = cloudBlobDirectory.ListBlobs(false, 0, null, null);
        System.Collections.IEnumerator lstEnumarator = lstEnumarable.GetEnumerator();

        List filenames = new List(Types::String);

        while(lstEnumarator.MoveNext())
        {
            IListBlobItem item = lstEnumarator.Current;

            if (item is CloudBlockBlob)
            {
                CloudBlockBlob      blob = item;

                blob.FetchAttributes(null, null, null);

                fileCon = str2con(blob.name, "/");

                filenames.addStart(conPeek(filecon, conLen(filecon)));

                info(strFmt("File : %1", conPeek(filecon, conLen(filecon))));
            }
        }
    }

    Way 2:

    public void readTheFiles(CloudBlobContainer _cloudBlobContainer)
    {
        System.Collections.IEnumerable lstEnumarable = _cloudBlobContainer.ListBlobs(null, false, 0, null, null);
        System.Collections.IEnumerator lstEnumarator = lstEnumarable.GetEnumerator();

        List filenames = new List(Types::String);
        while (lstEnumarator.MoveNext())
        {
            IListBlobItem item = lstEnumarator.Current;

            if (item is CloudBlockBlob)
            {
                CloudBlockBlob blob = item;
                System.IO.StreamReader  reader = new System.IO.StreamReader(blob.OpenRead(null, null, null));
                Info(reader.ReadToEnd());
            }
        }
    }

    Read the file content:

    public void readFileValue(CloudBlobContainer _cloudBlobContainer)
    {
        CloudBlobDirectory  cloudBlobDirectory  = _cloudBlobContainer.GetDirectoryReference("My Folder");
        CloudBlockBlob      blob                = cloudBlobDirectory.GetBlockBlobReference("File");

        System.IO.Stream memory = blob.openRead(null, null, null);

        // Read the content
        System.IO.StreamReader streamReader = new System.IO.StreamReader(memory);

        // Read each line
        str  strRecord = streamReader.ReadLine();

        // while (!System.String::IsNullOrEmpty(strRecord))
        {
            try
            {
                container   conRecord = str2con_RU(strRecord, ',');

                info(conPeek(conRecord, 1));

                info(conPeek(conRecord, 2));

                strRecord = streamReader.ReadLine();
            }
            catch
            {
                throw error("Message");
            }
        
        }
    }

    Upload the file:

     public void UploadFileToBlob(CloudBlobContainer _cloudBlobContainer)
    {
        CloudBlobDirectory      cloudBlobDirectory;
        CloudBlockBlob          CloudBlockBlob;
        System.Byte[]           reportBytes = new System.Byte[0]();

        // File path
        cloudBlobDirectory  = _cloudBlobContainer.GetDirectoryReference("My Folder");
        CloudBlockBlob      = cloudBlobDirectory.GetBlockBlobReference("File.Txt");

        // ---------------- or ----------------
        CloudBlockBlob      = _cloudBlobContainer.GetBlockBlobReference("File.Txt");

        // Encode
        System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
        reportBytes = enc.GetBytes("YOUR XML STRING/TEXT or Data in file");
        System.IO.MemoryStream stream = new System.IO.MemoryStream(reportBytes);
 
        // upload stream
        CloudBlockBlob.UploadFromStream(stream, null, null, null);
    }

    Move the file:

    public void moveTheFileFromOneFolderToAnotherFolder(CloudBlobContainer _cloudBlobContainer)
    {
        CloudBlobDirectory      sourceCloudBlobDirectory;
        CloudBlobDirectory      destinationCloudBlobDirectory;
        CloudBlockBlob          sourceCloudBlockBlob;
        CloudBlockBlob          destinationCloudBlockBlob;

        sourceCloudBlobDirectory        = _cloudBlobContainer.GetDirectoryReference("My Folder");
        destinationCloudBlobDirectory   = _cloudBlobContainer.GetDirectoryReference("My Folder 2");

        sourceCloudBlockBlob            = sourceCloudBlobDirectory.GetBlockBlobReference("File1.Txt");
        destinationCloudBlockBlob       = destinationCloudBlobDirectory.GetBlockBlobReference("File1.Txt");

        // Upload the file to destination
        destinationCloudBlockBlob.UploadFromStream(sourceCloudBlockBlob.OpenRead(null, null, null), null, null, null);
    }

    Delete the file:

    public void deleteFileFromFolder(CloudBlobContainer _cloudBlobContainer)
    {
        CloudBlobDirectory      sourceCloudBlobDirectory;
        CloudBlockBlob          sourceCloudBlockBlob;

        sourceCloudBlobDirectory        = _cloudBlobContainer.GetDirectoryReference("My Folder");
        sourceCloudBlockBlob            = sourceCloudBlobDirectory.GetBlockBlobReference("File2.Txt");

        // Delete file
        sourceCloudBlockBlob.delete(0, null, null, null);
    }

Reference :https://kishoredynamics11.blogspot.com/2023/04/upload-file-to-share-point-using-x.html

Upload file to share Point using x++

 using System.IO;

using System.IO.Path;
using Microsoft.Azure;
using Blobstorage = Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.Dynamics.Platform.Integration.SharePoint;
using Microsoft.Dynamics.ApplicationPlatform.Services.Instrumentation;
using Microsoft.DynamicsOnline.Infrastructure.Components.SharedServiceUnitStorage;
using Microsoft.Dynamics.AX.Framework.FileManagement;
public class DocumentsUploadToSharePoint// extends RunBaseBatch
{
    str docfiletype;
    Microsoft.Dynamics.AX.Framework.FileManagement.IDocumentStorageProvider storageProvider;
	
    public static void main(Args    args)
    {
        DocumentsUploadToSharePoint documentsUploadToSharePoint = new DocumentsUploadToSharePoint();

        documentsUploadToSharePoint.upload();
    }

    public void upload()
    {
        System.Exception            ex;
        System.IO.Stream            memoryStream;
        Filename 		    fileNameExt; 
	VendTable 		    vendTable;
	DocuRef                	    docuRef;
	DocuValue              	    docuValue;
        str 			    errorMessage;
		
	select * from docuValue
	    join docuref
	        where docuValue.RECID == docuref.VALUERECID
	    join vendTable
		where docuref.REFRECID == vendTable.RECID
		    && vendTable.accountNum =='00000112'
		    && Docuref.REFCOMPANYID == vendTable.DataAreaId;
    
        memoryStream = this.readfromAzureBlob(docuref);
        memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

        fileNameExt =  docuValue.FileName + '.' + docuValue.FileType;
	//str folderPath = "/sites/'new site'/'Folder'";
	str folderPath = "/MyFolder";
	str fileContentType = System.Web.MimeMapping::GetMimeMapping(fileNameExt);

	ISharePointProxy    proxy = null;
	DOCUPARAMETERS      docuParameters;
	str                 src = '';
	str                 hostName = '';

	docuParameters = DOCUPARAMETERS::find();
 
        try
        {
            ttsbegin;
	    if(docuParameters)
	    {
	        src 	    = docuParameters.DefaultSharePointServer;
	        hostName    = builder.Host;  
			
	        System.UriBuilder builder = new System.UriBuilder(src);

	        str externalId = xUserInfo::getCurrentUserExternalId();

	        try
	        {
		    //proxy = SharePointHelper::CreateProxy(hostName, '/', externalId);
		    proxy = SharePointHelper::CreateProxy(hostName, '/sites/MySite', externalId);
	        }
	        catch(Exception::CLRError)
	        {
		    proxy = null;
	        }
	    }
	    if(proxy)
	    {
	        if(SharePointHelper::VerifyAuthentication(proxy))
	        {
		    Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider prov =
		        new Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider(proxy, folderPath);

		    prov.SaveFileWithOverwrite(newguid(), fileNameExt, fileContentType, memoryStream);
	    }
	    else
	    {
	        info('@ApplicationFoundation:SPServerUserNotAuthorized');
	    }
			
        }
        else
        {
	    throw Error("SharePoint connection error");
        }
        ttscommit;
    }
    catch (ex)
    {
       System.Exception e = ex;
       while (e != null)
       {
        errorMessage += e.Message;
	e = e.InnerException;
       }

       if (appl.ttsLevel() > 0)
       {
            ttsabort;
       }

        checkFailed("Process failed");
	error(errorMessage);
    }

    Public System.IO.Stream readfromAzureBlob(DocuRef _docuRef)
    {
        AsciiStreamIo 	file;
        container 	record;
        str 		downloadUrl;
       
	//if (docuValue.FileType != 'PDF')
	//{
	//    continue;
	//}
		
	if (_docuRef.isValueAttached())
        {
	    System.IO.Stream docuRefStream = DocumentManagement::getAttachmentStream(_docuRef);
	    return docuRefStream;
	}
		
        /*if (_docuRef.isValueAttached())
        {
            var docuValueloc = _docuRef.docuValue();
            //downloadUrl = docuValueloc.Path;

            if (!downloadUrl || docuValueloc.Type == DocuValueType::Others)
            {
                str accessToken = DocumentManagement::createAccessToken(_docuRef);
                downloadUrl = Microsoft.Dynamics.AX.Framework.FileManagement.URLBuilderUtilities::GetDownloadUrl(docuValueloc.FileId, accessToken);
            }
			
            //storageProvider = new Microsoft.Dynamics.AX.Framework.FileManagement.IDocumentStorageProvider();
            var  docContents = storageProvider.GetFile(docuValueloc.createLocation());
            //file = AsciiStreamIo::constructForRead(docContents.Content);//File::UseFileFromURL(downloadUrl));
            return docContents.Content;
        }*/
        return null;
    }

}

Restrict the user to open the particular Form, if the same form already opened by any other user.

 

Restrict the user to open the particular Form, if the same form already opened by any other user.

In my scenario when I open particular SalesOrder i want to restrict to others by throwing an error “The same Salesorder is already opened by any other user”

1. Firstly add one field in SalesTable. 

2. Navigation  AOT -> Tables -> SalesTable.

3.Create new field  userId Datatype is string.

Navigate to Forms\SalesTable\Methods\init, write the following code in it.

public void init()
{
 

         SalesTable    salestablecpy ;

         FormRun       callerForm;

        salestablecpy  = element.args().record();

        callerForm       = element.args().caller();

        If (!salestablecpy.UserId)
        {

            ttsBegin;

                 salestablecpy.UserId = curUserId();
                  salestablecpy.doUpdate();
            ttsCommit;
        

        }

       else if(salestablecpy.UserId != curUserId())
       {
      

            throw error(strFmt("The same Salesorder(%1) is already opened by %2",                                                                 salestablecpy.SalesId,salestablecpy.UserId));

       }

}

. Similarly, navigate to  Forms\SalesTable\Methods\Close,  write the following code in it.

void  close()
{  

        SalesTable    salestablecpy, saleesTableLoc  ;

        formrun      formrun ;

       salestablecpy = element.args().record();

       formrun = element.args().caller();

    if (salestablecpy.UserId)
    {
        ttsBegin;

           select forUpdate saleesTableLoc  where saleesTableLoc.RecId==salestablecpy.RecId;
           saleesTableLoc.UserId = ' ';
           saleesTableLoc.doUpdate();

      ttsCommit;
   
    }

}

Multi selected values in clicked ()

 

to get Multi selected values in clicked ()

  [FormControlEventHandler(formControlStr(SalesTableListPage, FormButtonControl1), FormControlEventType::Clicked)]

    public static void FormButtonControl1_OnClicked(FormControl sender, FormControlEventArgs e)

    {

        SalesTable   salesTableLoc;

        FormDataSource   salesTable = sender.formRun().dataSource("salesTable");

        MultiSelectionHelper helper = MultiSelectionHelper::construct();

        helper.parmDatasource(salesTable);

        salesTableLoc = helper.getFirst();

        while (salesTableLoc.RecId != 0)

        {  

                     info(salesTableLoc.SalesId);

            salesTableLoc = helper.getNext();

    }

       

    }

Get formRun, Form control, datasource in D365

 

Get formRun, Form control, datasource and selected record from form datasource using Eventhandlers on Form in D365

Get formRun, Form control, datasource and selected record from form datasource using Eventhandlers on Form in D365

Get formRun, Form control, datasource and selected record from form datasource :

[FormDataSourceEventHandler(formDataSourceStr(MyForm, MyRandomTableDS), FormDataSourceEventType::Written)] public static void MyRandomTableDS_OnWritten(FormDataSource sender, FormDataSourceEventArgs e)
{

FormRun formRun = sender.formRun() as FormRun;

// you can even call custom methods
formRun.myCustomMethod();

// Get the selected datasource record
TableName tableBuffer = sender.cursor();

// Get datasource variable
FormDataSource DSVariable = sender.formRun().dataSource(“TableName”);
}

Get form datasource from xFormRun
[FormEventHandler(formStr(SomeForm), FormEventType::Initialized)] public static void SomeForm_OnInitialized(xFormRun sender, FormEventArgs e)
{
FormDataSource MyRandomTable_ds = sender.dataSource(formDataSourceStr(SomeForm, MyRandomTableDS));
…
}

Access form control from xFormRun
[FormEventHandler(formStr(SomeForm), FormEventType::Initialized)] public static void SomeForm_OnInitialized(xFormRun sender, FormEventArgs e)
{
// set the control to invisible as an example
sender.design().controlName(formControlStr(SomeForm, MyControl)).visible(false);
}

Get FormRun from form control
[FormControlEventHandler(formControlStr(MyForm, MyButton), FormControlEventType::Clicked)] public static void MyButton_OnClicked(FormControl sender, FormControlEventArgs e)
{
FormRun formRun = sender.formRun() as FormRun;
formRun.myCustomMethod();
}

Get current record in form control event
[FormControlEventHandler(formControlStr(SomeForm, SomeButton), FormControlEventType::Clicked)] public static void SomeButton_OnClicked(FormControl sender, FormControlEventArgs e)
{
// as an example the datasource number is used for access; I perceive the formDataSourceStr as more robust
SomeTable callerRec = sender.formRun().dataSource(1).cursor();
}

SFTP

in order to connect  SFTP  we need to create a C# project and we need to write code in C#. After that DLL file, I need to add it as a reference for my D365 project.

We need to download Renci.ssh.Net from the NuGet package.

C# Code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Renci.SshNet;
namespace SFTPConnect
{
    public class sftpConnection
    {
        public SftpClient sftpClient;
 
        public SftpClient OpenSFTPConnection(string host, int port, string username, string password)
        {
            if (this.sftpClient == null)
            {
                this.sftpClient = new Renci.SshNet.SftpClient(host, port, username, password);
				
		--------------or-------------------------
		List<AuthenticationMethod> 	methods = new List<AuthenticationMethod>
		{
		    new PasswordAuthenticationMethod(username, password)
		};

		var connectionInfo = new ConnectionInfo(host, port, username, methods.ToArray());
						
		this.sftpClient = new SftpClient(connectionInfo);
            }
 
            if (!this.sftpClient.IsConnected)
            {
                this.sftpClient.Connect();
            }
 
            return this.sftpClient;
        }
 
        public List<string> GetDirectories(SftpClient _SftpClient, string path)
        {
            return _SftpClient.ListDirectory(path)/*.Where(n => n.IsDirectory)*/.Select(n => n.Name).ToList();
        }
 
        public void MoveFile(SftpClient _SftpClient, string sourcePath, string destinationPath, bool isPosix)
        {
            _SftpClient.RenameFile(sourcePath, destinationPath, isPosix);
        }
 
        public Stream DownloadFile(SftpClient _SftpClient, string sourcePath)
        {
            var memoryStream = new MemoryStream();
 
            _SftpClient.DownloadFile(sourcePath, memoryStream);
 
            memoryStream.Position = 0;
 
            return memoryStream;
        }
	public void upLoadFile(SftpClient _SftpClient, System.IO.Stream _sourceFile, string _fileName)
	{
	    _sourceFile.Position = 0;
	    _SftpClient.BufferSize = 8 * 1024;
	    _SftpClient.UploadFile(_sourceFile, _fileName);
	}
    }
}

X++ Code:

    public void sFTPConnection()
    {	
        str                       	sftpFile;
        ClrObject                 	list = new ClrObject("System.Collections.Generic.List`1[System.String]");
        SFTPConnect.sftpConnection	sftp = new SFTPConnect.sftpConnection();

        using (var sftpConnection = sftp.OpenSFTPConnection(host, port, username, password)) // Connect
        {
            try
            {
                int totalFiles = 0;
				
                sftpConnection.ChangeDirectory('/Upload');//Import Path

                list  = (sftp.GetDirectories(sftpConnection, '/Upload/'));// Files List
				
                ClrObject enumerator = list.getEnumerator();
                 
                while (enumerator.movenext())
                {
                    totalFiles ++;

                    sftpFile = enumerator.get_Current();
 
                    if(sftpFile != ".." && sftpFile !=null && sftpFile != ".")
                    {
		        System.IO.Stream Stream = sftp.DownloadFile(sftpConnection, '/Upload' + '/'+ sftpFile);
						
			this.ReadCSVFile(Stream,sftpFile);

			sftp.MoveFile(sftpConnection, 
					'Import Path'+ '/'+sftpFile, 
					'destination path'+ '/'+ sftpFile,
					false);					                      
                    }
                }
            }
	    catch
	    {
		throw error(infolog.text());
	    }
            finally
            {
                if(sftpConnection.IsConnected)
                    sftpConnection.Disconnect();
            }
        }             
              
    }
    
    public boolean ReadCSVFile(System.IO.Stream  stream, str fileName)
    {
	AsciiStreamIo    file;
	container        record;

	file = AsciiStreamIo::constructForRead(stream);
	if (file)
	{
	  if (file.status())
	  {
                throw error("@SYS52680");
	  }
	  file.inFieldDelimiter(',');
	  file.inRecordDelimiter('\r\n');

	  while (!file.status())
	  {
	        record = file.read();
		recordCount++;

		if (conLen(record) && recordCount !=1)
		{  
		    conPeek(record,2));
		}
	   }
	}
	
	return true;
}  

Tested in Console Application:

    To test in the console application we need to add the class library project as a reference.

    We can debug using a console application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Renci.SshNet;

namespace TestSFTPConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            SFTPTest.SFTPConnect sftp = new SFTPTest.SFTPConnect();

            using (var sftpConnection = sftp.OpenSFTPConnection("Host", 22, "userName", "UserPassword"))
            {
                //try
                {
                    sftpConnection.ChangeDirectory("/uploads");// folder name

                    List<string> list = sftp.GetDirectories(sftpConnection, "/uploads/");
                    List<string>.Enumerator enumerator = list.GetEnumerator();
                    while (enumerator.MoveNext())
                    {
                        string sftpFile = enumerator.Current;

                        if (sftpFile != ".." && sftpFile != null && sftpFile != ".")
                        {
                            System.IO.Stream Stream = sftp.DownloadFile(sftpConnection, "/uploads" + '/' + sftpFile);
                        }
                    }
                }
            }
        }

    }
}

Reference Link:

https://dynamicsax4u.wordpress.com/2020/08/18/read-files-from-sftp-server-and-write-data-in-ax365-part-2/

Upload File in SFTP using X++

For this, we need to create a DLL file that we will call in our x++.

Please follow the below link for the DLL file creation and add that file as a reference for the x++ project.

Addinga DLL file as a reference to the D365 project

In the C# class, we need to use ssh Extention and for that, we need to follow the below process.

• Right-click on the C# solution and select the manage NuGet package.

        

•         Right-click on the setting button and add the below URL

                    https://api.nuget.org/v3/index.json 

        

• Search Renci in browse and install the below extensions.

        

 X++ code:

public void sendFileToSFTP(System.IO.Stream 	sourceStream)
{
    try
    {
        str FileName = 'TestFile.csv';
         // 'Destination file path' ->  /MainFolder/SubFolder

	str success = ExportSFTP.SFTPConnect::uploadSFTPFile('Host URL','User Name',
								'Pasword', sourceStream,
								'Destination File Path','PortNum', FileName);
	if(success == 'pass')
	{
	    Info(strFmt('%1 File successfuly sent to SFTP', sftpFileName));
	}
	else
	{
	    Error('Error in sending file to SFTP due to incorrect Host/Port/User Credential');
	}
    }
    catch
    {
	Info(infolog.text());
    }
}

C# Code:

using System;
using System.IO;
using System.Net;
using System.Collections.Generic;
using Renci.SshNet;
using System.Text;

namespace ExportSFTP
{
    public class SFTPConnect
    {
	public static string uploadSFTPFile(string host,
					    string username,
					    string password,
					    System.IO.Stream sourceFile,
					    string destinationPath,
					    int    port,
					    string fileName,
					    string privateKeyFilePath = '')
	{
	    string 			successStr = 'Fail';
	    List<AuthenticationMethod> 	methods;

	    /*It depends if the private key file is present for authentication. 
		If the SFTP is key secured then the private key file has to be passed.*/
	    if (privateKeyFilePath != '')
	    {
	        var privateKeyFile = new PrivateKeyFile(privateKeyFilePath, passPhrase);// passPhrase - Password for key file

				
		methods = new List<AuthenticationMethod>
		{
		    new PasswordAuthenticationMethod(username, password),
		    new PrivateKeyAuthenticationMethod(username, privateKeyFile)
		};
	    }
	    else
	    {
		methods = new List<AuthenticationMethod>
		{
		    new PasswordAuthenticationMethod(username, password)
		};
	    }
			
	    try
	    {
		var connectionInfo = new ConnectionInfo(host, port, username, methods.ToArray());
				
		using (SftpClient sftpclient = new SftpClient(connectionInfo))
		{
		    sftpclient.Connect();
		    sftpclient.ChangeDirectory(destinationPath.Trim());
		    sourceFile.Position = 0;
		    sftpclient.BufferSize = 8 * 1024;
		    sftpclient.UploadFile(sourceFile, fileName);
		}
				
		successStr = 'Pass';
	    }
	    catch (WebException e)
	    {
		successStr = 'Fail';
	    }
			
            return successStr;
	}
    }
}

Reference:https://kishoredynamics11.blogspot.com/2023/03/send-file-to-sftp-server-using-x.html

Adding DLL files as a reference to the D365 project.

 

1) Create a project using class class library 

2) create a project once created it will show as below 

3) write your own logic based on your based on your requirement .

4) build the project once build is done it will DLL file   in your project stored location /bin/debug / as shown below

5) copy the DLL file and paste in your Model

            AOSService\PackagesLocalDirectory\yourmodel\bin\

6) create a project in d365 and add refence as shown below  . we need to select  DLL in browser window and click on OK 

    

    

Note : after DLL added as reference in D365 project,  If we modified DLL then we need repeat the steps 4,5,6 to get the latest DLL as reference in your d365 project  

Adding DLL In Source control explorer :

1. Open the source controller and navigate to the metadata node. Right-click on your model, and choose the '+ Add items to folder' option.

2. Choose the 'bin' folder and click 'Next.'

3. In the 'Excluded items,' select your DLL. Then, click 'Finish.'

4. This will add your DLL to the bin folder. During check-in, ensure to include it from the pending changes.

Reference kishore 

Simple OData in D365 FO

Setup Postman to call D365 F&O Data Entities

·        You’ll need Postman, Tenant ID, Client ID, Client secret value, and D365 F&O URL environment

Create Get token request in Postman

• System administration > Setup > Microsoft Entra ID applications

• Click “New” -> Enter APP-ID(client id ), Meaningful name and User ID (the permission you would like to assign).
  
  
  

1.    Create a GET request then enter the URL to be the following. Replace <Tenant ID> to be your Tenant ID in the Azure Portal.

https://login.microsoftonline.com/<Tenant ID>/oauth2/v2.0/token

2.    Select Headers tab and specify the following values. Most should be the default value. However, you will need to set the Host to login.microsoftonline.com

1.    Click on the Body tab and specify the following key Value pairs

·        Client_id can be found in the App registration

·        Client_secret is the client secret value in the App registration

·        Grant_type should be the test ‘client_credentials’

·        Scope should be the URL instance that you wish to connect to, followed by the text ‘/.default’

·        Click save and send the request

The above access token will be used to authenticate the OData URLs. 

1) Get   :D365 URL/Data/Public collection name

2)Post  :in order to insert the data 

Create new record 12345

3) Patch : in order update the records we need to pass the values along with URL's

4) Delete  :  in order Delete the records we need to pass the values along with URL's

by using  $expand keyword to include data from the related table and $select to specify the fields to retrieve.

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq 'USMF'&Cross-company=true&$select=PurchaseOrderNumber,VendorOrderReference&$expand=PurchaseOrderLinesV2 ($select=PurchaseOrderNumber,LineNumber)

reference : https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/odata