Wednesday, December 20, 2006
Tuesday, December 19, 2006
Compute dataset syntax and sample
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
When you create an expression, use the ColumnName property to refer to columns. For example, if the ColumnName for one column is "UnitPrice", and another "Quantity", the expression would be as follows:
"UnitPrice * Quantity"
Note:
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 = 'Jones'"
The following characters are special characters and must be escaped, as explained here, if they are used in a column name:
\n (newline)
\t (tab)
\r (carriage return)
~
(
)
#
\
/
=
>
<
+
-
*
%
&
|
^
'
"
[
]
If a column name contains one of the previous characters, the name must be wrapped in brackets. For example to use a column named "Column#" in an expression, you would write "[Column#]":
Total * [Column#]
Because brackets are special characters, you must use a slash ("\") to escape the bracket, if it is part of a column name. For example, a column named "Column[]" would be written:
Total * [Column[\]]
(Only the second bracket must be escaped.)
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. 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'"
"Price <= 50.00"
"Birthdate < #1/31/82#"
For columns that contain enumeration values, cast the value to an integer data type. For example:
"EnumColumn = 5"
OPERATORS
Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
When you create comparison expressions, the following operators are allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in expressions:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
STRING OPERATORS
To concatenate a string, use the + character. The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive. 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. If the string in a LIKE clause contains a * or %, those characters should be escaped in brackets ([]). If a bracket is in the clause, the bracket characters should be escaped 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'"
"ItemName LIKE 'product*'"
Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
PARENT/CHILD RELATION REFERENCING
A parent table may be referenced in an expression by prepending the column name with Parent. For example, the Parent.Price references the parent table's column named Price.
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. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.
If a table has more than one child, the syntax is: Child(RelationName). 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)
AGGREGATES
The following aggregate types are supported:
Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Statistical standard deviation)
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(Orders2Details).Price)
An aggregate can also be performed on a single table. For example, to create a summary of figures in a column named "Price":
Sum(Price)
Note:
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.
If a table has no rows, the aggregate functions will return a null reference (Nothing in Visual Basic).
Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function, shown in the following section.
FUNCTIONS
The following functions are also supported:
CONVERT
Description
Converts particular expression to a specified .NET Framework Type.
Syntax
Convert(expression, type)
Arguments
expression -- The expression to convert.
type -- The .NET Framework type to which the value will be converted.
Example: myDataColumn.Expression="Convert(total, 'System.Int32')"
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 can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.
LEN
Description
Gets the length of a string
Syntax
LEN(expression)
Arguments
expression -- The string to be evaluated.
Example: myDataColumn.Expression="Len(ItemName)"
ISNULL
Description
Checks an expression and either returns the checked expression or a replacement value.
Syntax
ISNULL(expression, replacementvalue)
Arguments
expression -- The expression to check.
replacementvalue -- If expression is a null reference (Nothing in Visual Basic), replacementvalue is returned.
Example: myDataColumn.Expression="IsNull(price, -1)"
IIF
Description
Gets one of two values depending on the result of a logical expression.
Syntax
IIF(expr, truepart, falsepart)
Arguments
expr -- The expression to evaluate.
truepart -- The value to return if the expression is true.
falsepart -- The value to return if the expression is false.
Example: myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')
TRIM
Description
Removes all leading and trailing blank characters like \r, \n, \t, ' '
Syntax
TRIM(expression)
Arguments
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(expression, start, length)
Arguments
expression -- The source string for the substring.
start -- Integer that specifies where the substring starts.
length -- Integer that specifies the length of the substring.
Example: myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
When you create an expression, use the ColumnName property to refer to columns. For example, if the ColumnName for one column is "UnitPrice", and another "Quantity", the expression would be as follows:
"UnitPrice * Quantity"
Note:
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 = 'Jones'"
The following characters are special characters and must be escaped, as explained here, if they are used in a column name:
\n (newline)
\t (tab)
\r (carriage return)
~
(
)
#
\
/
=
>
<
+
-
*
%
&
|
^
'
"
[
]
If a column name contains one of the previous characters, the name must be wrapped in brackets. For example to use a column named "Column#" in an expression, you would write "[Column#]":
Total * [Column#]
Because brackets are special characters, you must use a slash ("\") to escape the bracket, if it is part of a column name. For example, a column named "Column[]" would be written:
Total * [Column[\]]
(Only the second bracket must be escaped.)
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. 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'"
"Price <= 50.00"
"Birthdate < #1/31/82#"
For columns that contain enumeration values, cast the value to an integer data type. For example:
"EnumColumn = 5"
OPERATORS
Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
When you create comparison expressions, the following operators are allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in expressions:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
STRING OPERATORS
To concatenate a string, use the + character. The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive. 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. If the string in a LIKE clause contains a * or %, those characters should be escaped in brackets ([]). If a bracket is in the clause, the bracket characters should be escaped 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'"
"ItemName LIKE 'product*'"
Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
PARENT/CHILD RELATION REFERENCING
A parent table may be referenced in an expression by prepending the column name with Parent. For example, the Parent.Price references the parent table's column named Price.
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. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.
If a table has more than one child, the syntax is: Child(RelationName). 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)
AGGREGATES
The following aggregate types are supported:
Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Statistical standard deviation)
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(Orders2Details).Price)
An aggregate can also be performed on a single table. For example, to create a summary of figures in a column named "Price":
Sum(Price)
Note:
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.
If a table has no rows, the aggregate functions will return a null reference (Nothing in Visual Basic).
Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function, shown in the following section.
FUNCTIONS
The following functions are also supported:
CONVERT
Description
Converts particular expression to a specified .NET Framework Type.
Syntax
Convert(expression, type)
Arguments
expression -- The expression to convert.
type -- The .NET Framework type to which the value will be converted.
Example: myDataColumn.Expression="Convert(total, 'System.Int32')"
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 can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.
LEN
Description
Gets the length of a string
Syntax
LEN(expression)
Arguments
expression -- The string to be evaluated.
Example: myDataColumn.Expression="Len(ItemName)"
ISNULL
Description
Checks an expression and either returns the checked expression or a replacement value.
Syntax
ISNULL(expression, replacementvalue)
Arguments
expression -- The expression to check.
replacementvalue -- If expression is a null reference (Nothing in Visual Basic), replacementvalue is returned.
Example: myDataColumn.Expression="IsNull(price, -1)"
IIF
Description
Gets one of two values depending on the result of a logical expression.
Syntax
IIF(expr, truepart, falsepart)
Arguments
expr -- The expression to evaluate.
truepart -- The value to return if the expression is true.
falsepart -- The value to return if the expression is false.
Example: myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')
TRIM
Description
Removes all leading and trailing blank characters like \r, \n, \t, ' '
Syntax
TRIM(expression)
Arguments
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(expression, start, length)
Arguments
expression -- The source string for the substring.
start -- Integer that specifies where the substring starts.
length -- Integer that specifies the length of the substring.
Example: myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
Handle Divide by zero error encountered in SQL Server
Divide by zero error encountered.
http://www.sql-server-helper.com/error-messages/msg-8134.aspx
http://www.sql-server-helper.com/error-messages/msg-8134.aspx
Monday, December 18, 2006
Wednesday, December 13, 2006
Sharepoint web part
Deploy web part
http://unboxedsolutions.com/sean/articles/356.aspx
http://www.devx.com/dotnet/Article/17518
http://www.intranetjournal.com/articles/200510/ij_10_10_05a.html
http://www.intranetjournal.com/articles/200511/ij_11_04_05a.html
http://www.ftponline.com/portals/microsoft/webpart/articles/rizzo1/
http://unboxedsolutions.com/sean/articles/356.aspx
http://www.devx.com/dotnet/Article/17518
http://www.intranetjournal.com/articles/200510/ij_10_10_05a.html
http://www.intranetjournal.com/articles/200511/ij_11_04_05a.html
http://www.ftponline.com/portals/microsoft/webpart/articles/rizzo1/
Tuesday, December 05, 2006
Monday, December 04, 2006
c# tips
Question Operator
ct.GroupByFieldNames=new string[]{(string)(qd.GroupByAttribute == null ? "" : qd.GroupByAttribute.Name)};
ct.GroupByFieldNames=new string[]{(string)(qd.GroupByAttribute == null ? "" : qd.GroupByAttribute.Name)};
Sunday, December 03, 2006
Subscribe to:
Posts (Atom)