Tip
All examples used are valid against the Microsoft Northwind Sample Database You can run the resulting SQL from the examples against Northwind in order to test and play with the SqlObjects library.
WHERE¶
Basic Syntax¶
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Categories")
.WHERE("CategoryID").IsEqualTo(1)
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Categories]
WHERE 1=1
AND [CategoryID] = 1
WHERE Clause with Multipart Identifier Table Alias
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Categories", "c")
.WHERE("c", "CategoryID").IsEqualTo(1)
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Categories] c
WHERE 1=1
AND [c].[CategoryID] = 1
Comparison Operators¶
WHERE clause conditions of the form
[LeftOperand] [ComparisonOperator] [RightOperand]
can be written using the following list of Comparison Operators:
Operator |
Output |
|---|---|
|
= |
|
> |
|
>= |
|
< |
|
<= |
|
<> |
AND¶
WHERE Clause with Multiple Conditions Using .AND()
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("UnitPrice").IsGreaterThan(10.00m).AND()
.COLUMN("UnitsInStock").IsGreaterThanOrEqualTo(1)
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [UnitPrice] > 10.00
AND [UnitsInStock] >= 1
This could also be written using the .AND(string operand) method as shown below and it will produce
the same output as above.
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false)
.AND("UnitPrice").IsGreaterThan(10.00m)
.AND("UnitsInStock").IsGreaterThanOrEqualTo(1)
.Build()
;
Console.WriteLine(sql);
Note
For the most part, using .AND() with no arguments can be skipped. All conditions will be output
with the AND logical operator by default. It is included in all examples to mimic SQL as much as
possible and for explicit understanding. This is not the case with OR. If you want to use the
OR logical operator with multiple conditions then you must use the .OR() method.
OR¶
WHERE Clause with Multiple OR Conditions
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Categories")
.WHERE("CategoryName").IsEqualTo("Beverages").OR()
.COLUMN("CategoryName").IsEqualTo("Produce").OR()
.COLUMN("CategoryName").IsEqualTo("Seafood")
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Categories]
WHERE 1=1
AND [CategoryName] = 'Beverages'
OR [CategoryName] = 'Produce'
OR [CategoryName] = 'Seafood'
This could also be written using the .OR(string columnName) method as shown below and it will produce
the same output as above.
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Categories")
.WHERE("CategoryName").IsEqualTo("Beverages")
.OR("CategoryName").IsEqualTo("Produce")
.OR("CategoryName").IsEqualTo("Seafood")
.Build();
;
Console.WriteLine(sql);
Using Condition Scopes for Mixing .AND() With .OR()
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("UnitPrice").IsGreaterThan(10.00m).AND()
.StartConditionScope() // Opens parentheses (
.COLUMN("CategoryID").IsEqualTo(2).OR()
.COLUMN("CategoryID").IsEqualTo(4)
.EndConditionScope() // Closes parentheses )
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [UnitPrice] > 10.00
AND
(
[CategoryID] = 2 OR
[CategoryID] = 4
)
BETWEEN¶
WHERE Clause Using BETWEEN [StartVal] AND [EndVal]
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("UnitPrice").BETWEEN(10.00m).AND(20.00m)
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [UnitPrice] BETWEEN 10.00 AND 20.00
IN¶
WHERE Clause Using IN (...)
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("CategoryID").IN(1,2,3)
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [CategoryID] IN (1,2,3)
WHERE Clause Using IN (...) with an Array
var intArray = new int[3] { 1, 2, 3 };
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("CategoryID").IN(intArray)
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [CategoryID] IN (1,2,3)
WHERE Clause Using IN (...) with a Collection
var intList = new List<int> { 1, 2, 3 };
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("CategoryID").IN(intList.ToArray())
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [CategoryID] IN (1,2,3)
You can use the AND(string operand) method as shown below. However, if you want to specify a column name
as the left operand then you must manually add the enclosing square brackets [] or it will interpret
the value as a string and enclose it in single quotes ''
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false)
.AND("[CategoryID]").IN(1,2,3)
.Build()
;
Console.WriteLine(sql);
LIKE¶
WHERE Clause Using a LIKE Condition
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false).AND()
.COLUMN("ProductName").LIKE("Ch%")
.Build()
;
Console.WriteLine(sql);
Output:
SELECT
*
FROM [dbo].[Products]
WHERE 1=1
AND [Discontinued] = 0
AND [ProductName] LIKE 'Ch%'
You can use the AND(string operand) method as shown below. However, if you want to specify a column name
as the left operand then you must manually add the enclosing square brackets [] or it will interpret
the value as a string and enclose it in single quotes ''
var sql = TSQL
.SELECT()
.STAR()
.FROM("dbo", "Products")
.WHERE("Discontinued").IsEqualTo(false)
.AND("[ProductName]").LIKE("Ch%")
.Build()
;
Console.WriteLine(sql);