Usage of Compute method in C DataTable of numeric value and string and operator and table and so on

This article illustrates the use of Compute method in C # DataTable. Share it for your reference, as follows:

The Compute function has only two arguments: Expression, and Filter.

Expresstion is a computed expression. See here for details on Expression:

http://msdn2.microsoft.com/zh-cn/library/system.data.datacolumn.expression(VS.80).aspx

Filter is a conditional filter, which is similar to Where condition of sql.

DataTable dt = new DataTable();
// Nested 3 Meta-operation   Awesome 5 Body throwing ground 
object obj = dt.Compute("iif(1000=5,1000,iif(100>100,4001,2000))", null);
Response.Write(obj);
System.Data.DataTable table = new DataTable();
// Evaluate constants without initializing columns 
object test = table.Compute("1+1", "");
Console.WriteLine(test);
string a = "123";
System.Double b = 123;
decimal c = 123m;
Console.WriteLine(Convert.ToDecimal(a));
//test=2;
test = table.Compute("1+1", "false");
Console.WriteLine(test);
//test=2; Constant calculation sum filter Irrelevant 
test = table.Compute("abs(1)", "");
Console.WriteLine(test);
//test=null I don't know why this one didn't report an error and returned null, Other mathematical functions will be wrong 
test = table.Compute("2%2", "");
Console.WriteLine(test);
//test=0;
// Other functions refer to the computed columns below 
// Initialization 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 
//************************************* Supported aggregate functions **********************//
// Find quantity 
test = table.Compute("count(id)", "false");
Console.WriteLine(test);
//test=0;
test = table.Compute("count(id)", "true");
Console.WriteLine(test);
//test=10;
// Summation 
test = table.Compute("sum(value)", "");
Console.WriteLine(test);
//test=55;
//test = table.Compute("sum(id)","");
/**/
//// Throw an exception, which 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;
// 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 computation 
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 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; //substring The starting character position of is 1 No 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 sqlserver Adj. isnull
column.Expression = "isnull(value,10)";
test = table.Select("id='id1'")[0]["exp1"];
Console.WriteLine(test);
//test=1;
//3 Meta-operator , Equivalent to sqlserver Adj. case when
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 3 Meta-operation 
column.Expression = "iif(value>5,1000,iif(id like '%1',4000,2000))";
test = table.Select("id='id1'")[0]["exp1"];
Console.WriteLine(test);
//test=4000;
// Client calculations as a percentage of the 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 nba Winning difference in regular season 
column.Expression = "max(value)-value";
test = table.Select("id='id1'")[0]["exp1"];
Console.WriteLine(test);
//test=9
//*********************** Parent-child table calculation *************************************/
// Initialize child table , Father-son table relationship 
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 parent and child tables , For example, if the parent table is inventory quantity and the child table is order quantity, the quantity to be replenished can be calculated 
column.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, there is no method to calculate year-on-year and month-on-month, and the calculated column cannot be used as a constraint 
// End, DataTable It can let you try your best to use your intelligence to reduce the complexity sql Statement and mitigates server computation compliance 

For more readers interested in C # related content, please check out the topics on this site: "C # Common Control Usage Tutorial", "WinForm Control Usage Summary", "C # Data Structure and Algorithm Tutorial", "C # Object-Oriented Programming Introduction Tutorial" and "C # Programming Thread Use Skills Summary"

I hope this article is helpful to everyone's C # programming.