Using System;
Using System.ComponentModel;
Using System.Data;
Using System.Windows.Forms; Namespace WindowsApplication1 ... {public partial class form1:form ... {public Form1 () ...
{InitializeComponent (); } private void Button1_Click (object sender, EventArgs e) ... {//system.data.datatable calculation function detailed, reprint please keep the following information//test environment vs2005,c#//Author: jinjazz//author blog:http://
blog.csdn.net/jinjazz/system.data.datatable table = new DataTable (); You can evaluate constants without initializing the Column object Test = table.
Compute ("1+1", "");
Console.WriteLine (test);
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, I do not know for this what is not a mistake, and return null, the other mathematical functions will be 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 T
EST = 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; More regrettable is not found to be able to calculate the year and the chain method, 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 ^&^}}