DataTable相关：datatable计算功能

看了下大家对datatable的计算功能，搜集了一些例子贴上。

 public class DataTableCompute
    {
        public static void SomeDataTableCompute()
        {
            DataTable myTesttable = new DataTable();

            object resultConst = myTesttable.Compute("2*3", "");//计算常量

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("3*2", "false");

            Console.WriteLine(resultConst);
           
            resultConst = myTesttable.Compute("abs(1)", "");

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("10/2", "");

            Console.WriteLine(resultConst);

            //初始化一个dataTable测试
            myTesttable.Columns.Add("id", typeof(string));

            myTesttable.Columns.Add("value", typeof(int));

            for (int i = 1; i <= 10; i++)//赋值
            {
                System.Data.DataRow dRow = myTesttable.NewRow();

                dRow["id"] = "id" + i.ToString();

                dRow["value"] = i;

                myTesttable.Rows.Add(dRow);
            }

            //支持以下sql中的函数
            resultConst = myTesttable.Compute("count(id)", "false");//求数量

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("count(id)", "true");

            Console.WriteLine(resultConst);


            resultConst = myTesttable.Compute("sum(value)", "");//求和

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("avg(value)", "");//平均

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("min(value)", ""); //最小

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("max(value)", "");//最大

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("StDev(value)", "");//统计标准偏差

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("Var(value)", "");//统计方差

            Console.WriteLine(resultConst);

            resultConst = myTesttable.Compute("max(value)/sum(value)", "");//复杂计算

            Console.WriteLine(resultConst);


          //支持列的操作

            DataColumn column = new DataColumn("exp1", typeof(float));

            myTesttable.Columns.Add(column);

            column.Expression = "value*2";//简单计算

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "len(id)";//字符串函数

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "len(' '+id+' ')";//字符串函数

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);
            
            column.Expression = "len(trim(' '+id+' '))";

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "substring(id,3,len(id)-2)";

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "convert(substring(id,3,len(id)-2),'System.Int32')*1.6";//类型转换

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "isnull(value,10)"; //isnull

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "iif(value>5,1000,2000)"; //三元运算符

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "iif(id like '%1',1000,2000)"; //like运算符

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "iif(id not in('id1'),1000,2000)";//in运算符

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "iif(value>5,1000,iif(id like '%1',4000,2000))"; //嵌套的三元运算

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "value/sum(value)"; //客户端计算所占总数的百分比

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "max(value)-value"; //客户端计算差值,比如nba常规赛的胜场差

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            //父子表关系
            DataTable myTestTableChild = new DataTable();

            myTestTableChild.Columns.Add("id", typeof(string));

            myTestTableChild.Columns.Add("value", typeof(int));

            DataSet myTestDataSet = new DataSet();

            myTestDataSet.Tables.Add(myTestTableChild);

            myTestDataSet.Tables.Add(myTesttable);

            DataRelation relation = new DataRelation("relation", myTesttable.Columns["id"], myTestTableChild.Columns["id"]);

            myTestDataSet.Relations.Add(relation);

            for (int i = 1; i <= 10; i++)
            {
                DataRow dRow = myTestTableChild.NewRow();

                dRow["id"] = "id1";

                dRow["value"] = i;

                myTestTableChild.Rows.Add(dRow);
            }

            column.Expression = "count(child(relation).value)";//计算子表记录数

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "value/sum(child(relation).value)";//计算父子表的百分比

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);

            column.Expression = "iif(value-sum(child(relation).value)>0,0,value-sum(child(relation).value))";//计算父子表的差值

            resultConst = myTesttable.Select("id='id1'")[0]["exp1"];

            Console.WriteLine(resultConst);
        }
    }

代码下载：http://download.csdn.net/detail/yysyangyangyangshan/4359153