Update_Recordset in Dynamics Ax

Description:-

In AX, you can manipulate a set of data by sending only one command to the database. This way of manipulating data improves performance a lot when trying to manipulate large sets of records. The commands for manipulations are insert_recordset, update_recordset, and delete_from. With these commands, we can manipulate many records within one database transaction, which is a lot more efficient than using the insert, update, or delete methods.

Insert_recordset

A very efficient way of inserting a chunk of data is to use the insert_recordset operator, as compared to using the insert() method. The insert_recordset operator can be used in two different ways; to either copy data from one or more tables to another, or simply to add a chunk of data into a table in one database operation.

The first example will show how to insert a chunk of data into a table in one database operation. To do this, we simply use two different table variables for the same table and set one of them to act as a temporary table. This means that its content is not stored in the database, but simply held in memory on the tier where the variable was instantiated.

Example:-

static void Insert_RecordsetInsert(Args _args)
{
    A_Student _Student;
    A_Student    _StudentTemp;
    /*Set the carTableTmp variable to be a temporary table.
    This means that its contents are only store in memory not in the database.*/
    _StudentTemp.setTmp();
    // Insert 4 records into the temporary table.
    _StudentTemp.Student_ID = "CT_023";
    _StudentTemp.Student_Name = "Manshi";
    _StudentTemp.insert();
    _StudentTemp.Student_ID = "CT_024";
    _StudentTemp.Student_Name = "Kishor";
    _StudentTemp.insert();
    _StudentTemp.Student_ID = "CT_025";
    _StudentTemp.Student_Name = "Jankiben";
    _StudentTemp.insert();
    _StudentTemp.Student_ID = "CT_026";
    _StudentTemp.Student_Name = "Lalita";
    _StudentTemp.insert();
    /*Copy the contents from the fields carId and carBrand
    in the temporary table to the corresponding fields in
    the table variable called carTable and insert the chunk
    in one database operation.*/
    Insert_Recordset _Student
    (Student_ID, Student_Name)
    select Student_ID, Student_Name from _StudentTemp;
   
    info(strFmt("Recoed Inserted !!"));
}

The other, and perhaps more common way of using the insert_recordset operator, is to copy values from one or more tables into new records in another table. A very simple example on how to do this can be to create a record in the InventColor table for all records in the InventTable.

Example:-

static void Insert_RecordsetCopy(Args _args)
{
InventColor inventColor;
InventTable inventTable;

InventColorId defaultColor = "B";
Name defaultColorName = "Blue";
;
insert_recordset inventColor (ItemId, InventColorId, Name)
select itemId, defaultColor, defaultColorName
from inventTable;
}

The field list inside the parentheses points to fields in the InventColor table. The fields in the selected or joined tables are used to fill values into the fields in the field list.

Update_recordset

The update_recordset operator can be used to update a chunk of records in a table in one database operation. As with the insert_recordset operator the update_recordset is very efficient because it only needs to call an update in the database once.

The syntax for the update_recordset operator can be seen in the next example:

Example:-

static void Update_RecordsetExmple(Args _args)
{
    A_Student _Student;
    // str ID = 'CT_025';
    info("BEFORE UPDATE");
    while select _Student
    where _Student.Student_ID == 'CT_025'
    {
        info(strfmt("Student-ID %1 has Student-Name %2 ", _Student.Student_ID,_Student.Student_Name));
    }
   
    update_recordset _Student
    setting Student_Name = 'Manshi'
    where _Student.Student_ID == 'CT_025';
   
    info("AFTER UPDATE");
   
    while select _Student
    where _Student.Student_ID == 'CT_025'
    {
        info(strfmt(" From This Student-ID %1 Name Replace With-%2 ", _Student.Student_ID,_Student.Student_Name));
    }
}

Notice that no error was thrown even though the Job didn’t use selectforupdate, ttsbegin, and ttscommit statements in this example. The selectforupdate is implicit when using the update_recordset, and the ttsbegin and ttscommit are not necessary when all the updates are done in one database operation. However, if you were to write several update_recordset statements in a row, or do other checks that should make the update fail, you could use ttsbegin and ttscommit and force a ttsabort if the checks fail.

Delete_from

As with the insert_recordset and update_recordset operators, there is also an option for deleting a
chunk of records. This operator is called delete_from and is used as the next example shows:

Example:-

static void Delete_FromExample(Args _args)
{
    A_Student _Student;

    delete_from _Student
    where _Student.Student_ID == 'CT_025';
    //info(strFmt("Name-%1,ID-%2 Deleted From Database",_Student.Student_Name,_Student.Student_ID));
}

Author : Umesh Patel

Related Posts