ADO Universal Operation Data Unit

Delphi Development of 2 layer C/S database application, many people through adoquery or adotable directly manipulate the database, in fact, although this method is the most direct, but has its drawbacks: if you want to upgrade the program to 3 layer C/s will be very difficult. And through the common data operation method, like the development of 3 layer C/s as the development of 2-layer C/S program, through Adoquery or adotable to obtain data, through the Datasetprovider conversion data format for Olevariant, By Clientdataset the memory dataset and then associating with the client display control, all the methods of manipulating the data are highly centralized, and it is easy to upgrade to multiple layers later. This is the high degree of flexibility that comes with indirection.

ADO cannot submit the Clientdataset.delta of a table query like FIREDAC and UNIDAC, which is inconvenient when saving data like "documents" and needs to be done indirectly before being submitted.

Firedac and UNIDAC, although not supported by default, can be supported as long as the properties are set.

Where Firedac is set: Updateoptions.checkreadonly:=false

The original consists of: a table joins the B table query, the fields in clientdataset both a table and B table, Attempting to submit clientdataset.delta to table A, Clientdataset.delta will change the field value of Table B and package it, while the field of Table B readonly:=true, attempting to submit a read-only field will report an error.

Unit Untdb;

Interface

Uses
System.sysutils, System.classes, Data.db, Data.Win.ADODB, Datasnap.provider,
System.variants, vcl.forms;

Type
Tfrmdb = Class (Tdatamodule)
Adoconnection1:tadoconnection;
Qryquery:tadoquery;
Datasetprovider1:tdatasetprovider;
Qryexecute:tadoquery;
Procedure Datamodulecreate (Sender:tobject);
Procedure Qryquerybeforeopen (Dataset:tdataset);
Procedure Qryqueryafteropen (Dataset:tdataset);
Private
{Private declarations}
Procedure Connectdb;
Public
{Public declarations}
function Querysql (const sql:string): olevariant;
function ExecuteSQL (const sql:string): Boolean;
function saveData (const tablename:string; delta:olevariant): Boolean;
function Savedatas (tablenames, deltas:olevariant;
Tablecount:integer): Boolean;
End

Var
Frmdb:tfrmdb;

Implementation

{%classgroup ' Vcl.Controls.TControl '}

Uses unitwait;

{$R *.DFM}
{Tfrmdb}

Procedure Tfrmdb.connectdb;
Begin
Adoconnection1.close;
Adoconnection1.connectionstring: = ' FILE name= ' +
Extractfilepath (application.exename) + ' db.udl ';
Try
adoconnection1.connected: = True;
Except
On E:exception do
Raise Exception.create (E.message);
End
End

Procedure Tfrmdb.datamodulecreate (Sender:tobject);
Begin
Connectdb;
End

function Tfrmdb.executesql (const sql:string): Boolean;
Begin
Result: = False;
If sql = "Then
Exit;
Qryexecute.close;
QryExecute.sql.Clear;
QryExecute.sql.Text: = SQL;
Result: = qryexecute.execsql > 0;
End

Procedure Tfrmdb.qryqueryafteropen (Dataset:tdataset);
Begin
Formwait.close;
Formwait.free;
End

Procedure Tfrmdb.qryquerybeforeopen (Dataset:tdataset);
Begin
Formwait: = tformwait.create (application);
Formwait.show;
Formwait.update;
End

function Tfrmdb.querysql (const sql:string): olevariant;
Begin
Result: = null;
If sql = "Then
Exit;
Qryquery.close;
QryQuery.sql.Clear;
QryQuery.sql.Text: = SQL;
Qryquery.open;
Result: = Datasetprovider1.data;
End

function tfrmdb.savedata (const tablename:string; delta:olevariant): Boolean;
Var
Errcnt:integer;
Begin
Result: = False;
if (TableName = ") or Varisnull (delta) then
Exit;
Qryquery.close;
QryQuery.sql.Clear;
QryQuery.sql.Text: = ' select * from ' + TableName + ' where 1=2 ';
Qryquery.open;
Datasetprovider1.applyupdates (Delta, 0, ERRCNT);
Result: = errcnt = 0;
End

function Tfrmdb.savedatas (tablenames, deltas:olevariant;
Tablecount:integer): Boolean;
Var
I, Errcnt:integer;
Begin
If not adoconnection1.intransaction then
Adoconnection1.begintrans; Open transaction
Try
For I: = 0 to TableCount-1 do
Begin
Qryquery.close;
QryQuery.sql.Clear;
QryQuery.sql.Text: = ' select * from ' + tablenames[i] + ' where 1=2 ';
Qryquery.open;
Datasetprovider1.applyupdates (Deltas[i], 0, errcnt);
End
Adoconnection1.committrans; Commit a transaction
Result: = True;
Except
Adoconnection1.rollbacktrans; Rolling back a transaction
Result: = False;
End
End

End.

ADO Universal Operation Data Unit