Title: Application Development in Visual Basic CP3013
1Application Development in Visual Basic - CP3013
2Data Access Technologies
- Introduction to
- Data Access Technologies
3Areas 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)
5What 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
6Introduction 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
7Terminology
- 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
8What 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
9Developer 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
10Code Leverage
Database Interface
Database Code being Leveraged
Client Application Code
11Database 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
12Database 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
13Interface Overview
RDBMS Database Server
DatabaseInterface
Client Application Code
HostSystem
ISAMFiles
14Uniform 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
16Main Criteria
- Four main criteria used in the decision-making
process - High-performance access to data
- Reliability
- Vendor commitment.
- Broad industry support.
17High 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.
18Reliability
- 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.
19Vendor 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.
20Broad 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
22Common 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)
23Common Data Access Technologies - Lates
- New technologies part of .NET framework
- ADO.NET
- SQL Server.Net
- OLEDB.NET
24Connectivity 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
25VB 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
26Packaged (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
27Non-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
29Current Data Access Technologies Overview
- Going to overview each of following
- ODBC
- MFC
- DAO
- JET
- RDO
- UDA
- OLE DB
- ADO
30ODBC (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
31ODBC 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
32ODBC - 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
33ODBC - 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)
34ODBC Architecture
RDBMS Database Server
ODBC Drivers
ODBCDriverManager
Client Application Code
HostSystem
ISAMFiles
ODBC32.DLL?
35MFC 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
36DAO - 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
37DAO Architecture
RDBMS
DAO Automation Server (DLL)
Access/Jet Database Engine
ODBC Driver Manager
Client Application Code
ODBCDrivers
ISAMFiles
DAO3032.DLL?
MDB Files
38COM - Recap
- MS Component Object Model
- reusable code
- low level spec on how components communicate
- language, location and compiler independent
39DAO 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
40DAO 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
41DAO 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
42DAO 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
43DAC
- 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
44Jet - 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
45RDO - 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
46RDO - features
- RDO calls the ODBC API directly rather than
through Jet, like DAO. - Therefore provides good performance for
applications that use relational database servers
47RDC
- 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
48Definition 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.
49UDA Architecture
50Definition 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.
51Definition 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.
52Unified 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
53Unified 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.)
54Universal 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
55Universal 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.
56OLE 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
57OLE - 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
58OLE 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
59OLE 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
60OLE 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
61Consumer / 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
62Access 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
63OLE 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
64ADO 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)
65ADO 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
66ADO 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?
67ADO 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
68OLEDB/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
69Database Client TechnologiesStrengths /
Weaknesses
70Descriptors 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.
74ADO.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.
75ADO.NET (contd)
- Built with the ADO programmer in mind
- contains many similarities to ADO, and also
- introduces several new concepts in its design.
76Data 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
77SQL 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
78OLEDB.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
79Conclusion
- 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