Datatable. compute Method Instance

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data; namespace console11 {class program {static void main (string [] ARGs) {system. data. datatable table = new datatable (); // calculates the constant. The object test = table column can be not initialized. compute ("1 + 1", ""); console. writeline (TEST); string a = "123"; system. double B = 123; decimal c = 123 m; console. writeline (float) A); // test = 2; test = table. compute ("1 + 1", "false"); console. writeline (TEST); // test = 2; constant calculation has nothing to do with filter. test = table. compute ("ABS (1)", ""); console. writeline (TEST); // test = NULL. If there is no error for this, and null is returned, other mathematical functions will carry the test = TABLE error. compute ("2% 2", ""); console. writeline (TEST); // test = 0; // for other functions, refer to the calculation column below. // initialize the ale 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, here it must be an aggregate function //********************************* * ******************************/// calculate the number 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. Here it cannot be 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; // calculate the 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 calculation 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 calculation of column. expression = "value * 2"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 2; // string Function Column. expression = "Len (ID)"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 3; // string Function Column. expression = "Len ('' + ID + '')"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 5; // string Function Column. expression = "Len (TRIM ('' + ID + '')"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 3; // string Function 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 the isnull column of sqlserver. expression = "isnull (value, 10)"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 1; // ternary operator, equivalent to the case when column of sqlserver. expression = "IIF (value> 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 column. expression = "IIF (value>, IIF (Id like '% 1',)"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 4000; // percentage of the total count calculated by the client column. expression = "value/sum (value)"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 0.01818182 // The client calculates the difference value, for example, column. expression = "max (value)-value"; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (test ); // test = 9 // ************************* parent-child table calculation ******* * **************************** // initialize the sub-table, 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 records in the sub-table column. expression = "count (Child (relation ). value) "; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 10; // calculate the percentage column of the Parent and Child tables. expression = "value/sum (Child (relation ). value) "; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test = 0.01818182; // calculate the difference value between the parent and child tables. For example, if the parent table is the inventory quantity, the sub-table is the order quantity, and the number column to be supplemented is calculated. expression = "IIF (value-sum (Child (relation ). value)> 0, 0, value-sum (Child (relation ). value) "; test = table. select ("ID = 'id1'") [0] ["exp1"]; console. writeline (TEST); // test =-54; // unfortunately, no year-on-year comparison or period-over-period comparison method can be found, in addition, the calculation Column cannot end as a constraint, datatable allows you to use your talents to reduce complex SQL statements and reduce server computing compliance with ^ & ^ }}}