SQLBuilder samples

SELECT TOP

var dt = kit.select("t.ZH_TroubleOID as oid, t.T_Title, t.T_OrgName")
    .from("ZH_Trouble t")
    .where("t.T_Status= 1")
    .orderby("t.SYS_Created desc")
    .top(6)
    .query();

Paged SELECT

Pass ORDER BY into rowNumber so sorting applies inside the window.

TIP

Paged SQL uses ROW_NUMBER(); outer ORDER BY may differ from non-paged queries.

var dt = kit.select("a.ZH_DangerAreaOID,a.SYS_Created")
    .from("ZH_DangerArea a")
    .where("(a.SYS_Deleted is null or a.SYS_Deleted=0)")
    .setPage(pageSize,pageNum)
    .rowNumber("DA_Idx","rowm")
    .orderby("rowm asc")
    .query();

GROUP BY

var dt = kit.select("r.RS_Year,COUNT(*) as cc,SUM(r.RS_Agreementfunds) as funds,SUM(r.RS_Population) as popu,SUM(r.RS_Households) as house,SUM(r.RS_Numberofvillages) as village")
    .from("ZH_RelocatStatist r")
    .groupBy("r.RS_Year")
    .orderby("r.RS_Year desc")
    .top(10)
    .query();

UPDATE (single table)

cc += kit.setTable("ZH_PortCell")
    .set("PC_X", cell["x"].ToString())
    .set("PC_Y", cell["y"].ToString())
    .set("PC_W", cell["w"].ToString())
    .set("PC_H", cell["h"].ToString())
    .where("ZH_PortCellOID", fk)
    .doUpdate();

UPDATE with joins

TIP

MySQL only allows UPDATE with INNER JOIN joins; SQL Server is more permissive.

kit.set("C_Year", "2022")
.set("C_EndTime", DateTime.Now)
.set("C_TrainType", "p.Po_TrainType", false)
.set("C_Days", "p.Po_Day", false)
.set("C_PlanTotal", "(select SUM(d.Pa_PlanTotal) from PX_PostClassDe as d where PX_Class_FK=PX_ClassOID)")
.setTable("c")
.from("PX_Class as c left join PX_PostClass p on c.PX_PostClass_FK=p.PX_PostClassOID")
.where("c.PX_ClassOID in ('" + newDassOIDs + "')")
.doUpdate();

INSERT (single row)

kit.setTable("HH_SysUser")
.set("Id",YitIdHelper.NextId())
.set("Account", row["USR_LOGIN"])
.set("Password", newpwd)
.set("Phone", row["MobilePhone"])
.set("Sex", 1)
.set("Status","1",false)
.set("AccountType","666")
.set("OrgId",0)
.set("OrderNo",100)
.set("IsDelete","0",false)

INSERT multi-row

TIP

Each newRow() starts a new row. Finish with doInsert(). For mixed insert/update use BatchSQL.

kit.setTable("KB_DeptWorkor");
foreach (DataRow man in dt.Rows)
{
    kit.newRow()
        .set("KB_DeptWorkorOID", Guid.NewGuid())
        .set("HH_Org_FK", row.HH_Org_FK)
        .set("Dw_Task", "1")
        .set("Dw_Belong", "1", false)
        .set("SYS_LAST_UPD", DateTime.Now)
        .set("SYS_Deleted", "0", false);
}
var cc= kit.doInsert();

UNION

TIP

union() starts another SELECT branch.
unionAs wraps the union with aliases / UNION ALL.
selectUnioned sets the outer projection—needed when paging wraps the union in a CTE.

var dt = kit.select("'Done' as statue,count(*) as co")
        .from("KB_Task")
        .where("KB_Statue", "2")
        .where("KB_Type", kanBanPara.type)
        .where("KB_StartDate", kanBanPara.StratDate, ">=")
        .where("KB_EndDate", kanBanPara.EndDate, "<=")
        .whereFormat(sqll)
        .union()
        .select(" 'In progress' as statue,count(*) as co")
        .from("KB_Task")
        .where("KB_Statue", "1")
        .where("KB_Type", kanBanPara.type)
        .where("KB_StartDate", kanBanPara.StratDate, ">=")
        .where("KB_EndDate", kanBanPara.EndDate, "<=")
        .whereFormat(sqll)
        .unionAs(wrapAsName:"a",isUnionAll:false,wrapSelect:true)
        .selectUnioned("a.statue,a.co")

MERGE

WARNING

Full MERGE is native on SQL Server and Oracle only.

cc = kit.setTable("HH_SysRole")
    .from("UCML_RESPONSIBILITY as r")
    .mergeOn("HH_SysRole.Code=r.R_Code")
    .set("Name", "r.RESP_NAME", false)
    .set("Remark", "r.RESP_DESC_TEXT", false)
    .set("OrderNo", "r.[level]", false)
    .set("DataScope", "r.accessType", false)
    .setI("Code", "r.R_Code", false)
    .setI("TenantId", "1300000000001")
    .setI("IsDelete", "0", false)
    .doMergeInto();

Filtered source + alias:

var cc = kit.setTable("ZH_Danger")
    .from("HH_MdmRisk as a")
    .whereInGuid("a.HH_MdmRiskOID",oids)
    .mergeAs("r")
    .mergeOn("ZH_Danger.ZH_SrcDanger_FK=r.HH_MdmRiskOID")
    .setU("D_Title", "r.MR_IssueName", false)
    .setU("D_Level", "(case when r.MR_Focused='1' then '3' else '1' end) ", false)
    .doMergeInto();

Recursive CTE

WARNING

withRecurTo switches the builder into recursive CTE mode until apply() returns to SQLBuilder.

var dt = kit.withRecurTo("o")
    .select(commFields)
    .selectDeep("tDeepNum")
    .fromRoot("UCML_Organize")
    .joinOn("UCML_OrganizeOID", "ParentOID")
    .whereRoot((r,cur) =>
    {
        r.where(cur.RootAs+".UCML_OrganizeOID", rootID);
    })
    .whereNext((n,cur) =>
    {
        n.where(cur.CTEJoinAs+".tDeepNum<" + deep);
    })
    .apply()
    .select("*,(select COUNT(*) from UCML_Organize n where n.ParentOID=o.UCML_OrganizeOID) as childcc")
    .from("o")
    .where("o.UCML_OrganizeOID", rootID, "<>")
    .query();