Connecting to Databases through X++ PART -IV

Connecting to Databases through X++ PART -IV

Connection Class:

Connection class is mainly used for accessing the database in which a user has logged into AX i.e. Current Database and carry out the operations. This class is exetensively used in ReleaseUpdateDB classes, the classes used in data upgrades. This class cannot be run on client and should always be run on server. One more unique thing that I noticed is that the statements that you want to execute should be asserted first for permissions and then passed on to other method where they are executed. Create a class with following methods and set its RunOn property to Server.

class TestSQLExecuteClass
{
}

//This method tests the permissions for statement and then calls the method that will execute the statement
static void dbConnectionClass()
{
    ResultSet   rs;
    SqlStatementExecutePermission perm;
    ;

    perm = new SQLStatementExecutePermission("select * from CustTable where DATAAREAID = ‘CEU’");
    perm.assert();

    rs = TestSQLExecuteClass::statementExeQuery("select * from CustTable where DATAAREAID = ‘CEU’");

    while (rs.next())
    {
        info(rs.getString(1));
    }
    CodeAccessPermission::revertAssert();
}

//Executes the passed statement
private static ResultSet statementExeQuery(str _sql, Connection _con = null)
{
    ResultSet   resultSet;
    Statement   statement;
    ;

    try
    {
        if(!_con)
        {
            _con = new Connection();
        }

        statement = _con.createStatement();

// Do not call assert() here, do it in the caller
        // BP deviation documented
        resultSet = statement.executeQuery(_sql);
    }
    catch (Exception::Error)
    {
        throw error("@SYS99562");
    }

    return resultSet;
}

Now you can call the method in a job as shown below:

static void dbConnectionClass(Args _args)
{
    ;

    TestSQLExecuteClass::dbConnectionClass();
}

These examples shown here are pretty simple and easy to understand and start with. Hope it helps you in building ‘connections’

Related posts, 

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -IV

-Harry

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -III

      OLEDB Connection:

OLEDB is a set of APIs designed by Microsoft and used for accessing different types of data stored in a uniform manner. Dynamics AX as such doesn’t have any specific classes built for this purpose. But one can make use of .Net Framework’s System.Data.OleDb namespace through AX’s COM Interoperability feature and use it in AX.

Below is an example code that depicts this scenario:

static void dbOLEDBConnection(Args _args)
{
    System.Exception                    e;
    System.Data.OleDb.OleDbConnection   objConn;
    System.Data.OleDb.OleDbCommand      cmdSelect;
    System.Data.OleDb.OleDbDataReader   reader;
    InteropPermission                   perm;
    str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
                     "Persist Security Info=False;Initial Catalog=AX2009;Data Source= theAxapta ";
    str exceptionStr;
    ;

    try
    {
        perm = new InteropPermission(InteropKind::ClrInterop);
        if (perm == null)
        {
            throw error("Error with file permissions");
        }
        perm.assert();

        objConn = new System.Data.OleDb.OleDbConnection(connectStr);
        objConn.Open();

        cmdSelect   = objConn.CreateCommand();
        cmdSelect.set_CommandText("SELECT * FROM CustTable where DATAAREAID = ‘CEU’");
        reader      = cmdSelect.ExecuteReader();

        while (reader.Read())
        {
            info(reader.GetString(0));
        }
    }
    catch(Exception::CLRError)
    {
        CodeAccessPermission::revertAssert();

        perm = new InteropPermission(InteropKind::ClrInterop);
        if (perm == null)
        {
            return;
        }
        perm.assert();

        e = ClrInterop::getLastException();

        CodeAccessPermission::revertAssert();

        while( e )
        {
            exceptionStr += e.get_Message();
            e = e.get_InnerException();
        }
        info(exceptionStr);
    }
    catch
    {
        error("An Exception has occurred");
    }

    if(objConn)
        objConn.Close();
}

Other related posts:

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -IV

-Harry

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -II

    ADO Connection:

ADO is a set of COM objects for accessing databases or data stores. In AX we have following Classes/Objects which help to implementing ADO concept.

Class Name	Description
CCADOConnection	Helps in establishing a connection to the target database.
CCADOCommand	Helps in executing a command (a Text type or a Stored procedure)
CCADORecordSet	Stores the data
CCADOFields	A collection of all fields in CCADORecordSet
CCADOField	A single field from the collection of fields
CCADOParameter	A class that helps in passing parameters that a command needs or demands

Here is an example:

static void dbCCADOConnection(Args _args)
{
    CCADOConnection connection = new CCADOConnection();
    CCADOCommand    ccADOCommand;
    CCADORecordSet  record;
    str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
                     "Persist Security Info=False;Initial Catalog=AXDEVDB;Data Source= theAxapta";

    COM     recordSet;  /*This is required to call moveNext method to parse the record set. In AX 4.0 this method was there in the CCADORecordSet class but in AX 2009 this has been deleted*/
    ;

    // Executing a SQL Statement
    try
    {
        connection.open(connectStr);
        ccADOCommand = new CCADOCommand();
        ccADOCommand.commandText("Select * from CustTable where DataAreaId = ‘CEU’");
        ccADOCommand.activeConnection(connection);
        record = ccADOCommand.execute();
        recordSet = record.recordSet();
        while (!record.EOF())
        {
            info(any2str(record.fields().itemIdx(0).value()));
            recordSet.moveNext();
        }
    }
    catch
    {
        error("An Exception has occurred");
    }

    connection.close();
}

Previous Post:

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -IV

-Harry

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -I

In This series of post we will discuss about all possible ways through which we can connect to different databases.

     1.    ODBC Connection (Open Data Base Connection)

     2.    ADO Connection (ActiveX Data Objects)

      3. OLEDB Connection (Object Linking and Embedding, Database)

      4. Connection class

    1.  ODBC Connection:

ODBC used to define a connection between a computer and a database stored on another system. The ODBC connection allows computer user to access the information stored in a database that is not local to that computer. In Dynamics AX, we  have ODBCConnection class to carry out this type of database connection need. This class further uses LoginProperty class for login information and uses Statement and ResultSet classes for carrying out DML operations.

Here is an example of how to use this class.

static void theAxapta_ODBCConnection(Args _args)
{
    LoginProperty   loginProp;
    ODBCConnection  conn;
    Resultset       resultSet, resultSetCount;
    Statement       statement1, statement2;
    ;

    loginProp = new LoginProperty();

    loginProp.setServer(‘theAxapta’);//you can use IP address as well
    loginProp.setDatabase(‘AXDEVDB′);

    conn = new ODBCConnection(loginProp);

    statement1  = conn.createStatement();
    resultSet   = statement1.executeQuery("SELECT * from CustTable where DATAAREAID = ‘CEU’");

    while (resultSet.next())
    {
        info(resultSet.getString(1));
    }
}

Other related posts:

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -IV

-Harry

How to Read/Write an Excel file through X++ code

How to write an Excel file through X++ code

In This Post you will found two code sample
1.Write data in excel through X++ code.
2. Read from an Excel through X++ code

1.Write data in excel through X++ code.





static void thaAxapta_Write2Excel(Args _args)
{
InventTable inventTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
    row++;
    cell = cells.item(row, 1);
    cell.value(inventTable.ItemId);
    cell = cells.item(row, 2);
    cell.value(inventTable.ItemName);
}
application.visible(true);
}

2. Read from an Excel through X++ code

static void theAxapta_ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
try
{
    workbooks.open(filename);
}
catch (Exception::Error)
{
    throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
    row++;
    itemId = cells.item(row, 1).value().bStr();
    name = cells.item(row, 2).value().bStr();
    info(strfmt('%1 - %2', itemId, name));
    type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}

Exporting data to Excel from axapta x++

Exporting data to Excel from axapta x++

Hi All!
Sometimes we need to export data from Microsoft Dynamics AX to Excel using axapta x++ code and we don't know how to do this...
Exists some differents ways to do this, but I think the best way is using the SysExcel class of Dynamics AX and its related.
The only problem I found using this class... is that it can not be used in a batch process.
Sample code:

static void TheaxaptaCreateExcel(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   CustTable            custTable;
   int                  row = 1;
   str                  fileName;
   ;
   //Filename
   fileName = "C:\\Test.xlsx";
   //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();
   //Open Excel document
   //xlsApplication.visible(true);
   //Create Excel WorkBook and WorkSheet
   xlsWorkBookCollection    = xlsApplication.workbooks();
   xlsWorkBook              = xlsWorkBookCollection.add();
   xlsWorkSheetCollection   = xlsWorkBook.worksheets();
   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("Account Num");
   xlsWorkSheet.cells().item(row,2).value("Name");
   row++;
   //Fill Excel with CustTable AccountNum and Name fields (only 20 records)
   while select custTable
   {
      if(row == 20)
        break;
      xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
      xlsWorkSheet.cells().item(row,2).value(custTable.Name);
      row++;
   }
   //Check whether the document already exists
   if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   //xlsApplication.quit();
   //xlsApplication.finalize();
}
-Harry

Integrating Axapta with Microsoft Outlook

Integrating Axapta with Microsoft Outlook

In Microsoft Dynamics AX 4.0, you can set up the Microsoft Office Outlook Integration to integrate contacts, tasks and appointments between Microsoft Dynamics AX and Microsoft Office Outlook.
To set up the Microsoft Office Outlook Integration to integrate contacts, tasks and appointments between Microsoft Dynamics AX and Microsoft Office Outlook, follow these steps:

1. View the e-mail account setting in Control Panel, follow these steps:

a. Click Start, click Control Panel, and then click Mail.
b. In the Mail Setup – Outlook dialog box, click E-mail Accounts.
Note If the profile is not set for the desired user, click Show profiles, change to use the correct profile, click Properties, and then click E-mail Accounts.
c. Click to select the View or change existing e-mail accounts option, and then click Next.
d. Click Change.
e. In the E-mail Accounts dialog box, note the user name in the User Name field, and then note the value in the E-mail Address field.
2. Verify that all information on the Microsoft Office Outlook tab is correct in the Employee dialog box. To do this, follow these steps:

a. Click Administration, and then click Users.
b. In the User dialog box, click a user, and then click User relations.
c. On the General tab, select the employee record that you try to integrate with the current user in Microsoft Dynamics AX in the Employee field.
d. Click Human Resources, click Employee, and then click the record that you selected in step 2c.
e. In the Employee dialog box, click the Contact information tab, and then specify the correct e-mail account information in the E-mail field. This information should match the value in the E-mail Address field in step 1e.
f. Click the Microsoft Office Outlook tab, and then view the information. You may notice that not all information is specified.
Note In this step, you must verify that the "Microsoft Office Outlook user Identification" setting matches the user name in the User Name field in step 1e. This will make sure that you can synchronize Microsoft Dynamics AX and the Outlook client on the computer.
3. Run the Use current Microsoft Office Outlook profile function to set the program to use the current Outlook profile. To do this, click Function, and then click Use current Microsoft Office Outlook profile.
4. Follow these steps to synchronize contacts, tasks and appointments between Microsoft Dynamics AX and Outlook:
a. Run the Pick contact Microsoft Office Outlook folder function.
b. Run the Pick task Microsoft Office Outlook folder function.
c. Run the Pick appointment Outlook folder function.


5. Click Save.
if you want to save send copy in your outlook then check the box

-Harry

Sending mail from AX using .NET Framework

Sending mail from AX using .NET Framework

Sometimes happen that SysMailer class (using CDO) is not the right solution for sending mails with attachments. There is a little sample of X++ Job that is using System.Net.Mail namespace to achieve same.

static void JobNETSendMail(Args _args)
{
    System.Net.Mail.MailMessage             mailMessage;
    System.Net.Mail.Attachment              attachment;
    System.Net.Mail.AttachmentCollection    attachementCollection;
    System.Net.Mail.SmtpClient              smtpClient;
    System.Net.Mail.MailAddress             mailAddressFrom;
    System.Net.Mail.MailAddress             mailAddressTo;
    str                                     strBody;
    str                                     strSMTPServer;
    str                                     strFileName;
    FileIOPermission                        perm;
    ;

    // preparing parameters
    mailAddressFrom = new System.Net.Mail.MailAddres"test@localmail.com", "");
    mailAddressTo = new  System.Net.Mail.MailAddress("admin@localmail.com","");
    strBody = "There is a email body";
    strSMTPServer = "MailServerName";
    
    // preparing mail with body, subject, from, to.
    mailMessage = new System.Net.Mail.MailMessage(mailAddressFrom, mailAddressTo);
    mailmessage.set_Subject("There is a email subject");
    mailmessage.set_Body(strBody);
    attachementCollection = mailMessage.get_Attachments();

    strFileName = "C:\\path\\filename";
    // assert permision
    perm = new FileIOPermission(strFileName,'w');
    perm.assert();

    // attaching file to that email.
    attachment = new System.Net.Mail.Attachment(strFileName);
    attachementCollection.Add(attachment);
    smtpClient = new System.Net.Mail.SmtpClient(strSMTPServer);
    smtpClient.Send(mailmessage);
    
    // release permision
    CodeAccessPermission::revertAssert();
}

-Harry

Open web pages from X++ code

Sending mail from AX using .NET Framework

Sometimes happen that SysMailer class (using CDO) is not the right solution for sending mails with attachments. There is a little sample of X++ Job that is using System.Net.Mail namespace to achieve same.

static void JobNETSendMail(Args _args)
{
    System.Net.Mail.MailMessage             mailMessage;
    System.Net.Mail.Attachment              attachment;
    System.Net.Mail.AttachmentCollection    attachementCollection;
    System.Net.Mail.SmtpClient              smtpClient;
    System.Net.Mail.MailAddress             mailAddressFrom;
    System.Net.Mail.MailAddress             mailAddressTo;
    str                                     strBody;
    str                                     strSMTPServer;
    str                                     strFileName;
    FileIOPermission                        perm;
    ;

    // preparing parameters
    mailAddressFrom = new System.Net.Mail.MailAddres"test@localmail.com", "");
    mailAddressTo = new  System.Net.Mail.MailAddress("admin@localmail.com","");
    strBody = "There is a email body";
    strSMTPServer = "MailServerName";
    
    // preparing mail with body, subject, from, to.
    mailMessage = new System.Net.Mail.MailMessage(mailAddressFrom, mailAddressTo);
    mailmessage.set_Subject("There is a email subject");
    mailmessage.set_Body(strBody);
    attachementCollection = mailMessage.get_Attachments();

    strFileName = "C:\\path\\filename";
    // assert permision
    perm = new FileIOPermission(strFileName,'w');
    perm.assert();

    // attaching file to that email.
    attachment = new System.Net.Mail.Attachment(strFileName);
    attachementCollection.Add(attachment);
    smtpClient = new System.Net.Mail.SmtpClient(strSMTPServer);
    smtpClient.Send(mailmessage);
    
    // release permision
    CodeAccessPermission::revertAssert();
}

-Harry

Top 5 AIF Development Tips and Tricks

Top 5 AIF Development Tips and Tricks
1. Enable the debugger
You will notice, if you put a breakpoint into your document class, that when you run the associated action, the breakpoint will get ignored – leaving you in the dark. This is because the action is called using a ‘runas’ function call  to change the user executing the code.
To workaround this replace the runas method calls with direct calls in\Classes\AifOutboundProcessingService\ and \Classes\AifInboundProcessingService, eg:

1	/*
2	runas(message.sourceEndpointAxUserId(),

3	classnum(AifInboundProcessingService),
4	staticmethodstr(AifInboundProcessingService, processAsUser),

5	[message.pack(), messageId]);
6	*/

7	AifInboundProcessingService::processAsUser([message.pack(), messageId]);

2. Run jobs instead of waiting for batch processing
The usual way for the AIF to run is using the batch processing framework, where you setup an interval for the inbound and outbound processing  to run.  This minute or so can feel like an age when you are in the middle of developing:

AIF batch processing
So use a custom job to have the AIF run instantly at the click of a button, here is an example of the receive job:

01	static void runAIFReceive(Args _args)
02	{

03	AifGatewayReceiveService aifGatewayReceiveService;
04	AifInboundProcessingService aifInboundProcessingService;

05	;
06

07	aifGatewayReceiveService = new AifGatewayReceiveService();
08	aifGatewayReceiveService.run();

09	aifInboundProcessingService = new AifInboundProcessingService();
10	aifInboundProcessingService.run(true);  // pass true for debug mode

11

}

3. Use file adapters

When using the AIF, you will most likely be using a Biztalk, Web service or MSMQ adapter. Testing actions using one of these adapters can be a pain as you will most likely require another program to send or receive the message.
To get around this you can use a file adapter during testing, so that you can just write the message in plain XML and drop the file into a directory on your file system to be processed.
Then when you are finished testing / developing you can easily swap the file adapter out.
4. Compose messages with the Visual Studio XML editor
During development (when you use a file adapter), you can create the message using notepad or any other text editor. I recommend using the Visual Studio XML editor to quickly compose these to take advantage of intellisense, schema validation and other useful features (like inserting a guid):

5. Use the pipeline
It is unlikely (if you are integrating with a third party) that the XML schemas  of the external system match those in AX.
To transform the message into the format AX can handle you can use the pipeline to add a component to run an xslt on the inbound XML:



Enjoy...
Harry