Skip to main content

DataView Rowfilter With Like % in C#.Net


DataView RowFilter Syntax in C#,ASP.Net Example

This example describes syntax of DataView.RowFilter expression.
It shows how to correctly build expression string (without "SQL injection") using methods to escape values.
Column names
If a column name contains any of these special characters ~ ( ) # \ / = > < + – * % & | ^ ' " [ ],
you must enclose the column name within square brackets [ ].
If a column namecontains right bracket ] or backslash \, escape it with backslash (\] or \\).

[C#]
dataView.RowFilter = "id = 10″; // no special character in column name "id"
dataView.RowFilter = "$id = 10″; // no special character in column name "$id"
dataView.RowFilter = "[#id] = 10″; // special character "#" in column name "#id"
dataView.RowFilter = "[[id\]] = 10″; // special characters in column name "[id]"
Literals
String values are enclosed within single quotes ''.
If the string contains singlequote ', the quote must be doubled.

[C#]
dataView.RowFilter = "Name = 'John'" // string value
dataView.RowFilter = "Name = 'John "A"'" // string with single quotes "John 'A'"
dataView.RowFilter = String.Format("Name = '{0}'", "John 'A'".Replace("'", """));
Number values are not enclosed within any characters.
The values should be the same as is the result of int.ToString()
or float.ToString() method for invariant or English culture.

[C#]
dataView.RowFilter = "Year = 2008″ // integer value
dataView.RowFilter = "Price = 1199.9″ // float value
dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat,
"Price = {0}", 1199.9f);
Date values are enclosed within sharp characters # #.
The date format is the same as is the result of DateTime.ToString() method for invariant or Englishculture.

[C#]
dataView.RowFilter = "Date = #12/31/2008#" // date value (time is 00:00:00)
dataView.RowFilter = "Date = #2008-12-31#" // also this format is supported
dataView.RowFilter = "Date = #12/31/2008 16:44:58#" // date and time value
dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat,
"Date = #{0}#", new DateTime(2008, 12, 31, 16, 44, 58));
Alternatively you can enclose all values within single quotes ' '.
It means youcan use string values for numbers or date time values.
In this case the currentculture is used to convert the string to the specific value.

[C#]
dataView.RowFilter = "Date = '12/31/2008 16:44:58′" // if current culture is English
dataView.RowFilter = "Date = '31.12.2008 16:44:58′" // if current culture is German
dataView.RowFilter = "Price = '1199.90′" // if current culture is English
dataView.RowFilter = "Price = '1199,90′" // if current culture is German
Comparison operators
Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression.
You can use these operators = <> < <= >>=.
Note: String comparison is culture-sensitive,
it uses CultureInfo fromDataTable.Locale property of related table (dataView.Table.Locale).
If the properties not explicitly set, its default value is DataSet.
Locale (and its default valueis current system culture Thread.Current Thread.Current Culture).

[C#]
dataView.RowFilter = "Num = 10″ // number is equal to 10
dataView.RowFilter = "Date < #1/1/2008#" // date is less than 1/1/2008
dataView.RowFilter = "Name <> 'John'" // string is not equal to 'John'
dataView.RowFilter = "Name >= 'Jo'" // string comparison
Operator IN is used to include only values from the list.
You can use the operatorfor all data types, such as numbers or strings.

[C#]
dataView.RowFilter = "Id IN (1, 2, 3)" // integer values
dataView.RowFilter = "Price IN (1.0, 9.9, 11.5)" // float values
dataView.RowFilter = "Name IN ('John', 'Jim', 'Tom')" // string values
dataView.RowFilter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values
dataView.RowFilter = "Id NOT IN (1, 2, 3)" // values not from the list
Operator LIKE is used to include only values that match a pattern with wildcards.
Wildcard character is * or %, it can be at the beginning of a pattern '*value',
at the end 'value*', or at both '*value*'.
Note: Wildcard in the middle of apatern 'va*lue' is not allowed.

[C#]
dataView.RowFilter = "Name LIKE 'j*'" // values that start with 'j'
dataView.RowFilter = "Name LIKE '%jo%'" // values that contain 'jo'
dataView.RowFilter = "Name NOT LIKE 'j*'" // values that don't start with 'j'
If a pattern in a LIKE clause contains any of these special characters * % [ ],
those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].

[C#]
dataView.RowFilter = "Name LIKE '[*]*'" // values that starts with '*'
dataView.RowFilter = "Name LIKE '[[]*'" // values that starts with '['
The following method escapes a text value for usage in a LIKE clause.

[C#]
public static string EscapeLikeValue(string valueWithoutWildcards)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < valueWithoutWildcards.Length; i++)
{
char c = valueWithoutWildcards[i];
if (c == '*' || c == '%' || c == '[' || c == ']')
sb.Append("[").Append(c).Append("]");
else if (c == ""\")
sb.Append(""");
else
sb.Append(c);
}
return sb.ToString();
}

[C#]
// select all that starts with the value string (in this case with "*")
string value = "*";
// the dataView.RowFilter will be: "Name LIKE '[*]*'"
dataView.RowFilter = String.Format("Name LIKE '{0}*'", EscapeLikeValue(value));
Boolean operators
Boolean operators AND, OR and NOT are used to concatenate expressions.
Operator NOT has precedence over AND operator and it has precedence overOR operator.

[C#]
// operator AND has precedence over OR operator, parenthesis are needed
dataView.RowFilter = "City = 'Tokyo' AND (Age < 20 OR Age > 60)";
// following examples do the same
dataView.RowFilter = "City <> 'Tokyo' AND City <> 'Paris'";
dataView.RowFilter = "NOT City = 'Tokyo' AND NOT City = 'Paris'";
dataView.RowFilter = "NOT (City = 'Tokyo' OR City = 'Paris')";
dataView.RowFilter = "City NOT IN ('Tokyo', 'Paris')";
Arithmetic and string operators
Arithmetic operators are addition +, subtraction -, multiplication *,division / and modulus %.

[C#]
dataView.RowFilter = "MotherAge – Age < 20″; // people with young mother
dataView.RowFilter = "Age % 10 = 0″; // people with decennial birthday

There is also one string operator concatenation +.
Parent-Child Relation Referencing
A parent table can be referenced in an expression using parent column name withParent prefix.
A column in a child table can be referenced using child columnname with Child prefix.
The reference to the child column must be in an aggregate function because childrelationships may return multiple rows.
For example expression SUM(Child.Price)returns sum of all prices in child table related to the row in parent table.
If a table has more than one child relation, the prefix must contain relation name.

For example expression Child(OrdersToItemsRelation).Price references to columnPrice in child table using relation named OrdersToItemsRelation.

Aggregate Functions

There are supported following aggregate functions SUM, COUNT, MIN, MAX,AVG (average), STDEV (statistical standard deviation) and VAR (statistical variance).
This example shows aggregate function performed on a single table.

[C#]
// select people with above-average salary
dataView.RowFilter = "Salary > AVG(Salary)";
Following example shows aggregate functions performed on two tables which haveparent-child relation. Suppose there are tables Orders and Items with theparent-child relation.

[C#]
// select orders which have more than 5 items
dataView.RowFilter = "COUNT(Child.IdOrder) > 5″;
// select orders which total price (sum of items prices) is greater or equal $500
dataView.RowFilter = "SUM(Child.Price) >= 500″;


Functions

There are also supported following functions. Detailed description can be found here DataColumn.Ex¬pression.
• CONVERT – converts particular expression to a specified .NET Framework type
• LEN – gets the length of a string
• ISNULL – checks an expression and either returns the checked expression or a replacement value
• IIF – gets one of two values depending on the result of a logical expression
• TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ '
• SUBSTRING – gets a sub-string of a specified length, starting at a specifiedpoint in the string

Comments

Popular posts from this blog

Download SCERTUP BTC 2011 Application Form

Note : Professional Courses Student can also now apply for UP BTC 2011 and date extended. The SCERT of UP was established at Lucknow in 1981 as an apex government organization at the state level. The State Council of Educational  Research  &  Training , U.P., is providing academic support to the District Primary Education Program intended primarily to accelerate the processes of pedagogical renewal in the state.   The Utter Pradesh Education Board Basic Training Certificate or B.T.C Application Form 2011 is now available for download on the Uttar Pradesh State Council of Educational Research & Training (SCERT) official website. Pre-Service Training * Training of Special B.T.C. Trainees. Training Urdu Special B.T.C. in process * Training of Shiksha Mitras, EGS/ AIE Instructors & Literacy functionaries. * Pre-service Training for physical Education and nursery training also being conducted. The thrust areas include  (a) development of curriculum, r

UP TET 2011 Exam Application,UPTET 2011 Result | UPTET 2011 Revised Result

UPTET 2011 Result | UPTET 2011 Revised Result | UPTET 2011 Updated Result |Board of High School and Intermediate Education Uttar Pradesh, Allahabad UP TET Advertisement 2011 and Exam Detail/Admit Card/Call Letter Download UP TET 2011 FORM & DETAILS  GET LATEST DETAIL ABOUT 72825 Primary Teacher Merit List UP TET 2011 Application form submission last date is 18-Oct-2011.Forms will be distributed from PNB bank all over UP.According to latest news Appearing B.Ed student also eligible for TET exam 2011 so they can also submit their forms to concern District.There is no requirement for Rojgar Registration No and Bank receipt within the application form.UP TET 2011 exam results will be avail on http://www.uptet2011.com . Now you can easily get Application from from the P.N.B bank Branches. UPTET 2011 Result | UPTET 2011 Revised Result | UPTET 2011 Updated Result |  Board of High School and Intermediate Education Uttar Pradesh, Allahabad

remove index.php from wordpress on win2003 shared hosting

No need to install any  ISAPI filter to  remove  the  index . php  from  WordPress  permalinks. No need of  .htaccess  file. . Use these simple steps to WordPress Permalinks in IIS 6.0 using Custom 404 Redirect for Windows Shared hosting/manas hosting or any windows shared hosting. When you run wordpress on IIS server your permalinks have to include the prefix /index.php/ which looks ugly and is totally un necessary.If you have these  requirements as follow:  a .IIS for Windows Shared Hosting b .WordPress Site c .Access to change your 404 error page with your web hosting. This can be remove by following simple steps. 1 .  Once installed wordpress blog on your site you’ll want to log into the admin section and select options and then select permalinks. Set your permalinks as you wish. 2 . Then  You’ll create a new text file and name it "404-error.php". In this text file you’re going to include the text: <?php // This is the default file for the s