SQLBuilder basics

TIP

Chained APIs build one SQL at a time. Start a new statement with clear() when needed.
UPDATE / INSERT / DELETE auto-clear; SELECT does not—call clear() between reads.
One builder instance = one context (unlike older StrSQLMaker).

SELECT

select / distinct / top

select appends columns (comma-separated). Empty → *.

.distinct()
.top(5)

from

Table list, joins, or subquery:

.from("a left join b on a.id=b.fk")

Subquery + join:

int cc= kit
    .from("a", (d) => {
        d.select("d.DD_FFocued,r.HH_MdmRiskOID")
        .from("HH_MdmDangDeal d join HH_MdmRisk r on d.DD_RiskIssueId = r.MR_Id");
    })
    .join("join ZH_Danger g on a.HH_MdmRiskOID=g.ZH_SrcDanger_FK")
    .where("a.idx=1")
    ...

Use join for LEFT JOIN … text (include the full join clause).

orderby / groupBy / having

.orderby("a.idx")
.groupBy("a.idx")
.having("count(a.idx)>1")

Paging

.setPage(5,1)
.rowNumber("a.idx","rownum")

INSERT / UPDATE / DELETE

setTable

Target table (or alias) for DML.

set

Third parameter controls parameterization (false for raw SQL fragments):

.set("DC_Date","getdate()",false)
.set("DC_Date",DateTime.Now)

Use sink() / rise() for grouped AND/OR fragments.

Execution

query

DataTable dt=kit.query();

Map rows:

var tar = kit.select("name,object_id,type,modify_date")
    .from("sys.objects")
    ...
    .query((row) => new TableOutput { ... });

Entity projection:

.query<KBTask>()

queryPaged

PagedDataTable paged=kit.queryPaged();
PageOutput<KBTask> paged=kit.queryPaged<KBTask>();

doInsert / doUpdate / doDelete / doMergeInto

int cc=kit.doInsert();
int cc=kit.doUpdate();
int cc=kit.doDelete();
int cc=kit.doMergeInto();

Helpers

• queryRow() — exactly one row or null
• queryRowValue() — scalar
• count() — SELECT COUNT(*)
• queryRowString(defaultVal)
• queryFirst<T>()
• queryScalar<T>()

WHERE

See also Where conditions.

Chained where = AND. Use or() to switch to OR mode.

where("isShow=1")
where("state","1")
where("a.idx",1,">")
whereGuid("ZH_DangerOID", guid)

Grouping

• sink() / sinkOR() / rise() — nested predicates
• whereIn / whereInGuid — list semantics as documented in Chinese reference (null → skip, empty → 1=2, etc.)

OR patterns

.sinkOR()
.whereLikeLeft("d.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)

MatchBulk sample

Sync source DataTable to target table (see merge).

UNION

Chain .union() between select blocks; configure outer wrap with unionAs / selectUnioned when paging.

Advanced WHERE samples

where not in subquery, where not exist, whereOR groups, whereGuid, whereFormat, injection-safe patterns (parameterize or validate).

Raw execution

exeQuery(string sql, Paras para)

Run arbitrary SQL through the same DBInstance.

Export SQL without running

toSelect, toSelectCount, toUpdate, toDelete, toMergeInto → SQLCmd (+ parameters).