Managed extensions for VCL - ADO.Net tutorial

ADO.NET is a set of classes that expose data access services to the .NET programmer. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational data, XML, and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages, or Internet browsers.

Managed VCL contains components to work with ADO.Net objects. These components are named as well as in ADO.Net and have TClr prefix (for example, TClrSqlConnection, TClrDataTable) except that ADO.Net DataSet wrapper is named TClrDatabase to distinguish it from VCL TDataSet.

ADO.Net objects could be divided on two parts:

ADO.NET cleanly factors data  access from data manipulation into discrete components that can be used separately or in tandem. ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet object in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers. The ADO.NET DataSet object can also be used independently of a .NET Framework data provider to manage data local to the application or sourced from XML.

Disconnected ADO.Net

Disconnected ADO.Net means ADO.Net DataSet that is not connected to DBMS. To create Disconnected ADO.Net application perform following steps:

  1. Place TClrDatabase component onto the form and change its name to Database.
  2. Place TClrDataTable component onto the form and change its name to DataTable.
  3. Set Database property of DataTable to component that is created on step 1.
  4. Add TDataSource and TDBGrid componets onto the form and connect them to DataTable.
  5. Set TableName property to MyTable.
  6. Add button to the form and change its name to btnCreateTable and caption to "Create table".
  7. Add this code to OnClick event:
    DataTable.FieldDefs.Add('Col1', ftInteger);
    DataTable.FieldDefs.Add('Col2', ftDateTime);
    DataTable.FieldDefs.Add('Col3', ftWideString, 20);
    DataTable.CreateTable();
  8. Add another button and change its name to btnFillTable and caption to "Fill table".
  9. Add this code to OnClick event:
    var
    	i: Integer;
    begin
    	with DataTable do begin
    		DisableControls();
    		try
    			Active := True;
    			for i:=1 to 1000 do begin
    				Append();
    				Fields[0].AsInteger := i;
    				Fields[1].AsDateTime := Now() + i/24;
    				Fields[2].AsString := Format('Row #%d', [i]);
    				Post();
    			  end;
    		  finally
    		  	EnableControls();
    		  end;
    	  end;
    end;
  10. Compile and test an application.

For more complex code , look Database\In-memory example.

In addition to TClrDataTable Managed VCL contains TClrDataView component. It corresponds to DataView .Net object and has RowFilter, RowStateFilter and Sort properties. It can be used also as detail dataset in master/detail relashionship. However as well as in ADO.Net DataView is quite slow on making changes (updates, inserts, deletes) and becomes slower when number of its rows grows.

Using ADO.Net with DBMS

Managed VCL ADO.Net components interact with DBMS with following components: TClrXXXConnection, TClrXXXCommand, TClrXXXDataAdapter, TClrXXXCommandBuilder and TClrDataReader, where XXX represent .Net provider-specific prefix such as Sql, OleDb, Odbc or Oracle.

To establish connection to DBMS TClrXXXConnection component is used. Connection parameters are passed to DBMS in form of connection string.

To execute command on DBMS TClrXXXCommand component is used. Connection property of the command must be set to valid TClrXXXConnection component. CommandType and CommandText properties represents SQL command and Parameters property is the collection of command parameters . Look examples in Examples\Command directory.

To get command results TClrDataReader component is used. It is read-only unidirectional TDataSet descendant. Only methods to get data (Field.AsString, Field.AsInteger etc.) and Next() could be used. Look examples in Examples\DataReader directory.

To fill TClrDatabase cache with query results and send updates/inserts/deletes back to DBMS TClrXXXDataAdapter is used. It has properties SelectCommand, UpdateCommand, InsertCommand and DeleteCommand. Set these properties to valid TClrXXXCommand objects. To fill TClrDatabase cache with query results use FillSchema (), Fill() and FillWithSchema() methods. To send data back to DBMS use Update() method (methods AcceptChanges()/RejectChanges() of TClrDataTable do not send any changes to DBMS but just change rows state) .

When sending changes to DBMS TClrXXXDataAdapter may use user-provided commands (UpdateCommand, InsertCommand, DeleteCommand properties of TClrXXXDataAdapter). If you want to automatically generate SQL commands, use TClrXXXCommandBuilder component. Set its DataAdapter property to valid TClrXXXDataAdapter and it will automatically generate update/insert/delete queries. When data schema changes, call RefreshSchema() method to regenerate query. You may examine generated queries with UpdateCommandText, InsertCommandText and DeleteCommandText properties.

Managed VCL ADO.Net components holds corresponding ADO.Net objects and have properties and methods that have the same names as in ADO.Net. So .Net framework documentation may provide many additional information and examples that in many cases could be easily translated to Managed VCL.

Design-time data view

Unlike other client-side cursor implementations ADO.Net stores many tables in one object (ADO.Net DataSet or Managed VCL TClrDatabase). TClrDataTable and TClrDataView work with one table from TClrDatabase.

To have live data at design time query should be cached in TClrDatabase first. To do this - place TClrXXXConnection, TClrXXXCommand, TClrXXXDataAdapter, TClrDatabase, TClrDataTable  onto the form and connect them tog ether. Right-click on TClrDatabase and choose and select "Edit database ...". In database designer load all data adapters that you want to have data at design-time. To see live data later (and to load data when form loads) set needed flags in TClrDataAdapter table mappings and InitMode property - if imFillSchema is set, FillSchema(stMapped) will be called, if imFill - Fill() will be called in TClrDatabase.Loaded() procedure.