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

AX 2012 R2 VPC And Materials Download

AX 2012 R2 VPC And Course Materials Download

You must have Partner Source credentials to access the following links.

Dynamics AX2012 R2 VPC
VPCLink

Dynamics AX2012 R2 Course Materials

MaterialsLink

-Harry

X++ code to create & post General Journal

X++ code to create & post General Journal

Some time we need to generate a General Journal through X++ code rather than go to GL module. For eg. WE are creating a journal after completion of some particular condition.
Try following code to create a General Journal and than post in in the same code through X++.

static void theaxapta_CreateGLJournalPost(Args _args)
{
AxLedgerJournalTable journalTable; // class
AxLedgerJournalTrans journalTrans; // class
container acctPattern;
container offSetAcctPattern;
LedgerJournalTable ledgerJournalTable; // table
ledgerJournalCheckPost ledgerJournalCheckPost;// table
;
journalTable = new AxLedgerJournalTable();
journalTrans = new AxLedgerJournalTrans();
//Journal Name
journalTable.parmJournalName("GenJrn");
journalTable.save();
journalTrans.parmJournalNum(journalTable.ledgerJournalTable().JournalNum);
journalTrans.parmTransDate(systemDateGet());
journalTrans.parmCurrencyCode("USD");
journalTrans.parmAmountCurDebit(1200);
journalTrans.parmAccountType(LedgerJournalACType::Ledger);
acctPattern = ["21345-Disp","211345", 2, "Department","0000114","CostCenter", "0000087"];
journalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(acctPattern));
journalTrans.parmOffsetAccountType(LedgerJournalACType:: Ledger );
offSetAcctPattern = ["40100-Disp","40100", 4, "Department","0000114","CostCenter", "0000087", "CustomPurposeA","Nile", "CustomPurposeB", "Site1"];
journalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern));
journalTrans.save();
ledgerJournalCheckPost = ledgerJournalCheckPost::newLedgerJournalTable(journalTable.ledgerJournalTable(),NoYes::Yes);
ledgerJournalCheckPost.run();
info(strFmt("Journal No. %1.", journalTable.ledgerJournalTable().JournalNum));
}


Other Related Posts:
Inventory Transfer Journal through X++ code

-Harry

How to use Complex Query Ranges in Dynamics AX

Use of Complex Query Ranges in Dynamics AX

     1.  Adding a query with a datasource.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable));
// Add our range
queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

    2.  Simple criteria

Lets find the record where the value of ItemId field is Item1. Take note of the single quotes and parenthesis surrounding the entire expression.

queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("Item1")));
Find records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));

Find records where the ItemType is Service or the ItemId is Item1. Note the nesting of the parenthesis in this example.

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))', any2int(ItemType::Service), queryValue("Item1")));

Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.

queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));

    3.  Complex criteria with combined AND and OR clauses

We need to find those records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard QueryRange syntax.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',fieldStr(InventTable, ItemType),any2int(ItemType::Service),any2int(ItemType::Item),fieldStr(InventTable, ProjCategoryId),queryValue("Spares")));

-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

Dynamics AX 2012 Certification Exams Details- Part II

MB6-869: Microsoft Dynamics AX 2012 Development Introduction

Preparation materials

·      80303A: Development I in Microsoft Dynamics AX 2012 (2 Days)

·      80304A: Development II in Microsoft Dynamics AX 2012 (2 Days)

This Exam contains following topics

Understanding Dynamics AX 2012 Architecture (13 percent)

·      Identify key development features and functionality.

·         This topic may include: development workspace; IntelliMorph; MorphX; object-oriented design; navigation

·      Demonstrate understanding of the data architecture.

·         This topic may include: working with data in forms; sorting records; filtering records; finding records

·      Demonstrate understanding of architecture components.

·         This topic may include: layers; models; labels; Help system; reporting

·      Work with customization tools.

·         This topic may include: using MorphX to customize the user interface; using the X++ editor to develop customizations; identifying best practices; using the Type Hierarchy Browser and Type Hierarchy Context tools; using the reverse engineering tool

Managing the Data Dictionary (13 percent)

·      Work with MorphX, the Application Object Tree (AOT), and projects.

·         This topic may include: working with development projects; features of the AOT; Microsoft Visual Studio projects node; objects in the data dictionary; navigating the AOT and data dictionary

·      Work with tables and relations.

·         This topic may include: table structure and components; fields; field groups; indexes; delete actions; creating tables; creating relations; primary keys; foreign keys; surrogate keys

·      Work with data types and base enumerations.

·         This topic may include: primitive types; extended types; creating data types; using data types; creating base enumerations; using base enumerations

·      Work with maps and views.

·         This topic may include: map functionality; map advantages; view functionality; view advantages

Managing the User Interface (13 percent)

·      Work with menus and menu items.

·         This topic may include: creating and using menu items; menu functionality; creating menus

·      Manage forms.

·         This topic may include: data sources; design; document view; editing data in a form

·      Work with forms.

·         This topic may include: form types; list pages and list page metadata; working with the action pane; form parts

Managing Security (11 percent)

·      Work with role and task based security.

·         This topic may include: identifying key concepts, terms, and benefits; working with roles, process cycles, and duties; working with privileges, entry points, and permissions

·      Understand security concepts and settings.

·         This topic may include: default security settings; sample security settings

·      Work with XDS and server enforcement of security.

·         This topic may include: server-based code authentication; data security filters; org model; effective date

Working with X++ Control Statements (13 percent)

·      Work with variables.

·         This topic may include: declaration; simple data types; composite data types; arrays; containers

·      Work with operators.

·         This topic may include: assignment operators; arithmetic operators; relational operators; operator precedence

·      Work with conditional statements and loops.

·         This topic may include: if…else; ternary; switch; while loops; do…while loops; for loops

·      Work with communication tools.

·         This topic may include: print; boxes; infolog; dialog

Managing Objects and Classes (12 percent)

·      Work with classes, objects, and inheritance.

·         This topic may include: defining key attributes; method access control; extending a class; expression operators for inheritance; referencing object methods; method types; inheritance among tables

·      Work with scoping, events, and parameters in X++.

·         This topic may include: scope of objects within a class; events in X++

Accessing the Database (15 percent)

·      Retrieve data.

·         This topic may include: table buffers; select statements; field lists; while select statements; sorting; joins; cross-company data access

·      Manipulate data.

·         This topic may include: insert; update; insert_recordset; update_recordset; delete; delete_from; transaction tracking system

·      Work with queries.

·         This topic may include: executing a query; building a query; QueryBuildDataSource; QueryBuildRange

Managing Exception Handling (10 percent)

·      Work with exceptions and optimistic concurrency exceptions.

·         This topic may include: handling errors

·      Work with throw and try/catch commands.

·         This topic may include: handling errors

Previous Post:

Dynamics AX 2012 Certification Exams Details- Part I

-Harry