SQLClip

Overview

INFO

SQLClip is syntactic sugar over SQLBuilder: fluent like SQL, but entity-driven instead of raw fragments. It targets developers who think in tables/columns while staying in C#.

WARNING

Best for ad-hoc joins and column picks that repositories or pure LINQ make awkward. It relies on out var table aliases—mind their lifetime to avoid subtle bugs.

Obtain a clip

var clip= DBCash.useClip(0);

From DBInstance:

var db = DBCash.GetDBInstance(0);
var work3= db.useClip();

SQLBuilder.useClip overloads

public static SQLClip useClip(this SQLBuilder builder,bool inherit=false)

public static R useClip<R>(this SQLBuilder builder,Func<SQLClip,R> clipAction,bool inherit=false)

public static SQLBuilder useClip<R>(this SQLBuilder builder,out R val, Func<SQLClip, R> clipAction, bool inherit = false)

Examples

var vclink= kit.useClip()
    .from<BusiViewCompLinkDataSet>(out var v)
    .where(()=>v.BusiViewCompLinkOID, para.VCLinkOID)
    .select(()=>new { v.ParentOID ,v.UCMLClassOID})
    .queryUnique();

var vclink= kit.useClip((clip)=>{
    clip.from<BusiViewCompLinkDataSet>(out var v)
    .where(()=>v.BusiViewCompLinkOID, para.VCLinkOID)
    .select(()=>new { v.ParentOID ,v.UCMLClassOID})
    .queryUnique();    
})

kit.useClip(out var vclink,(clip)=>{
    clip.from<BusiViewCompLinkDataSet>(out var v)
    .where(()=>v.BusiViewCompLinkOID, para.VCLinkOID)
    .select(()=>new { v.ParentOID ,v.UCMLClassOID})
    .queryUnique();    
})

SELECT

var cmd= clip.from<HHDutyItem>(out var a)
    .join<HHGoodsBag>(out var b)
    .on(() => a.HH_GoodsBag_FK == b.HH_GoodsBagOID)
    .where(() => a.Di_Name == "1")
    .where(()=>b.Gb_Idx==id)
    .whereIn(()=>a.Di_Idx, new List<int?> { 1,3 })
    .select(()=> new { a.Di_Code,a.Di_Idx,b.Gb_Idx })
    .toSelect();

SELECT a.Di_Code,a.Di_Idx,b.Gb_Idx 
from hh_dutyitem AS a 
join hh_goodsbag AS b ON a.HH_GoodsBag_FK = b.HH_GoodsBagOID 
where  ( a.Di_Name = @kgwh_0_wp0 
AND b.Gb_Idx = @kgwh_0_wp1 
AND a.Di_Idx  IN  (1,3) )

UPDATE

var cc= clip.setTable<UCMLClassDataSet>(out var u)
    .set(() => u.ChineseName, "caption")
    .set(() => u.ClassName, "name")
    .where(() => u.UCMLClassOID, 1)
    .doUpdate();

DELETE

var cc= clip.setTable<UCMLClassDataSet>(out var u)
    .where(() => u.UCMLClassOID, 1)
    .doDelete();

API

from

clip.from<BusiViewCompLinkDataSet>(out var v)

where

Use the out alias for columns.

clip.where(() => u.UCMLClassOID, 1)

whereLike / whereLikeLeft

clip.whereLike(() => d.Di_Name, "admin")
clip.whereLikeLeft(() => d.Di_Name, "admin")

whereIn

clip.whereIn(()=>a.Di_Idx, new List<int?> { 1,3 })

Subquery:

.whereIn(() => d.Di_Name, (c) =>
{
    return c.from<HHWordBag>(out var w)
    .where(() => w.Wb_Code, "a")
    .select(w.Wb_Name);
})

sinkOR / rise

clip.sinkOR()
clip.rise()

select

clip.select(()=> new { a.Di_Code,a.Di_Idx,b.Gb_Idx })
clip.from<BusiViewCompLinkDataSet>(out var v)
    .select(v)

setTable

clip.setTable<UCMLClassDataSet>(out var u)

queryUnique / queryList

var row= clip.queryUnique()
var list= clip.queryList()

doUpdate / doDelete

var list= clip.doUpdate()
var list= clip.doDelete()