前言
當將資料從資料庫取出存放到DataSet或DataTable時後,有可以能會需要在依照條件去搜尋取得的資料,
碰到這種情況可以使用DataTable.Select方法,請看範例。
範例
001 | public void TDataTableSelect() |
004 | DataTable table = new DataTable(); |
005 | table.Columns.Add( "key" , typeof ( string )); |
006 | table.Columns.Add( "value" , typeof ( int )); |
007 | table.Columns.Add( "date" , typeof (DateTime)); |
009 | for ( int i = 0; i < 5; i++) |
012 | dr[ "key" ] = "key" + i; |
013 | dr[ "value" ] = i * 100; |
014 | dr[ "date" ] = DateTime.Now.AddYears(i).Date; |
018 | /****************************************** |
019 | * 常數計算 DataTable.Compute 方法 |
021 | * expression:要計算的運算式。 |
022 | * filter:要限制在運算式中評估之資料列的篩選條件。 |
023 | * ****************************************/ |
026 | object testVar = table.Compute( "1 + 1" , "" ); |
027 | Response.Write( "加總運算:" + testVar.ToString() + " |
031 | testVar = table.Compute( "1 + 1" , "false" ); |
032 | Response.Write( "加總運算,無filter:" + testVar.ToString() + " |
036 | testVar = table.Compute( "abs(1)" , "" ); |
037 | Response.Write( "絕對值運算:" + testVar.ToString() + " |
041 | testVar = table.Compute( " 2%2 " , "" ); |
042 | Response.Write( "求餘數運算" + testVar.ToString() + " |
045 | // 加法運算,exception: 無法評估。運算式 'System.Data.NameNode' 不是彙總。 |
046 | //testVar = table.Compute("value+1", "true"); |
047 | //Response.Write(testVar.ToString() + " |
050 | /****************************************** |
051 | * 彙總計算 DataTable.Compute 方法 |
053 | * expression:要計算的運算式。 |
054 | * filter:要限制在運算式中評估之資料列的篩選條件。 |
055 | * ****************************************/ |
058 | testVar = table.Compute( "count(key)" , "" ); |
059 | Response.Write( "筆數計算:" + testVar.ToString() + " |
063 | testVar = table.Compute( "count(key)" , "key='key1'" ); |
064 | Response.Write( "筆數計算,有filter:" + testVar.ToString() + " |
068 | testVar = table.Compute( "sum(value)" , "" ); |
069 | Response.Write( "欄位總和計算:" + testVar.ToString() + " |
073 | testVar = table.Compute( "avg(value)" , "" ); |
074 | Response.Write( "欄位平均計算:" + testVar.ToString() + " |
078 | testVar = table.Compute( "min(value)" , "" ); |
079 | Response.Write( "找出最小值:" + testVar.ToString() + " |
083 | testVar = table.Compute( "max(value)" , "" ); |
084 | Response.Write( "找出最大值:" + testVar.ToString() + " |
088 | testVar = table.Compute( "StDev(value)" , "" ); |
089 | Response.Write( "統計標準偏差:" + testVar.ToString() + " |
090 | "); // result = 158.113883008419; |
093 | testVar = table.Compute( "Var(value)" , "" ); |
094 | Response.Write( "統計方差:" + testVar.ToString() + " |
095 | "); // result = 25000; |
098 | testVar = table.Compute( "max(value) / sum(value)" , "" ); |
099 | Response.Write( "複雜計算:" + testVar.ToString() + " |
102 | /****************************************** |
103 | * 欄位計算 DataColumn.Expression 屬性 |
105 | * 運算式,用來計算資料行的值或建立彙總資料行。運算式的傳回型別是由資料行的 DataType 所判斷。 |
106 | * ****************************************/ |
108 | DataColumn column = new DataColumn( "exp1" , typeof ( float )); |
109 | table.Columns.Add(column); |
112 | column.Expression = "value*2" ; |
113 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
114 | Response.Write( "乘法計算:" + testVar.ToString() + " |
118 | column.Expression = "len(key)" ; |
119 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
120 | Response.Write( "字串長度計算函數:" + testVar.ToString() + " |
124 | column.Expression = "len(' '+key+' ') " ; |
125 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
126 | Response.Write( "含空白字串長度計算函數:" + testVar.ToString() + " |
130 | column.Expression = "len(trim(' '+key+' '))" ; |
131 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
132 | Response.Write( "去空白後字串長度計算函數:" + testVar.ToString() + " |
135 | // 切字串函數 (SQL的Substring起始Index為1) |
136 | column.Expression = "substring(key,4,len(key)-3)" ; |
137 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
138 | Response.Write( "切字串函數:" + testVar.ToString() + " |
142 | column.Expression = "convert(substring(key,4,len(key)-3),'System.Int32')*1.6" ; |
143 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
144 | Response.Write( "資料型別轉換:" + testVar.ToString() + " |
148 | column.Expression = "isnull(value,10)" ; |
149 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
150 | Response.Write( "SQL-ISNULL:" + testVar.ToString() + " |
154 | column.Expression = "iif(value>5,1000,2000)" ; |
155 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
156 | Response.Write( "CASE運算式" + testVar.ToString() + " |
160 | column.Expression = "iif(key like '%1',1000,2000)" ; |
161 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
162 | Response.Write( "LINK運算式:" + testVar.ToString() + " |
166 | column.Expression = "iif(key not in('key1'),1000,2000)" ; |
167 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
168 | Response.Write( "IN運算式:" + testVar.ToString() + " |
172 | column.Expression = "iif(value>5,1000,iif(key like '%1',4000,2000))" ; |
173 | testVar = table.Select( "key='key1'" )[0][ "exp1" ]; |
174 | Response.Write( "多層三元運算:" + testVar.ToString() + " |
177 | /****************************************** |
178 | * 資料查詢 DataTable.Select 方法 |
180 | * filterExpression:用來篩選資料列的準則。 |
181 | * sort:指定資料行和排序方向的字串。 |
182 | * ****************************************/ |
187 | testVar = table.Select( "key='key0'" )[0][ "key" ].ToString(); |
188 | Response.Write( "基本查詢:" + testVar.ToString() + " |
192 | testVar = table.Select( "key='key1' and value=100" )[0][ "key" ].ToString(); |
193 | Response.Write( "多條件查詢:" + testVar.ToString() + " |
197 | string nowDate = DateTime.Now.ToString( "#yyyy-MM-dd#" ); |
198 | testVar = table.Select( "date=" + nowDate + "" )[0][ "key" ].ToString(); |
199 | Response.Write( "日期查詢:" + testVar.ToString() + " |
注意事項
- 當使用 DataTable.Select("條件") 時,當中的條件如為字串則需要加單引號,如數字則不用
- 當使用 DataTable.Select("條燕") 時,當中如為查詢日期時間,則日期時間前後要加#字號
參考資料
DataTable 方法
jinjazz
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。