Update_Recordset in Dynamics Ax

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));
}

How to Add Auto Number Column in Asp.net GridView

Description:- 
While working in ASP.NET, you often come across a need to display serial number or Auto-number in a Grid view control. This can be accomplished by adding the Container.DataItemIndex in the html mark-up of the Gridview control. On GridView Control add new Template Field and bind Container.DataItemIndex.

<div>
   <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
      <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
      <AlternatingRowStyle BackColor="#BFE4FF" />
      <Columns>
         <asp:TemplateField HeaderText="No.">
            <ItemTemplate><%# Container.DataItemIndex + 1 %></ItemTemplate>
         </asp:TemplateField>
         <asp:BoundField DataField="CountryId" HeaderText="CountryId" />
         <asp:BoundField DataField="CountryName" HeaderText="CountryName"/>
      </Columns>
   </asp:GridView>
</div>

On my Code behind I have Bind Country Table in GridView for taking Data from SQL Server.

Protected void Page_Load(object sender, EventArgs e)
{
   SqlDataAdapter adapter = new SqlDataAdapter();
   DataSet ds = new DataSet();
   int i = 0;
   string sql = null;
   string connetionString = "Data Source=’ServerName’;Initial Catalog=’ DataSourceName’;Persist        Security Info=True;User ID=’UserName’;Password=’Password’";
  sql = "select * from Country";
  SqlConnection connection = new SqlConnection(connetionString);
  connection.Open();
  SqlCommand command = new SqlCommand(sql, connection);
  adapter.SelectCommand = command;
  adapter.Fill(ds);
  adapter.Dispose();
  command.Dispose();
  connection.Close();
  GridView1.DataSource = ds.Tables[0];
  GridView1.DataBind();
}

Now check on your browser for Index.
Commonly Implemented Features in SysOperation and RunBase in Ax 2012

Commonly Implemented Features in SysOperation and RunBase in Ax 2012

Description:- SysOperation is a framework in Microsoft Dynamics® AX 2012 that allows application logic to be written in a way that supports running operations interactively or via the Microsoft Dynamics AX batch server. The framework provides capabilities that are very similar to the RunBase framework that came before it. The batch framework has very specific requirements for defining operations:

1.       The operation must support parameter serialization so that its parameters can be saved to the batch table.
2.       The operation must have a way to display a user interface that can be launched from the batch job configuration user interface.
3.       The operation must implement the interfaces needed for integration with the batch server runtime.

The RunBase framework defines coding patterns that implement these requirements. The SysOperation framework provides base implementations for many of the patterns defined by the RunBase framework.
SysOperation and RunBase are frameworks geared toward building operations that can run via the batch server or interactively. In order for an operation to run via the batch server, it must meet these requirements:

1.       It must support parameter serialization via the SysPackable interface.
2.       It must support the standard run () method defined in the BatchRunable interface.
3.       It must support the batch server integration methods found in the Batchable interface.
4.       It must provide a mechanism to show input parameters with a user interface.

Currently in Microsoft Dynamics AX, all operations that must run via the batch server must derive from either the SysOperationController or the RunBaseBatch base class.
The following two samples illustrate the basic capabilities provided by the two frameworks:

1.       SysOpSampleSimpleRunbaseBatch
2.       SysOpSampleSimpleController

The simplest operation based on the RunBaseBatch base class has to implement 12 overrides. The purpose of this sample is simply to compare the RunBase and SysOperation frameworks. For full details of the RunBase framework, see the following article on MSDN: RunBaseBatch FrameWork

There are four classes: the controller, the service operation, the data contract, and the user interface builder.

let’s Start to Create Class in Ax Classes node and Name it “SysOpSampleSimpleRunbaseBatch”.

ClassDeclaration
  1. Derives from RunBaseBatch.
  2. Declares variables for operation input parameters.
  3. Declares variables for dialog box controls.
  4. Declares a macro defining a list of variables that need to be serialized.
class SysOpSampleSimpleRunbaseBatch extends RunBaseBatch
{
    str text;
    int number;
    DialogRunbase       dialog;

    DialogField numberField;
    DialogField textField;

    #define.lookupAlways(2)
    #define.CurrentVersion(1)

    #LOCALMACRO.CurrentList
        text,
        number
    #ENDMACRO
}

protected Object dialog()
{
    dialog = super();
    textField = dialog.addFieldValue(IdentifierStr(Description255), text, 'Text Property','Type some text here');
    textField.lookupButton(#lookupAlways);

    numberField = dialog.addFieldValue(IdentifierStr(Counter), text, 'Number Property','Type some number here');
    return dialog;
}

public void dialogPostRun(DialogRunbase _dialog)
{
    FormControl control;
    super(_dialog);

    // register overrides for form control events
    numberField.registerOverrideMethod(methodstr(FormIntControl, validate), methodstr(SysOpSampleSimpleRunbaseBatch, numberFieldValidate), this);
    textField.registerOverrideMethod(methodstr(FormStringControl, lookup), methodstr(SysOpSampleSimpleRunbaseBatch, textFieldLookup), this);

}

public boolean getFromDialog()
{
    text = textField.value();
    number = numberField.value();

    return super();
}

public boolean numberFieldValidate(FormIntControl _control)
{
    if (_control.value() < 0)
    {
        error('Please type a number >= 0');
        return false;
    }
    return true;
}

public container pack()
{
    return [#CurrentVersion, #CurrentList];
}

public int parmNumber(int _number = number)
{
    number = _number;
    return number;
}

public str parmText(str _text = text)
{
    text = _text;
    return text;
}

protected void putToDialog()
{
    super();
    textField.value(text);
    numberField.value(number);
}

public void run()
{
    // marshall call to server
    SysOpSampleSimpleRunbaseBatch::showTextInInfolog(this.pack());
}

public void  textFieldLookup(FormStringControl _control)
{
    FormStringControl       companyControl;
    SysTableLookup          tableLookup;
    Query                   query = new Query();

    companyControl = _control;
    tableLookup = SysTableLookup::newParameters(tablenum(DataArea),companyControl);
    tableLookup.addLookupfield(fieldnum(DataArea,Id),true);
    tableLookup.addLookupfield(fieldnum(DataArea,Name),false);

    query.addDataSource(tablenum(DataArea));
    tableLookup.parmQuery(query);
    tableLookup.performFormLookup();
}

public boolean unpack(container packedClass)
{
    Integer         version         = conPeek(packedClass,1);

    switch (version)
    {
        case #CurrentVersion:
            [version,#CurrentList] = packedClass;
            break;
        default:
            return false;
    }
    return true;
}

public static ClassDescription description()
{
    return 'Intermediate RunBaseBatch Sample';
}

public static void main(Args args)
{
    SysOpSampleSimpleRunbaseBatch operation;

    operation = new SysOpSampleSimpleRunbaseBatch();
    if (operation.prompt())
    {
        operation.run();
    }
}

private static server void showTextInInfolog(container packedRunBase)
{
    SysOpSampleSimpleRunbaseBatch thisClass;

    // If not a CLR session then marshall over. If already in a CLR session
    // then execute the logic for the operation
    if (!xSession::isCLRSession())
    {
        new XppILExecutePermission().assert();
        SysDictClass::invokeStaticMethodIL(classStr(SysOpSampleSimpleRunbaseBatch),
                                           staticMethodStr(SysOpSampleSimpleRunbaseBatch, showTextInInfolog),
                                           packedRunBase);
        // exit call executed in CLR session.
        return;
    }

    thisClass = new SysOpSampleSimpleRunbaseBatch();
    if (!thisClass.unpack(packedRunBase))
    {
        throw AifFault::fault('SysOpSampleSimpleRunbaseBatch unpack error', 'unpackError');
    }

    if (xSession::isCLRSession())
    {
        info('Running in a CLR session.');
    }
    else
    {
        info('Running in an interpreter session.');
        if (isRunningOnServer())
        {
            info('Running on the AOS.');
        }
        else
        {
            info('Running on the Client.');
        }
    }

    info(strFmt('SysOpSampleSimpleRunbaseBatch: %1, %2', thisClass.parmNumber(), thisClass.parmText()));
}

Create Class in Classes node and Name it SysOpSampleSimpleDataContract.

[DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(SysOpSampleSimpleUserInterfaceBuilder))]
class SysOpSampleSimpleDataContract
{
    str text;
    int number;
}

[DataMemberAttribute,
SysOperationLabelAttribute('Number Property'),
SysOperationHelpTextAttribute('Type some number >= 0'),
SysOperationDisplayOrderAttribute('2')]
public int parmNumber(int _number = number)
{
    number = _number;
    return number;
}

[DataMemberAttribute,
SysOperationLabelAttribute('Text Property'),
SysOperationHelpTextAttribute('Type some text'),
SysOperationDisplayOrderAttribute('1')]
public Description255 parmText(str _text = text)
{
    text = _text;
    return text;
}

Create Class in Classes node and Name it SysOpSampleSimpleController.

class SysOpSampleSimpleController extends SysOpSampleBaseController
{
}

public ClassDescription caption()
{
    return 'Intermediate SysOperation Sample';
}

void new()
{
    super();

    this.parmClassName(classStr(SysOpSampleSimpleService));
    this.parmMethodName(methodStr(SysOpSampleSimpleService, showTextInInfolog));
}

public static void main(Args args)
{
    SysOpSampleSimpleController operation;

    operation = new SysOpSampleSimpleController();
    operation.startOperation();
}

Create Class in Classes node and Name it SysOpSampleSimpleService.

class SysOpSampleSimpleService extends SysOperationServiceBase
{
}

public void showTextInInfolog(SysOpSampleSimpleDataContract data)
{
    if (xSession::isCLRSession())
    {
        info('Running in a CLR session.');
    }
    else
    {
        info('Running in an interpreter session.');
        if (isRunningOnServer())
        {
            info('Running on the AOS.');
        }
        else
        {
            info('Running on the Client.');
        }
    }


    info(strFmt('SysOpSampleSimpleService: %1, %2', data.parmNumber(), data.parmText()));
}

Create Class in Classes node and Name it SysOpSampleSimpleUserInterfaceBuilder.

class SysOpSampleSimpleUserInterfaceBuilder extends SysOperationAutomaticUIBuilder
{
    #define.lookupAlways(2)

    DialogField numberField;
    DialogField textField;
}

public boolean numberFieldValidate(FormIntControl _control)
{
    if (_control.value() < 0)
    {
        error('Please type a number >= 0');
        return false;
    }
    return true;
}

public void postBuild()
{
    super();

    // get references to dialog controls after creation
    numberField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SysOpSampleSimpleDataContract, parmNumber));
    textField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SysOpSampleSimpleDataContract, parmText));
    // change text field metadata to add lookup
    textField.lookupButton(#lookupAlways);

}

public void postRun()
{
    super();

    // register overrides for form control events
    numberField.registerOverrideMethod(methodstr(FormIntControl, validate), methodstr(SysOpSampleSimpleUserInterfaceBuilder, numberFieldValidate), this);
    textField.registerOverrideMethod(methodstr(FormStringControl, lookup), methodstr(SysOpSampleSimpleUserInterfaceBuilder, textFieldLookup), this);

}

public void  textFieldLookup(FormStringControl _control)
{
    FormStringControl       companyControl;
    SysTableLookup          tableLookup;
    Query                   query = new Query();

    companyControl = _control;
    tableLookup = SysTableLookup::newParameters(tablenum(DataArea),companyControl);
    tableLookup.addLookupfield(fieldnum(DataArea,Id),true);
    tableLookup.addLookupfield(fieldnum(DataArea,Name),false);

    query.addDataSource(tablenum(DataArea));
    tableLookup.parmQuery(query);
    tableLookup.performFormLookup();
}
The service and data contract classes define the operation. The derived controller class provides the main entry point and overrides the new() method to associate the operation classes with the controller. The base controller reflects on the operation and constructs metadata classes that define the operation. The base class SysOperationAutomaticUIBuilder uses the metadata derived from the operation to create the user interface. In the sample, there is a derived user interface builder called SysOpSampleSimpleUserInterfaceBuilder. This overrides the postBuild() and postRun() overrides on the base builder to subscribe to form control events related to validation and lookup.

The system uses SysOperationContractProcessingAttribute to associate the custom user interface builder with the data contract.

[DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(SysOpSampleSimpleUserInterfaceBuilder))]
class SysOpSampleSimpleDataContract
{
      str text;
      int number;
}

If this attribute is not present, the default builder, SysOperationAutomaticUIBuilder, is used. As an experiment, comment out the attribute in the preceding code, and then run the operation to see the differences.

The postBuild() override in the custom user interface builder is where the form control metadata needs to be modified before the controls are instantiated. The framework maintains an association between controls and data contracts in a map that can be accessed via the this.bindInfo() method. The map is keyed by the name of the property in the data contract.

public void postBuild()
{
      super();
      // get references to dialog controls after creation
      numberField = this.bindInfo().getDialogField(this.dataContractObject(),
            methodStr(SysOpSampleSimpleDataContract, parmNumber));
      textField = this.bindInfo().getDialogField(this.dataContractObject(),
            methodStr(SysOpSampleSimpleDataContract, parmText));
      // change text field metadata to add lookup
      textField.lookupButton(#lookupAlways);
}

The postRun() override in the custom user interface builder is where the form control events are subscribed to. The subscriptions must be added to the controls after they have been instantiated.

public void postRun()
{
      super(); // register overrides for form control events
      numberField.registerOverrideMethod(methodstr(FormIntControl, validate),
            methodstr(SysOpSampleSimpleUserInterfaceBuilder, numberFieldValidate), this);
      textField.registerOverrideMethod(methodstr(FormStringControl, lookup),
            methodstr(SysOpSampleSimpleUserInterfaceBuilder, textFieldLookup), this);
}

The registerOverRideMethod method on the controls is a run-time equivalent to the control overrides used in normal forms. If you use an override method in a standard Microsoft® MorphX® form, you can use the same method override in a dynamic form by using this mechanism. Note that both the RunBase and SysOperation frameworks allow the use of modeled forms as the operation user interface.

The SysOperation framework provides the override SysOperationController.templateForm() for that purpose, however, this topic is outside the scope of this white paper.

The samples in this section show how the user interface for the operation can use many of the same features that are available in the normal form programming model. Control overrides fire run-time events that can be subscribed to. The SysOperation version of the sample shows how the different aspects of the operation can be factored into separate classes.

To show that everything is possible with code, the RunBase sample is modified so that it marshals its interactive execution into a CLR session, in the same way that the SysOperation framework does. This illustrates the design principle that drove the SysOperation framework: move as much of the boilerplate code as possible into the base classes.

private static server void showTextInInfolog(container packedRunBase)
{
      SysOpSampleSimpleRunbaseBatch thisClass;
      // If not in a CLR session then marshal over. If already in a CLR session
      // then execute the logic for the operation
      if (!xSession::isCLRSession())
      {
new XppILExecutePermission().assert();
SysDictClass::invokeStaticMethodIL (classStr (SysOpSampleSimpleRunbaseBatch),
StaticMethodStr (SysOpSampleSimpleRunbaseBatch,
showTextInInfolog),
packedRunBase);
// exit call executed in CLR session.
return;
      }
      thisClass = new SysOpSampleSimpleRunbaseBatch();
      if (!thisClass.unpack(packedRunBase))
      {
throw AifFault::fault('SysOpSampleSimpleRunbaseBatch unpack error', 'unpackError');
      }
      if (xSession::isCLRSession())
      {
info('Running in a CLR session.');
      }
      else
      {
info('Running in an interpreter session.');
if (isRunningOnServer())
{
info('Running on the AOS.');
}
else
{
info('Running on the Client.');
}
      }
      info(strFmt('SysOpSampleSimpleRunbaseBatch: %1, %2',
      thisClass.parmNumber(), thisClass.parmText()));
}
Now Generate CIL and run your Controller Class.