Application Development in Visual Basic CP3013

1
Application Development in Visual Basic - CP3013

• Data Access Technologies

2
Data Access Technologies

• Introduction to
• Data Access Technologies

3
Areas To Be Covered

• What are Data Access Technologies (DAT)
• Customer Reqts of DAT
• Common DAT
• Current DAT Overview
• Latest DAT

4

• What are Data Access Technologies (DAT)

5
What are Data Access Technologies

• Components for connecting applications to
  databases / data sources
• We will consider current Database Client
  Technologies for
• Use with Windows Platform
• Will look in detail at VB aspects
• Technology complex unless
• understand each one
• know how they relate to each other

6
Introduction Database Client Technologies
(contd)

• Some work independently others rely on others
• Look at strengths and weaknesses of each one
• Answering when and were to use each one

7
Terminology

• Database Access Client Technologies
• Connecting to a database and
• Doing it with client applications
• Data Access Technologies
• Encompasses all types of data sources, in line
  with current MS thinking on UDA
• Were still developing clients applications
• May be other derivatives

8
What they do

• Provide abstractions
• Databases very complex pieces of s/w
• Programs to communicate via native interface can
  be very complicated
• Database client technologies simplify this
• Provide an interface that is less complex than
  underlying database

9
Developer Benefits

• Can write relatively simple programs that
  leverage an enormous amount of code (code that
  resides in the database) to perform very complex
  tasks.
• A good database interface is like a magnifying
  glass for your code
• A few lines of code in application can access
  many line of database code

10
Code Leverage
Database Interface
Database Code being Leveraged
Client Application Code
11
Database Native Interface

• Accessing a dbs native interface is
• Complex
• Give limited and inflexible applications
• Tie application to that db
• To change to another db can be
• very difficult
• time consuming
• maybe impossible

12
Database Client Technologies Provide

• Uniform interface for communication to different
  and disparate db systems
• with Modern data access interfaces can write
• single program to perform complex operations
• using multiple types of dbs

13
Interface Overview
RDBMS Database Server
DatabaseInterface
Client Application Code
HostSystem
ISAMFiles
14
Uniform Interface

• A good interface
• Magnifies code
• Uniform interface to different db systems
• Several interfaces have been developed
• differ in what they accomplish and
• how they go about it

15

• Customer Reqts of Data Access Technologies

16
Main Criteria

• Four main criteria used in the decision-making
  process
• High-performance access to data
• Reliability
• Vendor commitment.
• Broad industry support.

17
High Performance Access to Data

• Provide same level of performance possible
  through the data providers proprietary API.
• Customers will not compromise performance, for
  any other benefit.
• Services that augment native capabilities of data
  providers must be held to the same standards must
  not stand in the way of native performance.
• Applications and components frequently scale to
  support hundreds or thousands of concurrently
  connected users, performance must be maintained
  as usage grows.

18
Reliability

• Customers want database solutions to perform
  reliably.
• use terms such as rock-solid and fail-safe to
  describe requirements in this area.
• need to minimize maintenance and support costs,
• reduce the total cost of ownership.

19
Vendor Commitment

• Customers indicate they are making strategic
  commitments to vendors of data access
  technologies, and are looking for reciprocation.
  
• Indicate database decisions are long term,
• Not purchasing a single release but a string of
  database and related product releases.
• On flip side, customers are wary of becoming too
  dependent on a single vendor, a situation they
  term vendor lock-in or vendor tie-in.
• New technologies should evolve gracefully from
  current, to avoid costly replacement of existing
  capabilities.

20
Broad Industry Support

• Defined as market share, as well as support of
  vendors of related products and technologies.
• For customers, broad industry support is more
  important gauge than blessing of a standards body
  when choosing data access products.
• Broad industry support carries many benefits
• safety in numbers,
• availability skilled people to work with
  products, and
• products that work together without expensive
  integration and customization.

21

• Common Data Access Technologies

22
Common Data Access Technologies - Current

• ODBC (open database connectivity)
• MFC (Microsoft Foundation Classes) ODBC classes
• JET
• DAO (Data Access Objects)
• RDO (Remote Data Objects)
• OLE DB (object-linking and embedding database)
• ADO (ActiveX Data Objects)

23
Common Data Access Technologies - Lates

• New technologies part of .NET framework
• ADO.NET
• SQL Server.Net
• OLEDB.NET

24
Connectivity Architecture
Application
Data Control
ADO DC
RDC
MFC
RDO
DAO
ADO
OLE DB
JET
ODBC
SQL Data e.g. Oracle, FoxPro, SQL Server, Jet,
Other
Non SQL Data e.g. Mail, Video, Directory
Services, Text, Other
Mainframe Legacy Data
25
VB Associated Types

• Database Client Technologies changed with
  practically each new version of VB
• VB3 DAO / DAC
• VB5 RDO / RDC
• VB6 ADO
• VB.NET ADO.NET
• Were looking at above, plus consider others e.g.
  JET, ODBC, MFC, OLE DB

26
Packaged (in VB)Data Access Technologies

• DAO (Data Access Objects) VB5 Prof and Enter
  only
• Data Control
• RDO (Remote Data Objects)
• RDC (Remote Data Control) Enterprise Only VB5
• ADO (ActiveX Data Objects) VB6
• ADO - Data Control
• ADO.NET VB.NET

27
Non-PackagedDatabase Client Technologies

• JET
• ODBC (open database connectivity)
• MFC (Microsoft Foundation Classes) ODBC classes
• OLE DB (object-linking and embedding database)

28

• Current Data Access Technologies Overview

29
Current Data Access Technologies Overview

• Going to overview each of following
• ODBC
• MFC
• DAO
• JET
• RDO
• UDA
• OLE DB
• ADO

30
ODBC (What is it) Open Database Connectivity

• Created Late 80s
• Uniform interface to write client s/w for
  relational dbs
• Popular - accepted as standard for relational
  database
• Single API for client application to work with
  different dbs

31
ODBC API(The Interface)

• Applications using ODBC API can
• communicate with any relational db
• for which there is an ODBC driver
• Compared to other db interfaces it is a low level
  interface
• Enables client application to
• configure and
• control the relational database at a relatively
  low level

32
ODBC - Limitations

• Limited to relational dbs
• Due to relational nature, difficult to use for
  non-relational data sources, such as
• Object dbs
• non-relational dbs
• network directory services
• email stores
• etc

33
ODBC - Provides

• ODBC Driver Manager (ODBC32.DLL
• Calls functions in the ODBC drivers (DLLs) to
  perform operations on the database
• Import Library (ODBC32.LIB)
• Client applications link to it to use functions
  exposed by the ODBC driver manager
• ODBC header files for the ODBC API
• Doesnt support Embedded SQL uses Call Level
  Interface (see later)

34
ODBC Architecture
RDBMS Database Server
ODBC Drivers
ODBCDriverManager
Client Application Code
HostSystem
ISAMFiles
ODBC32.DLL?
35
MFC ODBC Classes

• Not generally used with VB, mainly used with OO
  development in C
• Easier to use than ODBC API
• Not got low level control
• A higher level db interface

36
DAO - Data Access Objects

• A set of (COM) Automation Interfaces for
  MSAccess/JET database engine
• Talks directly to Access/JET Dbs
• Can also talk to other dbs through the JET engine
• DAO requires the JET engine

37
DAO Architecture
RDBMS
DAO Automation Server (DLL)
Access/Jet Database Engine
ODBC Driver Manager
Client Application Code
ODBCDrivers
ISAMFiles
DAO3032.DLL?
MDB Files
38
COM - Recap

• MS Component Object Model
• reusable code
• low level spec on how components communicate
• language, location and compiler independent

39
DAO Architecture

• COM - based automation interfaces provides more
  than a function based API
• DAO provides an object model for db programming
• Set of objects for
• connecting to a db and
• performing operations on the data
• High level database interface

40
DAO for OO

• Better suited to OO development than straight API
  as provides a set of classes
• DAO objects integrate easily into source code of
  an OO application
• Integrating API functions into OO applications
  requires developer to write own set of classes to
  encapsulate API functions

41
DAO Object Model with Access Database

• Encapsulates structural pieces of Access database
  e.g.
• Tables
• Query
• Indexes etc
• Therefore without having to use SQL DDL
  statements can directly modify the
• structure or
• schema of access database

42
DAO Limitations

• Several layers of software are involved
• If talking to a db server such as Oracle or SQL
  Server
• All calls into and out of the db must pass
  through the Access/Jet engine
• Can be a significant bottleneck for applications
  using a db server
• Easier than ODBC API but not got low level control

43
DAC

• One of VBs intrinsic controls
• Provides simple properties, methods and events
  interface to variety of data formats
• Sits on top of DAO and relies on it to perform
  processing
• Requires JET as it sits on top of the DAOs

44
Jet - Joint Engine Technology

• A set of DLLs containing callable procedures that
  read and write dbs
• Can access ODBC data sources using
• JET, DAO and Data Control
• not recommended as gives an excessive processing
  overhead in most cases
• as JET is optimised for MSAccess db not ODBC data
  sources

45
RDO - Remote Data Objects

• Originally developed as an abstraction of ODBC
  API for VB programmers
• Therefore closely tied to VB and ODBC
• Easier to use than ODBC API
• But not got low level control
• Therefore a high level db interface

46
RDO - features

• RDO calls the ODBC API directly rather than
  through Jet, like DAO.
• Therefore provides good performance for
  applications that use relational database servers

47
RDC

• One of VBs intrinsic controls (VB5)
• Provides simple properties, methods and events
  interface to variety of data formats
• Sits on top of RDO and relies on it to perform
  processing like DAC / DAO
• As sits on top of RDO it
• does not rely on JET to perform database
  processing
• relies on ODBC only

48
Definition of the Universal Data Access Strategy
(1)

• Platform, application and tools initiative
• defines / delivers standards and technologies
• key element in MSs foundation for application
  development, the MSs Windows Distributed
  interNet Applications (DNA) architecture.
• High-performance access to variety of data and
  information sources on multiple platforms
• Easy-to-use programming interface that works with
  practically any tool or language, leveraging
  technical skills developers already have.

49
UDA Architecture
50
Definition of the Universal Data Access Strategy
(2)

• Technologies that support UDA enable
  organizations to
• create easy-to-maintain solutions and
• use their choice of best-of-breed tools,
  applications and data sources on the client,
  middle-tier, or server.
• It does not require
• expensive and time-consuming movement of all
  corporate data into a single data store,
• commitment to a single vendors products.

51
Definition of the Universal Data Access Strategy
(3)

• Based on open industry specifications
• with broad industry support and
• works with all major established database
  products.
• Evolutionary step from todays standard
  interfaces including ODBC, RDO and DAO
• Extends functionality of well-known and
  well-tested technologies.

52
Unified Data Access Model Based on COM (1)

• Delivered through common set of OO interfaces.
• Interfaces based on COM
• COM used because it provides
• integrated services, including transactions,
  security, message queuing and data access to
  support a wide range of application scenarios
• widest choice of tools from multiple vendors
  using multiple development languages
• large customer base for customisable applications
  and reusable components
• works with current users / developers systems

53
Unified Data Access Model Based on COM (2)

• As works with COM, UDA
• is open and works with virtually any tool or
  programming language.
• able to provide consistent data access model at
  all tiers of the application architecture.
• UDA exposes COM-based interfaces optimized for
  both
• low-level (OLE DB) and
• high-level application development (ADO.)

54
Universal Data Access (1)

• Architecture designed to provide uniform access
  to a variety of data sources where
• ADO, a provider-neutral and language-independent
  data access object model
• Works with OLE DB and its service component
  architecture, which extends a providers native
  functionality transparently to the application
  and the provider

55
Universal Data Access (2)

• Architecture represents new approach to extending
  database functionality dynamically at run time by
  aggregating OLE DB services using standard COM
  aggregation.
• Currently OLE DB providers include Oracle, SQL
  Server, IBM DB2, Sybase, Informix, CA-Ingres, and
  ODBC sources.
• OLE DB, ADO, and other services are part of the
  Microsoft Data Access 2.0 (2.1) Components.

56
OLE DB - object-linking and embedding database

• Expands on ODBC in two ways
• Provides an COM (OLE) interface for db
  programming
• Provides an interface to both relational and
  non-relational data sources
• OLE name for COM when OLE DB created
• Hence why OLE DB rather than COM DB
• COM is name for MS component technology
• OLE associated with UI components

57
OLE - Recap

• Object Linking and Embedding
• Used to integrate programs (mainly MS)
• Can use one application from within another,
  without leaving the originals interface
• e.g. using a excel spreadsheet within a word
  document

58
OLE DB features

• Provides a COM interface for db programming,
  which is
• more robust
• more flexible leading to
• better performance
• more robust error handling
• interfacing for non-relational data sources
• Than traditional call-level interfaces, such as
  the ODBC interface

59
OLE DB cf ODBC

• Both low level database API
• OLE DB incorporates functionality of ODBC for
  relational databases
• Extends to provide access to non-relational data
  sources
• Two kinds of OLE DB software
• consumers and
• providers

60
OLE DB Architecture
OLE DBProviders
OLE DBConsumer
ODBC Data Sources
OLEDB
Client Application Code
MSDASQL.DLL
ADO
SQL Server
Db Client Application that uses OLE DB
SQLOLEDB.DLL
MDB Files
MSJTOR35.DLL
61
Consumer / Providers

• Consumer
• Any application that uses OLE DB interfaces
• e.g. an application VB which uses OLE DB to
  connect to db server is an OLE DB consumer
• Providers
• DLLs that implement OLE DB interfaces and do
  communication with the data source
• Similar to ODBC drivers, except implement COM
  interfaces instead of API functions

62
Access to any data source

• OLE DB gives access to any data source for which
  there is a OLE DB provider e.g.
• email stores, object databases, network,
  directories andother non-relational data sources
• The OLE DB provider MSDASQL.DLL
• can talk to ODBC data sources.
• therefore if ODBC driver but no OLE DB provider,
  can still interface

63
OLE DB - Finally

• OLE DB
• exposes a set of COM interfaces that can be
  called from your program
• doesnt offer an Automation interface
• Future of database client developments in Windows
• MS developments focused on OLE DB
• ODBC unlikely to be updated

64
ADO ActiveX Data Objects

• Built on top of OLE DB
• OLE DB consumer
• Applications using ADO, use the OLE DB interfaces
  directly
• Provides object model for db programming
• similar to DAOs object model
• more flexible
• e.g. can create recordset in ADO without first
  creating a connection object (unlike DAO)

65
ADO and OLE DB

• OLE DB is
• Large
• Complex
• programs using it, must use complex COM
  interfaces
• ADO
• much simpler to use than OLE DB
• classified as a high level db interface

66
ADO Usage

• Can be used with more programming languages than
  OLE DB
• Provides an Automation Interface, so
• can be used by scripting languages such as
  VBScript and Java Script
• OLE DB cant be used by scripting languages
• VB can access OLE DBs COM Interface?

67
ADO DC

• One of VBs intrinsic controls (VB6)
• The ADO equivalent of RDC and DAC, VB6 still
  comes with DAC (DAO)
• Provides simple properties, methods and events
  interface to variety of data formats
• Sits on top of ADO and relies on it to perform
  processing

68
OLEDB/ADO - Summary

• OLE DB / ADO have promising future
• Microsoft is developing these
• Others not discontinued, but not being developed
  by Microsoft
• OLE DB gives unparalleled power and flexibility,
  low level requires more code
• ADO flexible but simple object model with
  reasonable performance

69
Database Client TechnologiesStrengths /
Weaknesses
70
Descriptors for Strengths / Weakness

• Object Model does technology provide an object
  model that lends itself to OO programming
• Nonrelational data sources capability to access
  non relational data (all access relational dbs)
• Low-level control amount of db configurability
  and low level control for working with relational
  db servers
• Performance excecution overhead technology
  imposes accessing relational db servers
• Code-to-functionality ratio how much code to
  write compared to db functionality gained from
  that code

71

• Latest Data Access Technologies Summary

72
.NET Framework

• New computing platform
• Aims to simplify application development in
  highly distributed internet environment
• Designed to fulfill following objectives
• To provide consistent OO programming environment
  whether object code is stored and executed
  locally, executed locally but Internet-distributed
  , or executed remotely.
• To provide a code-execution environment that
  minimizes software deployment and versioning
  conflicts.

73
.NET Framework (contd)

• To provide code-execution environment that
  guarantees safe execution of code, including code
  created by unknown or semi-trusted third party.
• To provide a code-execution environment that
  eliminates the performance problems of scripted
  or interpreted environments.
• To make the developer experience consistent
  across widely varying types of applications, such
  as Windows-based and Web-based applications.
• To build all communication on industry standards
  to ensure that code based on the .NET Framework
  can integrate with any other code.

74
ADO.NET

• In .NET Framework, Microsoft introduced ADO.NET
• Evolution of data access architecture provided by
  ADO programming model.
• Does not replace ADO for the COM programmer
• Provides.NET programmer with access to relational
  data sources, XML, and application data.
• Supports variety of development needs, including
  the creation of database clients and middle-tier
  business objects used by applications, tools,
  languages, and Internet browsers.

75
ADO.NET (contd)

• Built with the ADO programmer in mind
• contains many similarities to ADO, and also
• introduces several new concepts in its design.

76
Data Providers

• Connect to a db, Execute commands and Retrieve
  results, which are either
• Processed directly or
• Passed to an ADO.Net Dataset
• Designed to be lightweight
• minimal layer between data source and code
• increasing performance without sacrificing
  functionality
• .Net framework provides two
• SQL Server.Net and
• OLEDB.NET

77
SQL Server.NET

• Uses own protocol to communicate with SQL Server
• Optimised to access SQL Server directly without
  ODBC / OLE DB layer
• Need access to MS SQL Server 7.0 or later

SQL Server .NET Data Provider
MS SQL Server 7.0 or later
78
OLEDB.NET

• Uses native OLEDB through COM to enable data
  access

OLE DB.NET Data Provider
OLE DB Service Component
OLE DB Provider
MS SQL Server 7.0 or later
79
Conclusion

• High level technologies give code leverage
• Low level technologies give more control over
  data source
• Latest give access to datasources not just
  databases
• Current for VB is DAO and ADO
• Future is ADO.NET
• Well be discussing these in more detail