Transcript Database Programming (using ADO)

Database Programming (using ADO)
• Universal Data Access
– Microsoft strategy for providing access to information across the
enterprise.
– UDA provides high-performance access to a variety of relational
and non-relational information sources
• Text files, Spreadsheets, Relational Databases, Email
– Easy-to-use programming interface that is tool and language
independent.
– Enables you to integrate diverse data sources, create easy-tomaintain solutions, and use your choice of best-of-breed tools,
applications, and platform services
UDA
– does not require expensive and timeconsuming movement of data into a single
data store,
– nor does it require commitment to a single
vendor’s products
– based on open industry specifications with
broad industry support, and works with all
major established database platforms
Microsoft Data Access Components
– the key technologies that enable UDA
– Data-driven client/server applications deployed over the Web or a
LAN can use these components to easily integrate information
from a variety of sources, both relational (SQL) and non-relational
• These components include ActiveX Data Objects (ADO),
OLE DB, and Open Database Connectivity (ODBC)
ActiveX Data Objects (ADO)
• The strategic application programming interface (API) to
data and information.
• ADO provides consistent, high-performance access to data
and supports a variety of development needs, including the
creation of front-end database clients and middle-tier
business objects that use applications, tools, languages, or
Internet browsers.
• ADO is designed to be the one data interface needed for
single and multi-tier client/server and Web-based datadriven solution development
ActiveX Data Objects (ADO)
• ADO provides an easy-to-use interface to OLE DB, which
provides the underlying access to data.
• ADO implements a minimal number of layers between the
front end and data source—all to provide a lightweight,
high-performance interface
• ADO uses the Component Object Model (COM) automation
interface - a language-neutral way of implementing objects
that can be used in environments different from the one
they were created in, even across machine boundaries
• VB.NET uses XML-based SOAP messaging instead of COM
OLE DB
• The Microsoft strategic system-level programming interface
to data across the organisation
• Standardized interface that allows programmers to use a
standard set of programming tools to refer to data from any
source
• OLE DB is designed for relational and non-relational
information sources, including mainframe and hierarchical
databases; e-mail and file system stores; text, graphical,
and geographical data; custom business objects, etc.
OLE DB
• OLE DB defines a collection of COM interfaces that
encapsulate various database management system
services.
• These interfaces enable the creation of software
components that implement such services.
• OLE DB components consist of data providers, which
contain and expose data; data consumers, which use data;
and service components, which process and transport data
(such as query processors and cursor engines).
Database Programming (using ADO)
• ActiveX Data Objects (ADO 2.0)
• Enables your client applications to access and
manipulate data in a database [server] through any
OLE DB provider
• Benefits are ease of use, high speed, low memory
overhead
How to Reference ADO 2.0 in Visual
Basic
• To gain access to the ADO 2.0 objects in Visual Basic, set a
reference to the appropriate ADO type library
• There are two ADO type libraries. One is called ADODB and
is contained in MSADO15.DLL
– It appears in the References dialog box (available from the Project
menu) as "Microsoft ActiveX Data Objects 2.0 Library“
• The other is called ADOR and is contained in
MSADOR15.DLL
– It appears in the References dialog as "Microsoft ActiveX Data
Objects Recordset 2.0 Library"
Database Programming (using ADO)
• The first type library (ADODB) is the larger and more
feature-rich of the two
• It contains the main ADO objects and is the one
used in most circumstances
• The second is a "lightweight" subset of the ADODB
type library that supports only recordsets
Database Programming (using ADO)
Connecting an ADO Data Control to a
Database
• Place an ADO Data Control on the form
• Properties of the ADO Data Control that must
be set:
– ConnectionString: specifies the database file that
the data control will communicate with
– RecordSource: specifies the table that will be
accessed
12
Database Programming (using ADO)
• ActiveX Data Objects (ADO)
This is the
ADO
control
added to
the toolbox
Database Programming (using ADO)
Database Programming (using ADO)
Sample Data Model
represented in Microsoft
Access
Database Programming (using ADO)
• Student Table
Database Programming (using ADO)
• Create an instance of the ADO control on a form
The control is named adoStudent
Database Programming (using ADO)
• Set the CONNECTIONSTRING property
Database Programming (using ADO)
Database Programming (using ADO)
Database Programming (using ADO)
Database Programming (using ADO)
• Set the RECORDSOURCE property
Database Programming (using ADO)
• Set the RECORDSOURCE property
Select
adCmdTable
Select a Table
from the DB
The RECORDSOURCE
property is now set to
tblStudent
Database Programming (using ADO)
• Add Data-Aware (Bound) controls to the form
which are used to display the fields of data in the
DB table (tblStudent)
Name: txtID
Name: txtName
Name: txtAddress
Database Programming (using ADO)
• Set the DATASOURCE and DATAFIELD properties of
each of the Text Boxes
• DATASOURCE = name of the ADO control
(adoStudent)
• DATAFIELD = name of the required field in the DB
table (tblStudent)
Database Programming (using ADO)
• The DATASORCE property
Database Programming (using ADO)
• The DATAFIELD property
Database Programming (using ADO)
• VB Front-End connected to a DB Back-End
MoveLast
MoveFirst
MovePrevious
MoveNext
Data Control with two bound text
boxes
First
Previous
Last
Next
29