delphi基于结构的CRUD(protobuf)
以采购订单为例。
unit proto.tcgddtcgdd2; //代码由代码工厂自动生成 //2022-08-20 16:04:14 {$I def.inc} interface uses {$IFDEF firedac} db.firedac, db.firedacPool, {$ENDIF} {$IFDEF unidac}db.unidac, db.unidacpool, {$ENDIF} Grijjy.ProtocolBuffers, SysUtils, classes, System.NetEncoding, db; type Ttcgdd = record //采购订单主表 [Serialize(1)] billid: string; [Serialize(2)] adddate: string; [Serialize(3)] supplierid: string; [Serialize(4)] suppliername: string; [Serialize(5)] warehouseid: string; [Serialize(6)] warehousename: string; [Serialize(7)] makerid: string; [Serialize(8)] note: string; [Serialize(9)] shenhe: string; [Serialize(10)] maker: string; end; type Ttcgdd2 = record //采购订单明细表 [Serialize(1)] billid: string; [Serialize(2)] recno: integer; [Serialize(3)] goodsid: string; [Serialize(4)] goodsname: string; [Serialize(5)] gg: string; [Serialize(6)] dw: string; [Serialize(7)] dj: double; [Serialize(8)] qty: double; [Serialize(9)] amount: double; end; Tmodels = record //采购订单主从表 [Serialize(1)] status: integer; [Serialize(2)] exception: string; [Serialize(3)] message: string; [Serialize(4)] tcgdds: TArray<Ttcgdd>;//主表 [Serialize(5)] tcgdd2s: TArray<Ttcgdd2>;//从表 end; TRes = record //应答 [Serialize(1)] status: integer; [Serialize(2)] exception: string; [Serialize(3)] message: string; end; function select(url: string; body: TBytes): TBytes; function insert(url: string; body: TBytes): TBytes; function update(url: string; body: TBytes): TBytes; function delete(url: string; body: TBytes): TBytes; implementation function select(url: string; body: TBytes): TBytes; var db: tdb; pool: tdbpool; arr: TArray<string>; serial: TgoProtocolBuffer; rows: Tmodels; i: integer; res: TRes; begin serial := TgoProtocolBuffer.Create; try try arr := url.Split(['/']); pool := GetDBPool(arr[4]); db := pool.Lock; var where: string; db.qry.Close; db.qry.SQL.Clear; if high(arr) >= 5 then where := ' where ' + TNetEncoding.URL.Decode(arr[5]); db.qry.SQL.Text := 'select * from tcgdd' + where; db.qry.Open; if db.qry.isempty then begin rows.status := 500; rows.exception := 'No found any data.'; result := serial.Serialize<Tmodels>(rows); exit; end; SetLength(rows.tcgdds, db.qry.RecordCount); db.qry.First; i := 0; while not db.qry.Eof do begin rows.tcgdds[i].billid := db.qry.fieldbyname('billid').asstring; rows.tcgdds[i].adddate := db.qry.fieldbyname('adddate').asstring; rows.tcgdds[i].supplierid := db.qry.fieldbyname('supplierid').asstring; rows.tcgdds[i].suppliername := db.qry.fieldbyname('suppliername').asstring; rows.tcgdds[i].warehouseid := db.qry.fieldbyname('warehouseid').asstring; rows.tcgdds[i].warehousename := db.qry.fieldbyname('warehousename').asstring; rows.tcgdds[i].makerid := db.qry.fieldbyname('makerid').asstring; rows.tcgdds[i].note := db.qry.fieldbyname('note').asstring; rows.tcgdds[i].shenhe := db.qry.fieldbyname('shenhe').asstring; rows.tcgdds[i].maker := db.qry.fieldbyname('maker').asstring; inc(i); db.qry.Next; end; db.qry.Close; db.qry.SQL.Clear; if high(arr) >= 5 then where := ' where ' + TNetEncoding.URL.Decode(arr[6]); db.qry.SQL.Text := 'select * from tcgdd2' + where; db.qry.Open; if db.qry.isempty then begin rows.status := 500; rows.exception := 'No found any data.'; result := serial.Serialize<Tmodels>(rows); exit; end; SetLength(rows.tcgdd2s, db.qry.RecordCount); db.qry.First; i := 0; while not db.qry.Eof do begin rows.tcgdd2s[i].billid := db.qry.fieldbyname('billid').asstring; rows.tcgdd2s[i].recno := db.qry.fieldbyname('recno').asinteger; rows.tcgdd2s[i].goodsid := db.qry.fieldbyname('goodsid').asstring; rows.tcgdd2s[i].goodsname := db.qry.fieldbyname('goodsname').asstring; rows.tcgdd2s[i].gg := db.qry.fieldbyname('gg').asstring; rows.tcgdd2s[i].dw := db.qry.fieldbyname('dw').asstring; rows.tcgdd2s[i].dj := db.qry.fieldbyname('dj').AsFloat; rows.tcgdd2s[i].qty := db.qry.fieldbyname('qty').AsFloat; rows.tcgdd2s[i].amount := db.qry.fieldbyname('amount').AsFloat; inc(i); db.qry.Next; end; rows.status := 200; rows.message := 'success'; result := serial.Serialize<Tmodels>(rows); except on E: Exception do begin res.status := 500; res.exception := E.message; result := serial.Serialize<TRes>(res); end; end; finally pool.Unlock(db); serial.Free; end; end; function insert(url: string; body: TBytes): TBytes; var db: tdb; pool: tdbpool; arr: tarray<string>; serial: TgoProtocolBuffer; res: TRes; begin serial := TgoProtocolBuffer.Create; try try var rows: Tmodels; serial.DeSerialize<Tmodels>(rows, body); arr := url.Split(['/']); pool := GetDBPool(arr[4]); db := pool.Lock; db.startTrans; for var row: Ttcgdd in rows.tcgdds do begin db.qry.Close; db.qry.SQL.Clear; db.qry.SQL.Text := 'insert into tcgdd (billid,adddate,supplierid,suppliername,warehouseid,warehousename,makerid,note,shenhe,maker) values (:billid,:adddate,:supplierid,:suppliername,:warehouseid,:warehousename,:makerid,:note,:shenhe,:maker)'; db.qry.ParamByName('billid').AsString := row.billid; db.qry.ParamByName('adddate').AsString := row.adddate; db.qry.ParamByName('supplierid').AsString := row.supplierid; db.qry.ParamByName('suppliername').AsString := row.suppliername; db.qry.ParamByName('warehouseid').AsString := row.warehouseid; db.qry.ParamByName('warehousename').AsString := row.warehousename; db.qry.ParamByName('makerid').AsString := row.makerid; db.qry.ParamByName('note').AsString := row.note; db.qry.ParamByName('shenhe').AsString := row.shenhe; db.qry.ParamByName('maker').AsString := row.maker; db.qry.ExecSQL; end; for var row: Ttcgdd2 in rows.tcgdd2s do begin db.qry.Close; db.qry.SQL.Clear; db.qry.SQL.Text := 'insert into tcgdd2 (billid,recno,goodsid,goodsname,gg,dw,dj,qty,amount) values (:billid,:recno,:goodsid,:goodsname,:gg,:dw,:dj,:qty,:amount)'; db.qry.ParamByName('billid').AsString := row.billid; db.qry.ParamByName('recno').AsInteger := row.recno; db.qry.ParamByName('goodsid').AsString := row.goodsid; db.qry.ParamByName('goodsname').AsString := row.goodsname; db.qry.ParamByName('gg').AsString := row.gg; db.qry.ParamByName('dw').AsString := row.dw; db.qry.ParamByName('dj').AsFloat := row.dj; db.qry.ParamByName('qty').AsFloat := row.qty; db.qry.ParamByName('amount').AsFloat := row.amount; db.qry.ExecSQL; end; db.commitTrans; res.status := 200; res.message := 'success'; Result := serial.Serialize<TRes>(res); except on E: Exception do begin db.rollbackTrans; res.status := 500; res.exception := E.Message; Result := serial.Serialize<TRes>(res); end; end; finally pool.Unlock(db); serial.Free; end; end; function update(url: string; body: TBytes): TBytes; var db: tdb; pool: tdbpool; arr: tarray<string>; serial: TgoProtocolBuffer; res: TRes; begin serial := TgoProtocolBuffer.Create; try try var rows: Tmodels; serial.Deserialize<Tmodels>(rows, body); arr := url.Split(['/']); pool := GetDBPool(arr[4]); db := pool.Lock; db.startTrans; for var row: Ttcgdd in rows.tcgdds do begin db.qry.Close; db.qry.SQL.Clear; db.qry.SQL.Text := 'update tcgdd set billid=:billid,adddate=:adddate,supplierid=:supplierid,suppliername=:suppliername,warehouseid=:warehouseid,warehousename=:warehousename,makerid=:makerid,note=:note,shenhe=:shenhe,maker=:maker where billid=:key0'; db.qry.ParamByName('billid').AsString := row.billid; db.qry.ParamByName('key0').value := row.billid; db.qry.ParamByName('adddate').AsString := row.adddate; db.qry.ParamByName('supplierid').AsString := row.supplierid; db.qry.ParamByName('suppliername').AsString := row.suppliername; db.qry.ParamByName('warehouseid').AsString := row.warehouseid; db.qry.ParamByName('warehousename').AsString := row.warehousename; db.qry.ParamByName('makerid').AsString := row.makerid; db.qry.ParamByName('note').AsString := row.note; db.qry.ParamByName('shenhe').AsString := row.shenhe; db.qry.ParamByName('maker').AsString := row.maker; db.qry.ExecSQL; end; for var row: Ttcgdd2 in rows.tcgdd2s do begin db.qry.Close; db.qry.SQL.Clear; db.qry.SQL.Text := 'update tcgdd2 set billid=:billid,recno=:recno,goodsid=:goodsid,goodsname=:goodsname,gg=:gg,dw=:dw,dj=:dj,qty=:qty,amount=:amount where billid=:key0 and recno=:key1'; db.qry.ParamByName('billid').AsString := row.billid; db.qry.ParamByName('key0').value := row.billid; db.qry.ParamByName('recno').AsInteger := row.recno; db.qry.ParamByName('key1').value := row.recno; db.qry.ParamByName('goodsid').AsString := row.goodsid; db.qry.ParamByName('goodsname').AsString := row.goodsname; db.qry.ParamByName('gg').AsString := row.gg; db.qry.ParamByName('dw').AsString := row.dw; db.qry.ParamByName('dj').AsFloat := row.dj; db.qry.ParamByName('qty').AsFloat := row.qty; db.qry.ParamByName('amount').AsFloat := row.amount; db.qry.ExecSQL; end; db.commitTrans; res.status := 200; res.message := 'success'; Result := serial.Serialize<TRes>(res); except on E: Exception do begin db.rollbackTrans; res.status := 500; res.exception := E.Message; Result := serial.Serialize<TRes>(res); end; end; finally pool.Unlock(db); serial.Free; end; end; function delete(url: string; body: TBytes): TBytes; var db: tdb; pool: tdbpool; arr: tarray<string>; serial: TgoProtocolBuffer; res: TRes; begin serial := TgoProtocolBuffer.Create; try try arr := url.Split(['/']); pool := GetDBPool(arr[4]); db := pool.Lock; var where: string; db.startTrans; db.qry.Close; db.qry.SQL.Clear; where := ' where ' + TNetEncoding.URL.Decode(arr[3]); db.qry.SQL.Text := 'delete from tcgdd' + where; db.qry.ExecSQL; db.qry.Close; db.qry.SQL.Clear; where := ' where ' + TNetEncoding.URL.Decode(arr[4]); db.qry.SQL.Text := 'delete from tcgdd2' + where; db.qry.ExecSQL; db.commitTrans; res.status := 200; res.message := 'success'; Result := serial.Serialize<TRes>(res); except on E: Exception do begin db.rollbackTrans; res.status := 500; res.exception := E.Message; Result := serial.Serialize<TRes>(res); end; end; finally pool.Unlock(db); serial.Free; end; end; end.
标签:rows,protobuf,CRUD,delphi,Serialize,db,qry,ParamByName,row From: https://www.cnblogs.com/hnxxcxg/p/16607968.html