DataTable Select

http://www.dotblogs.com.tw/joysdw12/archive/2010/11/09/19294.aspx

public void TDataTableSelect()
{
// 建立測試DataTable
DataTable table = new DataTable();
table.Columns.Add("key", typeof(string));
table.Columns.Add("value", typeof(int));
table.Columns.Add("date", typeof(DateTime));
DataRow dr;
for (int i = 0; i < 5; i++)
{
dr = table.NewRow();
dr["key"] = "key" + i;
dr["value"] = i * 100;
dr["date"] = DateTime.Now.AddYears(i).Date;
table.Rows.Add(dr);
}

/******************************************
 * 常數計算 DataTable.Compute 方法
 * 參數:
 *      expression:要計算的運算式。
 *      filter:要限制在運算式中評估之資料列的篩選條件。
 * ****************************************/

// 加總運算 
object testVar = table.Compute("1 + 1", "");
Response.Write("加總運算:" + testVar.ToString() + "

"); // result = 2;

// 加總運算，無filter
testVar = table.Compute("1 + 1", "false");
Response.Write("加總運算，無filter:" + testVar.ToString() + "

"); // result = 2;

// 絕對值運算
testVar = table.Compute("abs(1)", "");
Response.Write("絕對值運算:" + testVar.ToString() + "

"); // result = null;

// 求餘數運算
testVar = table.Compute(" 2%2 ", "");
Response.Write("求餘數運算" + testVar.ToString() + "

"); // result = 0;

// 加法運算，exception: 無法評估。運算式 'System.Data.NameNode' 不是彙總。
//testVar = table.Compute("value+1", "true"); 
//Response.Write(testVar.ToString() + "

");

/******************************************
 * 彙總計算 DataTable.Compute 方法
 * 參數:
 *      expression:要計算的運算式。
 *      filter:要限制在運算式中評估之資料列的篩選條件。
 * ****************************************/

// 筆數計算
testVar = table.Compute("count(key)", "");
Response.Write("筆數計算:" + testVar.ToString() + "

"); // result = 3;

// 筆數計算，有filter
testVar = table.Compute("count(key)", "key='key1'");
Response.Write("筆數計算，有filter:" + testVar.ToString() + "

"); // result = 1;

// 欄位總和計算
testVar = table.Compute("sum(value)", "");
Response.Write("欄位總和計算:" + testVar.ToString() + "

"); // result = 1000;

// 欄位平均計算
testVar = table.Compute("avg(value)", "");
Response.Write("欄位平均計算:" + testVar.ToString() + "

"); // result = 200;

// 找出最小值
testVar = table.Compute("min(value)", "");
Response.Write("找出最小值:" + testVar.ToString() + "

"); // result = 0;

// 找出最大值
testVar = table.Compute("max(value)", "");
Response.Write("找出最大值:" + testVar.ToString() + "

"); // result = 400;

// 統計標準偏差
testVar = table.Compute("StDev(value)", "");
Response.Write("統計標準偏差:" + testVar.ToString() + "

"); // result = 158.113883008419;

// 統計方差
testVar = table.Compute("Var(value)", "");
Response.Write("統計方差:" + testVar.ToString() + "

"); // result = 25000;

// 複雜計算
testVar = table.Compute("max(value) / sum(value)", "");
Response.Write("複雜計算:" + testVar.ToString() + "

"); // result = 0.4;

/******************************************
* 欄位計算 DataColumn.Expression 屬性
* 屬性值:
*      運算式，用來計算資料行的值或建立彙總資料行。運算式的傳回型別是由資料行的 DataType 所判斷。
* ****************************************/

DataColumn column = new DataColumn("exp1", typeof(float));
table.Columns.Add(column);

// 乘法計算
column.Expression = "value*2";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("乘法計算:" + testVar.ToString() + "

"); // result = 200;

// 字串長度計算函數
column.Expression = "len(key)";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("字串長度計算函數:" + testVar.ToString() + "

"); // result = 4;

// 含空白字串長度計算函數
column.Expression = "len(' '+key+' ') ";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("含空白字串長度計算函數:" + testVar.ToString() + "

"); // result = 6;

// 去空白後字串長度計算函數
column.Expression = "len(trim(' '+key+' '))";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("去空白後字串長度計算函數:" + testVar.ToString() + "

"); // result = 4;

// 切字串函數 (SQL的Substring起始Index為1)
column.Expression = "substring(key,4,len(key)-3)";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("切字串函數:" + testVar.ToString() + "

"); // result = 1;

// 資料型別轉換
column.Expression = "convert(substring(key,4,len(key)-3),'System.Int32')*1.6";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("資料型別轉換:" + testVar.ToString() + "

"); // result = 1.6;

// SQL-ISNULL
column.Expression = "isnull(value,10)";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("SQL-ISNULL:" + testVar.ToString() + "

"); // result = 100;

// CASE運算式
column.Expression = "iif(value>5,1000,2000)";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("CASE運算式" + testVar.ToString() + "

"); // result = 1000;

// LINK運算式
column.Expression = "iif(key like '%1',1000,2000)";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("LINK運算式:" + testVar.ToString() + "

"); // result = 1000;

// IN運算式
column.Expression = "iif(key not in('key1'),1000,2000)";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("IN運算式:" + testVar.ToString() + "

"); // result = 2000;

// 多層三元運算
column.Expression = "iif(value>5,1000,iif(key like '%1',4000,2000))";
testVar = table.Select("key='key1'")[0]["exp1"];
Response.Write("多層三元運算:" + testVar.ToString() + "

"); // result = 1000;

/******************************************
* 資料查詢 DataTable.Select 方法
* 參數:
*      filterExpression:用來篩選資料列的準則。
*      sort:指定資料行和排序方向的字串。
* ****************************************/

DataRow[] row;

// 基本查詢
testVar = table.Select("key='key0'")[0]["key"].ToString();
Response.Write("基本查詢:" + testVar.ToString() + "

"); // result = key0;

// 多條件查詢
testVar = table.Select("key='key1' and value=100")[0]["key"].ToString();
Response.Write("多條件查詢:" + testVar.ToString() + "

"); // result = key1;

// DateTime日期查詢
string nowDate = DateTime.Now.ToString("#yyyy-MM-dd#");
testVar = table.Select("date=" + nowDate + "")[0]["key"].ToString();
Response.Write("日期查詢:" + testVar.ToString() + "

"); // result = key0;
}