Data Selection and Manipulation in Ax 2009 or 2012

Data Selection and Manipulation in Ax 2009 or 2012

You can use SQL statements either interactively or within source code, to access and retrieve data that is stored the database. Data manipulation is performed through the following statements:

• insert adds one or more new records into a table.
• update modifies data in existing table records.
• delete removes existing records from a table.

Before any data can be changed in Microsoft Dynamics AX, the data must first be selected for update by using a select statement. The select forUpdate command selects records exclusively for update.

The insert, update, and delete methods perform operations on only one record at a time. The array insert, insert_recordset, RecordInsertList, and update_recordset statements perform operations on multiple records at a time. However, the X++ record-set operations can fall back to record-by-record operations in a number of situations.

For more detail information Go to the :

http://msdn.microsoft.com/en-us/library/aa883417(v=ax.10).aspx

The RecordInsertList class provides array insert capabilities in the kernel.

The RecordSortedList class inserts multiple records in a single database trip

insert_recordset copies data from one or more tables directly into one resulting destination table on a single server trip. It is faster than using an array insert. However, array inserts are more flexible if you want to manipulate the data before you insert it.

insert_recordset is a record set-based operator, which performs operations on multiple records at a time. However, it can fall back to record-by-record operations in a number of situations.

The update_recordset construction allows you to update multiple rows in a single trip to the server. This allows certain tasks to be significantly speeded up by utilizing the power of the SQL backend.

update_recordset is similar to delete_from in X++ and to UPDATE SET in SQL. It works on the database server-side on an SQL-style record set rather than retrieving each record separately by fetching, changing, and updating.

If the update() method is overridden, the implementation falls back to a classic looping construction, updating records one by one just as delete from does for deletions. This also means that the construction works on temporary tables, and entire-table-cached tables by using the looping construction.

array insert :

Array inserts, sometimes referred to as bulk inserts, are implemented in the kernel. They buffer a group of rows and insert them in a single trip to the SQL data store. This vastly reduces the number of trips, and speeds up inserts by approximately a factor of 2 – 10. You can use RecordSortedList or RecordInsertList to hold your rows until they are inserted. Both classes have an insertDatabase method that is used to insert the records into the database as efficiently as possible. However, the insertDatabasemethod does not empty the list itself.

Array inserts can fall back to record-by-record operations in a number of situations.

You can also use insert_recordset to insert multiple rows on a single server trip. It is faster than an array insert, but limited to simple manipulations that can be implemented as SQL expressions. Array inserts enable you to perform more manipulation on the data before it is inserted.

void tutorialRecordSortedList()
{
    RecordSortedList recordSortedList;
    CustTable        custTable;
    ;
    recordSortedList = new RecordSortedList(tablenum(CustTable));
    recordSortedList.sortOrder(fieldnum(CustTable,AccountNum));
 
    ttsBegin;
        // Prepare record #1 for insertion
        custTable.AccountNum = '1000';     
        custTable.CreditMax = 10000.0;
        recordSortedList.ins(custTable);
 
        // Prepare record #2 for insertion
        custTable.AccountNum = '2000';     
        custTable.CreditMax = 500.0;
        recordSortedList.ins(custTable);
 
        // Prepare record #N for insertion
        custTable.AccountNum = 'N000';     
        custTable.CreditMax = 9999999.9;
        recordSortedList.ins(custTable);
 
        // All records inserted in 1 database operation
        recordSortedList.insertDatabase(); 
    ttsCommit;
}
void tutorialRecordInsertList()
{
    MyTable myTable;
    RecordInsertList insertList = new RecordInsertList(myTable.TableId);
    int recordsCurrentlyInserted, i, recordsToInsert = 125;
    ;
    for (i = 1; i <= recordsToInsert; i++)
    {
        myTable.value = i;
        recordsCurrentlyInserted = insertList.add(myTable);
        info(strfmt("%1 records added, %2 records currently inserted.",
             i,recordsCurrentlyInserted));
    }
    recordsCurrentlyInserted = insertList.insertDatabase();
    info(strfmt("%1 records added, %2 records currently inserted.",
        recordsToInsert,recordsCurrentlyInserted));
}