Introducing Dataset

Dataset is the concept introduced to efficiently utilize database resources. The overhead incurred by persistent connections of large numbers of users to limited database resources was a major contributor to overall application performance degradation. In order to solve this problem, a connectionless Dataset was introduced that enables data operation without a continuous connection to the database.

Dataset is the basic unit that processes data and is used as the data format that is sent and received when managing data internally with Nexacro or communicating with the server. It provides methods to add, modify, delete, or operate data and provides functions such as merging and copying with other Datasets.

Dataset is a connectionless way to reduce the load on the database and increase system availability to provide more application services. It also has the advantage of reducing the amount of code and making data maintenance easier. However, since the original data is copied to the local Dataset and used, the memory utilization on the client-side increases in large data operations, requiring proper data management in the application.

In general, Dataset is not used independently but is used in the form of binding to the component that visually expresses data. Components such as Grid, Combo, and ListBox only have a form to show on the screen, but Dataset has the actual meaningful data.

Data Binding

Data binding is a technique that connects and synchronizes data and information between the Dataset object containing data information and a visually expressed component. Binding techniques can be divided into four levels depending on how they are used.

Full Binding

Grid is the component with the form being logically the same as Dataset. Therefore, Grid is completely mapped with Dataset. In this way, the binding that Dataset and component can be completely mapped one-to-one is called Full Binding. Among several components, only Grid is capable of the full binding.

Master & Detail Binding

It is the binding between Grid and the component that displays single data. As shown in the figure below, this is the case when a specific column of the selected record is input/output in the Edit component when the record of Grid is selected.

Lookup Binding

It is a type of binding in which the component that inputs/outputs single data has another Dataset inside. As shown in the figure below, this is the case when a specific column of the selected record is connected to the Combo component when the record of Grid is selected, and the corresponding value is found in the Dataset bound to the Combo and output to the Combo.

Presentation Binding

It is a binding technique for giving style to the data output to the component. This is the case when the style property value of the component is entered in the Dataset as shown on the left of the figure below and then bound to the style property of the component.

Calculating Data Average & Sum by Conditions

If the getSum method is to get the sum of a specified amount, then the getCaseSum method can add conditions. It is only calculating data that meets certain conditions.

Example

If you enter a number in the Edit component and click the Button component, then the sum, average, and count of values greater than that number are displayed.

sample_dataset_01.xfdl

Core features used in the example

getCaseSum: The parameters you specify with the getSum method are similar. The difference in the method starting with getCase~ is that you can add Conditional Expression as the first parameter.

How to implement an example

Configuring Form Screen

Place the Grid component. Create the Dataset object and set the data as shown below. Bind the created Dataset object to the Grid component. Use the Grid component only as the tool to display data. To sum or average the data, specify the value of the type property in a numeric format such as "INT".

Place the Edit component and the Button component to input the value to be entered in the conditional expression, and place the TextArea component to display the result.

Writing onclick Event Function

When the Button component is clicked, the calculation result value is displayed in the TextArea component. For the getCaseAvg method, additionally execute the nexacro.round method to process decimal points.

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	var strCmpExpr = "Column0 > "+this.Edit00.value;
	var casesum = this.Dataset00.getCaseSum(strCmpExpr, "Column0");
	var caseavg = nexacro.round(this.Dataset00.getCaseAvg(strCmpExpr, "Column0"), 1);
	var casecount = this.Dataset00.getCaseCount(strCmpExpr, "Column0");
	this.TextArea00.set_value("getCaseSum: "+casesum+"\n"
		+"getCaseAvg: "+caseavg+"\n"
		+"getCaseCount: "+casecount);
};

If the column type property value is "String", then it must be converted to a number type before processing the operation. In the example above, if the type property value of the Column0 column is "String", the getCaseSum syntax can be changed as follows.

var casesum = this.Dataset00.getCaseSum(strCmpExpr, "parseInt(Column0)");

Checking with QuickView

Run it with QuickView (Ctrl + F6) and enter the number within the data range to check the result value when you click the button.

Checking Column Information & Changing type Property Value

You can check the column information and change the property value of the desired column. The example shows how to change the value of the type property.

Example

Clicking the [getColumnInfo] button displays the column information of the Dataset object in the TextArea component. Select the column index you want to change, select the type property value, and click the [set_type] button to change the column type property value. You can check the changed information by clicking the [getColumnInfo] button.

sample_dataset_03.xfdl

Core features used in the example

getColumnInfo: Returns the ColumnInfo object. You can check the values of the name, prop, size, sumtext, and type properties. The value of the name property is readonly and cannot be changed.

How to implement an example

Configuring Form Screen

Create the Dataset object and set the data as shown below. Since this is for checking only the column property information, do not add data. Place the Button, Spin, Combo, and TextArea components appropriately.

As the innerdataset property value of the Combo component, enter the list that can be used for the type property value.

Writing onload Event Function

In the Spin component, specify the column index to be changed. Specify the max property value in the onload event function.

this.sample_dataset_03_onload = function(obj:nexacro.Form,e:nexacro.LoadEventInfo)
{
	this.Spin00.set_max(this.Dataset00.getColCount()-1);
};

Writing onclick Event Function

When the [getColumnInfo] button is clicked, column information is checked by repeating the number of columns of the Dataset object.

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	var strText = new String();
	for (var i = 0; i < this.Dataset00.getColCount(); i++) 
	{
		var objColInfo = this.Dataset00.getColumnInfo(i);
		strText += "ID = " + objColInfo.name;
		strText += " SIZE = " + objColInfo.size;
		strText += " type = " + objColInfo.type;
		strText += "\n";
	}
	this.TextArea00.set_value(strText);
};

When the [set_type] button is clicked, the type property value of the specified column index is changed.

this.Button01_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	var objColInfo = this.Dataset00.getColumnInfo(this.Spin00.value);
	objColInfo.set_type(this.Combo00.value);
};

Checking with QuickView

Run it with QuickView (Ctrl + F6), specify the column index, change the type property, and check the result value.

Checking Deleted Row Information

Information deleted from the Dataset object cannot be checked in the bound component (Grid component or other components). However, the Dataset object manages the deleted information and can be checked through a specific method.

Example

Clicking the [delete] button deletes the Row information. You can add Row by clicking the [add] button. You can edit the contents by selecting each cell. Information added, deleted, or modified in this way is displayed in the Grid component below when you click the [execute] button.

sample_dataset_04.xfdl

Core features used in the example

getRowType: Returns the type corresponding to the specified Row. Deleted Rows cannot be checked. In the example, the information corresponding to the addition (2) and change (4) is checked and processed.
getOrgColumn: Returns the initial value corresponding to the specified Row, Column. If the value has changed, you can check the original value.
getDeletedRowCount: Able to check the number of deleted Rows. Deleted Row information is processed in the form of an array. You can check the array information using the getDeletedRowset method. The result of executing the getDeletedRowCount method is the same as the length property value of the array obtained by executing the getDeletedRowset method.
getDeletedColumn: Returns the value corresponding to the specified column and Row (index) from the deleted Row information.

How to implement an example

Configuring Form Screen

Place the Grid component and the Button component as shown in the example. Then, create 2 Dataset objects. For one (Dataset00), specify data as shown below and bind it to the first Grid component. For the other Dataset (Dataset01), bind it to the second Grid component without specifying data.

Running Grid Contents Editor

Change the edittype property value of the first Grid component to "normal" so that the data can be modified.

Writing onclick Event Function

Write the script to be processed when each button is clicked in the onclick event function. When the [execute] button is clicked, the fn_orgDataset function is executed to add column information and check the deleted, modified, or added data.

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.Dataset00.deleteRow(this.Dataset00.rowposition)
};

this.Button02_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
		this.Dataset00.addRow();
};

this.Button01_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.Dataset01.addColumn("COL_TYPE", "STRING");
	for(var i=0;i<this.Dataset00.colcount;i++)
	{
		this.Dataset01.addColumn(this.Dataset00.getColID(i), "STRING");
	}
	this.fn_orgDataset();
};

this.fn_orgDataset = function()
{
	var sOrgCol = "";
	var sOrgVal = "";
	this.Dataset01.clearData(); 
	
	for (var i = 0; i < this.Dataset00.rowcount; i++) 
	{
		var nRowType = this.Dataset00.getRowType(i);
		if (nRowType == Dataset.ROWTYPE_INSERT || nRowType == Dataset.ROWTYPE_UPDATE)
		{
			this.Dataset01.addRow();
			this.Dataset01.setColumn(this.Dataset01.rowposition, "COL_TYPE", nRowType);
			for (var j = 0; j < this.Dataset00.colcount; j++) 
			{
				sOrgCol = this.Dataset00.getColID(j);
				sOrgVal = this.Dataset00.getOrgColumn(i, sOrgCol);

				this.Dataset01.setColumn(this.Dataset01.rowposition, sOrgCol, sOrgVal);
			}
		}
	}
	
	trace(this.Dataset00.getDeletedRowset().length);
	for (var i = 0; i < this.Dataset00.getDeletedRowCount(); i++) 
	{
		this.Dataset01.addRow();
		this.Dataset01.setColumn(this.Dataset01.rowposition, "COL_TYPE", Dataset.ROWTYPE_DELETE);
		for (var j = 0; j < this.Dataset00.colcount; j++) 
		{
			sOrgCol = this.Dataset00.getColID(j);
			sOrgVal = this.Dataset00.getDeletedColumn(i, sOrgCol);
			this.Dataset01.setColumn(this.Dataset01.rowposition, sOrgCol, sOrgVal);
		}
	}
	
	this.Grid01.createFormat();
};

The constant indicating the Row type is processed as the following values.

Dataset.ROWTYPE_INSERT: 2

Dataset.ROWTYPE_UPDATE: 4

Dataset.ROWTYPE_DELETE: 8

Checking with QuickView

Run it with QuickView (Ctrl + F6), delete, change, or add data, and click the [execute] button to check the Row information.

Creating Dataset with Script

You can create the Dataset object with the script and bind it to the Grid component. We will also take a look at how to add and utilize Dataset objects with AppVariables.

Example

Clicking the button creates the Dataset object as the variable of AppVariables, and creates the Dataset object as the child of the Form object. The created Dataset object is displayed by being bound to the Grid component.

sample_dataset_05.xfdl

Core features used in the example

addVariable: Adds the variable to the AppVariables area. You can create the Dataset object and specify the created object as the variable. The set variable can be accessed with nexacro.getApplication().[Variable Name].

How to implement an example

Configuring Form Screen

Place the Grid component, the Button component, and the TextArea component as shown in the example. Since the Dataset object is created and processed by the script, there is no operation when configuring the screen.

Writing onclick Event Function

The difference between the Dataset object added to AppVariables and the Dataset object added as the child of the Form object differs only in the methods (addVariable, addChild) that process the created Dataset object, and the rest of the process is the same.

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	var objApp = nexacro.getApplication();
	var objGdS = new Dataset;	
	objApp.addVariable("gds_data", objGdS);
	objApp.gds_data.addColumn("COL0","String");
	objApp.gds_data.addColumn("COL1","String");
	objApp.gds_data.addRow();
	objApp.gds_data.setColumn(objApp.gds_data.rowposition,"COL0","global COL0");
	objApp.gds_data.setColumn(objApp.gds_data.rowposition,"COL1","global COL1");
	this.Grid00.set_binddataset("gds_data");
	this.Grid00.createFormat();
	
	var objLdS = new Dataset;
	objLdS.set_name("ds_data");
	this.addChild("ds_data", objLdS);  
	this.ds_data.addColumn("COL0","String");
	this.ds_data.addColumn("COL1","String");
	this.ds_data.addRow();
	this.ds_data.setColumn(this.ds_data.rowposition,"COL0","1");
	this.ds_data.setColumn(this.ds_data.rowposition,"COL1","first");
	this.ds_data.addRow();
	this.ds_data.setColumn(this.ds_data.rowposition,"COL0","2");
	this.ds_data.setColumn(this.ds_data.rowposition,"COL1","second");
	this.Grid01.set_binddataset("ds_data");	
	this.Grid01.createFormat();
	
    this.TextArea00.set_value('global dataset rowcount : ' + objApp.all["gds_data"].rowcount+'\n'
		+'local  dataset rowcount : ' + this.all["ds_data"].rowcount);
};

Checking with QuickView

Run it with QuickView (Ctrl + F6) and click the button to check if the Dataset is created and bound normally.

Restoring Changed Column Information

When the value of the Dataset object is changed, the changed status information is managed. Therefore, if necessary, you can restore the original data.

Example

Clicking the button after changing the value of the Dataset object bound to the Grid component restores the original data.

sample_dataset_06.xfdl

Core features used in the example

getColCount: Returns the total number of columns. In the example, the getRowType method is used to check if there is changed data, and if the return value of the getColumn method and the return value of the getOrgColumn method of all columns of Row with the changed data are different, then the value is restored.

How to implement an example

Configuring Form Screen

Place the Grid component and the Button component as shown in the example. Create the Dataset object as shown below and bind it to the Grid component.

Running Grid Contents Editor

Run Grid Contents Editor, select the Body cell of the first column, and change the displaytype, edittype property values to "checkboxcontrol", "checkbox". For the body cell of the rest of the column, change the edittype property value to "normal". Then, add one column and modify the text property value of the Body cell as follows.

Writing onclick Event Function

Check if there is Row with the value changed and restore the changed value. The code to restore the deleted Row is not included.

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	var iRowCount = this.Dataset00.getRowCount();
	var iColCount = this.Dataset00.getColCount();
	
	for (var i = 0; i < iRowCount; i++)
	{
        if (this.Dataset00.getRowType(i) != "1")
        {
            for (var j = 0; j < iColCount; j++)
            {   
                if (this.Dataset00.getColumn(i, j) != this.Dataset00.getOrgColumn(i, j))
                {
                    this.Dataset00.setColumn(i, j, this.Dataset00.getOrgColumn(i, j));
                }
            }
        }
	}
}

Checking with QuickView

Run it with QuickView (Ctrl + F6) and click the button to check that the original value is restored when the button is clicked with the value modified.

Changing RowType

RowType of the Dataset object is automatically updated when there is a change, such as a value change or deletion. The original value is also managed according to RowType. In this example, we will see how to forcefully modify the changed RowType to confirm the changed value.

Example

If you click the button after changing the value of the Dataset object bound to the Grid component, then the RowType value is changed and the changed state is displayed in the TextArea component. In the figure below, if you change the original value (1) to 12 and then change RowType to ROWTYPE_NORMAL, then you can see that the original value is changed to 12.

sample_dataset_07.xfdl

Core features used in the example

updatecontrol: This is the property that sets whether to automatically change RowType when the data of the Dataset object is changed. The default value is set to true, so the value changes automatically. If you want to change the value using the setRowType method, you must first change the updatecontrol property value to false and then use the setRowType method.

How to implement an example

Configuring Form Screen

Place the Grid component, the Button component, and the TextArea component as shown in the example. Create the Dataset object as shown below and bind it to the Grid component.

Running Grid Contents Editor

Run Grid Contents Editor, select the Body cell of the first column, and change the edittype property value to "normal".

Writing onclick Event Function

Check if there is Row with the value changed, and output the changed value (getColumn) and original value (getOrgColumn). After changing RowType, output the original value (ROWTYPE_NORMAL_getOrgColumn).

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	var rowcnt = this.Dataset00.getRowCount();
	for(var i=0;i<rowcnt;i++)
	{
		if(this.Dataset00.getRowType(i)==4)
		{
			this.fn_addvalue("index:"+i+" getOrgColumn: "+this.Dataset00.getOrgColumn(i,0));
			this.fn_addvalue("index:"+i+" getColumn: "+this.Dataset00.getColumn(i,0));
			this.Dataset00.set_updatecontrol(false);
			this.Dataset00.setRowType(i,Dataset.ROWTYPE_NORMAL);
			this.Dataset00.set_updatecontrol(true);
			this.fn_addvalue("index:"+i+" ROWTYPE_NORMAL_getOrgColumn: "+this.Dataset00.getOrgColumn(i,0));	
		}
	}
}

this.fn_addvalue = function(str:String)
{
	if(this.TextArea00.value)
	{
		this.TextArea00.set_value(this.TextArea00.value+"\n"+str);
	} else {
		this.TextArea00.set_value(str);
	}
}

Checking with QuickView

Run it with QuickView (Ctrl + F6) and click the button to check the value displayed when the button is clicked with the value modified.

Filtering without Case Sensitivity

Let us take a look at how to process case-insensitive filtering with English text when using the filter method.

Example

Filtering is processed by the entered text. When the [uppercase] button is clicked, it is filtered without case sensitivity, and when the [sensitive case] button is clicked, the entered English text is filtered as-is.

sample_dataset_08.xfdl

Core features used in the example

filter: Processes filtering according to conditions. If there is no condition (empty string), the condition is disabled and the original data is displayed.

How to implement an example

Configuring Form Screen

Place the Grid component, Button component, and Edit component as shown in the example. Create the Dataset object as shown below and bind it to the Grid component.

Writing onclick Event Function

In order to process case-insensitively, all strings to be compared must be matched with uppercase or lowercase letters. In the example, toUpperCase is used to convert both the Dataset object data and the text entered in the Edit component to uppercase and compare them.

this.Button00_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.Dataset00.filter();
    var sFilter = "Column1.toString().toUpperCase().indexOf('" + this.Edit00.value.toUpperCase() + "') >= 0";
    this.Dataset00.filter(sFilter);
}

this.Button01_onclick = function(obj:nexacro.Button,e:nexacro.ClickEventInfo)
{
	this.Dataset00.filter();
    var sFilter = "Column1.toString().indexOf('" + this.Edit00.value + "') >= 0";
    this.Dataset00.filter(sFilter);
};

Checking with QuickView

Run it with QuickView (Ctrl + F6), enter the data to be filtered, and click the two buttons to compare the results.

Getting Calculation Values Related to Grouped Data

If data is grouped by specifying the keystring property, then check the values for the grouped data. You can display the desired value by specifying the prop property value of the ColumnInfo object. We will take a look at how to check multiple data for selected grouped data at once.

Example

Clicking the Row corresponding to SUBTOTAL displays the average and maximum values related to the grouped data.

sample_dataset_09.xfdl

Core features used in the example

getGroupRangeStart: Returns the starting index value referenced by Row containing group information. The example returns the starting index value (0 for A and 5 for B) of the data grouped into two values, A and B.
getGroupRangeCount: Returns the number of Rows referenced by the Row containing group information. The example returns the Count value (4 for A and 3 for B) of data grouped into two values, A and B. The last index value can be obtained by adding the value obtained from the getGroupRangeCount method to the value obtained from the getGroupRangeStart method.

How to implement an example

Place the Grid component on the screen.

Create the Dataset object as shown below and bind it to the Grid component. Specify the keystring property value of the Dataset object as 'Column0'.

In Grid Contents Editor, set the expr property value of the first column as follows.

Display the text 'SUBTOTAL' in Row that displays the sum of the grouped data.

Place the TextArea component next to the Grid component.

Write the oncellclick event function as follows.

Check whether the Row selected with the getRowLevel method is the Row that contains group information. After checking the starting index and ending index of the grouped data with the getGroupRangeStart, getGroupRangeCount methods, call the desired method.

this.Grid00_oncellclick = function(obj:nexacro.Grid,e:nexacro.GridClickEventInfo)
{
	var temp_str;
	var nStartIdx ;
	var nEndIdx ;
	if(this.Dataset00.getRowLevel(e.row)==0)
	{
		temp_str = "CLICK SUBTOTAL ROW";
	}
	else
	{
		nStartIdx = this.Dataset00.getGroupRangeStart(e.row);
		nEndIdx = this.Dataset00.getGroupRangeStart(e.row)+this.Dataset00.getGroupRangeCount(e.row);
		temp_str = "keystring: "+this.Dataset00.getColumn(e.row, "Column0")+"\n";
		temp_str += "getAvg: "+nexacro.round(this.Dataset00.getAvg("Column1", nStartIdx, nEndIdx),2)+"\n";
		temp_str += "getMax: "+this.Dataset00.getMax("Column1", nStartIdx, nEndIdx)+"\n";
		temp_str += "getMin: "+this.Dataset00.getMin("Column1", nStartIdx, nEndIdx)+"\n";
		temp_str += "getSum: "+this.Dataset00.getSum("Column1", nStartIdx, nEndIdx)+"\n";
		temp_str += "getCount: "+this.Dataset00.getCount("Column1", nStartIdx, nEndIdx);
	}
	this.TextArea00.set_value(temp_str);
};

Run it with QuickView (Ctrl + F6) and select the Row corresponding to SUBTOTAL to output the information about the grouped data to the TextArea component.

Adding to AppVariables

Let us see how to add the Dataset object created by the script to AppVariables.

Example

When the button is clicked, the script creates the Dataset object and adds it as AppVariables. Check if there is the AppVariable added and display the XML value.

sample_dataset_10.xfdl

Core features used in the example

isExistVariable: Check if there is a variable with the ID value in AppVariables.
getGroupRangeCount: Returns the number of Rows referenced by the Row containing group information. The example returns the Count value (4 for A and 3 for B) of data grouped into two values, A and B. The last index value can be obtained by adding the value obtained from the getGroupRangeCount method to the value obtained from the getGroupRangeStart method.

How to implement an example

Place the Grid component on the screen.

Create the Dataset object as shown below and bind it to the Grid component. Specify the keystring property value of the Dataset object as 'Column0'.

In Grid Contents Editor, set the expr property value of the first column as follows.

Display the text 'SUBTOTAL' in Row that displays the sum of the grouped data.

Place the TextArea component next to the Grid component.

Write the oncellclick event function as follows.

this.Grid00_oncellclick = function(obj:nexacro.Grid,e:nexacro.GridClickEventInfo)
{
	var temp_str;
	var nStartIdx ;
	var nEndIdx ;
	if(this.Dataset00.getRowLevel(e.row)==0)
	{
		temp_str = "CLICK SUBTOTAL ROW";
	}
	else
	{
		nStartIdx = this.Dataset00.getGroupRangeStart(e.row);
		nEndIdx = this.Dataset00.getGroupRangeStart(e.row)+this.Dataset00.getGroupRangeCount(e.row);
		temp_str = "keystring: "+this.Dataset00.getColumn(e.row, "Column0")+"\n";
		temp_str += "getAvg: "+nexacro.round(this.Dataset00.getAvg("Column1", nStartIdx, nEndIdx),2)+"\n";
		temp_str += "getMax: "+this.Dataset00.getMax("Column1", nStartIdx, nEndIdx)+"\n";
		temp_str += "getMin: "+this.Dataset00.getMin("Column1", nStartIdx, nEndIdx)+"\n";
		temp_str += "getSum: "+this.Dataset00.getSum("Column1", nStartIdx, nEndIdx)+"\n";
		temp_str += "getCount: "+this.Dataset00.getCount("Column1", nStartIdx, nEndIdx);
	}
	this.TextArea00.set_value(temp_str);
};

Run it with QuickView (Ctrl + F6) and select the Row corresponding to SUBTOTAL to output the information about the grouped data to the TextArea component.