[分享]更新服务端数据的几种方式**

alang

平台上跟服务端交互有三层类：
事务TTransaction
  +--数据连接 TConnection
        +- 数据集 TDataSet

于是更新服务端数据就可以从这三个层次着手来处理了

alang

数据集可以有：
  标准数据集、透视数据集  请参考 http://wiki.justep.cn/%E6%95%B0%E6%8D%AE%E9%9B%86
  存储过程数据集、存储过程  请参考 http://bbs.justep.com/forum.php?mod=viewthread&tid=15936
  TQuery

TQuery举例：
var
  lConn: TConnection;
  lTrans: TTransaction;
  lDataSet: TDataSet;
  lHandle: TTransactionHandle;
  lQry: TQuery;
begin
  lQry := TQuery.Create(nil);
  lConn := TConnection.Create(nil);
  lTrans := TTransaction.Create(nil);
  try
    lConn.ConnectionString := 'DATABASEURL=Biz:\OPERATION\OPERATIONDB.DATABASE';
    lConn.Transaction := lTrans;
    lQry.Connection := lConn;
    lQry.CommandText := 'Update CRM_PRODUCT set UOM=''2'' where PRODUCTID=:id';
    lDataSet := InfoBrokerXGSJXX.Info.DataSetByID('BZSJJ1');

    lHandle := lConn.Transaction.Start(True);
    try
      lDataSet.First;
      while not lDataSet.Eof do
      begin
        lQry.Params.ParamByName('id').AsString := lDataSet.Fields[0].AsString;
        lQry.Execute;
        lDataSet.Next;
      end;
      lConn.Transaction.Commit(lHandle);
    except
      lConn.Transaction.Rollback(lHandle);
      raise;
    end;
  finally
    lQry.Free;
    lConn.Free;
    lTrans.Free;
  end;
  Dialogs.Showmessage('ok');
end;

alang

数据连接TConnection
  function ExecuteSQL(SQLText: string; Params: TObject): Longint; overload;
  procedure ExecuteSQL(SQLText: string; Params: TParams; CheckRowsAffected: Longint; CheckRowsAffectedFailMessage: string); overload;
  function ExecuteSQL(SQLText: string): Longint; overload;

举例：
var
  lConn: TConnection;
  lParams: TParams;
  lParam: TDataParam;
  lSQL: String;
  lDataSet: TDataSet;
  lHandle: TTransactionHandle;
begin
  lParams := TParams.Create;
  lConn := TConnection.Create(nil);
  try
    lConn.ConnectionString := 'DATABASEURL=Biz:\OPERATION\OPERATIONDB.DATABASE';
    lConn.Transaction := InfoBrokerXGSJXX.Info.Transaction;
    lParam := lParams.CreateParam(TFieldType.ftString, 'id', TParamType.ptInput);
    lSQL := 'Update CRM_PRODUCT set UOM=''2'' where PRODUCTID=:id';
    lDataSet := InfoBrokerXGSJXX.Info.DataSetByID('BZSJJ1');

    lHandle := lConn.Transaction.Start(True);
    try
      lDataSet.First;
      while not lDataSet.Eof do
      begin
        lParam.AsString := lDataSet.Fields[0].AsString;
        lConn.ExecuteSQL(lSQL, lParams, 1, '数据已被更改！');
        lDataSet.Next;
      end;
      lConn.Transaction.Commit(lHandle);
    except
      lConn.Transaction.Rollback(lHandle);
    end;
  finally
    lParam.Free;
    lParams.Free;
    lConn.Free;
  end;
  Dialogs.Showmessage('ok');
end;

alang

事务TTransaction
  procedure Execute(ServerCommandItem: TServerCommandItem);

举例：
var
  CMDExecuteSQL: TCMDExecuteSQL;
  lParam: TDataParam;
  lTransaction: TTransaction;
  lHandle: TTransactionHandle;
  lDataSet: TDataSet;
begin
  CMDExecuteSQL := TCMDExecuteSQL.Create;
  try
    CMDExecuteSQL.ConnectionString := 'DATABASEURL=Biz:\OPERATION\OPERATIONDB.DATABASE';
    CMDExecuteSQL.CommandText := 'Update CRM_PRODUCT set UOM=''2'' where PRODUCTID=:id';
    lParam := CMDExecuteSQL.Params.CreateParam(TFieldType.ftString, 'id', TParamType.ptInput);
    CMDExecuteSQL.CheckRowsAffected := 1;
    CMDExecuteSQL.CheckRowsAffectedFailMessage := 'error';
    lTransaction := InfoBrokerXGSJXX.Info.Transaction;
    lDataSet := InfoBrokerXGSJXX.Info.DataSetByID('BZSJJ1');

    lHandle := lTransaction.Start(True);
    try
      lDataSet.First;
      while not lDataSet.Eof do
      begin
        lParam.AsString := lDataSet.Fields[0].AsString;
        lTransaction.Execute(CMDExecuteSQL);
        lDataSet.Next;
      end;
      lTransaction.Commit(lHandle);
    except
      lTransaction.Rollback(lHandle);
      raise;
    end;
  finally
    CMDExecuteSQL.Free;
  end;
end;