Source Code Examples

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

Eg.1: 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.

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.

Eg.2: 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.

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.

Eg.3: 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.

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.

Eg.4: 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.

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.

Eg.5: 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.

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.

Eg.6: 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.

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.