|
ADO .Net Tutorial - Felix John COLIBRI.
|
- abstract : developing ADO .Net applications, using the SQL Server database
- key words : ADO .Net - SQL Server - Delphi - Windows Forms
- software used : Windows XP, Delphi 2006
- 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 - ADO.NET Tutorial
We will show how to develop database applications using SQL Server and the
ADO.NET components with Delphi.
We will present:
This tutorial will not cover all aspects of ADO.NET 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, Sql Server, Interbase, 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 (SQL Server in our case)
- the network layers
- one or more Clients which include:
- the network layers (Tcp/Ip here)
- the Client layer (the Sql 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:
To implement this scheme, we have to install:
- the Server part of SQL Server
- the Client part of SQL Server
- 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 the `free MSDE SQL Server part. To make a
long story short:
- use GOOGLE to find the MSDE Microsoft download page
- click on the setup
- use GOOGLE to find the Northwind Microsoft download page
- click on the .MSI file
If that does not work, use GOOGLE do locate the Colibri MSDE installation 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. 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
and:
- SqlConnection connects to the Server (address, user name, password etc)
- SqlCommand 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 DataAdapter. It
contains:
- four SqlCommand 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 DataAdapter like this:
- the Table rows can be stored and handled in memory. Those rows are provided:
- by the DataAdapter
- 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 DataAdapter plays the role of a middle man:
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
ADO.NET DataProviders are specified by INTERFACEs. So any component set
implementing those INTERFACEs can be used.
In the case of SQL Server, we can use
- the SQL Server data provider (included in the .Net Framework)
- 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, since Delphi has encapsulated the SQL Server OleDb
provider in a BDP component
In red are all the routes available:
In this article, we will be interested in the direct programming ADO. Net, and
will present the use of the the BDP for handling SQL Server Tables elsewhere.
4 - ADO.NET Programming
4.1 - Tasks
We will present here:
Our SQL Server parameters (specified when we installed SQL Server) are:
- server name : pol_1400_5\my_instance
- user: sa
- password: my_pass
You will use your own SQL Server parameters to establish the connection.
4.2 - Create Database
4.2.1 - Create Database request
To create a database, we must:
- open a connection to a system database called MASTER
- use the CREATE DATABASE request which will create at least 2 files
(the data file and a log file)
The syntax of the CREATE DATABASE request is:
CREATE DATABASE database_name
[ON
{ [PRIMARY]
(NAME = logical_name,
FILENAME ='physical_name'
[,SIZE = size]
[,MAXSIZE = max_size | UNLIMITED]
[,FILEGROWTH = growth_increment])
}[,...n]]
[LOG ON
{
(NAME = logical_name,
FILENAME = 'physical_name'
[,SIZE=size | UNLIMITED]
[,MAXSIZE = max_size | UNLIMITED]
[,FILEGROWTH = growth_increment])
}[,...n]]
[,COLLATE collation_name]
[FOR LOAD | FOR ATTACH]
|
We will use the minimal version of this request:
CREATE DATABASE invoice2
ON PRIMARY
(
NAME ='the_invoice_data'
, FILENAME = 'c:\programs\us\db\sql_server\_data\the_invoice_data.mdf'
)
LOG ON
(
NAME = 'the_invoice_log'
, FILENAME = 'c:\programs\us\db\sql_server\_data\the_invoice_log.ldf'
)
|
4.2.2 - Establish a connection to MASTER
The steps are the following
|
start Delphi
|
|
|
create a new Windows Forms application by selecting "file | New | Windows
Forms Application"
|
|
|
collapse all parts of the Tool Palette, open the "DataComponent" tag, and
select the SqlConnection component:
|
|
|
drag this component ON THE FORM
|
|
Delphi will display SqlConnection1 in the nonvisual components area:
CAUTION: the SqlConnetction component must be dropped on the Form, and it
will be displayed in the area below
|
|
|
click on SqlConnection1
In the Object Inspector, select the "Connection" property, and click the
ellipsis (the "..." box at the right)
|
|
|
the connection editor is displayed
|
|
|
enter
- the server name. In our case:
pol_1400_5\my_instance
- the security parameters. "Nt Security" for us.
- the database name:
master
The connection dialog looks like this:
|
|
|
click "Test"
|
|
|
the connection is established
|
4.2.3 - Create the Database
We will now sent the CREATE DATABASE request
|
|
place a Button on the Form, rename it "create_database_", double click on
the Button to create its Click method and add the Database creation code:
const k_create_database=
' CREATE DATABASE invoice2'
+ ' ON PRIMARY '
+ ' ('
+ ' NAME = ''the_invoice_data'' '
+ ' , FILENAME = ''c:\programs\us\db\sql_server\'
+ '_data\the_invoice_data.mdf'''
+ ' ) '
+ ' LOG ON '
+ ' ('
+' NAME = ''the_invoice_log'' '
+ ' , FILENAME = ''c:\programs\us\db\sql_server\'
+ '_data\the_invoice_log.ldf'' '
+ ' ) '
;
procedure TWinForm.create_database___Click(sender: System.Object;
e: System.EventArgs);
var l_c_sql_command: SqlCommand;
begin
SqlConnection1.Open;
l_c_sql_command:= SqlCommand.Create(k_create_database, Sqlconnection1);
try
l_c_sql_command.ExecuteNonQuery();
except
on e: exception do
display_bug_stop('*** err '+ e.Message);
end;
end; // create_database___Click
|
|
|
|
compile, run and click "create_database"
|
|
|
the files are created in the specified directory:
|
4.2.4 - Drop DataBase
If you want to remove the Datatabase, this is the SQL request:
4.2.5 - Display Database Names
We also can list all the available Databases on our SQL Server with:
SELECT name FROM sysdatabases
|
4.2.6 - The Creation project
Here is a snapshot of the project:
You can download here the 01_create_database.zip
project
4.3 - ADO .Net connection
We will now establish a connection between our Delphi application and the
Server.
4.3.1 - 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 MSSQL:
We can add to the MSSQL item an entry for our new INVOICE2 Database:
|
|
click on the MSSQL line
|
|
|
only the default MsConnection1 will be displayed
|
|
|
right click on MSSQL, select "Add New Connection"
|
|
|
Delphi tells us to enter the connection name
|
|
|
type the name that the Data Explorer will display. In our case:
invoice_2_connection
and click "Ok"
|
|
|
a new entry has been added to the MSSQL database list:
|
|
|
to enter the connection's parameters, right click on "invoice_2_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, and click "Ok"
|
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.2 - The BDP Connection
First we will use a BDP Connection:
|
|
create a new Windows Forms application and rename it
"p_11_sql_connect_invoice"
|
|
|
in the Data Explorer, select the invoice_2_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)
|
|
|
Delphi will open the BdpConnection1 Connection Editor:
|
|
|
you can click "test" to check the connection
|
4.3.3 - Connection using an SqlConnection
In this article, we will not use the BDPConnection component, which is a
Delphi generic connection component. We will use the native Sql Server
connection wrapper, called SqlConnection.
In our global ADO .Net architecture, the Sql Connection is here displayed in
red:
Let's see the code now.
4.3.4 - Connecting by code
To stress what is involved in the connection, we will first perform the
connection using code only.
|
|
add a Button to the Form, rename it "connect_", create its Click event,
and type the connection code:
const k_user_sa= 'user id=sa';
k_password_me= 'Pwd = my_pass';
k_sspi_security= 'integrated security=SSPI';
k_server_name= 'SERVER= "pol_1400_5\my_instance"';
k_invoice_database_name= 'DATABASE= invoice2';
k_invoice_connection_string=
k_user_sa
+ ';'+ k_password_me
+ ';'+ k_sspi_security
+ ';'+ k_server_name
+ ';'+ k_invoice_database_name;
var g_c_sql_connection: SqlConnection= Nil;
procedure TWinForm.connect__Click(sender: System.Object;
e: System.EventArgs);
begin
g_c_sql_connection:= SqlConnection.Create(k_invoice_connection_string);
with g_c_sql_connection do
begin
// -- add the StateChange event
Include(StateChange, Self.SqlConnection1_StateChange);
display('> connect');
Open();
display('< connect');
end; // with g_c_sql_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)
// -- ...
procedure SqlConnection1_StateChange(sender: System.Object;
e: System.Data.StateChangeEventArgs);
// -- ...
end;
|
and here is the body of the event:
procedure TWinForm.SqlConnection1_StateChange(sender: System.Object;
e: System.Data.StateChangeEventArgs);
begin
display(System.String.Format('change {0} -> {1} ',
e.OriginalState, e.CurrentState));
end; // SqlConnection1_StateChange
|
Compile, run and click "connect_"
|
|
|
here is the snapshot of the connection:
|
You may also add a "disconnect" button, which simple calls the
SqlConnection.Close() method.
4.3.5 - Using the SqlConnection component
Initializing all the parameters by code is quite boring and error prone, so the
standard way of connecting to an Sql Server Database is to
use a SqlConnection component from the Tools
Palette:
|
|
select the "Data Components" tab in the Tools Palette, select the
SqlConnection component and drop in on the Form (this is already
described in the create database paragraph
above)
|
|
|
click on the Form, click on the SqlConnection1 component below the Form,
and in the Object Inspector, click on the ConnectionString property
|
|
|
Delphi will display the Sql Server Connection Editor. Notice that this
editor is not the same as the BDP Connection Editor
|
|
|
fill in the database name, the Server Name, the security kind and the
Database Name:
|
|
|
instead of using the "integrated NT security", you can also use your user
name and password ("sa" and "my_pass" in our case):
|
To download the source, click
11_sql_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:
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 DATETIME
)
|
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 SqlConnection component
- use an SqlCommand component, connect it to the SqlConnection, fill in the
CommandText property with our request and call the
SqlConnection.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:
And in detail:
- we use an SqlConnection and an SqlCommand 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 SQL 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_sql_create_table"
|
|
|
drop an SqlConnection component on the Form and initialize the connection
string to be able to connect to our INVOICE 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 DATETIME'+ k_new_line
+ ' )';
procedure TWinForm.create_invoice__Click(sender: System.Object;
e: System.EventArgs);
var l_c_command: SqlCommand;
l_result: Integer;
begin
SqlConnection1.Open();
l_c_command:= SqlCommand.Create(k_create_invoice_table, SqlConnection1);
try
l_result:= l_c_command.ExecuteNonQuery();
display('Result '+ l_result.ToString);
except
on e: exception do
display_bug_stop(e.Message);
end;
end;
SqlConnection1.Close();
end; // create_invoice_Click
|
|
|
|
execute, run, and click "create_invoice_"
|
|
|
here is the snapshot of our project:
|
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
4.4.5 - Viewing the Table Schema
We can use the SqlConnection 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:
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: SqlCommand;
l_result: Integer;
begin
SqlConnection1.Open();
l_c_command:= SqlCommand.Create(k_drop_invoice_table, SqlConnection1);
if do_execute_.Checked
then begin
l_result:= l_c_command.ExecuteNonQuery();
display('Result '+ l_result.ToString);
end;
SqlConnection1.Close();
end; // drop_invoice__Click
|
|
|
|
execute, run, and click "drop_invoice_"
|
|
|
here is the snapshot of our project:
|
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_sql_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/21/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_reader" procedure is:
procedure TWinForm.execute_non_query(p_do_execute: Boolean;
p_request: System.String);
var l_c_command: SqlCommand;
l_count: Integer;
begin
if p_do_execute
then begin
l_c_command:= SqlCommand.Create(p_request, SqlConnection1);
Try
l_count:= l_c_command.ExecuteNonQuery();
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_sql_fill_table"
|
|
|
drop a SqlConnection 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
SqlConnection1.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');
SqlConnection1.Close();
end; // fill_invoice__Click
|
|
|
|
compile, execute, click "fill_invoice_"
|
|
|
here is a snapshot of our project:
|
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:
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
SqlConnection1.Open();
execute_non_query(do_execute_.Checked, k_delete_all_invoice);
SqlConnection1.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
You will find this project
in22_sql_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 SqlConnection 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
SqlConnection1.Open();
execute_non_query(do_execute_.Checked, k_update_invoice);
SqlConnection1.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
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:
- 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
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)
Sql Server uses @ followed by an identifier.
So:
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 SQL Server SqlDbType.Int 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= @i_customer'
+ ' WHERE (i_id= @old_i_id) '
;
var g_c_command: SqlCommand;
procedure TWinForm.prepare__Click(sender: System.Object;
e: System.EventArgs);
begin
g_c_command:= SqlCommand.Create(k_parametrized_update_request,
SqlConnection1);
SqlConnection1.Open();
g_c_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:
const k_parametrized_update_request=
'UPDATE invoice '
+ ' SET i_customer= @i_customer'
+ ' WHERE (i_id= @old_i_id) '
;
var g_c_command: SqlCommand;
procedure TWinForm.prepare__Click(sender: System.Object;
e: System.EventArgs);
begin
g_c_command:= SqlCommand.Create(k_parametrized_update_request,
SqlConnection1);
SqlConnection1.Open();
g_c_command.Prepare();
end; // prepare__Click
|
|
|
|
execute, run, and
- click "prepare_"
- input an ID and a NAME
- click "execute_"
|
|
|
here is the snapshot of our project:
|
You will find the source code in
23_sql_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 SqlConnection
- an SqlCommand
- 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:
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 SqlCommand with the request
- we call the SqlCommand.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:
Here are the steps:
|
|
create a new Windows Forms Application, rename it
"p_31_sql_display_data_reader"
|
|
|
drop an SqlConnection component on the Form and initialize the connection
string to be able to connect to our INVOICE 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_training= 'SELECT * FROM invoice';
procedure TWinForm.display_invoice__Click(sender: System.Object;
e: System.EventArgs);
var l_c_command: SqlCommand;
l_c_reader: iDataReader;
l_row_index: Integer;
l_column_index: Integer;
l_display: String;
begin
l_c_command:= SqlConnection1.CreateCommand();
l_c_command.CommandText:= k_select_training;
SqlConnection1.Open();
l_c_reader:= l_c_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
SqlConnection1.Close();
end; // display_invoice_click
|
|
|
|
execute, run, and click "display_invoice_"
|
|
|
here is the snapshot of our project:
|
Please note that
- we had already used DataReaders in the preceding projects:
- in the CREATE TABLE project
- in the INSERT INTO, as well as the UPDATE projects
- 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
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:
- SqlConnection and SqlCommand to fetch the data
- a DataAdapter to pump the data from the SqlCommand 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:
In the code:
- we drop the components:
- an SqlCommand, with a valid SELECT
- a SqlDataAdapter containing a SqlCommand property referencing the
SqlCommand object
- an empty DataSet
- we load the data
- by opening the connection
- by calling SqlDataAdapter.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_sql_display_datagrid_code"
|
|
|
drop an SqlConnection component on the Form and initialize the connection
string to be able to connect to our INVOICE 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 to fill the DataAdapter and display the data in a DataGrid:
const k_select_invoice= 'SELECT * FROM invoice';
procedure TWinForm.datatable__Click(sender: System.Object;
e: System.EventArgs);
var l_c_command: SqlCommand;
l_c_data_adapter: SqlDataAdapter;
l_c_data_set: Dataset;
l_c_data_table_invoice_ref: DataTable;
begin
l_c_command:= SqlCommand.Create(k_select_invoice, SqlConnection1);
l_c_data_adapter:= SqlDataAdapter.Create;
l_c_data_adapter.SelectCommand:= l_c_command;
l_c_data_set:= DataSet.Create('my_invoices');
SqlConnection1.Open();
l_c_data_adapter.Fill(l_c_data_set);
SqlConnection1.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; // datatable__Click
|
|
|
|
execute, run, and click "datatable_"
|
|
|
here is the snapshot of our project:
|
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)
- with dbExpress, the dataflow "direction" is uniform:
tDataGrid -> tDataSource -> tClientDataset -> tDataProvider ->
tSqlQuery -> tSqlConnection
but in ADO .Net, here Fill() reverses the chaining direction:
tDataGrid -> tDataTable -> tDataTable <== tDataAdapter ->
tSqlCommand -> tSqlConnection
- our 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]
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 DataSet:
procedure TWinForm.dataset__Click(sender: System.Object;
e: System.EventArgs);
var l_c_command: SqlCommand;
l_c_data_adapter: SqlDataAdapter;
l_c_data_set: Dataset;
begin
l_c_data_adapter:= SqlDataAdapter.Create;
l_c_data_adapter.SelectCommand:= SqlCommand.Create(k_select_invoice,
SqlConnection1);
l_c_data_set:= DataSet.Create('my_invoices');
SqlConnection1.Open();
l_c_data_adapter.Fill(l_c_data_set);
SqlConnection1.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_trainings" 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_sql_display_datagrid"
|
|
|
drop an SqlConnection component on the Form and initialize the connection
string to be able to connect to our INVOICE database, as
explained before. Check the connection
|
|
|
from the "Data Components" tab of the Tools Palette select the
SqlDataAdapter:
and drop it on the Form
|
|
|
click on the Form, then click on SqlDataAdapter1
|
|
|
the Object Inspector displays the properties of SqlDataAdapter
|
|
|
input the SQL request:
- click on the SelectCommand to unfold it
- select the SqlConnection property and select SqlConnection1
- in the CommandText property, type the SQL request:
SELECT * FROM invoice
|
|
|
here is the Object Inspector snapshot:
|
|
|
click the "Generate Dataset" link located at the bottom of the Object
Inspector
|
|
|
Delphi generates a DataSet11 object, which is displayed below the Form
|
|
|
from the "Data Controls" tab of the Tools Palette, select a DataGrid and
drop it on the Form
|
|
|
Click on the DataGrid. In the Object Inspector, select the DataSource
property and link it to DataSet11 by typing
Dataset11._Table
|
|
|
the column names of the Table are displayed in the Datagrid:
|
|
|
drop a Button on the Form, rename it "fill_dataset_11_", create its
Click event and type the code which will fill the DataTable:
procedure TWinForm.fill_dataset_11__Click(sender: System.Object;
e: System.EventArgs);
begin
SqlConnection1.Open();
SqlDataAdapter1.Fill(DataSet11);
SqlConnection1.Close();
dataGrid1.DataSource:= DataSet11.Tables[0];
end; // fill_dataset_11__Click
|
|
|
|
execute, run, and click "fill_dataset_11_"
|
|
|
here is the snapshot of our project:
|
Please note that:
- SqlDataAdapter.Fill() can only be called at runtime (not at design time)
- the DataSet generated is located in a special unit DataSet1Unit.pas that
was created by Delphi when we clicked "Generate Dataset"
- 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
- if we had used a BdpAdapter, we could have opened the BdpAdapter at design
time (and would have seen live data at design time)
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.
Lets display the invoices with their related items. The first trial is to build
a JOIN between both tables:
|
