Building WHERE conditions

Overview

SQLBuilder offers many ways to build filters. Conventions:

• Conditions combine with AND by default.
• Values are parameterized by default to reduce SQL injection risk.
• Avoid concatenating untrusted input into SQL.
• Prefer fluent chaining for readability.
• Prefer C# values (DateTime.Now, Guid.NewGuid()) over DB functions in fragments when possible.

where basics

=, >, <, <>, …

Two-arg where means equality. For other operators, pass a third argument:

where("id",1); 
where("idx", 1, ">") 
where("idx", 1, "<") 
where("idx", 1, "<>")

LIKE

Default whereLike is %value%.

• whereLike("name","1") → LIKE '%1%'
• whereLikes — multiple LIKE with OR
• whereLikeLeft / whereLikeLefts — prefix match (e.g. hierarchy codes)
• whereNotLike

IN

Use whereIn. Each value is parameterized; very large lists may hit provider limits (~2000 params on SQL Server).

whereIn behavior (2024-06-21):

• null list → condition skipped
• empty list → 1=2
• numeric collections may inline literals; other simple strings may inline; complex strings stay parameterized.

.whereIn("ZH_DangerOID",List<string>)

whereInGuid

For UCML-style GUID PK lists: invalid GUIDs collapse to 1=2.

.whereInGuid("ZH_DangerOID",List<string>)

var oldDt = kit.clear()
    .select("*")
    .from("ZH_Danger")
    .whereInGuid("ZH_SrcDanger_FK", oids)
    .query();

API notes

where can be chained; use or() to switch to OR mode. Variants include:

• Raw fragment: where("isShow=1")
• Parameterized eq: where("state","1")
• whereGuid — validates GUID shape
• sink() / sinkOR() / rise() — grouped conditions
• whereFormat — template with {0}, {1}, …
• whereExist / whereNotExist
• whereLike / whereNotLike
• Nested builders: where(Action<SQLBuilder>), whereIn with subquery delegate

sink() / rise()

Start a nested group (default AND; pass "OR" for OR). rise() closes the group.

or patterns

.sinkOR()
.whereLikeLeft("d.Varchar1", deptcode)
.whereLikeLeft("j.Varchar1", deptcode)
.rise();

ki.or((k) =>
{
    k.where("TT_Public=1")
    .where("KB_DeptInfo_FK", orgoid);
});

.whereFormat("(a.field= {0} or b.field={1})", contactoid,para2)

SQLBuilder examples

var cmd = kit
    .select("a.Name")
    .from("tableA as a")
    .where("a.id>1")
    .where("a.ID",1)
    .where("a.Date", DateTime.Now,"<")
    .where("a.Created", "getdate()","=",false)
    .whereIn("a.Name", (t) => {
        t.select("Name")
        .from("student")
        .where("id=1");
    })
    .whereIn("a.Name","1","2","3")
    .whereIn("a.Name",new List<string> { "1", "2", "3" })
    .whereIn("a.Name", new string[] { "1", "2", "3" })
    .whereNotIn("a.Name", new List<string> { "1", "2", "3" })
    .whereNotIn("a.Name", "1", "2", "3")
    .whereNotIn("a.Name", (t) => {
        t.select("Name")
            .from("student")
            .where("id=1");
    })
    .whereBetween("a.Idx",1,100)
    .whereNotBetween("a.Idx", 10, 20)
    .whereLike("a.Name","张三")
    .whereNotLike("a.Name", "李四")
    .whereLikes("a.Name",new string[] { "张","王","赵"})
    .whereLikes("a.Name", new string[] { "张", "王", "赵" },false)
    .whereLikes(new string[] { "a.Name" ,"a.Home","a.Father"},  "张")
    .whereLikeLeft("a.ClassCode", "100")
    .whereLikeLefts("a.ClassCode",new string[] {"001","002","003" })
    .whereLikeLefts("a.ClassCode", "001", "002", "003")
    .whereAllFieid(new string[] { "a.Name", "a.Home", "a.Father" }, "张","=")
    .whereAnyFieid(new string[] { "a.Name", "a.Home", "a.Father" }, "张", "=")
    .whereAnyFieldIs(100,"a.Score1","a.Score2", "a.Score3")
    .whereIsNull("a.Note")
    .whereIsNotNull("a.Caption")
    .whereList("a.Id","In",new int[] { 1,2,3,4,5,6})
    .whereExist("select 1 from tableB b where b.name=a.Name")
    .whereNotExist("select 1 from tableB b where b.Home=a.Home")
    .whereFormat("(a.id>{0} or a.idx<{1})",5,7)
    .sink()
    .sinkOR()
    .rise()
    .top(1)
    .setPage(10,1)
    .toSelect();

SQLClip examples

var list = kit.findList<HHDutyItem>((c, h) => {
        c.where(() => h.Di_Idx, 0)
            .whereIn(()=>h.Di_Code,"1","2","3")
            .whereIn(() => h.Di_Code, new string[] { "1", "2", "3" })
            .whereIsNull(()=>h.Di_Name)
            .whereIsNotNull(()=>h.SYS_DIVISION)
            .whereBetween(()=>h.Di_Idx,10,11)
            .whereLike(()=>h.Di_Name,"张")
            .whereLikeLeft(() => h.Di_Name, "张")
            .whereAnyFieldIs("",()=>h.Di_Name,()=>h.Di_Code)
            .sink()
            .rise()
            .useSQL((k) => {
                k.where("a.Id>1");
            })
            .orderBy(()=>h.Di_Idx)
            .orderByDesc(()=>h.Di_Name)
        ;
        
});