The Compute method in a DataTable in C # uses the collection

The parameters of the COMPUTE function are two: Expression, and filter.

Expresstion is a calculation expression, for more information about expression, see here "http://msdn2.microsoft.com/zh-cn/library/ System.data.datacolumn.expression (vs.80). aspx. The filter is a conditional filter, similar to the Where condition of SQL.

DataTable dt = new DataTable ();
Nested ternary operations fork to pleasantly surprised
Object obj = Dt.compute ("IIf (1000=5,1000,iif (100>100,4001,2000))", null);
Response.Write (obj);

System.Data.DataTable table = new DataTable ();
Calculate constants, you can not initialize columns
Object test = Table.compute ("+", "");
Console.WriteLine (test);

String a = "123";
System.Double B = 123;
Decimal c = 123m;
Console.WriteLine (Convert.todecimal (a));
test=2;

Test = Table.compute ("+", "false");
Console.WriteLine (test);
test=2; constant calculation is independent of filter

Test = Table.compute ("ABS (1)", "");
Console.WriteLine (test);
Test=null, do not know for this what no error, and return null, other mathematical functions will be wrong

Test = Table.compute ("2%2", "");
Console.WriteLine (test);
test=0;
Other functions refer to the following computed columns

Initialize Datatale
Table. Columns.Add ("id", typeof (String));
Table. Columns.Add ("Value", typeof (int));
for (int i = 1; i <=; 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");
/**/
Throws an exception, this must be an aggregate function

Supported aggregation functions **********************//

Ask for 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)", "");
/**/
Throws an exception, this 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;

Biggest
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 calculations
Test = Table.compute ("max (value)/sum (value)", "");
Console.WriteLine (test);
test=0.181818181818182

/**/
/******************************************* computed column *************************/

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

Simple calculation
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 the substring is 1, not 0.

Type conversions
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 SQL Server IsNull
Column. Expression = "IsNull (value,10)";
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
Test=1;

Ternary operator, equivalent to SQL Server case
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 operations
Column. Expression = "IIF (Value>5,1000,iif (id like '%1 ', 4000,2000)");
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=4000;

Percentage of total Client Computing
Column. Expression = "Value/sum (value)";
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=0.01818182

The client calculates the difference, such as the win field difference in the NBA regular season
Column. Expression = "max (value)-value";
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
Test=9

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

Initializing child tables, 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 <=; i++)
{
System.Data.DataRow drow = Tablechild.newrow ();
drow["id"] = "ID1";
drow["value"] = i;
TABLECHILD.ROWS.ADD (drow);
}

Calculate the number of child table records
Column. Expression = "count (Child (relation). Value)";
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=10;

Calculate the percentage of a parent-child table
Column. Expression = "Value/sum (Child (relation). Value)";
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=0.01818182;

Calculates the difference between a parent and child table, such as the parent table as inventory quantity, the child table is the ordered quantity, calculates the quantity that needs to be replenished
Column. Expression = "IIf (Value-sum (relation). Value) >0,0,value-sum (Child (relation). Value)";
Test = table. Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=-54;

Unfortunately, there is no way to calculate the year and the quarter, and the computed column cannot be used as a constraint
At the end, the DataTable allows you to make the most of your intelligence to reduce complex SQL statements and reduce server computing compliance

The Compute method in a DataTable in C # uses the collection