This example describes the compute method usage in C # DataTable. Share to everyone for your reference, specific as follows:
The parameters of the COMPUTE function are two: Expression, and filter.
Expresstion is a computational 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, similar to the Where condition of SQL.
DataTable dt = new DataTable ();
Nested ternary operation oregano to admiration object obj = Dt.compute ("IIf (1000=5,1000,iif (100>100,4001,2000))", null);
Response.Write (obj);
System.Data.DataTable table = new DataTable ();
Calculated constants, you can not initialize the Column 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 and filter Independent test = Table.compute ("ABS (1)", "");
Console.WriteLine (test);
Test=null, do not know why this is not an error, and return null, the other mathematical functions will be picked up the wrong test = Table.compute ("2%2", "");
Console.WriteLine (test);
test=0; Other functions refer to the computed column//initialization Datatale table below.
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 aggregate functions **********************////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 string//Average test = Table.compute ("avg (value)", "");
Console.WriteLine (test);
test=5;
The Minimum test = Table.compute ("min (value)", "");
Console.WriteLine (test);
Test=1;
Max 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 calculation 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; The starting character position for the 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; The equivalent of SQL Server's 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 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; The nested ternary 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; Percent column for the total number of client calculations.
Expression = "Value/sum (value)"; Test = table.
Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test); TEST=0.01818182//Client computing difference, such as the NBA regular season's win-difference column.
Expression = "max (value)-value"; Test = table.
Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test); Test=9//*********************** Parent-child table calculation **********************////Initialize child table, parent-child table relational 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 (relation). Value"; Test = table.
Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=10; Calculates the percentage column of a parent-child table.
Expression = "Value/sum (relation). Value"; Test = table.
Select ("Id= ' id1 '") [0]["EXP1"];
Console.WriteLine (test);
test=0.01818182; Calculates the difference between a parent-child table, such as the number of inventory, the child table for the order quantity, and the quantity column that needs to be replenished.
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;
It is regrettable that there is no way to calculate the year and the chain, and the computed column can not be used as a constraint//end, the DataTable allows you to maximize the wisdom to reduce the complexity of SQL statements and reduce server computing compliance
Read more about C # Interested readers can view the site topics: "C # Common control usage Tutorial", "WinForm Control Usage Summary", "C # Data structure and algorithm tutorial", "C # object-oriented Program design Introductory Course" and "C # programming Thread Usage Skill Summary"
I hope this article will help you with C # programming.