Datatable computing function)

C # -- details the computing function using system of datatable;
Using system. componentmodel;
Using system. Data;
Using system. Windows. forms;
Namespace windowsapplication1
{
Public partial class form1: Form
{

Public form1 ()
{
Initializecomponent ();
}

Private void button#click (Object sender, eventargs E)
{

System. Data. datatable table = new datatable ();

// Calculate the constant. No initialization column is allowed.
Object test = table. Compute ("1 + 1 ","");
Console. writeline (test );
// Test = 2;

Test = table. Compute ("1 + 1", "false ");
Console. writeline (test );
// Test = 2; constant calculation is irrelevant to filter

Test = table. Compute ("ABS (1 )","");
Console. writeline (test );
// Test = NULL. I do not know why this is correct, and null is returned. Other mathematical functions will carry errors.

Test = table. Compute ("2% 2 ","");
Console. writeline (test );
// Test = 0;
// For other functions, refer to the following calculation column.

// Initialize datatale
Table. Columns. Add ("ID", typeof (string ));
Table. Columns. Add ("value", typeof (INT ));
For (INT I = 1; I <= 10; I ++)
{
System. Data. datarow Drow = table. newrow ();
Drow ["ID"] = "ID" + I. tostring ();
Drow ["value"] = I;
Table. Rows. Add (Drow );
}

// Test = table. Compute ("value + 1", "true ");
//// Throw an exception, which must be an aggregate function

// ************************************ Support **********************//

// Calculate the quantity
Test = table. Compute ("count (ID)", "false ");
Console. writeline (test );
// Test = 0;

Test = table. Compute ("count (ID)", "true ");
Console. writeline (test );
// Test = 10;

// Sum
Test = table. Compute ("sum (value )","");
Console. writeline (test );
// Test = 55;

// Test = table. Compute ("sum (ID )","");
//// Throw an exception, which cannot be a string

// Average
Test = table. Compute ("AVG (value )","");
Console. writeline (test );
// Test = 5;

// Minimum
Test = table. Compute ("min (value )","");
Console. writeline (test );
// Test = 1;

// Maximum
Test = table. Compute ("max (value )","");
Console. writeline (test );
// Test = 10;

// Statistical standard deviation
Test = table. Compute ("STDev (value )","");
Console. writeline (test );
/// Test = 3.02765035409749

// Statistical variance
Test = table. Compute ("VaR (Value )","");
Console. writeline (test );
/// Test = 9.16666666666667

// Complex computing
Test = table. Compute ("max (value)/sum (value )","");
Console. writeline (test );
/// Test = 0.181818181818182
 
/*************************************** *************************/

System. Data. datacolumn column = new datacolumn ("exp1", typeof (float ));
Table. Columns. Add (column );

// Simple computing
Column. Expression = "value * 2 ";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 2;

// String functions
Column. Expression = "Len (ID )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 3;

// String functions
Column. Expression = "Len ('' + ID + '')";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 5;

// String functions
Column. Expression = "Len (TRIM ('' + ID + ''))";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 3;

// String functions
Column. Expression = "substring (ID, 3, Len (ID)-2 )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 1; // The starting character position of substring is 1, not 0

// Type conversion
Column. Expression = "convert (substring (ID, 3, Len (ID)-2), 'System. int32") * 1.6 ";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 1.6;

// Equivalent to isnull of sqlserver
Column. Expression = "isnull (value, 10 )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 1;

// Ternary operator, equivalent to the case when of sqlserver
Column. Expression = "IIF (value> 5,1000, 2000 )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 2000;

// Like Operator
Column. Expression = "IIF (Id like '% 1', 1000,2000 )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 1000;

// In Operator
Column. Expression = "IIF (id not in ('id1'), 1000,2000 )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 2000;

// Nested ternary operation
Column. Expression = "IIF (value>, IIF (Id like '% 1 ))";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 4000;

// Percentage of client computing in total
Column. Expression = "value/sum (value )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 0.01818182

// The client calculates the difference, for example, the NBA regular season victory.
Column. Expression = "max (value)-value ";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 9

// *********************** Parent-child table calculation ************ *************************/

// Initialize the child table and parent-child table relationships
Datatable tablechild = new datatable ();

Tablechild. Columns. Add ("ID", typeof (string ));
Tablechild. Columns. Add ("value", typeof (INT ));

System. Data. dataset DS = new dataset ();
DS. Tables. Add (tablechild );
DS. Tables. Add (table );
Datarelation relation = new datarelation ("relation", table. Columns ["ID"], tablechild. Columns ["ID"]);
DS. relations. Add (relation );

For (INT I = 1; I <= 10; I ++)
{
System. Data. datarow Drow = tablechild. newrow ();
Drow ["ID"] = "id1 ";
Drow ["value"] = I;
Tablechild. Rows. Add (Drow );
}

// Calculate the number of sub-table records
Column. Expression = "count (Child (relation). Value )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test = 10;

// Calculate the percentage of parent and child tables
Column. Expression = "value/sum (Child (relation). Value )";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
/// Test = 0.01818182;

// Calculate the difference between the parent and child tables. For example, the parent table is the inventory quantity, and the sub-table is the order quantity. Then, calculate the quantity to be supplemented.
Column. Expression = "IIF (value-sum (Child (relation). Value)> 0, value-sum (Child (relation). Value ))";
Test = table. Select ("ID = 'id1'") [0] ["exp1"];
Console. writeline (test );
// Test =-54;

// Unfortunately, no year-on-year comparison and period-over-period comparison method is found, and the calculation Column cannot be used as a constraint.
// ************. Datatable allows you to use your talents to reduce complicated SQL statements and reduce server computing compliance ^



}

}
}