CNClrLib.Data Namespace

The CNClrLib.Data namespace contains interfaces for accessing and managing data from diverse sources. These form the ADO.NET architecture and ADO.NET data providers. For example, providers are available for SQL Server, Oracle, ODBC, and OleDB. It contains interfaces for ADO.NET Entity Data Model (EDM) and WCF Data Services. These interfaces are defined as classes and interfaces in C# System.Data Namespaces.

DataSet Examples

The following example consists of several methods that, combined, create and fill a DataSet from the Northwind database.

Delphi

program NorthwindDataSet;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  CNClrLib.Enums,
  CNClrLib.Data,
  CNClrLib.Core;

var
  Console: _Console;

  procedure ConnectToData(connectionString: string);
  var
    connection: _SqlConnection;
    adapter: _SqlDataAdapter;
    command: _SqlCommand;
    dataSet: _DataSet;
    productsAdapter: _SqlDataAdapter;
    productsCommand: _SqlCommand;
    parentColumn,
    childColumn: _DataColumn;
    relation: _DataRelation;
  begin
    //Create a SqlConnection to the Northwind database.
    connection := CoSqlConnection.CreateInstance(connectionString);

    //Create a SqlDataAdapter for the Suppliers table.
    adapter := CoSqlDataAdapter.CreateInstance;

    // A table mapping names the DataTable.
    adapter.TableMappings.Add_2('Table', 'Suppliers');

    // Open the connection.
    connection.Open;
    Console.WriteLine_14('The SqlConnection is open.');

    // Create a SqlCommand to retrieve Suppliers data.
    command := CoSqlCommand.CreateInstance(
        'SELECT SupplierID, CompanyName FROM dbo.Suppliers;',
        connection);
    command.CommandType := CommandType_Text;

    // Set the SqlDataAdapter's SelectCommand.
    adapter.SelectCommand := command;

    // Fill the DataSet.
    dataSet := CoDataSet.CreateInstance('Suppliers');
    adapter.Fill(dataSet);

    // Create a second Adapter and Command to get
    // the Products table, a child table of Suppliers.
    productsAdapter := CoSqlDataAdapter.CreateInstance;
    productsAdapter.TableMappings.Add_2('Table', 'Products');

    productsCommand := CoSqlCommand.CreateInstance(
        'SELECT ProductID, SupplierID FROM dbo.Products;',
        connection);
    productsAdapter.SelectCommand := productsCommand;

    // Fill the DataSet.
    productsAdapter.Fill(dataSet);

    // Close the connection.
    connection.Close();
    Console.WriteLine_14('The SqlConnection is closed.');

    // Create a DataRelation to link the two tables
    // based on the SupplierID.
    parentColumn := dataSet.Tables.Item_1['Suppliers'].Columns.Item_1['SupplierID'];
    childColumn := dataSet.Tables.Item_1['Products'].Columns.Item_1['SupplierID'];
    relation := CoDataRelation.CreateInstance('SuppliersProducts', parentColumn, childColumn);
    dataSet.Relations.Add(relation);

    Console.WriteLine_15('The {0} DataRelation has been created.', relation.RelationName);
  end;

  function GetConnectionString: string;
  begin
    // To avoid storing the connection string in your code,
    // you can retrieve it from a configuration file.
    Result := 'Data Source=(local);Initial Catalog=Northwind;' +
              'Integrated Security=SSPI';
  end;

var
  connectionString: string;
begin
  try
    Console := CoConsole.CreateInstance;
    connectionString := GetConnectionString;
    ConnectToData(connectionString);
  except
    on E: Exception do
    begin
      Console.WriteLine_14(E.message);
    end;
  end;
end.

DataTable Examples

The following example creates two DataTable objects and one DataRelation object, and adds the new objects to a DataSet.

Delphi

program DataTableExample;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  CNClrLib.Data,
  CNClrLib.Enums,
  CNClrLib.Host,
  CNClrLib.Core;

var
  Console: _Console;
  dataSet: _DataSet;


  procedure MakeParentTable;
  var
    table: _DataTable;
    column: _DataColumn;
    row: _DataRow;
    PrimaryKeyColumns: _DataColumnArray;
    I: Integer;
  begin
    // Create a new DataTable.
    table := CoDataTable.CreateInstance('ParentTable');

    // Create new DataColumn, set DataType,
    // ColumnName and add to DataTable.
    column := CoDataColumn.CreateInstance;
    column.DataType := TClrAssembly.GetType('System.Int32');
    column.ColumnName := 'id';
    column.ReadOnly := true;
    column.Unique := true;
    // Add the Column to the DataColumnCollection.
    table.Columns.Add(column);

    // Create second column.
    column := CoDataColumn.CreateInstance;
    column.DataType := TClrAssembly.GetType('System.String');
    column.ColumnName := 'ParentItem';
    column.AutoIncrement := false;
    column.Caption := 'ParentItem';
    column.ReadOnly := false;
    column.Unique := false;
    // Add the column to the table.
    table.Columns.Add(column);

    // Make the ID column the primary key column.
    PrimaryKeyColumns := CoDataColumnArray.CreateInstance(1);
    PrimaryKeyColumns[0] := table.Columns.Item_1['id'];
    table.PrimaryKey := PrimaryKeyColumns;

    // Instantiate the DataSet variable.
    dataSet := CoDataSet.CreateInstance;
    // Add the new DataTable to the DataSet.
    dataSet.Tables.Add(table);

    // Create three new DataRow objects and add
    // them to the DataTable
    for I := 0 to 2 do
    begin
      row := table.NewRow;
      row.Item_1['id'] := i;
      row.Item_1['ParentItem'] := 'ParentItem ' + i.ToString;
      table.Rows.Add(row);
    end;
  end;

  procedure MakeChildTable;
  var
    table: _DataTable;
    column: _DataColumn;
    row: _DataRow;
    I: Integer;
  begin
    // Create a new DataTable.
    table := CoDataTable.CreateInstance('childTable');

    // Create first column and add to the DataTable.
    column := CoDataColumn.CreateInstance;
    column.DataType := TClrAssembly.GetType('System.Int32');
    column.ColumnName := 'ChildID';
    column.AutoIncrement := true;
    column.Caption := 'ID';
    column.ReadOnly := true;
    column.Unique := true;

    // Add the column to the DataColumnCollection.
    table.Columns.Add(column);

    // Create second column.
    column := CoDataColumn.CreateInstance;
    column.DataType := TClrAssembly.GetType('System.String');
    column.ColumnName := 'ChildItem';
    column.AutoIncrement := false;
    column.Caption := 'ChildItem';
    column.ReadOnly := false;
    column.Unique := false;
    table.Columns.Add(column);

    // Create third column.
    column := CoDataColumn.CreateInstance;
    column.DataType := TClrAssembly.GetType('System.Int32');
    column.ColumnName := 'ParentID';
    column.AutoIncrement := false;
    column.Caption := 'ParentID';
    column.ReadOnly := false;
    column.Unique := false;
    table.Columns.Add(column);

    dataSet.Tables.Add(table);

    // Create three sets of DataRow objects,
    // five rows each, and add to DataTable.
    for I := 0 to 4 do
    begin
      row := table.NewRow;
      row.Item_1['childID'] := i;
      row.Item_1['ChildItem'] := 'Item ' + i.ToString;
      row.Item_1['ParentID'] := 0;
      table.Rows.Add(row);
    end;

    for I := 0 to 4 do
    begin
      row := table.NewRow;
      row.Item_1['childID'] := i + 5;
      row.Item_1['ChildItem'] := 'Item ' + i.ToString;
      row.Item_1['ParentID'] := 1;
      table.Rows.Add(row);
    end;

    for I := 0 to 4 do
    begin
      row := table.NewRow;
      row.Item_1['childID'] := i + 10;
      row.Item_1['ChildItem'] := 'Item ' + i.ToString;
      row.Item_1['ParentID'] := 2;
      table.Rows.Add(row);
    end;
  end;

  procedure MakeDataRelation;
  var
    parentColumn,
    childColumn: _DataColumn;
    relation: _DataRelation;
  begin
    // DataRelation requires two DataColumn
    // (parent and child) and a name.
    parentColumn := dataSet.Tables.Item_1['ParentTable'].Columns.Item_1['id'];
    childColumn := dataSet.Tables.Item_1['ChildTable'].Columns.Item_1['ParentID'];
    relation := CoDataRelation.CreateInstance('parent2Child', parentColumn, childColumn);
    dataSet.Tables.Item_1['ChildTable'].ParentRelations.Add(relation);
  end;

  procedure MakeDataTables;
  begin
    // Run all of the functions.
    MakeParentTable;
    MakeChildTable;
    MakeDataRelation;
  end;

begin
  try
    MakeDataTables;
  except
    on E: Exception do
    begin
      Console.WriteLine_14(E.message);
    end;
  end;
end.

DataReader Examples

The following code example iterates through a DataReader object, and returns two columns from each row.

Delphi

program DataReaderExample;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  CNClrLib.Data,
  CNClrLib.Core;

var
  Console: _Console;

  procedure HasRows(connection: _SqlConnection);
  var
    command: _SqlCommand;
    reader: _SqlDataReader;
  begin
    command := CoSqlCommand.CreateInstance('SELECT CategoryID, CategoryName FROM Categories;',
      connection);
    connection.Open();

    reader := command.ExecuteReader;

    if reader.HasRows then
    begin
      while reader.Read do
      begin
        Console.WriteLine_17('{0}' + Chr(9) + '{1}', reader.GetInt32(0), reader.GetString(1));
      end;
    end
    else
    begin
      Console.WriteLine_14('No rows found.');
    end;

    reader.Close();
  end;

  function GetConnectionString: string;
  begin
    // To avoid storing the connection string in your code,
    // you can retrieve it from a configuration file.
    Result := 'Data Source=(local);Initial Catalog=Northwind;' +
              'Integrated Security=SSPI';
  end;

var
  connection: _SqlConnection;
begin
  try
    Console := CoConsole.CreateInstance;
    connection:= CoSqlConnection.CreateInstance(GetConnectionString);
    HasRows(connection);
  except
    on E: Exception do
    begin
      Console.WriteLine_14(E.message);
    end;
  end;
end.

What do you think about this topic? Send feedback!