DataColumn.Expression 属性

定义

获取或设置表达式,用于筛选行、计算列中的值或创建聚合列。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); };
public string Expression { get; set; }
[System.Data.DataSysDescription("DataColumnExpressionDescr")]
public string Expression { get; set; }
member this.Expression : string with get, set
[<System.Data.DataSysDescription("DataColumnExpressionDescr")>]
member this.Expression : string with get, set
Public Property Expression As String

属性值

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 函数。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.

示例

下面的示例在中创建三列 DataTableThe 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 ( "Products" ) 。列 ( "税务" ) 。Expression = "单价 * 0.086"DataSet1.Tables("Products").Columns("tax").Expression = "UnitPrice * 0.086"

另一种用途是创建聚合列。A second use is to create an aggregate column. 与计算所得的值类似,聚合基于中的整行集执行操作 DataTableSimilar 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. 当访问现在断开的表达式列时,将引发异常。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 =""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

错误False

In

IsIs

LikeLike

NotNot

NullNull

OrOr

ParentParent

正确True

如果列名称满足上述条件之一,则必须将其包装在方括号中,或 "" " (重音符) 引号。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#]":

总计 * [列号]Total * [Column#]

或 " ` 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="4d6e6-309">Total \* \Column[]\`

User-Defined 值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'"

"Price <= 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'

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 将首先尝试将数字分析为 Int32For 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. 在这种情况下 DataSet ,会将数字分析为 Int64 ,这将会成功。In this case DataSet will parse the number as an Int64, which will succeed. 如果文本是大于 Int64 的最大值的数字, DataSet 将使用分析文本 DoubleIf 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.DoubleReal literals using scientific notation, such as 4.42372E-30, are parsed using System.Double.

不带科学记数法但带有小数点的真实文本将被视为 System.DecimalReal literals without scientific notation, but with a decimal point, are treated as System.Decimal. 如果数字超过了支持的最大值或最小值 System.Decimal ,则将其分析为 System.DoubleIf 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. 类的属性的值 CaseSensitive DataSet 确定字符串比较是否区分大小写。The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive. 但是,您可以用类的属性重写该值 CaseSensitive DataTableHowever, you can override that value with the CaseSensitive property of the DataTable class.

通配符Wildcard Characters

在 LIKE 比较中,* 和% 可互换使用通配符。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:

"类1,如" * 产品 * ""ItemName LIKE '*product*'"

"类1,如" * 产品 ""ItemName LIKE '*product'"

"类1,如" product * ""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

通过在列名称前面追加,可以在表达式中引用父表 ParentA parent table may be referenced in an expression by prepending the column name with Parent. 例如, Parent.Price 引用名为的父表的列 PriceFor example, the Parent.Price references the parent table's column named Price.

当子级具有多个父行时,请使用 Parent (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.

通过在列名称前面追加,可以在表达式中引用子表中的列 ChildA 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) 将返回子表中名为的列的总和 PriceFor 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). 例如,如果表具有两个子表,分别名为 CustomersOrders ,并且该 DataRelation 对象名为 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 (子 (Customers2Orders) 。数量) Avg(Child(Customers2Orders).Quantity)

聚合Aggregates

支持下列聚合类型:The following aggregate types are supported:

Sum (求和) Sum (Sum)

平均 (平均) Avg (Average)

最小 (最小) Min (Minimum)

最大 () Max (Maximum)

Count(计数)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 (子 (Orders2Details) 。价格) 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.

如果表中没有行,聚合函数将返回 nullIf a table has no rows, the aggregate functions will return null.

始终可以通过检查列的属性来确定数据类型 DataTypeData 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 转换 (expressiontype) Convert(expression, type)
自变量Arguments expression -要转换的表达式。expression -- The expression to convert.

type 要将值转换到--. 网络类型。type -- .NET type to which the value will be converted.

示例: myDataColumn = "转换 (总计" ) "Example: myDataColumn.Expression="Convert(total, 'System.Int32')"

所有转换都有效,但有以下例外:只能在、、、、、、、 Boolean Byte SByte Int16 Int32 Int64 UInt16 UInt32 UInt64 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 只能在 Int32UInt32 、和自身之间强制转换 StringChar can be coerced to and from Int32, UInt32, String, and itself only. DateTime 仅可强制转换为和 StringDateTime can be coerced to and from String and itself only. TimeSpan 仅可强制转换为和 StringTimeSpan 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.

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

ISNULL

说明Description 检查表达式并返回检查的表达式或替换值。Checks an expression and either returns the checked expression or a replacement value.
语法Syntax ISNULL (expressionreplacementvalue) ISNULL(expression, replacementvalue)
自变量Arguments expression -要检查的表达式。expression -- The expression to check.

replacementvalue --如果 expression 为 nullreplacementvalue 则返回。replacementvalue -- If expression is null, replacementvalue is returned.

示例: myDataColumn = "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 (exprtruepart falsepart) IIF(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.

示例: myDataColumn = "IIF (总计>1000、" 昂贵 "、" 尊敬 ") 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 剪裁 (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 (expressionstart length) SUBSTRING(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 通过为属性分配一个空值或空字符串来重置该属性。You can reset the Expression property by assigning it a null value or empty string. 如果在 "表达式" 列中设置了默认值,则在重置属性后,将为所有以前填充的行分配默认值 ExpressionIf a default value is set on the expression column, all previously filled rows are assigned the default value after the Expression property is reset.

适用于