Tuesday, August 5, 2008

.NET DataTable Select Experssions

Sometimes I like to get raw data from a database table and select information from it through .NET code. I use the SELECT() method and pass it an expression. The problem is I never remember what types of expressions are valid. So I am listing them here as I come across them. One thing to remeber. If your column names have spaces, use brackets around the column name.

*Column name with spaces
string filter = "[job grade] = '" + jobGradeRowsToUpDate[j]["JobGrade"].ToString() + "'";

* NOT IN (item 1,item 2,...)
DataRow[] jobGradeRowsToDelete = currentJobGradeData.Select("jobgrade NOT IN (" + newJobGradeID + ")");

* not equal <>
DataRow[] jobGradeRowsToDelete = currentJobGradeData.Select("jobgrade <> '1'");

Other
Expression Operators
The list of supported operators is somewhat small:

Math Operators:

+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus, the remainder after division)
String Operator:

+ (concatenation, joining strings)
Comparison Operators:

< (less than) > (greater than)
<= (less than or equal to) >= (greater than or equal to)
<> (not equal to)
= (equal)
IN (compare to a list of items)
LIKE (pattern matching)
Scalar Functions:

Convert (change from one data type to another)
Len (return the length of the string)
IsNull (test for null)
IIf (Immediate If)
Trim (trim white space from string)
Substring (return a part of a string)
Aggregation Functions:

Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Standard deviation)
Var (Statistical variance)

**Source: http://home.hot.rr.com/graye/Articles/ADO_Expressions.htm

4 comments:

Emre said...

Thanx Brother you saved my life, i was searching for the not equal to expression.. good work!

Unknown said...

Thanks dude!

Was trying NOT EQUAL rather than <>, doh!

Anonymous said...

viagra jokes female use of viagra no prescription viagra viagra for cheap viagra sample viagra canada viagra sample viagra logo viagra suppliers in the uk viagra uk cheap purchase buy viagra cialis sublingual viagra free trial of viagra viagra results

Anonymous said...

[url=http://forums.bleachexile.com/member.php?u=57438]digital outdoor camera [/url] and
[url=http://forums.bleachexile.com/member.php?u=57532]chest pain
arthritis pain
leg pain
muscle relaxer
hip pain
[/url]

[url=http://kinopoisk.sms-jet.ru/sitemap.html]скачать фильмы[/url] скачать фильмы

digital camera model and
joint pain
knee pain
back pain relief
muscle relaxer
chronic pain