menu
  index  ==>  papers  ==>  db  ==>  interbase  ==>  turbo_delphi_interbase_tutorial   

Turbo Delphi Interbase Tutorial - Felix John COLIBRI.

  • abstract : developing database applications, using Turbo Delphi .Net and Interbase
  • key words : Turbo Delphi - Interbase - ADO .Net - Windows Forms
  • software used : Windows XP, Turbo Delphi
  • hardware used : Pentium 1.400Mhz, 256 M memory, 160 G hard disc
  • scope : Delphi 8, Delphi 2005, Delphi 2006, Turbo Delphi for Net
  • level : Delphi developer, Windows .NET developer
  • plan :

1 - Turbo Delphi and Interbase

We will show how to develop database applications using Turbo Delphi and the Interbase Server.

We will present:

This tutorial will not cover all aspects of Interbase development. We will not talk about:
  • stored procedures, triggers
  • transactions and handling concurrency
  • the detail of visual .NET controls (TreeView, ListView etc)
For those interested in more in depth coverage, we organize every month training classes about database development:

2 - The Client Server Architecture

All major SQL engines (Oracle, Interbase, Sql Server, MySql) use a Client Server architecture:
  • the Server code handles the data transfers between the Client applications and the hard disc
  • the Client code sends requests to the Server which then provides the requested data.
Usually the Server and the Clients are on different PCs, and communication between them is handled by network layers (TCP/IP or other).

This can be represented as follows:

  • the Server includes
    • the hard discs
    • the Server code (Interbase in our case)
    • the network layers
    image

  • one or more Clients which include:
    • the network layers (Tcp/Ip here)
    • the Client layer (the Interbase Server Client in our case)
    • data base access components (ADO .Net)
    • one or more software applications (an accounting project, a Web Portal etc)
    image

  • the Server is put in listening mode. At some time, a Client sends a request:

    image

  • the Server analyzes the request, computes the answer and returns the complete answer set in one or more packages to the Client

    image

  • naturally, there are in general several Clients connected to the same Server:

    image



To implement this scheme, we have to install:
  • the Server part of Interbase
  • the Client part of Interbase
  • the Client data access components (the .NET Framework and its ADO.NET assembly)
  • the development tool (Delphi 2006, or Turbo Delphi for .Net)
We already presented how to install Interbase. To make a long story short:
  • Interbase is shipped with most versions of Delphi. Use GOOGLE to find the Interbase download page
If that does not work, use GOOGLE do locate the Colibri Interbase tutorial paper which explains everything in detail, with snapshots and tests for every step.

The .NET Framework is installed by the Delphi installer.

The .NET Framework comes bundled with data access assemblies for handling SQL Server and Oracle, and Delphi installs the BDP. So nothing else is required.



3 - ADO.NET Architecture

We will present several Delphi projects that:
  • create and fill of database Tables
  • fetch rows from the Server and display them in DataGrids
  • let the user modify row values displayed in DataGrids, and update and reconcile those values with the Server data
Before using the data access components, let us first present the global ADO.NET architecture.



3.1 - Overall Architecture

ADO.NET is made up of 4 component categories:
  • the first layer directly talks to the data base and handles connection as well as request forwarding. These components are collectively called the DataProvider

    image

    and:

    • BdpConnection connects to the Server (address, user name, password etc)
    • BdpCommand is used to send requests (SELECT, CREATE, INSERT etc)
    • SqlDataReader is used to fetch the result of SELECT requests. The rows received from the Server can be handled by our code (for computation or display purposes)
  • an intermediate component transfers the data between the DataProvider and the components which store the data in memory: it is the BdpDataAdapter. It contains:
    • four BdpCommand components, one for each possible SQL request: SELECT, INSERT, DELETE, UPDATE  
    • a TableMappings component, allowing to replace the syntactic table names (Table1, Table2, ...) with semantic names (Invoice, Orders, ...)
    We can represent the BdpDataAdapter like this:

    image

  • the Table rows can be stored and handled in memory. Those rows are provided:
    • by the BdpDataAdapter
    • by reading a disc file (XML for example)
    • by Delphi code
    The memory storage components are called DataSet, and include:
    • a collection of DataTables
    • constraints (NOT NULL etc)
    • relations (foreign keys, cascading rules etc)
    • views, used for filtering rows, projecting columns, sorting, aggregate computation, searching etc
    Here are our memory components:

    image

  • finally, to display the rows and let the user modify them, we have
    • controls, such as TextBox, ListBox or DataGrid
    • DataBindings components which synchronize the modifications between the visual controls and in memory data
    Here is the visual part at the end of the chain:

    image

  • as already mentioned, since the DataSet contains in-memory Tables, we can
    • create the data by using Delphi code
    • save and load the data from a file (XML or other)
    image

  • and the visual controls can also display data coming from other sources than some DataSet, for example an ARRAY or a tList:

    image

  • the BdpDataAdapter plays the role of a middle man:
    • it loads the data form the Server into the DataSet. This is performed by calling:

          BdpDataAdapter.Fill(DataSet)

      image

    • it saves the modifications coming from the visual controls. This is done when we call:

          BdpDataAdapter.Update()

      image



The above figure show that the only Server dependent part is the DataProvider. In our case, which DataProvider should we use to handle SQL Server tables ?

Delphi is bundled with several DataProviders :

  • Sql Server and Oracle providers
  • an ODBC provider, which can be used to handle ODBC drivers
  • an OleDb provider. OleDb is the component set supposed to present a single interface for "all" data sources: SQL Servers, but also mail, Excel etc. So there is an ADO.NET DataProvider allowing to handle OleDb sources
  • a Borland Data Provider (BDP) which is a generalization of the ADO.NET DataProvider

    image



ADO.NET DataProviders are specified by INTERFACEs. So any component set implementing those INTERFACEs can be used.

In the case of Interbase, we can use

  • an ODBC data provider, if we have an ODBC driver for SQL Server
  • an OleDb data provider (the OleDb provider is included in the .Net Framework),
  • a BDP data provider
In red are all the routes available:

image



In this article, we will be interested in the direct programming ADO. Net, and will present the use of the the BDP for handling Interbase Tables elsewhere.



4 - ADO.NET Programming

4.1 - Tasks

We will present here: Our Interbase parameters (specified when we installed SQL Server) are the default ones:
  • user: SYSDBA
  • password: masterkey
You will use your own Interbase parameters to establish the connection.



4.2 - Create Database

4.2.1 - Create Database request

To create the database, we will use the IbConsole application:
   select "Start | Interbase| IbConsole"
   IbConsole starts

image

   right click "local server" and select "login"

   a login dialog is displayed, whith the default SYSDBA / masterkey parameters

image

   click "login"

   select "Database | New Database"

   a database creation dialog is displayed:

image

   enter the path and file name:

    C:\programs\us\db\interbase\_data\ib_order_entry.gdb

and an alias

    ib_order_entry

Click "Ok"

   the Database files are created:

image

   Close IbConsole
From now on, we will be able to perform all tasks from Turbo Delphi.



4.3 - ADO .Net connection

4.3.1 - Start Turbo Delphi

We start Turbo Delphi, and the familiar IDE window is displayed:

image



4.3.2 - The Data Explorer

We will first create a new connection entry for our new database in the Data Explorer

The Delphi IDE presents in the top right corner a notebook with a "Data Explorer" tab. The explorer contains a TreeView with all possible drivers, including Interbase, Oracle, MSSQL:

image



We can add to the "Interbase" item an entry for our new IB_ORDER_ENTRY Database:
   click on the "Interbase" line
   the existing connections will be displayed ("IbConn1" by default, and, in our case, "employee_7_connection")
   right click on "Interbase" item, select "Add New Connection"
   Delphi tells us to enter the connection name

image

   type the name that the Data Explorer will display. In our case:

  ib_order_entry_connection

and click "Ok"

   a new entry has been added to the "Interbase" database list:

image

   to enter the connection's parameters, right click on "ib_order_entry_connection" and select "modify connection"

   a connection editor is displayed
   enter the database name, the host name, the user name and the password

image

   click "Test" to check the connection

   the connection succeeds:

image

   and click "Ok" to quit


Our first test was with the Database Explorer, which is a separate utility implemented in its own .EXE. We will now build our own Delphi Projects which will be able to establish a connection to the newly created Database



4.3.3 - The BDP Connection

We will now use a BDPConnection from the Data Explorer:
   select "File | New | Windows Forms Application" to create a new application, and rename it "p_11_ib_connect_invoice"

   in the Data Explorer, select the ib_order_entry_connection, DRAG IT and DROP IT on the Form

   Delphi will create a BdpConnection1 component, with all connection parameters already initialized (pointed by the yellow arrow).

image

   to check the parameters, click the "connection_editor" link, located at the bottom of the Object Inspector (pointed by the red arrow) or alternately, right click on BdpConnection1 and select "Connection Editor"

   Delphi will open the BdpConnection1 Connection Editor:

image

   you can click "test" to check the connection


4.3.4 - The BdpConnection

In our global ADO .Net architecture, the BdpConnection is here displayed in red:

image

Dragging and droping a connection from the Data Explorer is the easiest way to create a connection to our Interbase Database.

Some developer prefer to use pure code, mainly because it is easier to see what steps are involved. So lets do a connection by code.



4.3.5 - Connecting by code

Here are the steps:
   collapse all parts of the Tool Palette, open the "Borland Data Provider" tag, and select the BdpConnection component:

image

   drag this component ON THE FORM

   Delphi will display SqlConnection1 in the nonvisual components area:

CAUTION: the BdpConnection component must be dropped on the Form, and BdpConnection2 will be displayed in the area below

   right click on BdpConnection2, select "Connection Editor", and select the "ib_order_entry_connection"


This worked, because we had created an entry in the DataBase Explorer.

If this had not been the case, we would have been forced to type the SqlConnection string by hand. This string is made of many parts, including a binary assembly signature. Using Google, we can find sites with those connection strings.

In our case, we will write code which does a manual initialisation, using the connection string from our previous examples:
   add a Button to the Form, rename it "connect_", create its Click event, and type the connection code:

const k_order_entry_database= 'IB_ORDER_ENTRY.GDB';

      k_assembly= 'assembly=Borland.Data.Interbase, Version=2.5.0.0, '
          + 'Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b';
      k_database= 'database=C:\programs\us\db\interbase\_data\'+ k_order_entry_database;
      k_user= 'username=sysdba';
      k_password= 'password=masterkey';

      k_connection_string=
                k_assembly
            + ';'+ k_database
            + ';'+ k_user
            + ';'+ k_password
            ;

var g_c_bdp_connection: BdpConnection= Nil;

procedure TWinForm.connect__Click(sender: System.Object;
    e: System.EventArgs);
  begin
    g_c_bdp_connection:= BdpConnection.Create(k_connection_string);

    with g_c_bdp_connection do
    begin
      // -- add the StateChange event
      Include(StateChange, Self.BdpConnection2_StateChange);

      Open();
    end; // with g_c_bdp_connection
  end; // connect__Click

The Include line simply adds a StateChange event to the dynamic g_c_sqlconnection component. Here is the code of the event

   in the CLASS add the event declaration:

type
  TWinForm = class(System.Windows.Forms.Form)
    public
      constructor Create;
      procedure BdpConnection2_StateChange(sender: System.Object;
          e: System.Data.StateChangeEventArgs);
    end; // TWinForm

and here is the body of the event:

procedure TWinForm.BdpConnection2_StateChange(sender: System.Object;
    e: System.Data.StateChangeEventArgs);
  begin
    display(System.String.Format('change {0} -> {1} ',
        e.OriginalState, e.CurrentState));
  end; // BdpConnection2_StateChange

Compile, run and click "connect_"

   here is the snapshot of the connection:

image


You may also add a "disconnect" button, which simple calls the BdpConnection.Close() method.



To download the source, click 11_ib_connect_invoice.zip




4.4 - Adding Tables

4.4.1 - The Table Content

We will create two tables, mimicking a tiny invoicing system:
  • the INVOICE table contains an ID, the NAME of the customer and a DATE
  • each invoice contains several items, each item being defined with an ID, a parent INVOICE NUMBER, the QUANTITY, the DESCRIPTION and the PRICE of the item
Here is a quick schema of our tables:

image



4.4.2 - The CREATE TABLE Request

The standard CREATE TABLE request which will create our INVOICE table is the following:

 
CREATE TABLE invoice
  (
     i_id INTEGER NOT NULL PRIMARY KEY,
     i_customer CHARACTER(7),
     i_date DATE
  )

Please note that

  • the "i_" prefix that we added to all Invoice field name is a personal convention


4.4.3 - Sending the CREATE TABLE request

To send the SQL request to the SQL Server, we must
  • connect to the Database using a BdpConnection component
  • use an BdpCommand component, connect it to the BdpConnection, fill in the CommandText property with our request and call the BdpConnection.ExecuteNonQuery() method
For coding purposes, SQL requests are divided in two groups:
  • the requests which modify in some way the data on the SQL Server: CREATE TABLE, DROP TABLE, ADD INDEX, INSERT rows, ALTER TABLE parameters and so on. We simply send the text of the request to the Server, and do not expect any data in return (or possibly a success / error notification code)
  • the request to retrieve some data (usually full rows, but also aggregates like COUNT, AVERAGE, SUM etc). For those requests, the Client must first allocate a reception buffer.
Because in the first case the Client only receives a code (usually an Integer) and in the second the Client must prepare a complete buffering apparatus, the methods used but all database components that we know use 2 different methods. In the case of ADO .NEt the methods are ExecuteNonQuery() and ExecuteReader().

Therefore to create new Tables, we will call ExecuteNonQuery().



Here in red are the parts of the ADO .Net architecture involved:

image



And in detail:

  • we use an BdpConnection and an BdpCommand components, and fill the CommandText property with the SQL request:

    image

  • using the ExecuteNonQuery() method, we send the request to the Sql Server which creates the Table

    image

  • the Interbase Server enventually sends back an error code


4.4.4 - The creation code

Here are the steps:
   create a new Windows Forms application, and rename it "p_21_ib_create_table"
   drop a BdpConnection component on the Form and initialize the connection string to be able to connect to our IB_ORDER_ENTRY database, as explained before. Check the connection
   drop a Button, rename it "create_invoice_", create its Click event, and add the INVOICE Table creation code:

const k_create_invoice_table=
           'CREATE TABLE invoice '+ k_new_line
         + '  ('+ k_new_line
         + '      i_id INTEGER NOT NULL PRIMARY KEY'+ k_new_line
         + '    , i_customer CHARACTER(7)'+ k_new_line
         + '    , i_date DATE'+ k_new_line
         + '  )';

procedure TWinForm.create_invoice__Click(sender: System.Object;
    e: System.EventArgs);
  var l_c_command: BdpCommand;
      l_result: Integer;
  begin
    BdpConnection1.Open();
    l_c_command:= BdpCommand.Create(k_create_invoice_table, BdpConnection1);

    l_result:= l_c_command.ExecuteNonQuery();

    BdpConnection1.Close();
  end; // create_invoice_Click

   execute, run, and click "create_invoice_"

   here is the snapshot of our project:

image
Please note that:
  • we added a "connect" and a "disconnect" Button, just to check the connection
  • we also have a "do_execute_" Checkbox: if this is not checked, clicking "create_invoice_" will merely display_the SQL Request. This allows us to check the SQL syntax, since any SQL Errors will take quite a while before we get the control back.
  • we also added TRY EXCEPT blocks, because the Exception.Message is reasonably understandable, whereas the .Net error stack is rather cryptic to us.


You can display the table in the Data Explorer
   select the Data Explorer tab, the "Interbase" line, and the "ib_order_entry_connection"
   right click on "ib_order_entry_connection" and select "Refresh"
   click on "ib_order_entry_connection"
   here is our invoice table:

image


4.4.5 - Viewing the Table Schema

We can use the BdpConnection to get back the Schema of any table. This involves an iDataReader INTERFACE which will be explained later. So we will not explain this code now, but it is included in the downloadable .ZIP file



4.4.6 - Drop a Table

To remove a Table from the database, we use the following SQL request:

 
  DROP TABLE invoice

The steps to send this request are the same as the code used for creating a table:
   drop a Button, rename it "drop_invoice_", create its Click event, and add the code allowing us to drop the invoice table:

const k_drop_invoice_table= 'DROP TABLE invoice';

procedure TWinForm.drop_invoice__Click(sender: System.Object;
    e: System.EventArgs);
  var l_c_command: BdpCommand;
      l_result: Integer;
  begin
    BdpConnection1.Open();
    l_c_command:= BdpCommand.Create(k_drop_invoice_table, BdpConnection1);
    l_result:= l_c_command.ExecuteNonQuery();
    BdpConnection1.Close();
  end; // drop_invoice__Click

   execute, run, and click "drop_invoice_"

   here is the snapshot of our project:

image


4.4.7 - The ITEM table

We also added the code to create, display the schema, and drop the ITEM table. This second table will be used to display a Master Detail relation. The code is in the .ZIP.



4.4.8 - The complete project

You can download the project here : 21_ib_create_table.zip



4.5 - Adding Rows

To add data rows to our INVOICE table, we use the following SQL request:

 
INSERT INTO invoice
  (i_id, i_customer, i_date)
  VALUES (101, 'Smith', '9/21/2006')

Using the same technique as the one used to create the Table, we could create a procedure for each row insertion.

To automate this process somehow:

  • for each row to insert, we will use a line like the following:

    fill_the_invoice(100, 'Smith',     '9/12/2006');
    fill_the_invoice(101, 'DevShop',   '9/14/2006');
    fill_the_invoice(102, 'EastMfg',   '9/14/2006');

  • the text of our generalized insert procedure is:

    procedure fill_the_invoice(p_id: Integer; p_customer, p_date: System.String);
      var l_values, l_request: System.String;
      begin
        l_values:= p_id.ToString
          + ', '''+ p_customer+ ''''
          + ', '''+ p_date+ '''';
        l_request:= 'INSERT INTO invoice '
          +  ' (i_id, i_customer, i_date) '+ k_new_line
          +  '    VALUES ('+ l_values+ ')';

        execute_non_query(do_execute_.Checked, l_request);
      end; // fill_the_invoice

  • and the generic "execute_non_query" procedure is:

    procedure TWinForm.execute_non_query(p_do_execute: Boolean;
        p_request: System.String);
      var l_c_bdp_command: BdpCommand;
          l_count: Integer;
          l_c_bdp_transaction: BdpTransaction;
      begin
        if p_do_execute
          then begin
              l_c_bdp_transaction:= BdpConnection1.BeginTransaction;
              l_c_bdp_command:= BdpCommand.Create(p_request, BdpConnection1);
              Try
                l_count:= l_c_bdp_command.ExecuteNonQuery();
                l_c_bdp_transaction.Commit;
              except
                on e: exception do
                  display_bug_stop(e.Message);
              end;
            end;
      end; // execute_non_query



Therefore:
   create a new Windows Forms project and rename it "p_22_ib_fill_table"
   drop a BdpConnection on the Form and initialize its ConnectionString
   drop a Button on the Form and create its clic event. Type the instructions which fill the table by using the procedure described above:

procedure TWinForm.fill_invoice__Click(sender: System.Object;
    e: System.EventArgs);

  procedure fill_the_invoice(p_id: Integer; p_customer, p_date: System.String);
    var l_values, l_request: System.String;
    begin
      l_values:= p_id.ToString
        + ', '''+ p_customer+ ''''
        + ', '''+ p_date+ '''';
      l_request:= 'INSERT INTO invoice '
        +  ' (i_id, i_customer, i_date) '+ k_new_line
        +  '    VALUES ('+ l_values+ ')';

      execute_non_query(do_execute_.Checked, l_request);
    end; // fill_the_invoice

  begin // fill_invoice__Click
    BdpConnection1.Open();

    fill_the_invoice(100, 'Smith',     '9/12/2006');
    fill_the_invoice(101, 'DevShop',   '9/14/2006');
    fill_the_invoice(102, 'EastMfg',   '9/14/2006');

    BdpConnection1.Close();
  end; // fill_invoice__Click

   compile, execute, click "fill_invoice_"

   here is a snapshot of our project:

image
Please note that :
  • our execute_non_query can be used for all SQL Server modification code (everything but SELECT )
  • the VALUES parameter requires some precautions:
    • strings and dates must be surrounded by quotation marks
    • if you send floating values (123.45), make sure that the decimal separator is the US dot (".") and not the one of your local Windows settings
    • the dates must be with the US format (month/day/year) and must be between quotation marks (if you forget the quotes, SQL will carries out a division !)
    All those gory details can be hidden in the fill_the_invoice procedure


We can display (and edit) the rows using the Data Explorer:
   select the Data Explorer tab, the "Interbase" line, and the ib_order_entry_connection
   right click on "ib_order_entry_connection" and select "Refresh"
   click on "ib_order_entry_connection"
   double click on "invoice"
   here is the datagrid with our rows:

image


4.5.1 - Deleting Rows

In order to carry out several tests, we added a request allowing to empty the Table.

Removing rows is performed with the DELETE request. This request very often includes a WHERE which specifies which rows should be deleted. If we forget the WHERE, all the row will be scraped ! Therefore, to delete the row with the ID 105, we could write:

 
DELETE 
  FROM invoice
  WHERE i_id= 105



In our case we want to remove all the rows. So we will use a DELETE without any WHERE. Here is the code:
   add a Button, rename it "delete_", create its Click event and write the deletion code. This code will call the execute_non_query procedure presented before:

const k_delete_all_invoice= 'DELETE FROM invoice';

procedure TWinForm.delete_invoice__Click(sender: System.Object;
    e: System.EventArgs);
  begin
    BdpConnection1.Open();

    execute_non_query(do_execute_.Checked, k_delete_all_invoice);

    BdpConnection1.Close();
  end; // delete_invoice__Click

   compile, execute, click "delete_invoice_"

   here is a snapshot of our project:

image
Note that

  • the snapshot above displays a "list_invoice_" Button. The code is in the .ZIP, but will be explained below
  • our project also contains code to insert, list and delete ITEM rows


You will find this project in 22_ib_fill_table.zip



4.5.2 - Modifying rows

To modify the value of some fields of a Table, we use the UPDATE request. To change all "Smith" names into "Martin" names, we could use:

 
UPDATE invoice
  SET i_customer= 'Martin'
  WHERE i_customer= 'Smith'



We placed the code in a new project:
   create a new project and rename it "p_23_sql_update_table"
   drop a BdpConnection on the Form and initialize it's ConnectionString as explained above  
   drop a Button on the Form and create its Click event. Type the instructions which modify some rows, for example by changing all "Smith" into "Martin":

const k_update_invoice=
            'UPDATE invoice' + k_new_line
          + '  SET i_customer= ''Martin''' + k_new_line
          + '  WHERE i_customer= ''Smith''';

procedure TWinForm.update_invoice__Click(sender: System.Object;
    e: System.EventArgs);
  begin
    BdpConnection1.Open();
    execute_non_query(do_execute_.Checked, k_update_invoice);
    BdpConnection1.Close();
  end; // update_invoice__Click

The execute_non_query procedure was explained before and was duplicated in the present code.

   compile, execute, click "update_invoice_"

   here is a snapshot of our project:

image


4.5.3 - Parameterized Queries

When we send a complex requests (that was not the case above), the Server tries to optimize the order of the computations. This optimization, for requests implying many Tables, can take hours. It is then recommended to proceed in 2 steps:
  • send a request containing the structure, but not all the literal values. The request contains placeholders for the future literal values. Since we only want the Server to compute the order of the computations (but not the final result), the request is sent using a special Prepare() call:

    image

  • the Server then computes the best way to perform the computations:

    image

  • when the Client later wishes to retrieve a result, it sends the litteral values of the parameters to the Server

    image

  • the request is now complete, and the Server computes the result and sends it back to the Client


To recap, parametrized queries are processed in two steps:
  • we send a request containing some unfilled positions, the parameters, and call Prepare()
  • later, possibly several times, we send the parameter values to the Server, and ask the Server to compute and perform the corresponding computation (an UPDATE in this paragraph, but a SELECT in the next paragraph)
Each SQL Engine and each access component set use a different syntax for specifying the parameters of parametrized queries:
  • either with a number (%1; %2, %3)
  • or implicitely by position
  • or with an arbitrary identifier (like :one, :x, :customer)
Interbase uses ?, and the position is used to match the placeholders with the BdpParameters array.

So:

  • here is a parametrized query, where the ID and CUSTOMER fields are not yet specified:

     
    UPDATE invoice
      SET i_customer= ?
      WHERE i_id= ?

    This request is sent to the Server using an BdpConnection and an BdpCommand, and calling BdpCommand.Prepare()

  • to execute the request:
    • we fill in the parameter values, using

      var my_c_parameter: SqlParameter;

      my_c_parameter:= g_c_command.Parameters.Add('i_customer', BdpType.String);
      my_c_parameter.Value:= 'Peter';

      my_c_parameter:= g_c_command.Parameters.Add('i_id', BdpType.Int32);
      my_c_parameter.Value:= 101;

    • we call BdpCommand.ExecuteNonQuery()
Note that:
  • each parameter must be explicitely created using Parameters.Add
  • the type used depends on the SQL engine and the Database access component. Our BPD BdpType.Int32 would become an FbDbType.Integer for FireBird, or a BdpType.Integer for Interbase using the BDP access components


Here is the complete code
   drop another Button on the Form, rename it "prepare_", create its Click event and write the code which prepares the request:

const k_parametrized_update_request=
            'UPDATE invoice '
          + '  SET i_customer= ?'
          + '  WHERE i_id= ? '
          ;

var g_c_bdp_command: BdpCommand;

procedure TWinForm.prepare__Click(sender: System.Object;
    e: System.EventArgs);
  begin
    g_c_bdp_command:= BdpCommand.Create(k_parametrized_update_request, BdpConnection1);;
    BdpConnection1.Open();
    g_c_bdp_command.ParameterCount:= 2;
    g_c_bdp_command.Prepare();
  end; // prepare__Click

   drop two TextBox on the Form where the User will place the ID and the CUSTOMER name

   drop another Button on the Form, rename it "execute_", create its Click event and write the code which fills in the parameters and executes the query:

procedure TWinForm.execute__Click(sender: System.Object;
    e: System.EventArgs);
  var l_c_bdp_parameter: BdpParameter;
  begin
    // -- initialize the parmeters
    l_c_bdp_parameter:= g_c_bdp_command.Parameters.Add('i_customer',
        BdpType.String);
    l_c_bdp_parameter.Size:= 7;
    l_c_bdp_parameter.Value:= customer_text_box_.Text;

    l_c_bdp_parameter:= g_c_bdp_command.Parameters.Add('i_id',
        BdpType.Int32);
    l_c_bdp_parameter.Value:= Convert.ToInt32(id_text_box_.Text);

    g_c_bdp_command.ExecuteNonQuery();
  end; // execute__Click

   execute, run, and
  • click "prepare_"
  • input an ID and a NAME
  • click "execute_"
   here is the snapshot of our project:

image


4.5.4 - Parameterized query with the BdpCommand component

We can also use the BdpCommand from the Palette. This will ease the writing of the request as well as the parameter creation:
   select the "Borland Data Provider" tab from the Palette, and click the BdpCommand component:

image

Drop the component on the Form

   select its CommandText property, and click the ellipsis "..."
   the sql request editor is displayed

image

   select INVOICE, "Update" and "Generate SQL"

   a fully parameterized query is computed

   remove all columns but CUSTOMER and ID (like the request above) and click "Ok"

   select the Parameters property, and click the ellipsis "..."
   the parameter editor is displayed

image

   remove all parameters but I_ID and I_CUSTOMER, and move I_CUSTOMER at the first position

   drop another Button on the Form, rename it "prepare_2_", create its Click event and write the code which prepares the request:

procedure TWinForm.prepare_2__Click(sender: System.Object;
    e: System.EventArgs);
  begin
    BdpConnection1.Open();
    BdpCommand1.ParameterCount:= 2;
    BdpCommand1.Prepare();
  end; // prepare_2__Click

   drop another Button on the Form, rename it "execute_2_", create its Click event and write the code which fills in the parameters and executes the query:

procedure TWinForm.execute_2__Click(sender: System.Object;
    e: System.EventArgs);
  begin
    BdpCommand1.Parameters[0].Value:= 'william';
    BdpCommand1.Parameters[1].Value:= 102;

    BdpCommand1.ExecuteNonQuery();
  end; // execute_2__Click

   execute, run, and
  • click "prepare_2"
  • click "execute_2"
   here is the snapshot of our project:

image


You will find the source code in 23_ib_update_table.zip



4.6 - Reading Data

4.6.1 - SqlDataReader

To display or otherwise handle the content of a TABLE, we must fetch the data from the Server. The components involved are:
  • an BdpConnection
  • an BdpCommand
  • an iDataReader object which allows us to read data rows
  • possibly some display controls
Here is the overall architecture, with in red the DataReader and a TextBox:

image



4.6.2 - Reading Data

To fetch data from Tables, we use the SELECT request:

 
SELECT i_id, i_customer, i_date
  FROM invoice
  WHERE i_id< 102



This request works like this:

  • we send the request to the Server:
    • we initialize an BdpCommand with the request
    • we call the BdpCommand.ExecuteReader() function
    • this call transparently allocates a one row buffer
    • and the function returns an SqlDataReader() object which will be used to fetch the rows
    image

  • the Server uses the Tables present in the FROM clause to build a new table satisfying the constraints specified in the request (which columns, which rows). This new Table, called an "answer set", is sent back to the Client:

    image

  • the SqlDataReader fetches those rows, and the Client carries out some computations on the rows. In our case, the rows are displayed in a TextBox:

    image



Here are the steps:
   create a new Windows Forms Application, rename it "p_31_ib_display_data_reader"
   drop an BdpConnection component on the Form and initialize the connection string to be able to connect to our IB_ORDER_ENTRY database, as explained before. Check the connection

   drop a Button on the Form, rename it "display_invoice_", create its Clic event and type the code which calls ExecuteReader():

const k_select_invoice= 'SELECT * FROM invoice';

procedure TWinForm.display_invoice__Click(sender: System.Object;
    e: System.EventArgs);
  var l_c_bdp_command: BdpCommand;
      l_c_reader: iDataReader;
      l_row_index: Integer;
      l_column_index: Integer;
      l_display: String;
  begin
    l_c_bdp_command:= BdpConnection1.CreateCommand();
    l_c_bdp_command.CommandText:= k_select_invoice;

    BdpConnection1.Open();
    l_c_reader:= l_c_bdp_command.ExecuteReader();

    l_row_index:= 0;
    while l_c_reader.Read() do
    begin
      l_display:= '';
      for l_column_index:= 0 to l_c_reader.FieldCount- 1 do
        l_display:= l_display+ ' '+ l_c_reader.GetValue(l_column_index).ToString;
      display(l_row_index.ToString+ ':'+ l_display);
      Inc(l_row_index);
    end; // while
    BdpConnection1.Close();
  end; // display_invoice_click

   execute, run, and click "display_invoice_"

   here is the snapshot of our project:

image


Please note that
  • we could have used a BdpCommand component from the Palette
  • we used a iEnumerator to display the rows. We also could use an iEnumerator for the fields. Since the iDataReader.FieldCount directly returns the field count, we used a FOR instead. Alternately we could use a FOREACH construct, or in a ToString function to display the full row
  • we had already used DataReaders in the preceding projects:
    • in the CREATE TABLE project
    • in the INSERT INTO, as well as the UPDATE projects


4.6.3 - Filling a DataGrid by code

We will now display the INVOICE rows in a DataGrid.

The following ADO Net components will be used:

  • BdpConnection and BdpCommand to fetch the data
  • a BdpDataAdapter to pump the data from the BdpCommand into a DataSet by calling Fill()
  • a DataSet which will contain a DataTable where ALL the rows will be stored
  • a DataGrid to display the data
This can be shown on our global architectural schema:

image

In the code:

  • we drop the components:
    • an BdpCommand, with a valid SELECT  
    • a BdpDataAdapter containing a BdpCommand property referencing the BdpCommand object
    • an empty DataSet
  • we load the data
    • by opening the connection
    • by calling BdpDataAdapter.Fill(xxx)
    • we can close connection
  • we finally connect the single DataTable of our DataSet to a DataGrid


Those are the steps:
   create a new Windows Forms application, and rename it "p_32_ib_display_datagrid_code"
   drop an BdpConnection component on the Form and initialize the connection string to be able to connect to our IB_ORDER_ENTRY database, as explained before. Check the connection
   from the "Data Controls" tag of the Tools Palette, select the DataGrid:

image

and drop it on the Form

   drop a Button, rename it "datatable_", create its Click event, and add the code which calls DataAdapter.Fill and display the data in a DataGrid:

const k_select_invoice= 'SELECT * FROM invoice';

procedure TWinForm.adapter__Click(sender: System.Object;
    e: System.EventArgs);
  var l_c_bdp_command: BdpCommand;
      l_c_data_adapter: BdpDataAdapter;
      l_c_data_set: Dataset;
      l_c_data_table_invoice_ref: DataTable;
  begin
    l_c_bdp_command:= BdpCommand.Create(k_select_invoice, BdpConnection1);

    l_c_data_adapter:= BdpDataAdapter.Create;
    l_c_data_adapter.SelectCommand:= l_c_bdp_command;

    l_c_data_set:= DataSet.Create('my_invoices');

    BdpConnection1.Open();
    l_c_data_adapter.Fill(l_c_data_set);
    BdpConnection1.Close();

    l_c_data_table_invoice_ref:= l_c_data_set.Tables[0];

    // -- view a single table in the dbGrid
    DataGrid1.DataSource:= l_c_data_table_invoice_ref;
  end; // adapter__Click

   execute, run, and click "datatable_"

   here is the snapshot of our project:

image


Some comments:
  • all the objects are created with local variables, which makes it possible to better understand which components are involved and how they interact
  • the connection needs to be open only for the loading of the DataSet. Once the data has been transfered in memory, the connection can be closed
  • it is FILL which is the key instruction. This instruction is called at runtime. If we use a BdpDataAdapter, this can also be done by toggling BpdDataAdapter.Active to True at design time (but this is not possible with the SqlDataAadapter, or the FbDataAdapter)
  • with dbExpress, the dataflow "direction" is uniform:

         tDataGrid -> tDataSource -> tClientDataset -> tDataProvider -> tSqlQuery -> tBdpConnection

    but in ADO .Net, here Fill() reverses the chaining direction:

         tDataGrid -> tDataTable -> tDataTable <== tDataAdapter -> tBdpCommand -> tBdpConnection

  • with SQL Server, the SqlCommand.CommandText may contain several SELECT requests separated by ";" :

    my_c_sql_command.CommandText:=
      'SELECT * FROM invoice ; SELECT * FROM cities';

    In this case, the DataSet would contain several DataTables : DataSet.Tables[0] and DataSet.Tables[1].

    However, this is not possible with the BdpDataAdapter. To use several DataTables in the same DataSet, uses separate BdpDataAdapters, and fill the same DataSet.

    The significant fact is that an ADO .Net DataSet contains a SET of DataTable (or, more accurately, a collection of them). In Win32 Delphi, a "DataSet" is a generic name for a tQuery and a tTable, not a set of Tables

    And this explains why we have to use DataSet.Tables[0]

  • finally we connect the DataGrid to our DataTable. We could also have connected the DataGrid to the full DataSet:

    procedure TWinForm.dataset__Click(sender: System.Object;
        e: System.EventArgs);
      var l_c_bdp_command: BdpCommand;
          l_c_bdp_dataadapter: BdpDataAdapter;
          l_c_data_set: Dataset;
      begin
        l_c_bdp_dataadapter:= BdpDataAdapter.Create;
        l_c_bdp_dataadapter.SelectCommand:= BdpCommand.Create(k_select_invoice, BdpConnection1);;

        l_c_data_set:= DataSet.Create('my_invoices');

        BdpConnection1.Open();
        l_c_bdp_dataadapter.Fill(l_c_data_set);
        BdpConnection1.Close();

        DataGrid1.DataSource:= l_c_data_set;
      end; // dataset__Click

    with the following result:

    image

    and when we click on "+":

    image

    and on "table":

    image

    Note

    • the title "my_invoice" was provided during the creation of the DataSet
    • and the navigation icon with a left arrow at the top right corner of the DataGrid enables to fold the DataTable back.
    This tree structure with only one TABLE is of little interest here, and this is why we connected the DataGrid directly to the single DataSet.Tables[0]


4.6.4 - DatGrid display with components

Instead of creating local components (for the command, the data adapter, the dataset), me can use components from the Tools Palette:
   create a new Windows Forms application, and rename it "p_33_ib_display_datagrid"
   drop an BdpConnection component on the Form and initialize the connection string to be able to connect to our IB_ORDER_ENTRY database, as explained before. Check the connection
   from the "Data Components" tab of the Tools Palette select the BdpDataAdapter:

image

and drop it on the Form

   the BdpDataAdapter1 is displayed at the side of the BdpConnection1 (yellow arrow):

image

   click on the Form, then click on BdpDataAdapter1

   the Object Inspector displays the properties of BdpDataAdapter

   click on the SelectCommand to unfold it, BdpConnection property and select BdpConnection1

   to input the SQL request, open the BdpDataAdapter configurator either by clicking the "configure BdpDataAdapter" link at the bottom of the Object Inspector (red arrow), or by right clicking on the BdpDataAdapter and selecting "configure BdpDataAdapter"

   the BdpDataAdapter configurator is displayed

image

   select INVOICE, "Select", "Generate SQL"

   the SQL request is displayed

   you may preview the data: select "Preview data | Refresh"

   the content of INVOICE is displayed

image

   more important, create and initialize the DataSet, by selecting "DataSet | New Dataset | Ok"
   a new component DataSet1 is added to the non visual area, and it is fully initialized (names etc)

   to fill the DataSet1 with the INVOICE data at design time, select BdpAdapter1, and toggle the Active property to True

   from the Tools Palette, select a DataGrid and drop it on the Form. Select its DataSource property and select DataTable1

   the INVOICE rows are displayed AT DESIGN TIME:

image


Please note that:
  • instead of letting Delphi generate the DataSet, we could instead:
    • drop a DataSet on the Form
    • fill it and connect it in a ButtonClick event
    The benefit of letting Delphi generate its own DataSet is to visualize the columns of the Table in a DataGrid
  • the BdpAdapter.CommandText has a Command Editor, which can be used to write SQL requests, and previsualize the result. However the creation of the DataSet is done with the BdpDataAdapter Configurator
  • you can also generate "typed DataSet" by selecting the link at the bottom of the Object Inspector, but we will not present this here


4.6.5 - Master Detail Relation

Displaying linked tables is not very difficult. The main point is to find how to specify this link in the requests as well as in the DataGrids.

We will display the INVOICEs and the ITEMs in two separate DataGrids:
   create a new Windows Forms application, and rename it "p_34_ib_master_detail"
   drop an BdpConnection component on the Form and initialize the connection string to be able to connect to our IB_ORDER_ENTRY database, as explained before. Check the connection
   set up the BdpDataAdapter / DataSet / DataGrid for INVOICE:
  • select a BdpDataAdapter from the Palette and drop it on the Form
  • click on the Form, then click on BdpDataAdapter1, select BdpConnection property and select BdpConnection1
  • open the BdpDataAdapter configurator by clicking the "configure BdpDataAdapter" link at the bottom of the Object Inspector
  • in the Configurator:
    • select INVOICE, "Select", "Generate SQL"
    • create and initialize the DataSet, by selecting "DataSet | New Dataset | Ok"
  • to fill the DataSet1 with the INVOICE data at design time, select BdpAdapter1, and toggle the Active property to True
  • from the Tools Palette, select a DataGrid and drop it on the Form. Select its DataSource property and select DataTable1
   now set up the BdpDataAdapter / DataSet / DataGrid for ITEM:
  • select a BdpDataAdapter from the Palette and drop it on the Form
  • click on the Form, then click on BdpDataAdapter2, select BdpConnection property and select BdpConnection1
  • open the BdpDataAdapter configurator by clicking the "configure BdpDataAdapter" link at the bottom of the Object Inspector
  • in the Configurator:
    • select ITEM, "Select", "Generate SQL"
    • here is the tricky part: we want the INVOICE DataTable to be in the same DataSet1. So select "DataSet | Existing Dataset"

      image

      and click "Ok"

  • to fill the DataSet1 with the ITEM data at design time, select BdpAdapter2, and toggle the Active property to True
  • from the Tools Palette, select a DataGrid and drop it on the Form.
   to add a Relation linking our two Tables, select DataSet1, Relations and click the ellipsis "..."
   the Relation Collection Editor is displayed

image

   add the new relation by clicking "Add"

   the Relation Editor is displayed:

image

   in the "Key Column", select I_ID, and in the "Foreign Key Columns", select IT_INVOICE_REF.

Click "Ok" and "Close"

   now specify that the second grid should display the linked data:
  • select DataGrid2
  • select its DataSource property and select DataSet1
  • select its DataMember property and select INVOICE.Relation1
   the master detail relation is displayed at design time:

image


We have kept in the downloadable 34_ib_master_detail.zip the code which builds the Relation at run time




4.7 - In Memory DataSet

4.7.1 - The DataSet Architecture

We will now examine the DataSet possibilities: filling, modifying, sorting, filtering etc. We could use a DataSet connected to and filled by a BdpDataAdapter, as we did above. It is more instructive to use an autonomous DataSet, than will be build and handled by code without any connection to the Interbase Server. But remember that all the computations could be carried out on a DataSet linked to an Interbase Server Table.

This is the part of our global architecture diagram that we will focus on:

image

The DataSet is represented in the Microsoft documentation as an aggregate containing:

  • a collection of DataTable
  • a collection of DataRelation
  • a collection of DataView