Creating filter strings
If you need to dynamically build a SQL filter (WHERE clause) to be used in a LoadCollection request, the framework provides the SqlLiteral class to help build filter strings that contain embedded literals.
SqlLiteral.Format method
The static SqlLiteral.Format method accepts any supported .NET CLR typed value and converts it into a literal that can be used when constructing a filter as shown in this example:
string filter = null;
filter = string.Format(
"SessionId = {0} AND trans_num = {1}",
SQL Literal.Format(SessionID),
SQL Literal.Format(TransNum));
This example produces a filter with the appropriate syntax for the literal values, similar to this:
SessionId = N' 7A4930D6-AE9E-4F32-9687-17ABDBF4E818' and trans_num = 1234
You must always pass in a strongly typed value in order to get the correct result. For example, do not pass a string containing a date value.
This table shows some examples of input types, values, and the SqlLiteral.Format result for each.
| .NET CLR type | Input value | SqlLiteral.Format result |
|---|---|---|
System.DateTime |
12/31/2006 23:59:59.999 |
N'20061231 23:59:59.999' |
System.Int32 |
123 |
123 |
System.String |
Joe's Bar |
N'Joe''s Bar’ |
System.Decimal |
150.32 |
150.32 |
System.Guid |
7A4930D6-AE9E-4F32-9687-17ABDBF4E818 |
N' 7A4930D6-AE9E-4F32-9687-17ABDBF4E818' |