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 .
[CSharp]View Plaincopyprint?
- 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
< go > The Compute method in a DataTable in C # uses the collection
