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 :
  
  • Turbo Delphi and Interbase
    
  • ADO.NET Architecture
    
  • ADO.NET Programming
    
  • Download the Sources
    

1 - Turbo Delphi and Interbase

We will present:

• the Client Server architecture
  
• the ADO.NET architecture
  
• and we will develop the following projects:
  
  • create database : how to to create a brand new Interbase database
    
  • the connection: connecting with ADO .Net
    
  • create Tables : sending SQL requests
    
  • writing data : creating, displaying and filling Tables
    
  • reading and displaying data : displaying data in a TextBox and a DataGrid
    
  • in memory data : creating and handling of DataSets and DataTables. Using DataViews and DataBindings
    
  • data modification : editing data by code and using a DataGrid, and updating and reconciliation of the SQL Server Tables
    

• stored procedures, triggers
  
• transactions and handling concurrency
  
• the detail of visual .NET controls (TreeView, ListView etc)
  

• Interbase Client Server training
  
• Delphi 2006 For .NET training where 2 days are entirely devoted to ADO.NET.
  
• Delphi ADO.NET training
  
• Delphi ASP.NET training where the database handling is done using ADO.NET, with Interbase
  

2 - The 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.
  

This can be represented as follows:

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

• 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)
    

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

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

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

• 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)
  

• Interbase is shipped with most versions of Delphi. Use GOOGLE to find the Interbase download page
  

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

• 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
  

3.1 - Overall Architecture

• the first layer directly talks to the data base and handles connection as well as request forwarding. These components are collectively called the DataProvider

  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:

• 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:

• 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:

• 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)
    

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

• 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)
    

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

        BdpDataAdapter.Update()
    

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

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 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

• create database : how to to create a brand new SQL Server database
  
• the connection: connecting with ADO .Net
  
• create Tables : sending SQL requests
  
• writing data : creating, displaying and filling Tables
  
• reading and displaying data : displaying data in a TextBox and a DataGrid
  
• in memory data : creating and handling of DataSets and DataTables. Using DataViews and DataBindings
  
• data modification : editing data by code and using a DataGrid, and updating and reconciliation of the SQL Server Tables
  

• user: SYSDBA
  
• password: masterkey
  

4.2 - Create Database

4.2.1 - Create Database request

	select "Start | Interbase| IbConsole"
	IbConsole starts
	right click "local server" and select "login"
	a login dialog is displayed, whith the default SYSDBA / masterkey parameters
	click "login"
	select "Database | New Database"
	a database creation dialog is displayed:
	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:
	Close IbConsole

4.3 - ADO .Net connection

4.3.1 - Start Turbo Delphi

4.3.2 - 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:

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
	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:
	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
	click "Test" to check the connection
	the connection succeeds:
	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

	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).
	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:
	you can click "test" to check the connection

4.3.4 - The BdpConnection

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

	collapse all parts of the Tool Palette, open the "Borland Data Provider" tag, and select the BdpConnection component:
	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"

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:

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

• 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
  

4.4.2 - The CREATE TABLE Request

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

• 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
  

• 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.
  

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

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

And in detail:

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

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

• the Interbase Server enventually sends back an error code
  

4.4.4 - The creation code

	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:

• 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.
  

	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:

4.4.5 - Viewing the Table Schema

4.4.6 - Drop a Table

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:

4.4.7 - The ITEM table

4.4.8 - The complete project

4.5 - Adding Rows

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

  
  

	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:

• 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
  

	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:

4.5.1 - Deleting Rows

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:

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:

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
  

4.5.2 - Modifying rows

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:

4.5.3 - Parameterized Queries

• 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:

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

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

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

• 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)
  

• either with a number (%1; %2, %3)
  
• or implicitely by position
  
• or with an arbitrary identifier (like :one, :x, :customer)
  

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()
    

• 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
  

	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:

4.5.4 - Parameterized query with the BdpCommand component

	select the "Borland Data Provider" tab from the Palette, and click the BdpCommand component: Drop the component on the Form
	select its CommandText property, and click the ellipsis "..."
	the sql request editor is displayed
	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
	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:

4.6 - Reading Data

4.6.1 - SqlDataReader

• an BdpConnection
  
• an BdpCommand
  
• an iDataReader object which allows us to read data rows
  
• possibly some display controls
  

4.6.2 - Reading Data

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
    

• 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:

• 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:

	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:

• 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

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
  

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
  

	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: 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:

• 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:

  

  and when we click on "+":

  

  and on "table":

  

  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

	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: and drop it on the Form
	the BdpDataAdapter1 is displayed at the side of the BdpConnection1 (yellow arrow):
	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
	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
	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:

• 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

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" 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
	add the new relation by clicking "Add"
	the Relation Editor is displayed:
	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:

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

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

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

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

We will first fill the DataTable by code:

4.7.2 - Creating a DataTable by code

• create the DataTable
  
• then create each column, by specifying its type and attributes.
  

	create a new Windows Forms application, and rename it "p_41_ib_create_in_memory"
	drop a Button, rename it "create_data_table_", create its Click event, and add the following creation code: var g_c_invoice_data_table: DataTable; procedure TWinForm.create_data_table__Click(sender: System.Object;     e: System.EventArgs);   var l_c_data_column: DataColumn;   begin     display_line;     display('create_data_table');     g_c_invoice_data_table:= DataTable.Create();     g_c_invoice_data_table.Columns.Add('i_id', TypeOf(Integer));     l_c_data_column:= DataColumn.Create('i_customer',          TypeOf(System.String));     l_c_data_column.MaxLength:= 30;     l_c_data_column.AllowDbNull:= False;     g_c_invoice_data_table.Columns.Add(l_c_data_column);     Include(g_c_invoice_data_table.RowChanged, row_changed);     Include(g_c_invoice_data_table.ColumnChanged, column_changed);   end; // create_data_table__Click
	execute, run, and click "create_data_table_"

4.7.3 - Monitoring the Changes

But now we will add rows, modify values etc. and this can be traced in several ways.

First we can connect some events of the DataTable. The carefull reader will have detected the two Include() lines at the end of our creation procedure.

We did add the RowChanged and ColumnChanged events. To get a list of all possible events and their signatures, we used the .Net Help:

	we selected any well known .Net type, like DataTable
	we pushed F1
	the root of the Help is displayed: We directly skip the first levels of the TreeView, and go directly to the Class Library:     Reference         Class Library This branch displays all the main NameSpaces, and in particular     Reference         Class Library             System.Data                 DataTable Class

If I had only one piece of advice for the newbie .Net developer, it would be to become familiar with the .Net Help. And this is not very difficult: the main parts are

• System (the basic types like Strings)
  
• System.Collection : the different containers, the enumerators
  
• System.Data for ADO .Net (and System.Data.SqlClient for SQL Server)
  
• System.IO for file and directory handling and streams
  
• System.Net for network handling (TCP IP)
  
• System.Runtime for remoting
  
• System.Web for ASP .Net
  
• System.Windows.Forms for all the visual controls
  

And here is the code of our events:

declare events RowChanged and EventChanged in the PUBLIC section of the tWinForm CLASS, with the parameters displayed in our code snippet: type   TWinForm = class(System.Windows.Forms.Form)     // -- ...     public       constructor Create;       procedure row_changed(sender: tObject;           e: System.Data.DataRowChangeEventArgs);       procedure column_changed(sender: tObject;           e: System.Data.DataColumnChangeEventArgs);     end; // TWinForm and write the code to display somehow the changes: procedure TWinForm.row_changed(sender: tObject;     e: System.Data.DataRowChangeEventArgs);   begin     display(System.String.Format('on_row_changed {0} ', e.Action)        + ' : ' + f_display_data_row(e.Row, 2))   end; // row_changed procedure TWinForm.column_changed(sender: tObject;     e: System.Data.DataColumnChangeEventArgs);   begin     display(System.String.Format('on_column_changed {0} ', e.ProposedValue)        + ' : ' + f_display_data_row(e.Row, 2))   end; // column_changed

Note that:

• to avoid writing the events with their strange parameters, we could have dropped a DataSet on the Form, created the events, removed the bogus DataSet and graftet the orphan events to our in memory DataTable
  

4.7.4 - Filling the DataTable

• the DataTable.NewRow() function creates and returns a new empty row
  
• using a reference of this row, we fill the fields
  

	drop a Button on the Form, rename it "insert_", create its Click event and type the code which will create some lines with reasonable field values: procedure TWinForm.insert__Click(sender: System.Object;     e: System.EventArgs);   procedure add_row(p_id, p_customer: System.String);     var l_c_data_row: DataRow;     begin       l_c_data_row:= g_c_invoice_data_table.NewRow();       l_c_data_row['i_id']:= p_id;       l_c_data_row['i_customer']:= p_customer;       g_c_invoice_data_table.Rows.Add(l_c_data_row);     end; // add_row   begin // insert__Click     add_row('111', 'cadillac');     add_row('222', 'gm');     add_row('333', 'ford');   end; // insert__Click
	drop another Button, "display_", and write the code which displays the DataTable content
	execute, run, and click "insert_" and then "display_"
	here is the snapshot of our project:

• for the display we used auxiliary routines. In fact, we placed these procedures in a U_ADO_NET_HELPERS unit which is contained in the downloadable .ZIP
  
• the ADO .Net DataTable events look somehow sparse and not so systematic as the very complete OnBefore and OnAfter events of Delphi DataSets.
  

• a Current version (the value before a change)
  
• a Proposed version (the value after the change)
  

	drop a Button, rename it "display_all_", create its Click event, and add the code which will display for each row the current and propose versions, if any are available: function f_display_data_row_state_version(p_c_data_row: DataRow;     p_column_count: Integer): System.String;   var l_column_index: Integer;       l_current_value, l_proposed_value, l_value: System.String;   begin     Result:= System.String.Format('{0} ', p_c_data_row.RowState);     for l_column_index:= 0 to p_column_count- 1 do     begin       if p_c_data_row.HasVersion(DataRowVersion.Current)         then l_current_value:= p_c_data_row[l_column_index,             DataRowVersion.Current].ToString()         else l_current_value:= '';       if p_c_data_row.HasVersion(DataRowVersion.Proposed)         then l_proposed_value:= p_c_data_row[l_column_index,             DataRowVersion.Proposed].ToString()         else l_proposed_value:= '';       l_value:= l_current_value;       if (l_current_value<> l_proposed_value)         then l_value:= l_value+ ' [=> '+ l_proposed_value+ ']';       Result:= Result+ ' '+ l_value;     end; // vor l_column_index   end; // f_display_data_row_state_version procedure TWinForm.display_all__Click(sender: System.Object;     e: System.EventArgs);   var l_c_row_enumerator: iEnumerator;       l_c_data_row: DataRow;       l_display: System.String;       l_data_row_state: DataRowState;       l_state: String;       l_c_row_version: DataRow;   begin     l_c_row_enumerator:= g_c_invoice_data_table.Rows.GetEnumerator();     while l_c_row_enumerator.MoveNext() do     begin       l_c_data_row:= l_c_row_enumerator.Current as DataRow;       display(f_display_data_row_state_version(l_c_data_row, 2));     end; // while l_c_row_enumerator   end; // display_all__Click
	execute, run, and click "insert_" and then "display_state_"
	here is the snapshot of our project:

4.7.5 - Modifying Field Values

	add a TextBox to the Form
	drop a Button, rename it "update_", create its Click event, and add the code which will change the second column of row 1 of the table, using the TextBox value: procedure TWinForm.update__Click(sender: System.Object;     e: System.EventArgs);   var l_c_data_row: DataRow;   begin     l_c_data_row:= g_c_invoice_data_table.Rows[1];     l_c_data_row['i_customer']:= update_text_box_.Text;   end; // update__Click
	execute, run, and click "create_", "insert_" "update_" and "display_state_"
	here is the snapshot of our project:

Here is an example:

	add a Checkbox to the Form and rename it BeginEdit
	change the "update_" code by adding a call to Row.BeginEdit before the modification of the field value: procedure TWinForm.update__Click(sender: System.Object;     e: System.EventArgs);   var l_c_data_row: DataRow;   begin     l_c_data_row:= g_c_invoice_data_table.Rows[1];     if begin_edit_.Checked       then l_c_data_row.BeginEdit;     l_c_data_row['i_customer']:= update_text_box_.Text;   end; // update__Click
	execute, run, and click "create_", "insert_", "beginedit_", "update_", "display_state_", "accept_changes_" and "display_changes"
	here is the snapshot of our project:

So:

• when we call BeginEdit, all rows modified before EndEdit are recorded with a modification marker, and the DataTable contains both the old value (called Current) and new value (called Proposed)

  We can retrieve both Current and Proposed:

  • we first test

        my_data_row.HasVersion(DataRowVersion.Proposed)

  • we then fetch the value using the version as an index

        my_row[my_column_index, DataRowVersion.Proposed]
    

• to use an enumerated value (like DataRowVersion.Proposed) , the syntax is:

      my_enumerated_type.my_enumerated_value

  This is the Java way of denoting enumerated values

• this BeginEdit EndEdit business somehow reminds us of the DataSet.Edit, DataSet.Post mechanism of Delphi, and the handling of the row states is also similar to the Delphi ClientDataSet row state
  

4.7.6 - Deleting Rows

The complete project which creates, inserts, updates and deletes is in the downloadable 41_ib_create_in_memory.zip file

4.7.7 - Views

As this ADO .Net architecture picture shows, each DataSet contains a collection of such views:

And each DataTable has a default view:

We can use Views to sort the rows of a DataTable by calling

Here is a complete example:

	create a new Windows Forms application, and rename it "p_43_sql_dataview"
	drop a Button, rename it "create_data_table", create its Click event, and add code to create an in memory Datatable (we create the DataSet because we will access the DataView collection). Our DataTable has 3 columns (i_id, i_customer and i_amount): var g_c_data_set: DataSet; procedure TWinForm.create_data_set__Click(sender: System.Object; e: System.EventArgs);   var l_c_invoice_data_table: DataTable;   procedure create_column_definitions;     var l_c_data_column: DataColumn;     begin       l_c_invoice_data_table.Columns.Add('i_id', TypeOf(Integer));       l_c_data_column:= DataColumn.Create('i_customer', TypeOf(System.String));       l_c_data_column.MaxLength:= 15;       l_c_invoice_data_table.Columns.Add(l_c_data_column);       l_c_invoice_data_table.Columns.Add('i_amount', TypeOf(Double));     end; // create_column_definition   procedure insert_row_values;     procedure add_invoice(p_id: Integer; p_customer: System.String; p_amount: Double);       var l_c_data_row: DataRow;       begin         l_c_data_row:= l_c_invoice_data_table.NewRow();         l_c_data_row['i_id']:= p_id;         l_c_data_row['i_customer']:= p_customer;         l_c_data_row['i_amount']:= p_amount;         l_c_invoice_data_table.Rows.Add(l_c_data_row);       end; // add_invoice     begin // insert_row_values       add_invoice(201, 'apple',   1234.51);       add_invoice(202, 'exxon',   625.51);       add_invoice(203, 'dow',     334.51);       add_invoice(204, 'ibm',     134.51);     end; // insert_row_values   var l_c_data_view: DataView;   begin // create_data_set__Click     g_c_data_set:= DataSet.Create('business');     l_c_invoice_data_table:= DataTable.Create();     l_c_invoice_data_table:= g_c_data_set.Tables.Add('invoice');     create_column_definitions;     insert_row_values;     l_c_data_view:= DataView.Create(l_c_invoice_data_table);     display_data_view(l_c_data_view, 3);   end; // create_data_set__Click
	drop a Button, rename it "sort_", create its Click event, and add code to sort the DataTable procedure TWinForm.sort__Click(sender: System.Object;     e: System.EventArgs);   var l_c_sorted_data_view: DataView;   begin     l_c_sorted_data_view:= g_c_data_set.Tables[0].DefaultView;     l_c_sorted_data_view.Sort:= 'i_customer ASC';     display_data_view(l_c_sorted_data_view, 3);   end; // sort__Click
	execute, run, and click "create_data_table_" and "sort_"
	here is the snapshot of our project:

We can also search the DataSet to find a row with some value. The Find() function will return the DataRow containing this value:

Let us stress that:

• it is MANDATORY to sort the DataTable before calling Find(). An exception will be raised if this has not been done
  
• if you do not want to perform the Sort() on the original table, use an auxilary table and resyncrhonize using a key value
  

• drop a TextBox on the Form
  
• drop a Button, rename it "find_", create its Click event, and add code to sort first, and then look for the value contained in the TextBox

  procedure TWinForm.find__Click(sender: System.Object;     e: System.EventArgs);   var l_c_sorted_data_view: DataView;       l_string_to_find: System.String;       l_find_index: Integer;   begin     l_string_to_find:= find_text_box_.Text;     // -- MUST sort     l_c_sorted_data_view:= g_c_data_set.Tables[0].DefaultView;     l_c_sorted_data_view.Sort:= 'i_customer ASC';     display_data_view(l_c_sorted_data_view, 3);     l_find_index:= l_c_sorted_data_view.Find(l_string_to_find);     display('found at '+ l_find_index.ToString);   end; // find__Click

• execute, run, and click "create_data_table_" and "find_"

• here is the snapshot of our project:

  
  

Here is the code:

	drop a Button on the Form, name it "filter_", create its Click event and add an expression which will filter the DefaultView: procedure TWinForm.filter__Click(sender: System.Object;     e: System.EventArgs);   var l_c_filtered_data_view: DataView;   begin     l_c_filtered_data_view:= g_c_data_set.Tables[0].DefaultView;     l_c_filtered_data_view.RowFilter:= ' i_customer> ''g'' ';     display_data_view(l_c_filtered_data_view, 3);   end; // filter__Click
	execute, run, and click "create_data_table_" and "filter_"
	here is the snapshot of our project:

Note that

• we performed the filtering after all other examples. In our case, the filter only left one row, and sorting would not have been very interesting.
  
• there are all kinds of other possibilities: launching SQL requests on the in memory DataSet, compute aggregates etc, but we will not present those in this tutorial.
  

4.8 - the DataBindings

4.8.1 - DataBindings Introduction

this will be understood as ("polymorphic magic"):

This works well when transfering data from the DataSet to a visual control. But in order to transfer modifications from a visual control back to the DataSet, we must use DataBindings:

These DataBindings are rather delicate to handle, and I found on Google several postings which were much more severe about them.

Anyway, the fundamental goal of DataBindings is to to define "synchronous movement zones": all the controls connected to a zone notify and are notified of any position changes in the underlying DataView.

DataBindings are obtained via BindingContexts. These contexts are associated with VISUAL CONTROLS. Therefore, there are BindingContext associated with the Form, the DataGrids, the TextBoxes etc. To keep things simple, we will use the BindingContext of the Form.

A BindingContext enables us to recover a CurrencyManager, where "currency" means "actual", "current" (nothing to do with money !).

The Microsoft schema to explain this is:

And these DataBindings can be to use to synchronize

• several visual controls, like DataGrids, ListBoxes, TextBoxes
  
• several data sources: DataView, of course, but also with in memory data values or structures ARRAY OF xxx etc.).
  

• those used to synchronize simple data (an Integer)
  
• those used to synchronize lists of data (values of a column of a DataTable, data of an ARRAY, a Collection). In fact any CLASS that implements the iList INTERFACE.
  

4.8.2 - Simple DataBindings of a DataTable

where

• my_property specifies the linked property. For example 'Text' for a TextBox
  
• my_view specifies the origin of the data. For example a DataSet or an ARRAY
  
• my_expression tells which part of the source is displayed. For example the column 'customer' of a DataSet
  

Here is a first example with two TextBox and a DataTable :

	create a new Windows Forms application, and rename it "p_43_sql_simple_data_binding"
	declare a DataSet in the PUBLIC section of the Form type   TWinForm = class(System.Windows.Forms.Form)     // -- ...     public       m_c_data_set: DataSet;       constructor Create;     end; // TWinForm
	drop a Button, rename it "create_data_table_", create its Click event, and add some code which will create and fill the DataSet: procedure TWinForm.create_data_table__Click(sender: System.Object;     e: System.EventArgs);   var l_c_invoice_data_table: DataTable;   procedure create_column_definitions;     var l_c_data_column: DataColumn;     begin       l_c_invoice_data_table.Columns.Add('i_id', TypeOf(Integer));       l_c_data_column:= DataColumn.Create('i_customer', TypeOf(System.String));       l_c_data_column.MaxLength:= 15;       l_c_invoice_data_table.Columns.Add(l_c_data_column);       l_c_invoice_data_table.Columns.Add('i_amount', TypeOf(Double));     end; // create_column_definition   procedure insert_row_values;     procedure add_invoice(p_id: Integer; p_customer: System.String;         p_amount: Double);       var l_c_data_row: DataRow;       begin         l_c_data_row:= l_c_invoice_data_table.NewRow();         l_c_data_row['i_id']:= p_id;         l_c_data_row['i_customer']:= p_customer;         l_c_data_row['i_amount']:= p_amount;         l_c_invoice_data_table.Rows.Add(l_c_data_row);       end; // add_invoice     begin // insert_row_values       add_invoice(201, 'macy',   1234.51);       add_invoice(202, 'exxon',   625.51);       add_invoice(203, 'dow',     334.51);       add_invoice(204, 'ibm',     134.51);     end; // insert_row_values   begin // create_data_set__Click     m_c_data_set:= DataSet.Create('business');     l_c_invoice_data_table:= DataTable.Create();     l_c_invoice_data_table:= m_c_data_set.Tables.Add('invoice');     create_column_definitions;     insert_row_values;   end; // create_data_set__Click
	drop a TextBox to display the 'i_id' column, and rename it "id_text_box"
	drop a TextBox to display the 'i_customer' column, and rename it "customer_text_box"
	drop a Button on the Form, rename it "bind_data_table_", create its Click event and add the code which will add a DataBinding to our TextBoxes and will create the CurrencyManager : procedure TWinForm.bind_data_table__Click(sender: System.Object;     e: System.EventArgs);   var l_c_binding: Binding;   begin     l_c_binding:= Binding.Create('Text',         m_c_data_set, 'INVOICE.I_ID');     id_text_box_.DataBindings.Add(l_c_binding);     customer_text_box_.DataBindings.Add('Text',         m_c_data_set, 'invoice.i_customer');     g_c_currency_manager:=         BindingContext[m_c_data_set.Tables[0].DefaultView] as CurrencyManager;     Include(g_c_currency_manager.CurrentChanged, current_changed);     Include(g_c_currency_manager.PositionChanged, position_changed);   end; // bind_data_table__Click
	drop a Button on the Form, rename it "next_", create its Click event which will modify the position of the CurrencyManager: procedure TWinForm.next__Click(sender: System.Object;     e: System.EventArgs);   begin     BindingContext[m_c_data_set, 'invoice'].Position:=         BindingContext[m_c_data_set, 'invoice'].Position+ 1;   end; // next__Click
	add a similar "previous_" Button
	execute, run, and click "create_", "display_", "bind_", and "next_"
	here is the snapshot of our project:

We will now bind visual controls to an ARRAY. This will work because in .Net, the dynamic ARRAYs do implement the iList INTERFACE.

Here are the components involved:

We will create an ARRAY of invoice Objects:

• this is the CLASS definition

  type c_invoice=   class      Public        m_id: Integer;        m_customer: System.String;        m_amount: Double;        Constructor create_invoice(p_id: Integer;            p_customer: System.String; p_amount: Double);        procedure display_invoice;        property id : Integer read m_id write m_id;        property customer : System.String            read m_customer write m_customer;        property amount : Double            read m_amount write m_amount;    end; // c_invoice

• and the table is defined by:

  g_c_invoice_array: array of c_invoice;

  
  

	create a new UNIT, rename it "u_c_invoice", and: define the c_invoice CLASS write its CONSTRUCTOR and display_invoice method add the id, customer and amount PROPERTIES
	import this unit in the USE clause of our main unit
	declare the ARRAY as a PUBLIC member of the Form
	drop a Button on the Form, rename it "create_array_", create its Click event and type the code which will create the table and fill it: procedure TWinForm.create_array__Click(sender: System.Object;     e: System.EventArgs);   var l_invoice_index: Integer;   procedure add_invoice(p_id: Integer; p_customer: System.String;       p_amount: Double);     var l_c_invoice: c_invoice;     begin       l_c_invoice:= c_invoice.create_invoice(p_id, p_customer, p_amount);       g_c_invoice_array[l_invoice_index]:= l_c_invoice;       Inc(l_invoice_index);     end; // add_invoice   begin // create_array__Click     SetLength(g_c_invoice_array, 4);     l_invoice_index:= 0;     add_invoice(201, 'macy',   1234.51);     add_invoice(202, 'exxon',   625.51);     add_invoice(203, 'dow',     334.51);     add_invoice(204, 'ibm',     134.51);   end; // create_array__Click
	drop a TextBox which will display the id PROPERTY and call it array_id_text_box
	drop a TextBox which will display the customer PROPERTY and call it array_customer_text_box
	drop a Button on the Form, rename it "bind_array_", create its Click event and add the code which will add DataBinding to each TextBox, and create the CurrencyManager : procedure TWinForm.bind_array__Click(sender: System.Object;     e: System.EventArgs);   begin     array_id_text_box_.DataBindings.Add('Text', g_c_invoice_array, 'id');     array_customer_text_box_.DataBindings.Add('Text',         g_c_invoice_array, 'customer');   end; // bind_array__Click
	drop a Button on the Form, rename it "next_", create its Click event and modify the position of the CurrencyManager: procedure TWinForm.next_t__Click(sender: System.Object;     e: System.EventArgs);   begin     BindingContext[g_c_invoice_array].Position:=         BindingContext[g_c_invoice_array].Position+ 1;   end; // next_t__Click and do the same to decrement the Position
	execute, run, and click "create_", "display_", "bind_", and "next_"
	here is the snapshot of our project:

• we placed the DataSet and ARRAY declarations in the Form CLASS. I imagine that it facilitates the communications with the ContextManager
  
• the CurrencyManager is only used to move the Position
  
• for the ARRAY, the data that we want to display must be in the PUBLIC parts of the c_invoice CLASS, and even presented as a PROPERTY (a simple member is not enough)
  
• the preceding results not very impressive. 8 hours of battle to find out how to connect the different parts. We find .Net to be too "detailed" (too fine granularity) and "hyper polymorphic" (between "()" or "[ ]" you can place almost anything, the compiler accept the code, but you might have to reboot after trying to run the project).
  

4.9 - Modifying Server Tables

4.9.1 - Update Introduction

To let the user modify Server data, we proceed into two stages

• we modify the data of an in memory DataTable (generally linked to visual controls)
  
• and we call BdpDataAdapter.Update
  

• either explicitely written by the programmer
  
• or automatically generated by the BdpDataAdapter. The generator uses the SELECT Command which was used to load the DataTable in the first place
  

4.9.2 - Changing data by code

	create a new Windows Forms application, and rename it "p_51_ib_update_via_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
	drop a Button, rename it "change_invoice_", create its Click event, and change some values of the in memory DataTable: var g_c_bdp_data_adapter: BdpDataAdapter;     g_c_data_set: DataSet; procedure TWinForm.change_invoice__Click(sender: System.Object;     e: System.EventArgs);   var l_c_invoice_data_table: DataTable;       l_c_data_row: DataRow;   begin     g_c_bdp_data_adapter:= BdpDataAdapter.Create(k_select_invoice, BdpConnection1);     g_c_data_set:= DataSet.Create('my_dataset');     g_c_bdp_data_adapter.TableMappings.Add('Table', 'my_invoice');     g_c_bdp_data_adapter.Fill(g_c_data_set);     l_c_invoice_data_table:= g_c_data_set.Tables['my_invoice'];     // -- insert a row     l_c_data_row:= l_c_invoice_data_table.NewRow();     l_c_data_row['i_id']:= Convert.ToInt32(id_textbox_.Text)+ 5;     l_c_data_row['i_customer']:= customer_textbox_.Text;     l_c_invoice_data_table.Rows.Add(l_c_data_row);     // -- modify a row     display('modify_row');     l_c_data_row:= l_c_invoice_data_table.Rows[3];     l_c_data_row['i_customer']:= customer_textbox_.Text;     display('delete_row');     l_c_invoice_data_table.Rows[Convert.ToInt32(delete_id_textbox_.Text)].Delete;   end; // change_invoice__Click

This is the code:

	drop a Button on the Form, rename it "update_command_builder_" and type the following code in its Click event: procedure TWinForm.update_commandbuilder__Click(sender: System.Object;     e: System.EventArgs);   var l_c_bdp_command_builder: BdpCommandBuilder;   begin     l_c_bdp_command_builder:= BdpCommandBuilder.Create(g_c_bdp_data_adapter);     g_c_bdp_data_adapter.Update(g_c_data_set);     display(l_c_bdp_command_builder.GetUpdateCommand.CommandText);   end; // update_commandbuilder__Click
	execute, run, and click "display_", "change_", "update_", "display_"
	here is the snapshot of our project:

The request displayed by CommandBuilder is rather complex, but basically it tries to avoid comparing NULL values

4.9.3 - Writing custom SQL requests

This is how it works:

• we examine each line of the DataTable, and check its State value: if the row was somehow changed, we write and sent the relevant SQL request
  
• for INSERT, it is very simple: we simply use the new values to fill the VALUES clause of the INSERT INTO request
  
• for UPDATE we stumble, as always, on the problem of concurrent accesses:
  • in the WHERE clause we must specify which rows we want to modify
    
  • the in-memory DataSet still contains the original values that we fetched at the time of the SELECT call, and we therefore know the original key (ID) of the row. So we can ask to modify the row having this previous key
    
  • we can also, if the application requires it, check that other values did not change. For a plane reservation, we certainly also would check that the available seats did not change between the SELECT call and the reservation request (the customer could ask whether there are some available seats, think for some time, and later, much later, finally decide to book a flight). In this case, we could include in the WHERE clause some code checking that the actual Server data did not change, by comparing the available seats to those available when we first sent the SELECT  
    

	drop a Button on the Form, rename it "update_manual_" and type the updating code in its Click event: a WHILE loop checks the state of each row and calls the relevant updating sub-procedure: procedure TWinForm.update_manual_sql__Click(sender: System.Object;     e: System.EventArgs);   var l_c_row_enumerator: iEnumerator;       l_c_row: DataRow;   begin // submit_updates     BdpConnection1.Open();     l_c_row_enumerator:= g_c_data_set.Tables[0].Rows.GetEnumerator();     while l_c_row_enumerator.MoveNext() do     begin       l_c_row:= l_c_row_enumerator.Current as DataRow;       case l_c_row.RowState of         DataRowState.Added : do_insert_row(l_c_row);         DataRowState.Modified : do_update_row(l_c_row);       end; // case     end; // while l_c_row_enumerator     BdpConnection1.Close();   end; // update_manual_Bdp__Click For INSERT we directly write the request: procedure do_insert_row(p_c_added_row: DataRow);   var l_values, l_request: System.String;   var l_c_bdp_command: BdpCommand;   begin     l_values:= p_c_added_row['i_id'].ToString       + ', '''+ p_c_added_row['i_customer'].ToString+ ''''       + ', '''+ p_c_added_row['i_date'].ToString+ ''''          ;     l_request:= 'INSERT INTO invoice '       +  ' (i_id, i_customer, i_date) '+ k_new_line       +  '    VALUES ('+ l_values+ ')';     l_c_bdp_command:= BdpCommand.Create(l_request, BdpConnection1);     l_c_bdp_command.ExecuteNonQuery;   end; // do_insert_row And for UPDATE we use a parameterized request: procedure do_update_row(p_c_modified_row: DataRow);   const k_update_invoice=           'UPDATE invoice '         + '  SET i_customer= ?'         + '  WHERE i_id= ? ';   var l_c_bdp_parameter: BdpParameter;       l_c_bdp_command: BdpCommand;   begin     l_c_bdp_command:= BdpCommand.Create(k_update_invoice, BdpConnection1);     l_c_bdp_command.ParameterCount:= 2;     l_c_bdp_parameter:= l_c_bdp_command.Parameters.Add('i_customer', BdpType.String);     l_c_bdp_parameter.Size:= 7;     l_c_bdp_parameter.Value:=  p_c_modified_row['i_customer'];     l_c_bdp_parameter:= l_c_bdp_command.Parameters.Add('i_id', BdpType.Int32);     l_c_bdp_parameter.Value:= p_c_modified_row['i_id',         DataRowVersion.Original];     l_c_bdp_command.ExecuteNonQuery;   end; // do_update_row
	execute, run, and click "display_", "change_", "update_manual", "display_"
	here is the snapshot of our project: