DataColumn.Expression DataColumn.Expression DataColumn.Expression DataColumn.Expression Property

定義

取得或設定運算式,用來篩選資料列、計算資料行中的值或建立彙總資料行。Gets or sets the expression used to filter rows, calculate the values in a column, or create an aggregate column.

public:
 property System::String ^ Expression { System::String ^ get(); void set(System::String ^ value); };
[System.Data.DataSysDescription("DataColumnExpressionDescr")]
public string Expression { get; set; }
member this.Expression : string with get, set
Public Property Expression As String

屬性值

運算式,用來計算資料行的值或建立彙總資料行。An expression to calculate the value of a column, or create an aggregate column. 運算式的傳回型別是由資料行的 DataType 所判斷。The return type of an expression is determined by the DataType of the column.

例外狀況

AutoIncrementUnique 屬性是設定為 trueThe AutoIncrement or Unique property is set to true.

在使用 CONVERT 函式時,運算式評估為字串,但是字串不包含可以轉變為型別參數的表示。When you are using the CONVERT function, the expression evaluates to a string, but the string does not contain a representation that can be converted to the type parameter.

在使用 CONVERT 函式時,不可能進行所要求的轉型。When you are using the CONVERT function, the requested cast is not possible. 請參閱下面段落中的型別轉換函式 (Conversion Function),取得有關可能轉型的詳細資訊。See the Conversion function in the following section for detailed information about possible casts.

在使用 SUBSTRING 函式時,起始引數超出範圍。When you use the SUBSTRING function, the start argument is out of range.

-或者--Or-

在使用 SUBSTRING 函式時,長度引數超出範圍。When you use the SUBSTRING function, the length argument is out of range.

在使用 LEN 函式或 TRIM 函式時,運算式不會評估為字串。When you use the LEN function or the TRIM function, the expression does not evaluate to a string. 這包括評估為 Char 的運算式。This includes expressions that evaluate to Char.

範例

下列範例會在中DataTable建立三個數據行。The following example creates three columns in a DataTable. 第二個和第三個數據行包含運算式;第二個會使用可變稅率來計算稅金,而第三個會將計算的結果新增至第一個資料行的值。The second and third columns contain expressions; the second calculates tax using a variable tax rate, and the third adds the result of the calculation to the value of the first column. 產生的資料表會顯示在DataGrid控制項中。The resulting table is displayed in a DataGrid control.

   private void CalcColumns()
   {
       DataTable table = new DataTable ();

       // Create the first column.
       DataColumn priceColumn = new DataColumn();
       priceColumn.DataType = System.Type.GetType("System.Decimal");
       priceColumn.ColumnName = "price";
       priceColumn.DefaultValue = 50;
        
       // Create the second, calculated, column.
       DataColumn taxColumn = new DataColumn();
       taxColumn.DataType = System.Type.GetType("System.Decimal");
       taxColumn.ColumnName = "tax";
       taxColumn.Expression = "price * 0.0862";
        
       // Create third column.
       DataColumn totalColumn = new DataColumn();
       totalColumn.DataType = System.Type.GetType("System.Decimal");
       totalColumn.ColumnName = "total";
       totalColumn.Expression = "price + tax";
   
       // Add columns to DataTable.
       table.Columns.Add(priceColumn);
       table.Columns.Add(taxColumn);
       table.Columns.Add(totalColumn);

       DataRow row = table.NewRow();
       table.Rows.Add(row);
       DataView view = new DataView(table);
       dataGrid1.DataSource = view;
   }
Private Sub CalcColumns()
     Dim rate As Single = .0862
     Dim table As New DataTable()
 
     ' Create the first column.
     Dim priceColumn As New DataColumn()
     With priceColumn
         .DataType = System.Type.GetType("System.Decimal")
         .ColumnName = "price"
         .DefaultValue = 50
     End With
     
     ' Create the second, calculated, column.
     Dim taxColumn As New DataColumn()
     With taxColumn
         .DataType = System.Type.GetType("System.Decimal")
         .ColumnName = "tax"
         .Expression = "price * 0.0862"
     End With
     
    ' Create third column
     Dim totalColumn As New DataColumn()
     With totalColumn
         .DataType = System.Type.GetType("System.Decimal")
         .ColumnName = "total"
         .Expression = "price + tax"
     End With
 
     ' Add columns to DataTable
     With table.Columns
         .Add(priceColumn)
         .Add(taxColumn)
         .Add(totalColumn)
     End With
    
     Dim row As DataRow= table.NewRow
     table.Rows.Add(row)
     Dim view As New DataView
     view.Table = table
     DataGrid1.DataSource = view
 End Sub

備註

Expression屬性的其中一種用法是建立計算結果欄。One use of the Expression property is to create calculated columns. 例如,若要計算稅額,單位價格會乘以特定地區的稅率。For example, to calculate a tax value, the unit price is multiplied by a tax rate of a specific region. 由於稅率會因區域而異,因此不可能在資料行中放入單稅率;而是使用Expression屬性來計算值,如下一節的 Visual Basic 程式碼所示:Because tax rates vary from region to region, it would be impossible to put a single tax rate in a column; instead, the value is calculated using the Expression property, as shown in the Visual Basic code in the following section:

DataSet1.Tables("Products").Columns("tax").Expression = "UnitPrice * 0.086"DataSet1.Tables("Products").Columns("tax").Expression = "UnitPrice * 0.086"

第二種用法是建立匯總資料行。A second use is to create an aggregate column. 與計算值類似,匯總會根據中DataTable的一組完整資料列來執行作業。Similar to a calculated value, an aggregate performs an operation based on the complete set of rows in the DataTable. 簡單的範例是計算集合中傳回的資料列數目。A simple example is to count the number of rows returned in the set. 這是您用來計算特定銷售人員完成的交易數目的方法,如下列 Visual Basic 程式碼所示:This is the method you would use to count the number of transactions completed by a particular salesperson, as shown in this Visual Basic code:

DataSet1.Tables("Orders").Columns("OrderCount").Expression = "Count(OrderID)"  

運算式語法Expression Syntax

當您建立運算式時,請使用ColumnName屬性來參考資料行。When you create an expression, use the ColumnName property to refer to columns. 例如,如果一個資料ColumnName行的是「單價」,另一個是「數量」,則運算式會如下所示:For example, if the ColumnName for one column is "UnitPrice", and another "Quantity", the expression would be as follows:

"UnitPrice * Quantity""UnitPrice * Quantity"

注意

如果在運算式中使用資料行,則運算式會被視為具有該資料行的相依性。If a column is used in an expression, then the expression is said to have a dependency on that column. 如果已重新命名或移除相依的資料行,則不會擲回任何例外狀況。If a dependent column is renamed or removed, no exception is thrown. 當存取立即中斷的運算式資料行時,就會擲回例外狀況(exception)。An exception will be thrown when the now-broken expression column is accessed.

當您建立篩選準則的運算式時,請使用單引號來括住字串:When you create an expression for a filter, enclose strings with single quotation marks:

"LastName = 'Jones'""LastName = 'Jones'"

如果資料行名稱包含任何非英數位元,或開頭為數字或符合(案例-區分大小寫)下列任何保留字,則需要特殊處理,如下列段落中所述。If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.

And

介於Between

子系Child

FalseFalse

InIn

Is

LikeLike

Not

NullNull

Or

父代Parent

TrueTrue

如果資料行名稱符合上述其中一個條件,則必須以方括弧或 "'" (抑音符號)引號括住。If a column name satisfies one of the above conditions, it must be wrapped in either square brackets or the "`" (grave accent) quotes. 例如,若要在運算式中使用名為 "Column #" 的資料行,您可以撰寫 "[Column #]":For example, to use a column named "Column#" in an expression, you would write either "[Column#]":

總計 * [Column #]Total * [Column#]

或「資料行 #」:`or "`Column#`":

總計 * `資料行 # 'Total * `Column#`

如果資料行名稱是以方括弧括住,則必須在其中加上\反斜線("\")字元,以將它的任何 '] ' 和 ' ' 字元(但不是任何其他字元)加以轉義。If the column name is enclosed in square brackets then any ']' and '\' characters (but not any other characters) in it must be escaped by prepending them with the backslash ("\") character. 如果資料行名稱是以抑音符號括住,則它不能包含任何以抑音符號的重音字元。If the column name is enclosed in grave accent characters then it must not contain any grave accent characters in it. 例如,將會寫入名為 "column [\]" 的資料行:For example, a column named "Column[]\" would be written:

Total * [Column [\]\ \]Total * [Column[\]\\]

or

Total * `Column []\</span><span class="sxs-lookup"><span data-stu-id="10ffd-310">Total \* \Column[]\`

使用者定義的值User-Defined Values

使用者定義的值可以在運算式內用來與資料行值進行比較。User-defined values may be used within expressions to be compared with column values. 字串值應該包含在單引號中(而且字串值中的每個單引號字元都必須以另一個單引號字元預先加上引號)。String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single quotation character). 日期值應該包含在以資料提供者為基礎的井字型大小(#)或單引號(')內。Date values should be enclosed within pound signs (#) or single quotes (') based on the data provider. 數值會允許十進位和科學標記法。Decimals and scientific notation are permissible for numeric values. 例如:For example:

"FirstName = 'John'""FirstName = 'John'"

「價格 < = 50.00」"Price <= 50.00"

「生日 < #1/31/82 #」"Birthdate < #1/31/82#"

對於包含列舉值的資料行,將值轉換成整數資料類型。For columns that contain enumeration values, cast the value to an integer data type. 例如:For example:

"EnumColumn = 5""EnumColumn = 5"

剖析常值運算式Parsing Literal Expressions

所有的常值運算式都必須以不因文化特性而異的地區設定來表示。All literal expressions must be expressed in the invariant culture locale. DataSet剖析並轉換常值運算式時,它一律會使用不因文化特性而異,而不是目前的文化特性。When DataSet parses and converts literal expressions, it always uses the invariant culture, not the current culture.

當值周圍有單引號時,就會識別字串常值。String literals are identified when there are single quotes surrounding the value. 例如:For example:

John'John'

Boolean常值為 true 和 false;它們不會以引號括住在運算式中。Boolean literals are true and false; they are not quoted in expressions.

Integer常值 [+-]?[0-9] + 會被視為System.Int32System.Int64System.DoubleInteger literals [+-]?[0-9]+ are treated as System.Int32, System.Int64 or System.Double. System.Double視數位的大小而定,可能會失去精確度。System.Double can lose precision depending on how large the number is. 例如,如果常值中的數位是2147483650, DataSet則會先嘗試將數位剖析Int32為。For example, if the number in the literal is 2147483650, DataSet will first attempt to parse the number as an Int32. 這不會成功,因為數位太大。This will not succeed because the number is too large. 在此情況DataSetInt64,會將數位剖析為,這會成功。In this case DataSet will parse the number as an Int64, which will succeed. 如果常值是大於 Int64 最大值的數位, DataSet則會使用Double來剖析常值。If the literal was a number larger than the maximum value of an Int64, DataSet will parse the literal using Double.

使用科學記號標記法的實數常值(例如 4.42372 E-30) System.Double會使用進行剖析。Real literals using scientific notation, such as 4.42372E-30, are parsed using System.Double.

不使用科學記號標記法但具有小數點的實數常值,會System.Decimal被視為。Real literals without scientific notation, but with a decimal point, are treated as System.Decimal. 如果數目超過支援System.Decimal的最大或最小值,則會將它剖析System.Double為。If the number exceeds the maximum or minimum values supported by System.Decimal, then it is parsed as a System.Double. 例如:For example:

142526.144524 會轉換成Decimal142526.144524 will be converted to a Decimal.

345262.78036719560925667 會被視為Double345262.78036719560925667 will be treated as a Double.

運算子Operators

允許使用布林值 AND、OR 和 NOT 運算子進行串連。Concatenation is allowed using Boolean AND, OR, and NOT operators. 您可以使用括弧來分組子句和強制優先順序。You can use parentheses to group clauses and force precedence. AND 運算子的優先順序高於其他運算子。The AND operator has precedence over other operators. 例如:For example:

(LastName = ' Smith ' 或 LastName = ' 張 ')和 FirstName = ' John '(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

當您建立比較運算式時,允許下列運算子:When you create comparison expressions, the following operators are allowed:

<

>

<=

>=

<>

=

ININ

LIKELIKE

運算式中也支援下列算術運算子:The following arithmetic operators are also supported in expressions:

+額外+ (addition)

- (減法)- (subtraction)

*倍增* (multiplication)

/(除)/ (division)

% (模數)% (modulus)

字串運算子String Operators

若要串連字號串,請使用 + 字元。To concatenate a string, use the + character. DataSet類別的CaseSensitive屬性值會決定字串比較是否區分大小寫。The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive. 不過,您可以使用CaseSensitive DataTable類別的屬性來覆寫該值。However, you can override that value with the CaseSensitive property of the DataTable class.

萬用字元Wildcard Characters

* 和% 都可以在類似的比較中交替使用萬用字元。Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. 如果 LIKE 子句中的字串包含 * 或%,則這些字元應該以方括弧([])括住。If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). 如果括弧在子句中,每個括弧字元應該以方括弧括住(例如 [[] 或 []])。If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). 在模式的開頭和結尾,或在模式的結尾或模式開頭,都允許萬用字元。A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. 例如:For example:

"腳本,例如 ' **產品 '""ItemName LIKE '*product*'"

"腳本,例如 ' * 產品 '""ItemName LIKE '*product'"

「類似 ' 產品 * ' 的 ' 1 '"ItemName LIKE 'product*'"

字串中間不允許使用萬用字元。Wildcard characters are not allowed in the middle of a string. 例如,不允許 ' te * xt '。For example, 'te*xt' is not allowed.

父/子系關聯參考Parent/Child Relation Referencing

在運算式中,可以藉由在資料行名稱前面加Parent上來參考父資料表。A parent table may be referenced in an expression by prepending the column name with Parent. 例如, Parent.Price會參考名為Price的父資料表的資料行。For example, the Parent.Price references the parent table's column named Price.

當子系有一個以上的父資料列時,請使用父系(RelationName)。ColumnName.When a child has more than one parent row, use Parent(RelationName).ColumnName. 例如,父系(RelationName)。價格會透過關聯性參考名為 Price 的父資料表的資料行。For example, the Parent(RelationName).Price references the parent table's column named Price via the relation.

在運算式中,您可以使用來預先填入資料行名稱, Child來參考子資料工作表中的資料行。A column in a child table may be referenced in an expression by prepending the column name with Child. 不過,因為子關聯性可能會傳回多個資料列,所以您必須在彙總函式中包含子資料行的參考。However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. 例如, Sum(Child.Price)會傳回子資料工作表中名為Price之資料行的總和。For example, Sum(Child.Price) would return the sum of the column named Price in the child table.

如果資料表有一個以上的子系,則語法為: Child(RelationName)If a table has more than one child, the syntax is: Child(RelationName). 例如,如果資料表有兩個名Customers為和OrdersDataRelation子資料工作表,而且物件名為Customers2Orders,則參考會如下所示:For example, if a table has two child tables named Customers and Orders, and the DataRelation object is named Customers2Orders, the reference would be as follows:

Avg(Child(Customers2Orders).Quantity)Avg(Child(Customers2Orders).Quantity)

彙總Aggregates

支援下列匯總類型:The following aggregate types are supported:

總和(加總)Sum (Sum)

平均(平均)Avg (Average)

最小值(最小值)Min (Minimum)

最大值(最大)Max (Maximum)

計數(計數)Count (Count)

StDev (統計標準差)StDev (Statistical standard deviation)

Var (統計變異數)。Var (Statistical variance).

匯總通常會沿著關聯性執行。Aggregates are ordinarily performed along relationships. 使用稍早所列的其中一個函數和子資料工作表資料行建立匯總運算式,如稍早所討論的父/子關聯性參考中所述。Create an aggregate expression by using one of the functions listed earlier and a child table column as detailed in Parent/Child Relation Referencing that was discussed earlier. 例如:For example:

Avg(Child.Price)Avg(Child.Price)

Avg(Child(Orders2Details).Price)Avg(Child(Orders2Details).Price)

匯總也可以在單一資料表上執行。An aggregate can also be performed on a single table. 例如,若要在名為 "Price" 的資料行中建立數位的摘要:For example, to create a summary of figures in a column named "Price":

Sum(Price)Sum(Price)

注意

如果您使用單一資料表來建立匯總,則不會有任何分組功能。If you use a single table to create an aggregate, there would be no group-by functionality. 相反地,所有資料列會在資料行中顯示相同的值。Instead, all rows would display the same value in the column.

如果資料表沒有資料列,彙總函式將會null傳回。If a table has no rows, the aggregate functions will return null.

您一律可以檢查DataType資料行的屬性來判斷資料類型。Data types can always be determined by examining the DataType property of a column. 您也可以使用 Convert 函數轉換資料類型,如下一節所示。You can also convert data types using the Convert function, shown in the following section.

匯總只能套用至單一資料行,而且不能在匯總內使用其他運算式。An aggregate can only be applied to a single column and no other expressions can be used inside the aggregate.

函式Functions

也支援下列函數:The following functions are also supported:

CONVERT

說明Description 將特定運算式轉換成指定的 .NET Framework 類型。Converts particular expression to a specified .NET Framework Type.
語法Syntax Convert (expressiontypeConvert(expression, type)
引數Arguments expression--要轉換的運算式。expression -- The expression to convert.

type--值將轉換成的 .NET Framework 類型。type -- The .NET Framework type to which the value will be converted.

Example: myDataColumn.Expression="Convert(total, 'System.Int32')"Example: myDataColumn.Expression="Convert(total, 'System.Int32')"

所有的轉換都是有效的,但Boolean有下列例外狀況:可以強制SByte轉型為Int64 Byte Int32 UInt16Int16UInt32UInt64、、、、、 String而且本身只有。All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char只能從Int32UInt32String和本身強制轉型為和。Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime只能強制轉型為和String來源本身。DateTime can be coerced to and from String and itself only. TimeSpan只能強制轉型為和String來源本身。TimeSpan can be coerced to and from String and itself only.

LEN

說明Description 取得字串的長度Gets the length of a string
語法Syntax LEN(expression)LEN(expression)
引數Arguments expression--要評估的字串。expression -- The string to be evaluated.

Example: myDataColumn.Expression="Len(ItemName)"Example: myDataColumn.Expression="Len(ItemName)"

ISNULL

說明Description 檢查運算式,並傳回已檢查的運算式或取代值。Checks an expression and either returns the checked expression or a replacement value.
語法Syntax ISNULL (expressionreplacementvalueISNULL(expression, replacementvalue)
引數Arguments expression--要檢查的運算式。expression -- The expression to check.

replacementvalue--如果 expression 為nullreplacementvalue則傳回。replacementvalue -- If expression is null, replacementvalue is returned.

Example: myDataColumn.Expression="IsNull(price, -1)"Example: myDataColumn.Expression="IsNull(price, -1)"

IIF

說明Description 根據邏輯運算式的結果,取得兩個值的其中一個。Gets one of two values depending on the result of a logical expression.
語法Syntax IIF (exprtruepartfalsepartIIF(expr, truepart, falsepart)
引數Arguments expr--要評估的運算式。expr -- The expression to evaluate.

truepart--運算式為 true 時要傳回的值。truepart -- The value to return if the expression is true.

falsepart--運算式為 false 時要傳回的值。falsepart -- The value to return if the expression is false.

Example: myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')Example: myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')

TRIM

說明Description 移除所有開頭和尾端的空白字元,例如 \r,\n,\t,' 'Removes all leading and trailing blank characters like \r, \n, \t, ' '
語法Syntax TRIM(expression)TRIM(expression)
引數Arguments expression--要修剪的運算式。expression -- The expression to trim.

SUBSTRING

說明Description 從字串中的指定點開始,取得指定長度的子字串。Gets a sub-string of a specified length, starting at a specified point in the string.
語法Syntax SUBSTRING (expressionstartlengthSUBSTRING(expression, start, length)
引數Arguments expression--子字串的來源字串。expression -- The source string for the substring.

start--整數,指定子字串的開始位置。start -- Integer that specifies where the substring starts.

length--指定子字串長度的整數。length -- Integer that specifies the length of the substring.

範例: myDataColumn = "SUBSTRING (phone,7,8)"Example: myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"

注意

您可以將Expression屬性指派為 null 值或空字串,藉以重設它。You can reset the Expression property by assigning it a null value or empty string. 如果在 [運算式] 資料行上設定預設值,則在重設Expression屬性之後,所有先前填入的資料列都會被指派預設值。If a default value is set on the expression column, all previously filled rows are assigned the default value after the Expression property is reset.

適用於

另請參閱