Transcript Delphi World Tour - Embarcadero Conferences Home Page

Developing Database Applications
with the Microsoft .NET Framework
Cary Jensen
Jensen Data Systems, Inc.
Data Access in Delphi 2005 for .NET
 BDE.NET
 dbExpress.NET
 dbGO (ADO for .NET)
 MyBase.NET
 IBExpress.NET
 DataSet Client for .NET
 ADO.NET
 BDP.NET
VCL.NET vs ADO.NET
VCL.NET
BDE.NET
dbExpress.NET
dbGo (ADO for .NET)
ClientDataSets
IBX.NET
DataSnap .NET Client
Third-Party VCL for .NET
Datasets
ADO.NET
ADO.NET
BDP.NET
Third-Party .NET Data
Providers
BDE Advantages
 If you already have it installed and working for existing
applications, new applications require little or no
configuration to use it.
 Many legacy applications exist which use it.
 Two or more applications written in Delphi can easily
share global aliases.
 It has relatively good performance across a large
number of data types
 It provides access to installed ODBC drivers
 Available for Windows and .NET
BDE Limitations
 In the typically deployment of BDE-based applications, a copy of the BDE
must be installed and configured on each workstation (a network installation
is possible, but requires manual registry configuration)
 The BDE is somewhat inefficient, in that it stores as many as three copies of
table metadata
 When used with local tables such as Paradox tables, failure to correctly
configure the BDE can produce periodic index corruption
 If a new BDE application is incorrectly installed, existing BDE applications
may fail to initialize properly
 Not available for Linux
 Borland SQL Links for Windows have been deprecated
dbExpress Advantages
 Data-related operations in dbExpress tend to be faster
than the BDE
 dbExpress-based applications can be deployed on
Win32, .NET, and Linux platforms
 Applications designed to use dbExpress can more easily
be ported to using DataSnap
 dbExpress applications require fewer support files and
can require far less configuration than BDE applications
dbExpress Limitations
 dbExpress applications are generally more
complicated than other types of Delphi database
applications to program. In addition to requiring more
components to access and edit data, dbExpress
applications generally require more lines of code than
non-dbExpress applications
 No equivalent to midaslib, dbexpint units. Must deploy
midas.dll, dbexpint.dll, dbxintf.dll, etc.
 There are few dbExpress drivers currently available.
 Information about deploying dbExpress application
compiled using Delphi 8 is scarce
MyBase.NET Advantages
 ClientDataSet applications have minimal installation
requirements
 The data can be stored in XML format, making it easy to
access this data from outside of the application
 Data can be loaded and saved without code. Simply point
the ClientDataSet's FileName property to a file that
contains the proper XML or binary client dataset data, and
it will take care of the rest. (It is better to explicity load and
save using SaveToFile and LoadFromFile.)
 Available in Windows, .NET, and Linux
MyBase Limitations
 Because the records in a client dataset are stored in-memory, the
number of records that you can load is limited by the amount of
real and virtual memory available on your machine
 Large files may take a significant amount of time to load from a file
into a client dataset
 ClientDataSet applications are limited to single user applications.
If you want to create a multiuser application, you must use one of
the other available technologies
 Requires the deployment of additional DLLs (midas.dll, …)
dbGo (ADO for .NET) Advantages
 Any Windows-based computer shipped in the past few
years already has MDAC installed. As a result, most ADObased applications require only the installation of the
specific OLE DB provider, at most.
 ADO can be used from a wide range of development
environments, including MS Access, VisualBasic, Delphi,
Visual C++, to name of few.
 A wide range of OLE DB providers are available.
dbGo (ADO for .NET) Limitations
 While MDAC is available on many machines, it is not available on all (for
example, Windows 95 machines do not have MDAC installed by default).
These machines must have MDAC (and DCOM) installed before an ADOenabled application can run
 ADO is available only for the 32-bit Windows operating system
 Having MDAC installed and configured is not enough. In some cases, you
must also install and register an appropriate OLE DB provider
 Performance using ADO is largely reliant on the quality of the OLE DB provider
that you are using
 Applies to VCL.NET only
 If you are using the OLE DB provider for ODBC, an appropriate ODBC driver
must also be installed and configured
InterBase Express Advantages
 InterBase Express provides access to InterBase without
the BDE
 Because InterBase Express components are designed
specifically for InterBase, they include a number of
specialized components that access and leverage
InterBase features
 Interbase Express components are available for
Windows, .NET, and Linux
InterBase Express Limitations
 InterBase Express works only with the InterBase database server
DataSnap Advantages
 DataSnap client applications have few installation
requirements
 Business rules can be implemented on a server,
permitting these rules to be updated independent of
the individual client applications
 They provide a reliable, distributed upgrade path to
dbExpress and MyBase applications
DataSnap Limitations
 While DataSnap offers additional flexibility over traditional client/server
solutions, it requires more development. Specifically, in addition to writing
the client application, you also need to write the application server.
 Cannot write DataSnap servers in Delphi 8
 Delphi 8 only supports DCOM connections
 Delphi 8 does not support load balancing or fail-over
 DataSnap applications require an additional license before they can be
deployed. This license can be found in the Enterprise edition of Delphi.
 DataSnap servers must also employ one of the other data access
mechanisms
 As with other ClientDataSet examples, you must deploy midas.dll.
Advantages of Third-Party Data Access Mechanisms
They likely provide optimized access to a particular file type or
database server.
They may support the familiar TDataSet interface.
They are likely to provide support for proprietary database features.
As is the case with the Advantage TDataSet Descendant for Delphi
8, they may provide cross-platform support
Limitations Third-Party Data Access Mechanisms
They likely support only one vendor's database
You must rely on the producer of the third-party data access
mechanism to stay current with Borland technologies
Advantages of ADO.NET
Supported by all first-class .NET languages
Native to the FCL
Results in smaller executables
Works with third-party .NET data providers
Powerful in-memory DataSets
Tight integration with XML
Limitations of ADO.NET
Only for the .NET platform
Compared with TDataSet.NET solutions, sports a different interface
from previous Delphi implementations
When using with third-party .NET data providers, requires the
deployment of additional assemblies
Different type namespaces for different .NET data providers
Advantages of BDP.NET
 Portable code : Write once and connect to all supported DB’s
 Open Architecture: Lets you add support to more DB’s easily
 Logical Data types mapped to .NET Native types
 Consistent data type mapping across DB’s
 Unlike OLEDB .NET Provider, no need to go through a COM interop
layer.
 Support for Database specific features
 Live, Delphi-like design-time data views
 Valuable component editors
Limitations of BDP.NET
 Yet another proprietary data access mechanism
 Requires the installation of additional assemblies into the global
assembly cache
 There are few BDP.NET supported databases
 Uses the ADO.NET interfaces as opposed to the TDataSet interfaces
 Current design-time code generation is aggressive, providing more
features than you may want
 Not supported on Win32 or Linux platforms
ADO.NET Major Interfaces and Classes
DataSet
DataTableCollection
IDbDataAdapter
IDataReader
DataRelationCollection
DataTable
DataRowCollection
IDbCommand
IDbParameter
IDbConnection
DataColumnCollection
DataRow
DataColumn
DataRelation
DataView
Constraint
Database
DataRowView
XML Document
DataSets, DataTables, and DataRows
var
Table: DataTable;
Row: DataRow;
data: String;
begin
Table := DataSet1.Tables[0];
Row := Table.Rows[2];
data := Row[1].ToString();
DataSet
Table1
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
r3c1
r3c2
r3c3
r3c4
r3c5
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
Table2
DataSets, DataTables, and DataRows
var
data: String;
begin
data := DataSet1.Tables[0].Rows[2][1].ToString()
DataSet
Table1
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
r3c1
r3c2
r3c3
r3c4
r3c5
r1c1
r1c2
r1c3
r1c4
r1c5
r2c1
r2c2
r2c3
r2c4
r2c5
Table2
Controls and CurrencyManagers
 All controls, that is, classes that descend from Control in
the .NET FCL, are data aware, and can be bound to a
data source. A BindingManagerBase instance is created
anytime a control is bound to a new data source.
 Controls that are bound to the same data source are
bound to the same BindingManagerBase instance.
BindingManagerBase is an abstract class, and two
concrete classes descend from it. These concrete classes
are CurrencyManager and PropertyManager.
Creating a Data Module
 Create a form that will serve as the data module (let’s call this form the data
module) Add the ADO.NET components that you want to share to the public
section of the data module's class declaration.
 When the data module is created, create the instances of, and configure, the
ADO.NET classes that you added to the form's public section
 Add one Control descendant to the data module for each data source that you
want to use in other forms. Bind each control to the data source you want to
expose.
 Declare a variable of the data module class type within scope of each form that
must use a data module data source
 For each form that uses the data module, assign that form’s BindingContext
property to the data module's (form-level) BindingContext property.
 For each Control descendant on each form, set the binding context (or
DataSource and DataMember properties) to the appropriate data source on the
data module.
Field References in Expressions
 Use the ColumnName property to reference fields
 if ColumnName contains one of the following characters,
the field reference must be enclosed in brackets:
(space), \n (newline), \t (tab), \r (carriage return), ~, (, ), #,
\, /, =, >, <, +, -, *, %, &, |, ^, ‘, “, [,]
 For consistency, you can use brackets even when the
column name does not include one of the special
characters.
 If your column name includes the ] character, it must be
preceded by a \.
Field Reference Examples
Column Name
AccountNo
Account#
Last Name
Column[1]
Reference
AccountNo
[Account#]
[Last Name]
[Column[1\]]
Literals
 Strings enclosed in single quotes
ex: 'Mr ' + [Contact Name]
 Dates enclosed between # characters:
ex: [Date] - #1/1/2004#
 Numeric values, including decimal values and scientific
notation, are not delimited
ex: [Percent] * 100
String Literals and Delphi
Consider the following string assignment (in C#):
dataColumn1.Expression = "'Mr ' + [First Name] + ' ' + [Last Name] ";
String literals are more complicated for Delphi developers, since single quotes are
already used to delimit strings. For example:
DataColumn1.Expression := '''Mr ''' +
' + [First Name] + ' + ''' ''' + ' + [Last Name]';
You can also use the QuotedStr function in Borland.Vcl.SysUtils:
DataColumn1.Expression := QuotedStr('Mr ') +
' + [First Name] + ' + QuotedStr(' ') + ' + [Last Name]';
Alternatively, enclose string literals between #39, like this:
DataColumn2.Expression := #39 + 'Mr ' + #39 +
' + [Invoice No] + ' + #39 + ' ' + #39 + ' + [Customer ID]';
You can even omit the concatenation operator (+) between #39 and strings:
DataColumn2.Expression :=
#39'Mr '#39' + [Invoice No] + '#39' '#39' + [Customer ID]';
Comparison operators
(in order of precedence)
<
>
<=
>=
<>
=
IN
LIKE
Boolean Operators
 ( expression)
 AND
 OR
 NOT
Programmatically Create Calculated Fields
 Create a DataColumn in your DataTable that will hold the
calculated field
 After loading data, iterate through the DataTable and assign the
calculation. Then make the calculated field to readonly
 Add a ColumnChanged event handler to the DataTable
 From the ColumnChanged event handler, test whether the
changed field is one of those involved in the calculation. If so,
make the calculated field writeable, re-calculate the calculation,
then make the field readonly once again
DataSync and DataHub
 New components introduced in Delphi 2005
 Provide a richer mechanism for data handling
 Works with both ADO.NET data providers and BDP for
.NET data providers
 Permit live views of data at design time
 Provide a migration path for remote data access
DataSync and DataHub
RemoteServer and RemoteConnection
 Provide automatic .NET remoting for .NET datasets
 Add a RemoteServer to an application
 Connect the RemoteServer to that application’s
DataSync
 Add a RemoteConnection to a client application
 Connect the RemoteConnection to a DataHub to provide
it with access to the DataSync on the server
RemoteServer and RemoteConnection