Developing Database Applications with the Microsoft 'NET Framework Cary Jensen Jensen Data Systems,

1
Developing Database Applications with the
Microsoft .NET Framework Cary JensenJensen Data
Systems, Inc.
2
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

3
VCL.NET vs ADO.NET
VCL.NET
ADO.NET

• BDE.NET
• dbExpress.NET
• dbGo (ADO for .NET)
• ClientDataSets
• IBX.NET
• DataSnap .NET Client
• Third-Party VCL for .NET Datasets

• ADO.NET
• BDP.NET
• Third-Party .NET Data Providers

4
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

5
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

6
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

7
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

8
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

9
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, )

10
dbGo (ADO for .NET) Advantages

• Any Windows-based computer shipped in the past
  few years already has MDAC installed. As a
  result, most ADO-based 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.

11
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 ADO-enabled 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

12
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

13
InterBase Express Limitations

• InterBase Express works only with the InterBase
  database server

14
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

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

16
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

17
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

18
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

19
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

20
Advantages of BDP.NET

• Portable code Write once and connect to all
  supported DBs
• Open Architecture Lets you add support to more
  DBs easily
• Logical Data types mapped to .NET Native types
• Consistent data type mapping across DBs
• 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

21
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

22
ADO.NET Major Interfaces and Classes
DataSet
DataTableCollection
IDbDataAdapter
DataRelationCollection
IDataReader
DataTable
DataRowCollection
IDbCommand
DataColumnCollection
IDbParameter
DataRow
DataColumn
IDbConnection
DataRelation
DataView
Database
Constraint
DataRowView
XML Document
23
DataSets, DataTables, and DataRows
var Table DataTable Row DataRow data
String begin Table DataSet1.Tables0
Row Table.Rows2 data
Row1.ToString()
DataSet
Table1
Table2
24
DataSets, DataTables, and DataRows
var data String begin data
DataSet1.Tables0.Rows21.ToString()
DataSet
Table1
Table2
25
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.

26
Creating a Data Module

• Create a form that will serve as the data module
  (lets 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 forms 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.

27
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), , (, ), , \, /, , gt, lt, ,
  -, , , , , , , , ,
• 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 \.

28
Field Reference Examples

• Column Name
• AccountNo
• Account
• Last Name
• Column1

• Reference
• AccountNo
• Account
• Last Name
• Column1\

29
Literals

• Strings enclosed in single quotesex 'Mr '
  Contact Name
• Dates enclosed between charactersex Date -
  1/1/2004
• Numeric values, including decimal values and
  scientific notation, are not delimitedex
  Percent 100

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

31
Comparison operators (in order of precedence)

• lt
• gt
• lt
• gt
• ltgt
• IN
• LIKE

32
Boolean Operators

• ( expression)
• AND
• OR
• NOT

33
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

34
(No Transcript)
35
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

36
DataSync and DataHub
37
RemoteServer and RemoteConnection

• Provide automatic .NET remoting for .NET datasets
• Add a RemoteServer to an application
• Connect the RemoteServer to that applications
  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

38
RemoteServer and RemoteConnection
39
Thank You !
40
(No Transcript)