ADO.Net VCL for Delphi

On this page you can learn more about the CrystalNet ADO.Net VCL for Delphi. For more information about the product you are interested in, click on the learn more button or navigate to one of its information pages.

Buy Download




ADO.Net VCL for Delphi is the fastest and most reliable database connectivity solutions for any database using ADO.Net technology in Delphi. It provides data access to data sources such as SQL Server, Oracle, SQLite and to data sources exposed through OLE DB and ODBC using ADO.Net data provider framework. ADO.Net VCL for Delphi is an enterprise solution with highest performance and unlimited possibilities. Data-sharing consumer applications can use ADO.NET VCL for Delphi to connect to these data sources and retrieve, handle, and update the data that they contain.

With its powerful common architecture base on ADO.Net technology, ADO.Net VCL for Delphi enables high-speed direct data access from Delphi to SQL Server, Oracle, SQLite and other data sources exposed through OLE DB and ODBC.

ADO.Net VCL for Delphi provides a library of components for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in a Virtual Table components. The Virtual Table component can also be used independently of any active connection to manage data local to the application or sourced from XML.

You have the choice of either using client-side or server-side cursors. The ADO.Net VCL for Delphi actually handle the work of cursors. The developer has the freedom of choice in development, for creating efficient applications.

ADO.Net VCL for Delphi Architecture


The ADO.Net VCL for Delphi consists of the following library of Data Access components:

Note: Direct Data Access components for the following data sources will be available in future: MySQL, Firebird, BD2, Informix etc. However, you can still connect to these data sources using ODBC Data Access Components or OLE DB Data Access Components provided ODBC drivers or OLE DB providers are installed on the client machine.


SqlClient Data Access Components

SqlClient Data Access Components (SqlClientDAC) is a feature-rich and high-performance library of components that provides native connectivity to SQL Server from Delphi for both 32-bit and 64-bit Windows platforms. SqlClientDAC-based applications connect to SQL Server directly through the ADO.Net SqlClient, which is a .NET Framework Data Provider for SQL Server. SqlClientDAC aims to assist programmers in developing of fast and native SQL Server database applications. SqlClientDAC uses its own protocol to communicate with SQL Server and it is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer.

Learn more »


OracleClient Data Access Components

OracleClient Data Access Components (OracleClientDAC) is a feature-rich and high-performance library of components that provides native connectivity to Oracle from Delphi for both 32-bit and 64-bit Windows platforms. OracleClientDAC-based applications connect to Oracle directly through the ADO.Net OracleClient, which is an Oracle Data Provider for .NET (ODP.Net). OracleClientDAC aims to assist programmers in developing of fast and native Oracle database applications. OracleClientDAC allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, self-tuning statement cache, and fast connection failover.

Learn more »


SQLite Data Access Components

SQLite Data Access Components (SQLiteDAC) is a feature-rich and high-performance library of components that provides native connectivity to SQLite from Delphi for both 32-bit and 64-bit Windows platforms. SQLiteDAC-based DB applications are easy to deploy, do not require installation of other data provider layers (such as BDE or ODBC), and that's why they can work faster than the ones based on standard Delphi data connectivity solutions. SQLiteDAC-based applications connect to SQLite directly through the SQLite.Net, which is an ADO.NET provider for SQLite.

Learn more »


ODBC Data Access Components

ODBC Data Access Components (ODBCDAC) is a feature-rich and high-performance library of components that provides data access for data sources exposed through ODBC from Delphi for both 32-bit and 64-bit Windows platforms. ODBCDAC-based applications connects to any data source exposed through ODBC using the .NET Framework Data Provider for ODBC. ODBCDAC aims to assist programmers in developing of fast and native database applications. ODBCDAC uses the native ODBC Driver Manager (DM) to enable data access.

ODBCDAC requires the x86 or x64 ODBC driver to be installed according to ODBC specification. Please see the following ODBC documentation:

Learn more »


OLE DB Data Access Components

OLE DB Data Access Components (OLEDBDAC) is a feature-rich and high-performance library of components that provides data access for data sources exposed through OLE DB from Delphi for both 32-bit and 64-bit Windows platforms. OLEDBDAC aims to assist programmers in developing of fast and native database applications. OLEDBDAC uses native OLE DB through COM interop to enable data access.

OLEDBDAC does not support OLE DB version 2.5 interfaces. OLE DB Providers that require support for OLE DB 2.5 interfaces will not function correctly with the OLE DB Data Access Components. This includes the Microsoft OLE DB provider for Exchange and the Microsoft OLE DB provider for Internet Publishing.

OLEDBDAC does not work with the OLE DB provider for ODBC (MSDASQL). To access an ODBC data source, use the ODBC Data Access Components.

Learn more »


Virtual Table Components

Virtual Table Components is a feature-rich and high-performance library of components that provides an in-memory cache of data retrieved from a data source. It offers a disconnected data source architecture which means there is no need of active connections during work with this component. It can be used to hold multiple tables with data. You can select data form tables, create views based on table. The component also provides you with rich features like saving data as XML and loading XML data.

Learn more »

Main Features

Direct Mode

Allows your application to work with SQL Server, Oracle, SQLite and other data sources through ODBC or OLE DB providers directly without involving a DB Client library, thus significantly facilitating deployment and configuration of your applications.

Performance

All our components and libraries are designed to help you write high-performance, lightweight data access layers, therefore they use ado.net data access technologies and techniques of optimization.

Handling Cursors

When a SQL command is executed and it has to return rows, the DBMS creates a cursor on the DBMS server. An application uses the cursor to retrieve rows from a database. There are serveral cursor kinds to choose from using the FetchOptions.CursorKind property.

Rowset Fetching

Rowset Fetching allows you to specify the number of records that will be fetched from the server in one network round trip. You can optimize this separately for each SELECT statement that you execute, thereby minimizing the number of network round trips by specifying the RowsetSize option. The rowset size is controlled by the FetchOptions.RowsetSize property and the rowsets are fetched according to the FetchOptions.Mode property.

DataReader Provider

DataReader is used to read the data from database and it is a read and forward only connection oriented architecture when fetching data from database. DataReader will fetch the data very fast when compared to other TCustomClrDataSet descendants.

DataAdapter Provider

DataAdapter acts as a Bridge between TCustomClrDataSet descendants and database. This dataadapter object is used to read the data from database and bind that data to TCustomClrDataSet descendants. It is a disconnected oriented architecture.

IDE Compatibility

Our product is compatible with the latest IDE versions: Embarcadero RAD Studio 10.2 Tokyo. It is also compatible with the previous IDE versions since CodeGear Delphi 2009.

DB Compatibility

Our product supports the latest versions of the popular database servers like Oracle, SQL Server, SQLite and other data sources (using ODBC and OLE DB providers) such as MySQL, InterBase, Firebird, PostgreSQL etc.

Support

Whether you are experiencing product-related problems, found a bug, or have questions related to this product or for any reason, feel free to contact us via our Contact Form page, our dedicated support specialists are here to help you!

ADO.Net VCL includes the following components:

SqlClientDAC components

SqlClientConnection

TSqlClientConnection
Lets you set up and control connections to SQL Server database.

SqlClientCommand

TSqlClientCommand
Executes a Transact-SQL statement or stored procedure against a SQL Server database.

SqlClientDataReader

TSqlClientDataReader
Provides a way of reading a forward-only stream of rows from a SQL Server database.

SqlClientDataAdapter

TSqlClientDataAdapter
Lets you fill Virtual Table components with data and update a SQL Server database.

SqlClientBulkCopy

TSqlClientBulkCopy
Lets you efficiently bulk load a SQL Server table with data from another source.

SqlClientQuery

TSqlClientQuery
Uses SQL statements to retrieve data from tables in a SQL Server database and pass it to one or more data-aware components through a TDataSource object.
This component provides a mechanism for updating data.

SqlClientTable

TSqlClientTable
Lets you retrieve and update data in a single table in a SQL Server database without writing SQL statements.

SqlClientStoredProc

TSqlClientStoredProc
Executes stored procedures and functions in a SQL Server database. Lets you edit cursor data returned as parameter.

OracleClientDAC components

OracleClientConnection

TOracleClientConnection
Lets you set up and control connections to Oracle database.

OracleClientCommand

TOracleClientCommand
Executes a Transact-SQL statement or stored procedure against an Oracle database.

OracleClientDataReader

TOracleClientDataReader
Provides a way of reading a forward-only stream of rows from an Oracle database.

OracleClientDataAdapter

TOracleClientDataAdapter
Lets you fill Virtual Table components with data and update an Oracle database.

OracleClientQuery

TOracleClientQuery
Uses SQL statements to retrieve data from tables in an Oracle database and pass it to one or more data-aware components through a TDataSource object.
This component provides a mechanism for updating data.

OracleClientTable

TOracleClientTable
Lets you retrieve and update data in a single table in an Oracle database without writing SQL statements.

OracleClientStoredProc

TOracleClientStoredProc
Executes stored procedures and functions in an Oracle database. Lets you edit cursor data returned as parameter.

SQLiteDAC components

SqliteConnection

TSQLiteConnection
Lets you set up and control connections to SQLite database.

SqliteCommand

TSQLiteCommand
Executes a Transact-SQL statement or stored procedure against a SQLite database.

SqliteDataReader

TSQLiteDataReader
Provides a way of reading a forward-only stream of rows from a SQLite database.

SqliteDataAdapter

TSQLiteDataAdapter
Lets you fill Virtual Table components with data and update a SQLite database.

SqlitetQuery

TSQLiteQuery
Uses SQL statements to retrieve data from tables in a SQLite database and pass it to one or more data-aware components through a TDataSource object.
This component provides a mechanism for updating data.

SqliteTable

TSQLiteTable
Lets you retrieve and update data in a single table in a SQLite database without writing SQL statements.

ODBCDAC components

OdbcConnection

TOdbcConnection
Lets you set up and control connections to a data source through ODBC drivers.

OdbcCommand

TOdbcCommand
Executes a Transact-SQL statement or stored procedure against a data source.

OdbcDataReader

TOdbcDataReader
Provides a way of reading a forward-only stream of rows from a data source.

OdbcDataAdapter

TOdbcDataAdapter
Lets you fill Virtual Table components with data and update a data source.

OdbcQuery

TOdbcQuery
Uses SQL statements to retrieve data from tables in a data source and pass it to one or more data-aware components through a TDataSource object.
This component provides a mechanism for updating data.

OdbcTable

TOdbcTable
Lets you retrieve and update data in a single table in a data source without writing SQL statements.

OdbcStoredProc

TOdbcStoredProc
Executes stored procedures and functions in a data source. Lets you edit cursor data returned as parameter.

OLEDBDAC components

OleDbConnection

TOleDbConnection
Lets you set up and control connections to a data source through OLE DB provider.

OleDbCommand

TOleDbCommand
Executes a Transact-SQL statement or stored procedure against a data source.

OleDbDataReader

TOleDbDataReader
Provides a way of reading a forward-only stream of rows from a data source.

OleDbDataAdapter

TOleDbDataAdapter
Lets you fill Virtual Tables components with data and update a data source.

OleDbQuery

TOleDbQuery
Uses SQL statements to retrieve data from tables in a data source and pass it to one or more data-aware components through a TDataSource object.
This component provides a mechanism for updating data.

OleDbTable

TOleDbTable
Lets you retrieve and update data in a single table in a data source without writing SQL statements.

OleDbStoredProc

TOleDbStoredProc
Executes stored procedures and functions in a data source. Lets you edit cursor data returned as parameter.

Virtual Table components

ClrMemTable

TClrMemTable
Use TClrMemTable to manage data in the client memory and optionally exchange the data with a data source. It offers a disconnected data source architecture which means there is no need of active connections during work with this component.

ClrDataTable

TClrDataTable
Represent an in-memory cache of data retrieved from a data source. It offers a disconnected data source architecture which means there is no need of active connections during work with this component.

Pre-requisite

Supported Target Platforms

  • Windows, 32-bit and 64-bit

General usability

  • Direct access to server data without using client library. Does not require installation of other data provider layers (such as BDE)
  • Interface compatible with standard data access methods, such as ADO
  • Bulk copying of data from a data source to Sql server database using TSqlClientBulkCopy component
  • Supports the posting of deferred updates, so called Cached Updates
  • Provides methods to sort the records. The dataset sorting allows you to get the required records order without requiring a database
  • Provides methods to filter the records. The dataset filtering allows you to get record subsets based on a condition
  • Provides flexible support for master-detail relationships between datasets
  • Support prefetching rows - specifies the number of rows that are to be fetched per server round trip. This is controlled by FetchOptions.RowsetSize property
  • Unicode support
  • Easy to deploy

Network and connectivity

  • Disconnected mode with automatic connection control for working with data offline
  • Transmission is via XML format, therefore can pass through firewalls

Compatibility

  • Full support of the latest server versions
  • Compatible with Code Gear RAD Studio 2009, Embarcadero RAD Studio 2010, XE, XE2, XE3, XE4, XE5, XE6, XE7, XE8, Seattle, Berlin, Tokyo
  • Support for all standard and third-party visual data-aware controls

Performance

  • High performance access to the data source
  • Fast controlled fetch of large data blocks
  • Advanced connection pooling
  • High performance of applying cached updates with batches controlled by UpdateOptions.UpdateBatchSize property.
  • Fast locate in a sorted DataSet
  • High performance batch processing
  • Intelligent fetch block size control
  • Rowset fetching support using TDbRecordSet Class which mimics the behaviour of ADO RecordSet Object using ADO.Net technology
  • ADO.NET VCL components uses XML to store and transfer data among applications, which provide fast access of data for desktop and distributed applications

Local data storage operations

  • Database-independent data storage with TClrMemtable and TClrDataTable components
  • CachedUpdates operation mode controlled UpdateOptions.UpdateMode property
  • Local sorting and filtering, including by calculated and lookup fields
  • Local master/detail relationship

Data access and data management automation

  • Automatic data updating with Query, Table, and StoredProc components
  • Fast-forward data retrieval with DataReader component
  • Automatic record refreshing and locking
  • Automatic query preparing

Data exchange

  • Transferring data from all types of TCustomClrDataSet descendants, Xml etc to a Sql Server database using TSqlClientBulkCopy component
  • Data export and import to/from XML

Product clarity

  • Documentation to get you started is available
  • A large amount of helpful demo projects

Licensing and support

  • Free license, only annual ADO.Net DAC Subscription with Priority Support is required
  • Royalty-free

ADO.Net VCL Compatibility

Database Server Compatibility

Database

Oracle - OracleClientDAC

Servers: 12c, 11g, 10g, 9i, 8i, 8.0,
including Oracle Express Edition 11g and 10g

Clients: 12c, 11g, 10g, 9i, 8i, 8.0

Microsoft SQL Server - SqlClientDAC

Servers:
SQL Server 2016, 2014, 2012, 2008 R2, 2008, 2005 (including Express edition)
SQL Server 2000 (including MSDE)
SQL Server 7
Clients:
SQL OLE DB and SQL Native Client

SQLite - SQLiteDAC

Database file with any of the following extensions:
.db3, .s3db, .sqlite3, .sl3,
.db2, .s2db, .sqlite2, .sl2,
.db, .sdb, .sqlite, .sl

Any database using OBDC driver - ODBCDAC

Microsoft-Supplied ODBC Drivers:
  • Microsoft Access Drivers
    • Microsoft Access Driver (*.mdb, *.accdb)
    • Microsoft Access Driver (*.mdb)
  • Microsoft Excel Drivers
    • Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
    • Microsoft Excel Driver (*.xls)
  • Paradox Drivers
    • Microsoft Paradox Driver (*.db)
  • Dbase Drivers
    • Microsoft dBase Driver (*.dbf)
  • Text Files
    • Microsoft Text Driver (*.txt; *.csv)
  • ODBC Driver for Oracle
    • Microsoft ODBC for Oracle
  • Microsoft SQL Server
    • SQL Server Native Client 11.0
    • SQL Server Native Client 10.0
    • SQL Native Client
    • ODBC Driver 11 for SQL Server
    • SQL Server

Any database using OLE DB provider - OLEDBDAC

Microsoft-Supplied OLE DB Providers:
Other OLE DB providers:


IDE Compatibility

ADO.Net VCL Components is compatible with the following IDEs:

  • Embarcadero RAD Studio 10.2 Tokyo
    • Embarcadero Delphi 10.2 Tokyo for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio 10.1 Berlin
    • Embarcadero Delphi 10.1 Berlin for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio 10 Seattle
    • Embarcadero Delphi 10 Seattle for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE8
    • Embarcadero Delphi XE8 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE7
    • Embarcadero Delphi XE7 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE6
    • Embarcadero Delphi XE6 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE5
    • Embarcadero Delphi XE5 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE4
    • Embarcadero Delphi XE4 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE3
    • Embarcadero Delphi XE3 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE2
    • Embarcadero Delphi XE2 for Windows 32-bit & 64-bit
  • Embarcadero RAD Studio XE
    • Embarcadero Delphi XE
  • Embarcadero RAD Studio 2010
    • Embarcadero Delphi 2010
  • CodeGear RAD Studio 2009
    • CodeGear Delphi 2009

Supported Target Platforms

  • Windows, 32-bit and 64-bit


4.0.2.0 04-Jun-2018
  • Stability and performance improvements
  • The product is no longer FREE.

4.0.1.0 15-Jan-2018
  • Added SQLite Data Access components
  • Improved data fetching process performance

4.0.0.0 23-Dec-2017
  • New design, structure and implementation of the ADO.Net VCL components for Delphi
  • Intelligent fetch block size control
  • High performance batch processing
  • Prefetching rows - specifies the number of rows that are to be fetched per server round trip.
  • Bulk Copy Operation
  • High overall performance
  • Batch Update
  • Fast controlled fetch of large data blocks
  • Supports Delphi 2009, 2010, XE, XE2, XE3, XE4, XE5, XE6, XE7, XE8, 10 Seattle, 10.1 Berlin, 102.2 Tokyo
  • Supports .Net Framework 4.5 and above
  • Unicode support
  • No longer support Delphi 7
  • Usability improvements
  • Stability and performance improvements

3.0.0.0 03-Mar-2015
  • Supports Delphi 2009, 2010, XE, XE2, XE3, XE4, XE5, XE6, XE7, XE8

2.0.0.0 21-Aug-2014
  • Support .Net Framework 3.0
  • Usability improvements
  • Stability and performance improvements

1.0.0.0 14-Mar-2013
  • First release of ADO.Net VCL for Delphi
  • Supports only Delphi 7









The code listings in this topic demonstrate how to retrieve data from a database by using the following ADO.NET VCL Components:

Examples

The following code listings demonstrate how to retrieve data from a database using TDbDataReader descendants component.

SqlClientDAC

The code in this example assumes that you can connect to the Northwind sample database on Microsoft SQL Server. The code creates a TSqlClientDataReader to select rows from the Products table, adding a parameter to restrict the results to rows with a UnitPrice greater than the specified parameter value, in this case 5. The code executes the command using the TSqlClientDataReader, and displays the results in the console window.

Delphi
SQlClientDAC Example program SqlClientExample;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, CNClrLib.ADONet.SqlClient;

var
  queryString: string;
  paramValue: Integer;
  connection: TSqlClientConnection;
  reader: TSqlClientDataReader;
begin
  try
    // Provide the query string with a parameter placeholder.
    queryString := 'SELECT ProductID, UnitPrice, ProductName from dbo.products '
            + 'WHERE UnitPrice > :pricePoint '
            + 'ORDER BY UnitPrice DESC';

    // Specify the parameter value.
    paramValue := 5;

    // Create and open the connection.
    connection := TSqlClientConnection.Create(nil);
    try
      // Setup connectionstring
      with connection.Params do
      begin
        DataSource := '(local)';
        InitialCatalog := 'Northwind';
        IntegratedSecurity := True;
      end;

      // OR you can set sql connectionstring to the connectionstring property of TSqlClientConnection

      connection.ConnectionString := 'Data Source=(local);Initial Catalog=Northwind;Integrated Security=true';

      // Open connection
      connection.Open;

      // Create the DataReader command.
      reader := TSqlClientDataReader.Create(nil);
      try
        reader.Connection := connection;
        reader.CommandText.Text := queryString;
        reader.ParamByName('pricePoint').AsInteger := paramValue;
        reader.Open;
        while reader.Read do
        begin
          Writeln(Format(chr(9) + '%s' + chr(9) + '%s' + chr(9) + '%s',
            [reader.GetString(0), reader.GetString(1), reader.GetString(2)]));
        end;

        reader.Close;
      finally
        reader.Free;
      end;
    finally
      connection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  ReadLn;
end.

OracleClientDAC

The code in this example assumes a connection to DEMO.CUSTOMER on an Oracle server. The code executes the command using the TOracleClientDataReader, and displays the results in the console window.

Delphi
OracleClientDAC Example program OracleClientExample;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, CNClrLib.ADONet.OracleClient;

var
  queryString: string;
  connection: TOracleClientConnection;
  reader: TOracleClientDataReader;
begin
  try
    // Provide the query string with a parameter placeholder.
    queryString := 'SELECT CUSTOMER_ID, NAME FROM CUSTOMER';

    // Create and open the connection.
    connection := TOracleClientConnection.Create(nil);
    try
      // Setup connectionstring
      connection.LoginPrompt := False;
      with connection.Params do
      begin
        DataSource := 'ThisOracleServer';
        UserID := 'DEMO';
        Password := 'ThisOraclePassword';
      end;
      // OR you can set sql connectionstring to the connectionstring property of TSqlClientConnection

      connection.ConnectionString := 'Data Source=ThisOracleServer;User ID=DEMO;Password=ThisOraclePassword';

      // Open connection
      connection.Open;

      // Create the DataReader command.
      reader := TOracleClientDataReader.Create(nil);
      try
        reader.Connection := connection;
        reader.CommandText.Text := queryString;
        reader.Open;
        while reader.Read do
        begin
          Writeln(Format(chr(9) + '%s' + chr(9) + '%s', [reader.GetString(0), reader.GetString(1)]));
        end;

        reader.Close;
      finally
        reader.Free;
      end;
    finally
      connection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  ReadLn;
end.

SQLiteDAC

The code in this example assumes a connection to SQLite Northwind data file. The code executes the command using the TSQLiteDataReader, and displays the results in the console window.

Delphi
SQLiteDAC Example program SQLiteExample;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, CNClrLib.ADONet.SQLite;

var
  queryString: string;
  paramValue: Integer;
  connection: TSQLiteConnection;
  reader: TSQLiteDataReader;
begin
  try
    // Provide the query string with a parameter placeholder.
    queryString := 'SELECT Id, UnitPrice, ProductName from product '
            + 'WHERE UnitPrice > :pricePoint '
            + 'ORDER BY UnitPrice DESC';

    // Specify the parameter value.
    paramValue := 5;

    // Create and open the connection.
    connection := TSQLiteConnection.Create(nil);
    try
      connection.LoginPrompt := False;
      connection.ConnectionString := 'Data Source=c:\Data\Northwind.sqlite;Version=3;Password=myPassword;';
      // OR
      connection.Params.ConnectionString := 'Data Source=C:\SQLite\DB\Northwind.sqlite;Version=3;';

      // Open connection
      connection.Open;

      // Create the DataReader command.
      reader := TSQLiteDataReader.Create(nil);
      try
        reader.Connection := connection;
        reader.CommandText.Text := queryString;
        reader.ParamByName('pricePoint').AsInteger := paramValue;
        reader.Open;
        while reader.Read do
        begin
          Writeln(Format(#13#10 + '%s' + #13#10 + '%s' + #13#10 + '%s',
            [reader.GetValue(0), reader.GetValue(1), reader.GetValue(2)]));
        end;

        reader.Close;
      finally
        reader.Free;
      end;
    finally
      connection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  ReadLn;
end.

ODBCDAC

The code in this example assumes that you can connect to the Microsoft Access Northwind sample database. The code creates a TOdbcDataReader to select rows from the Products table, set the parameter to restrict the results to rows with a UnitPrice greater than the specified parameter value, in this case 5. The code executes the command by using a TOdbcDataReader, and displays the results in the console window.

Delphi
OdbcDAC Example program OdbcExample;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, CNClrLib.ADONet.Odbc;

var
  queryString: string;
  paramValue: Integer;
  connection: TOdbcConnection;
  reader: TOdbcDataReader;
begin
  try
    // Provide the query string with a parameter placeholder.
    queryString := 'SELECT ProductID, UnitPrice, ProductName from dbo.products '
            + 'WHERE UnitPrice > :pricePoint '
            + 'ORDER BY UnitPrice DESC';

    // Specify the parameter value.
    paramValue := 5;

    // Create and open the connection.
    connection := TOdbcConnection.Create(nil);
    try
      connection.LoginPrompt := False;
      connection.Driver := 'Microsoft Access Driver (*.mdb)';
      connection.ConnectionString := 'Dbq=c:\Data\Northwind.mdb;Uid=Admin;Pwd=;';
      // OR
      connection.Params.ConnectionString := 'Dbq=c:\Data\Northwind.mdb;Uid=Admin;Pwd=;';

      // Open connection
      connection.Open;

      // Create the DataReader command.
      reader := TOdbcDataReader.Create(nil);
      try
        reader.Connection := connection;
        reader.CommandText.Text := queryString;
        reader.ParamByName('pricePoint').AsInteger := paramValue;
        reader.Open;
        while reader.Read do
        begin
          Writeln(Format(#13#10 + '%s' + #13#10 + '%s' + #13#10 + '%s',
            [reader.GetString(0), reader.GetString(1), reader.GetString(2)]));
        end;

        reader.Close;
      finally
        reader.Free;
      end;
    finally
      connection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  ReadLn;
end.

OLEDBDAC

The code in this example assumes that you can connect to the Microsoft Access Northwind sample database. The code creates a TOleDbDataReader to select rows from the Products table, set the parameter to restrict the results to rows with a UnitPrice greater than the specified parameter value, in this case 5. The code executes the command by using a TOleDbDataReader, and displays the results in the console window.

Delphi
OleDbDAC Example program OleDbExample;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, CNClrLib.ADONet.OleDb;

var
  queryString: string;
  paramValue: Integer;
  connection: TOleDbConnection;
  reader: TOleDbDataReader;
begin
  try
    // Provide the query string with a parameter placeholder.
    queryString := 'SELECT ProductID, UnitPrice, ProductName from dbo.products '
            + 'WHERE UnitPrice > :pricePoint '
            + 'ORDER BY UnitPrice DESC';

    // Specify the parameter value.
    paramValue := 5;

    // Create and open the connection.
    connection := TOleDbConnection.Create(nil);
    try
      connection.LoginPrompt := False;
      connection.Provider := 'Microsoft.Jet.OLEDB.4.0';
      connection.ConnectionString := 'Data Source=c:\Data\Northwind.mdb;User Id=admin;Password=;';
      // OR
      connection.Params.ConnectionString := 'Data Source=c:\Data\Northwind.mdb;User Id=admin;Password=;';

      // Open connection
      connection.Open;

      // Create the DataReader command.
      reader := TOleDbDataReader.Create(nil);
      try
        reader.Connection := connection;
        reader.CommandText.Text := queryString;
        reader.ParamByName('pricePoint').AsInteger := paramValue;
        reader.Open;
        while reader.Read do
        begin
          Writeln(Format(#13#10 + '%s' + #13#10 + '%s' + #13#10 + '%s',
            [reader.GetString(0), reader.GetString(1), reader.GetString(2)]));
        end;

        reader.Close;
      finally
        reader.Free;
      end;
    finally
      connection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  ReadLn;
end.

Virtual Table

The code in this example creates TClrMemTable, add field Definitions to the TClrMemTable object, open the dataset and insert data into the dataset and displays the results in the console window.

Delphi
VirtualTable Example program VirtualTable;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  CNClrLib.ADONet.Client,
  Data.DB;

var
  table: TClrMemTable;
begin
  try
      // Here we create a DataTable with four columns.
    table := TClrMemTable.Create(nil);
    try
      with table.FieldDefs.AddFieldDef do
      begin
        Name := 'Dosage';
        DataType := ftInteger;
      end;
      with table.FieldDefs.AddFieldDef do
      begin
        Name := 'Drug';
        DataType := ftString;
      end;
      with table.FieldDefs.AddFieldDef do
      begin
        Name := 'Patient';
        DataType := ftString
      end;
      with table.FieldDefs.AddFieldDef do
      begin
        Name := 'Date';
        DataType := ftDateTime
      end;

      // Here we open the dataset
      table.Open;

      // Here we add five Records.
      table.InsertRecord([25, 'Indocin', 'David', Now]);
      table.InsertRecord([50, 'Enebrel', 'Sam', Now]);
      table.InsertRecord([10, 'Hydralazine', 'Christoff', Now]);
      table.InsertRecord([21, 'Combivent', 'Janet', Now]);
      table.InsertRecord([100, 'Dilantin', 'Melanie', Now]);

      //Display Records
      table.First;
      while not table.Eof do
      begin
        Writeln(Format(chr(9) + '%s' + chr(9) + '%s' + chr(9) + '%s' + chr(9) + '%s',
                    [table.FieldByName('Dosage').AsString, table.FieldByName('Drug').AsString,
                    table.FieldByName('Patient').AsString, table.FieldByName('Date').AsString]));
        table.Next;
      end;
    finally
      table.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Readln;
end.


To access the full source code examples, kindly download the Compiled demo zip file from the Download Page.