Introduction to controls on the ADO panel

Introduction to controls on the ADO panel

One, tadoconnection components
This component is used to establish a connection to the database. ADO's Data source components and command components can be used to run commands and to extract data from the database.
This component is used to establish a connection to a database that can be shared by multiple datasets, but not necessarily in the application, because the ADO dataset and the command component can connect directly to the database by setting its ConnectionString property. However, if multiple datasets use the same database connection, using tadoconnection has the advantage of having to establish a separate database connection for each dataset, reducing resource consumption, and establishing transactions that span multiple datasets. A transaction (transaction) is a stage in a database operation in which the user's modifications to the database are kept in memory on the local computer, and only after a transaction is committed can the modified content be committed to the database. If a ROLLBACK transaction is selected, all modifications are canceled and not committed to the database.

The Øtadoconnection component provides the following features:
V-Control Database connection
V Control Server Registration
V Management Services
v providing a database connection for the associated data set
v Send SQL commands to the database
V get the original data of the database (metadata)

Common Properties of Øtadoconnection
1) Attributes
This property is used to set the behavior of the connected database for automatic processing, which is txactattributes
A collection of types, including two collection elements:
I. Xacommitretaining: A new transaction is automatically started after a transaction is committed.
II. Xaabortretaining: A transaction is rolled back at the same time that a new transaction is started.
2) CommandTimeout
The connection Timeout property, which sets the maximum time value that a command can wait to execute. The unit of measure in seconds. The default value is 30 seconds, that is, the connection command waits 30 seconds before it is executed, and the system discards the command.
3) Connected
Identifies whether the connection to the database is active.
The user can query the value of the connected property to determine the connection state of the database. If this property is true, the database is in a connected state; False, the current database connection is closed.
4) ConnectionString
A string is used to specify connection information for the database. The standard caller of the hyphen string is: adoconnection1.connectionstring:= ' Provider=providerret; Remote Server=serverret ';
Among them, the common parameters supported by the connection string are as follows:
Database connection parameters and description
Parameter description
Provider data provider name, such as MSDASQL.1
Password Password for login database
Parameter description
Persist Security supports secure login
User ID to log in to database users
DataSource data source name, data source settings require additional action
After the setting is successful, the user can set the property connected to true, and if there is no prompt indicating that the database has been successfully connected, the property defaultpatabase is assigned the path to the database specified by the connection.
5) Connectoptions
Specifies whether the database connection is synchronous or asynchronous. The type tconnectoption consists of two values:
V coconnectunspecified: The database connection is connected in a synchronous manner.
V coasyncconnect: Connect the database asynchronously. This type of connection is useful when the server is under heavy load. Referencing this connection, the application cannot get all the data when the connection is first established.
6) CursorLocation
Specifies whether the database pointer is to the client or server side. The type tcursorlocation consists of two values:
V Cluseserver: Use server-side database pointers for data sets with large data volumes.
V Cluseclient: When using the client's data pointer, the data will be downloaded to the local computer and operated locally.
7) DefaultDatabase
Indicates that the data source is automatically assigned by the data source after it has been successfully connected.
8) IsolationLevel
Specifies the level of independence between different transactions, and the transaction is actually a collection of operations on the database. A transaction is holistic, and if one of the steps in the transaction does not execute correctly, the entire transaction is not executed. Because the database server can support multiple connections at the same time, transactions from different connections may operate on the same data at the same time, which can cause data inconsistencies. To prevent this from happening, ADO introduces transaction-independent levels to determine the interrelationships between different transactions. After you set the transaction independence level and call the BeginTrans method, the new transaction independence level takes effect.
The tisolationlevel consists of 9 constant values, as follows:
Tisolationlevel Constants and descriptions
Constant parameter description
Ilunspecified uses the default independent level, no other independent level
Ilchaos a transaction from a higher independent level cannot be overwritten by the current transaction for data changes
Ilreaduncommitled the current transaction can read uncommitted data from other transactions
Ilbrowse the current transaction can read uncommitted data from other transactions
Ilcursorstability The data can be read after a transaction is committed
Ilreadcommitled The data can be read after a transaction is committed
Ilrepeatableread cannot read data from other transactions, and performing a requery operation can obtain this data
Ilserializable to get the independent level of transactions from other transactions
Ilisolated to get the independent level of transactions from other transactions

The definitions of these constants are described in detail in the Microsoft Data Access SDK and need to be further understood to be able to query Microsoft's SDK documentation.
9) Keepconnection
Specifies whether to persist the data connection if the dataset is not open.
The operation of opening and shutting down the database will affect the performance of the system, especially on the network, it will increase the load of the network to some extent. This property sets the data source to always be connected and can significantly improve the performance of the program.
) loginprompt
Specifies whether the logon dialog box prompts the user to log on each time a connection is established. If set to False, you must specify the user and password for the logon database in ConnectionString.
One) Mode
Specifies the operation permissions for the connection to the database, the value of which is as follows:
Connection mode parameters and description
Parameter description
Cmunknown database operation permissions are not specified or cannot be determined
Cmread read-only operation on the database
Cmwrite only write operations to the database
Cmreadwrite Read and write operations to the database
Cmsharedenyread prohibit other users from reading the database
Cmsharedenywrite prohibit other users from writing to the database
Cmshareexclusive prohibit other users from connecting to open data
Cmsharedengnone prohibit other users from doing anything to the database

The main methods of Øadoconnection are:
1) BeginTrans
To start a new transaction, you must ensure that the data connection is active.
2) Cancel
Closes the connection to the database.
3) CommitTrans
Submits a transaction to the database. Once the commit succeeds, the changes made to the database in the transaction are written to the database, and a transaction ends.
4) Execute (constcommandtext:widestring; varrecordsaffected; Executeoptions:texcuteoptions=[eoexecutenorecords]);
Executes a SQL command of type CommandText, where CommandText is the specified SQL command, reardsaffected specifies the number of records designed by the command, and executeoptions specifies the command characteristics as follows:
Value and description of Executeoption
Parameter description
Eoasyncexecute Executing commands asynchronously
Eoasyncfetch data is asynchronously given the value of the cache attribute
eoasyncfetchnonblocking non-blocking thread execution
Eoexecutenorecords no return record
5) Getprocedurenames (list:tstring);
Gets the name of the stored procedure on the database server and gets the name of the stored procedure in the list parameter.
6) Gettablenames (list:tstring; Systemtables:boolean=false);
Gets the data table in the database, gets the table name stored in the list parameter Systemtables parameter indicates whether to get the name of the database system table. Database system table refers to the database data type definition and user information data table, this system table is automatically generated by the database itself.
7) Open (const userid:widestring;constpassword:widestring)
Open a connection, the parameter userid is the user name of the database user. Password is the password for the user to log in to the database.
8) Rollbacktrams
Withdraws a transaction that is not fully executed. After a transaction is recalled, any modifications made in the transaction are not written to the database.

Main events of Øadoconnection
Event description
Afterconnect occurs after a connection is established
Afterdisconnect occurs after a connection is disconnected
Beforeconnect occurs before the connection is established
Beforedisconnect occurs before disconnecting
Onbegintranscomplete occurs when a transaction is started
Oncommittranscomplete occurs when a COMMIT transaction succeeds
onConnectComplete occurs when the connection is complete
OnDisconnect occurs when the disconnection
Onexecutecomplete occurs after a command is executed
Oninfomessage occurs when a message is received from a database
Onlogin occurs when the user logs on to the database
Onrollbacktranscomplite occurs after a transaction is withdrawn
Onwillconnect occurs when a connection data request is made
Onwillexecute occurs when the database receives an SQL command and will be executed before

Second, Adocommand components
Adocommand sends a SQL instruction to the database and returns the requested DataSet.
The Adocommand component is primarily used to run some data definition language (DDL) SQL commands or to run a stored procedure that does not return results. For SQL statements that return result sets, it is best to use the Tadodataset, Tadoquer, or tadostoredproc components. Although the Exexute method of the Adocommand component can return a result set, it is used by another ADO dataset component.
The Tadocommand component is similar to the Adocommand object, so the properties and methods in the Adocommand component can find the same hungry name in the Adocommand object and have the same effect. If you use ASP to develop dynamic Web pages, this will certainly have a deep understanding.
Tadocommand represents the Adocommand (ADO Command) object, which accesses the database through an ADO provider. The Tadocommand component executes the command that is set in its CommandText property and executes it by calling the Execute method. If a parameter is required in the command, the Parameters property is set with the same properties as the params property of the Tquery component of the BDE dataset.

Main properties of Øadocommand
1) CommandText
Specify the SQL command you want to execute, either by hand or by using the CommandText Editor dialog box to set this property.
The CommandText editor is specifically designed to write SQL commands for ADO components. The table list box is used to list all the tables in the database, select a table, click the "Add table to SQL" button, and the CommandText editor will automatically insert the name into the appropriate location of the SQL command. When a table is selected, all the fields in the table are automatically listed in the Field list box. Similarly, when you select a field in the Fields list box, click Add field to SQL, and the fields are inserted into the SQL command.
2) CommandType
Specifies the kind of command to execute:
CommandType parameters and descriptions
Parameter description
Cmdunknown Unknown Command type
Cmdtext Text Type
The name of a table is specified in the cmdtable command
The name of a stored procedure is specified in the cmdStoredProc command
The file name of the save dataset is specified in the Cmdfile command
The name of the table is specified in the Cmdtabledirect command, and all columns are returned
3) Connection
Specifies the name of the data source connection component used, the name of the ADOConnection component, which enables Adocommand to connect to the database.
4) Paramcheck
Specifies whether the parameter list needs to be reset when the SQL command changes dynamically.
5) Parameters
The parameter to be used when executing the SQL command, which is required in the parameter query, that is, in the SQL command or in the stored procedure, when parameters need to be passed, and the parameter is valid when the command type Cmmandtype is specified as Cmdtext or cmdStoredProc.

Øadocommand Main methods:
1) Cancel
Abort a command that is executing
2) Assign (source:tpersistent)
Copies all the properties of another Adocommand component into the current Adocommand component. When called, the Component object is accessed by name.
3) Execute
Executes the commands contained by the Adocommand component, and the returned result is a data recordset that can be called by the recordset Recordset properties of other ADO components.

Third, Adodataset components
Adodataset is the most common component in an ADO component that can acquire and represent the collection of data returned from the database on behalf of any other ADO component. A dataset is a table or multiple table data that is returned through an SQL command.
For the Adodataset DataSet component to function properly, you should first set its connection or connection string property to establish a connection to the database. If you are using a Rdsdataspace object to connect the dataset to an ADO-based application server, you need to set the Rdsconnection property to a Trdsconnection object.
Because the Adodataset component must return a result set, if the statement is used in its CommandText property, only the SELECT statement can be used instead of the one that causes the data manipulation language (DML), such as delete, INSERT, and UPDATE statements.
Also, because the dataset can use SQL statements. Therefore, you can query data from one or more base tables.

Common Properties of Øadodataset
1) Active
Indicates whether the current recordset is open, calls the Open method, opens the database, the active value is true, calls the Close method, the database shuts down, and active is false;
An application can read and write to a database only if the value of active is false. The case where you need to turn active to true is:
The status of the V database is set to Dsbrowse (browse mode, view, scan data).
V If the Beforeopen event is loaded in the program, when the event is triggered.
V If the program loads the Afteropen event when the event is triggered.
V When a data pointer is opened through a recordset.
Set the active property of the dataset to false in advance when you change dataset properties that affect the state of the database or the state of the data display component;
2) Autocalcfields
Set to True to allow the application to trigger the Oncalcfileds event. Calculated fields depend on one or more fields of the current record. Statistics are calculated from the existing field data. True to trigger the Oncalcfields event when the record data is modified or edited. The application automatically updates the values of calculated fields to ensure data consistency. The conditions that can trigger the Oncalcfileds event are:
The state of the V DataSet component becomes dsedit.
The V record has been modified.
V application to regain a record from the database.
As you can see, not all of the above conditions need to update the calculated fields. If the user needs to modify the data frequently, the Oncalcfields event is constantly being called. Frequent calls affect the performance of the application to a certain extent. In this case, you can set the property autocalcfields to False.
3) Cachesize
Specifies the buffer size of the data set. The dataset first takes the data out of the database and then stores it in a domain of memory. This area of memory is called a buffer. Setting CacheSize to 20 indicates that the dataset will fetch 20 records from the database at one time from the dataset and save the 20 records to the buffer. The default value is 1, which is also the minimum value.
4) CommandText
Specifies the command contained in the data collection, which can be an SQL statement, a table name, or a stored procedure name. The usual invocation forms are:
With ADODataset1 do
Begin
Commandtype:=cmdtext;
Command text:= ' Select * from customertable ';
Open
End
5) Filer
Filters for data sets that filter out unwanted data by using filters. The typical way to set the Filter property is:
With ADODataset1 do begin
Filterd:=false;
filter:= ' state= ' +quotedstr (' CA ') + ' OR ' + ' state= ' +quotedstr (' Ca ');
Filterd:=true;
End;
If the user modifies the data with the filtered data set, the modified data will automatically disappear from the current database if the filter's conditions are not met.
6) Maxrecord
Returns the number of data set records, with the default value of 0.

Common methods of Øadodataset
1) getindexnames (list:tstring)
Query all indexes in the table, the return value will be saved in the parameter list, called by:
Adodataset1.getindexnames (Listbox1.items);
2) deleterecords (Affectrecords:taffectrecords=arall)
Deletes the record for the Recordset, and the parameter affectrecords specifies the specific record to delete. Its value can be as follows:
AffectRecords Parameters and descriptions
Parameter Value Description
Arcurrent Delete only the current record
arfiltered delete data that satisfies filter filter criteria
ArAll Delete all records of a record
Arallchapters Delete all records from the ADO Connection Data section
The default value is arall, which deletes all records in the current Recordset.
3) Locate (const keyfields:string;constkeyvalues:variant;options:tlocateoptions)
Locates a record and takes the record as the current record. Where Keyfields is the field name of the index; KeyValues is the value to find; Options is the locate Data option, and his value can be:
V iocaseinsensitive: Location data is case insensitive.
V Iopartialkey: Partially matched to locate the data.
4) Requery (options:texecuteoption=[])
Refreshes the data in the dataset by re-executing the original command or SQL statement to regenerate the recordset.
5) SaveToFile (constfilename:string= "; FORMAT:TPERSISFORMAT=PFTADTG)
Saves the data in the current data set to the specified file in the specified format. FileName is the file name that you specified: format is a saved-for-the-files, and it can pick the following values:
V PFADTG: Generate files in ADTG (Advanced Data tablegram) format.
V Pfxml: Saves the file in XML format.
6) Seek (CONSTKEYVALUES:VARIANT;SEEKOPTION:TSEEKOPTION=SOFIRSTEQ)
Searches for records and moves a pointer to the dataset. The search action is based on the index in the current data set. Where KeyValues is the value being searched for.
Success:=adodataset1.seek (' Jones ', Sofirsteq);
If you search for the value of a composite index, it returns true, and vice versa, and you can search for multiple values at the same time:
Adodataset1.seek (vararrayof ([90030,90020]), SOFIRSTEQ);
This requires the use of the function vararrayof () to construct a parameter passed to the KeyValues.
The second parameter of seek seekoption the action of the search behavior. The values you can take are as follows:
The value and description of Seekoption
Parameter Value Description
Sofirsteq Rexord The database pointer is positioned at the first matching record, and if there are no matching records, points to the database that is not logged
The Solasteq record database pointer is positioned at the last matching record, and if there are no matching records, points to the database that is not logged
Soaftereq Record If you search for a matching record, point to the next line in the matching record, or to the nearest match record if it is not found
Soafter pointing to the next line of the matching record
Sobeforeeq If you search for a matching record, point to the previous bar of the matching record, or to the nearest match record if not found
Sobefore pointing to the previous bar of a matching record

Iv. adotable Components
Adodataset components, adotable components, adoquery components, and Adostreproc components are inherited from the parent class Tcustomadodataset, so there are many common places on properties, events, and methods.
The Tadotable component can access data from a single base table in the database only through ADO, which accesses all data and fields in a base table, and also accesses partial records, which are implemented by setting filter conditions in the Filter property.
Because the Tadotable component differs from other components, it specifically operates on tables in the database.

Some property methods that are unique to øtadotable:
1) Matersource Properties and Masterfields properties
The Matersource property is the data source used to establish the master-slave relationship, and the data in the current adqtable will vary based on the data source specified by the Matersource property. In a database rollup, there may be correlations between some tables, and a common scenario is that there are common fields between the two tables. Through these common fields, you can establish the association of the master-slave relationship. You can get data from another table by accessing one table, and when the master-slave relationship is established, the user is automatically obtained from the corresponding record in the table if a record is selected in the main table record.
The Masterfields property specifies the associated field that is used to establish the master-slave relationship, and the specified field is the link between the primary table and the table, which must be a field in the Master-slave table.
2) Readonly
Specifies whether the data in the adotable is read-only.
3) TableDirect
Specifies whether to access the database through the table name or run SQL commands in the background, and some of the data providers do not support access to the database through the table name, at which point the adotable can only access the database by running SQL's SELECT statement in the background. If set to True, the Adotable component runs the SQL command in the background to access the data table, false to access the database by table name, and the default value is Flase.
4) TableName
Specifies the name of the table adotable to manipulate.
The list of table names is displayed in Objectinspector only if the correct connection or Connectionstring,active property is set to True to set the TableName property.
5) Append method and Appendrecord (Constvalues:arrayofconst)
Use the Append method to add a new record and assign a value to the new record. The assignment is passed in as a parameter through an array. However, you must ensure that the number of dimensions and fields in the array is consistent and sequential.
6) Post method
    executes the Post method by writing the records that have been modified to the database. Typically, each time a record is executed, a post operation is performed to complete the update to the database.

Five, Adoquery components
The Adoquery component accesses multiple data tables with the powerful features of the SQL language, enables the operation of data browsing, modification, and deletion, and adoquery components can implement parameter queries. The so-called parameters can be understood as variables in use. The value is assigned before the SQL is executed. By using parameterized query, we can get different result of the query without modifying the SQL statement, given different parameter values. Typically, you use Adoquery to query a subset of fields or records from a dataset, or you can use SQL commands such as INSERTV, DELETE, update, ALTER table to update data, insert, and delete records. If the dataset contains only one base table, you can use Adoquery, or you can use the Tadotable dataset.

Main properties and methods of Øadoquery
1) SQL Properties
The SQL property is a variable of type tstring and contains the SQL command to be executed by the Adoquery component, which is one of the most important properties of adoquery. In an application, you can call the Open method or the Execsql method to execute the SQL statement specified in the SQL property. In the Code authoring phase, you can write with the property editor, and you can modify it dynamically during application execution.
2) Parameters
The Parameters property holds the SQL attribute in the SQL command to execute the required parameters that can be added during the program design phase, at which point the value of the parameter can be set at Objectinspector, and the number and type of parameters must match the parameters of the SQL statement in the SQL attribute.
3) Oepen method and Execsql method
These two methods are typically used to execute SQL commands specified by the SQL properties, which are essentially similar on a dynamic basis. However, the open method typically calls the SELECT statement, as long as the recordset is returned, and the Execsql method executes commands such as INSERTV, update, or delete, and does not return a recordset. Whether you call the Open method or the Execsql method, you must call the Close method before executing them.

VI. TADOSTREDPROC components
If a client application must use a stored procedure in the database, the Tadostoreproc component can be used, a stored procedure is a set of statements that are saved on the data server in advance, can be executed repeatedly, perform database-related tasks on the server, and deliver the results to the customer.

Main properties of the Øtadostoredproc component
The Tadostoredproc component has similar properties to other ADO datasets, and the properties that you often need to set are as follows:
1) Active
When set to True, the dataset can be activated and set to false; Close the dataset.
2) Connection
If the Tadoconnection component is added to the application, you can establish a connection to the database by selecting the component.
3) Connectionstring
If you set the Connection property, you can set a string to connect to the database in that property.
4) DataSource
is the data source for another dataset that provides some value for the current dataset, and generally does not set the property to the data source used by the current dataset. In fact, if you do this, Delphi will make an error at the design stage.
5) Filter
Sets the criteria for filtering records.
6) Filtered
Determines whether filter conditions set in filter are activated.
7) Parameters
Set the name of the stored procedure used by the dataset, which you can select from the drop-down box.

Main methods of ØTADOSTOREDPROC components
1) Create
This method is used to establish an instance of the Tadostoredproc component. In fact, when you insert the component in a form or data module by using the Tadostoredproc component on the ADO palette, an instance is created automatically, which is tantamount to implicitly invoking the Creat method. Therefore, it is seldom to call this method directly to establish an instance of the Tadostoredproc component.
2) Close
Used to destroy an instance of the Tadostoredproc component.
3) Deleterecords
Used to delete one or more records.
4) Edit
Sets the dataset to edit state.
5) Enable Controls
Use data-aware controls to display data again.
6) Disable Controls
Makes data-aware controls unable to display data.
7) Firse
Navigates to the first record in the DataSet.
8) Last
Navigates to the last record in the dataset.
9) Prior
Navigates to the previous record in the DataSet.
Ten) Next
Navigates to the next record in the DataSet.
One) Moveby
Navigate forward or backward through multiple records.
GetFieldData)
Extracts the current value of a field into the buffer.
IsEmpty)
Determines whether a data set is empty.
) Open
Open a data set.
) Refresh
Re-extract data from the database to update the data in the dataset.

Events for Øtadostoredproc components
The Tadostoredproc component is basically the same as the event for the Tadoquery component, so you can refer to the description of the Tadoquery component event.

Vii.. Trdsconnection components
The Trdsconnection component is used to implement a Rdsdata space object. When a recordset (Recordset) object is passed from one process or machine to another process or machine, the Rdsdataspace object is responsible for managing the collection of data. You should use the Trdsconnection object when you use an ADO-based business object (an application server) to build a multi-tiered application.
When building an application, use the Trdsconnection component instead of the Tadoconnection component to establish an association with a Tadodataset dataset. and select the Trdsconnection component instance used in the Rdsconnection property of the Tadodataset component.
By default, the Trdsconnection component is used with the RDS DataFactory object. If you do not need a dedicated business object, you can use the DataFactory object.

Øtrdsconnection Component Main Properties
1) Appserver
Used to access the communication interface of an application server, you can use this property if you access an attached property or method of a business object with a Recordsets object attache.
2) ComputerName
Specifies the source of a business object. If the property is empty, the business object is loaded from the local computer. Protocols such as HTTP, HTTPS, and DCOM can be used to establish business objects. If a business object is established using the HTTP and HTTPS protocols, the ComputerName property is a string containing the URL that determines the Ⅱs Web server, and the server's business object instance is established on that server, and if the DCOM protocol is used, The ComputerName property is the name of the computer.
3) Dataspaceobject
Provides access to the Rdsdataspace object interface.
4) Connected
Determines whether a connection to a remote data source has been established and is set to true to indicate a connection is established.
5) InternetTimeout
Set the number of elapsed times before the specified request count, in milliseconds
6) Name
Sets the name of the Trdsconnection component instance.
7) ServerName
Specifies the business object, which is the ProgID of the business object that needs to be initialized. The default value is in the RDSServer DataFactory property, which is the ProgID of the RDSServer DataFactory object.

Main methods of øtrdsconnection components
1) GetRecordSet
Used to extract a Recordset from a business object, the Tadodataset associated with the RDS connection component automatically calls the method, as follows:
function getrecordest (Const commandtext:widestring;connectionstring:widestring= "): _recordset
2) Free
Destroys an instance of a component and frees the resources it occupies.
3) Open
Open the Trdsconnection connection.
4) creat
Establishes an instance of the Trdsconnection component that is implicitly called when a trdsconnection component is added to a form or data module.
5) Destory
Used to destroy an instance of the Trdsconnection component, which is not normally called directly in the application. Instead, call the free method to destroy the instance of the component

Events for Øtrdsconnection components
1) Afterconnect
This event is triggered after the connection to the Trdsconnection component is established.
2) Afterdisconnect
This event is triggered when the connection to the Trdsconnection component is disconnected.
3) Reforeconnect
This event is triggered before the connection to the Trdsconnection component is established.
4) Beforeconnect
This event is triggered before the connection to the Trdsconnection component is disconnected.
5) Onlogin
The event is triggered after the user has correctly registered and opened a channel to the server.

http://blog.csdn.net/henreash/article/details/2294047

Introduction to controls on the ADO panel