Merge & bulk data

MERGE (SQLBuilder)

• Tables only; no entity required.
• Very flexible SQL.
• High performance.
• Best when copying inside one database without server-side ID generation.

var dt = kit
        .mergeInto("SK_RealInBill", "b")
        .from("r", (r) =>
        {
            r.select("*")
                .from("SK_RealInEFuel a")
                .whereIn("a.STATUS", "A", "AC");
        })
        .on("r.SK_RealInEFuelOID=SK_RealInBill.SK_RealInBillOID")
        .setI("SK_RealInBillOID", "r.SK_RealInEFuelOID", false)
        .setI("Ri_Task", "2")
        .setI("Ri_InSrc", "4")
        .set("Ri_PayAcc", "r.PAY_ACCOUNT", false)
        .set("Ri_PayAccName", "r.PAY_NAME", false)
        .set("Ri_Code", "r.RCPT_NO", false)
        .set("Ri_Num", "r.COST", false)
        .set("Ri_Man", "r.CZRMC", false)
        .set("Ri_InType", "(case when r.STATUS='A' then '1' when r.STATUS='AC' then '2'  else '' end) ", false)
    .doMergeInto();

MatchBulk

• DataTable + target table; no entity required.
• Very flexible server-side logic.
• High performance.
• Compare source rows to target and insert/update—good for cross-database sync.

var dt = kit.select("*")
            .from("UCML_RESPONSIBILITY r")
            .query();


var oldDt = kit.clear().select("*").from("HH_SysRole").query();

var mb = new MatchBulk("HH_SysRole", 0);
mb.checkTable = oldDt;
mb.keyCol = "Id";

foreach (DataRow row in dt.Rows)
{
    var code = row["R_Code"].ToString();
    mb.checkExist("Code='" + code + "'");

    var scope = "1";
    mb.add("Id", YitIdHelper.NextId())
        .add("Code", code)
        .add("TenantId", 1300000000001)
        .add("IsDelete", false)
        .add("Status", 1)
        .add("CreateTime", DateTime.Now)
        .set("UpdateTime", DateTime.Now)
        .set("Name", row["RESP_NAME"])
        .set("Remark", row["RESP_DESC_TEXT"])
        .set("OrderNo", row["level"])
        .set("DataScope", scope)
        .end();

}

cc += (int)mb.save();

BulkTable

• High throughput when the driver supports it (e.g. SQL Server).
• No entity; uses DataTable.
• Use for insert-only bulk loads without existence checks.

var mdt = kit.select("*")
            .from("ZH_Portal")
            .where("ZH_PortalOID", moid)
            .query();
var paotalBulk = new BulkTable("ZH_Portal", 0);
paotalBulk.bulkTarget = mdt;
paotalBulk.doInsert();

BatchSQL

• Medium performance; normal insert/update batching.
• Flexible SQL.
• Good when many tables, smaller volume, transactional consistency.

Notes

• After newRow, call addInsert / addUpdate or SQL is not queued.
• A BatchSQL instance is bound to one connection slot; use separate instances per database.

Core flow

bkit.newRow() returns a fresh SQLBuilder. addInsert/addUpdate call toInsert/toUpdate internally. exeNonQuery() runs all queued statements.

var bkit = DBCash.newBatchSQL(0);
foreach (DataRow track in trackDt.Rows) {
    var dangOID = track["ZH_Danger_FK"].ToString();
    var progs = progDt.Select(string.Format("ZH_Danger_FK='{0}'", dangOID));
    if(progs.Length > 0)
    {
        var st = progs[0]["FP_MarkState"];
        bkit.newRow()
            .setTable("ZH_DangTrack")
            .set("DT_ProgMarking", st)
            .where("ZH_DangTrackOID", track["ZH_DangTrackOID"]);
        bkit.addUpdate();
    }
}
cc=bkit.exeNonQuery();