数据库系统应用程序开发

数据库系统应用程序开发11.1 ADO对象模型Microsoft ActiveX Data Objects (ADO)enable your client applications to access and manipulate data from a database server through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.一、数据访问解决方案1、问题的提出：We want a simple, consistent application programming interface (API) that enables applications to gain access to, and modify a wide variety of data sources.∙ a text file∙ a cluster of different types of databases∙or something yet to be inventedthe typical data source is a relational database that is manipulated with commands written in Structured Query Language (SQL).API:∙ODBC:Open Database Connectivity (ODBC)is a standard interface for data access.∙OLE DB：The general solution Microsoft offers to this problem is OLE DB, a set of Component Object Model (COM) interfaces thatprovide uniform access to data stored in diverse informationsources. However, because the OLE DB application programminginterface is designed to provide optimal functionality in a wide variety of applications; it does not meet the requirement forsimplicity.∙ADO：ADO is a bridge between the application and OLE DB.2、解决方案：ADO defines a programming model：the sequence of activities necessary to gain access to and update a data source.∙The programming model summarizes the entire functionality of ADO.∙The programming model suggests an object model—the set of objects that correspond to and implement the programming model.对象拥有：∙方法—which perform some operation on data∙属性—which either represent some attribute of the data or control the behavior of some object method.∙事件—which are notifications that some operation has occurred, or is about to occur.二、ADO程序设计模型1、ADO程序设计基本模型ADO 提供进行下列任务的方法:1.连接到数据源2.指定对数据源访问的命令3.执行命令4.如果命令返回数据行，把它们存储在缓存中5.如果需要，从缓存中的数据更新数据源6.提供检测错误的方法一般情况下，可按上述步骤进行处理，但不一定使用全部，可灵活使用。

2、模型中的主要元素ADO程序模型中包括下面的元素：∙连接（connection）：连接是一个交换数据的环境，应用程序通过一个连接访问数据源。

∙命令(command)：命令对数据源中的数据进行操作,如增加、修改、删除、查询等，命令通过连接发送。

∙参数（Parameter）：命令所需的变化部分。

∙记录集（Recordset）：记录集是存放查询结果的一个存储区，可实现按行访问。

∙字段（Field）：记录集中的一个列，对数据源的操作通过字段进行。

∙错误（Error）：不适当的操作可能产生一个错误。

∙属性（Property）：每个对象有一组属性描述或控制对象的行为。

∙集合（Collection）：集合是一种对象，包含有若干特定的对象。

∙事件（Event）：事件是某种操作完成或即将发生的通知。

3、 ADO对象模型为了支持ADO程序模型, ADO对象模型中设计了若干对象来完成相应的功能。

ADO∙Connection对象：建立与数据源的连接∙Command对象：指定一个SQL语句,并可执行此语句。

∙Recordset对象：创建、保存结果集，访问数据行。

其余对象以集合的形式归独立对象所有：∙Errors集合：属Connection对象所有，提供与单个操作有关的所有错误信息。

由Error对象组成，每个Error对象代表一个错误信息。

∙Parameters集合：属Command对象所有，提供所需得参数。

由Parameter 对象组成，每个Parameter对象提供一个参数。

∙Fields集合：属Recordset对象所有，包含Recordset对象的所有字段。

由Field对象组成，每个Field对象代表一个字段。

∙Properties集合：每个Connection、Recordset、Command或Fields对象都包含一个Properties集合，代表ADO对象得动态特征。

由Property 对象组成，每个Property对象代表一个动态特征。

4、使用ADO对象的程序设计模型The goal of ADO is to gain access to, edit, and update data sources. The programming model embodies the sequence of activities necessary to accomplish this goal. ADO provides classes and objects to perform each of the following activities:∙Make a connection to a data source (Connection). Optionally, begina transaction.∙Optionally, create an object to represent a command, for example, an SQL command (Command).∙Optionally, specify columns, tables, and values in the SQL command as variable parameters (Parameter).∙Execute the command (Command, Connection, or Recordset).∙If the command is row-returning, store the rows in a cache (Recordset).∙Optionally, create a view of the cache so you can sort, filter, and navigate the data (Recordset).∙Edit the data by adding, deleting, or changing rows and columns (Recordset).∙If appropriate, update the data source with changes from the cache (Recordset).∙If a transaction was used, accept or reject the changes made during the transaction. End the transaction (Connection).5、VB中使用ADO访问数据库示例Sub Main()Dim cnn As New ADODB.ConnectionDim cmd As New mandDim rst As New ADODB.Recordset' Step 1 - Open a Connectioncnn.Open"DSN=Pubs;uid=sa;pwd=;"' Step 2 - Create a CommandSet cmd.ActiveConnection = cnnmandText= "SELECT * from Authors"' Step 3 - Execute the Commandrst.CursorLocation = adUseClientrst.Open cmd, , adOpenStatic, adLockBatchOptimistic' Step 4 - Manipulate the Datarst!au_lname.Properties("Optimize") = Truerst.Sort = "au_lname"rst.Filter = "phone LIKE '415 5*'"rst.MoveFirstDo While Not rst.EOFDebug.Print "Name = "; rst!au_fname; " "; rst!au_lname ; _", Phone = "; rst!phonerst!phone = "777" & Mid(rst!phone, 4)rst.MoveNextLooprst.Filter = adFilterNone' Step 5 - Update the Datacnn.BeginTransOn Error GoTo ConflictHandlerrst.UpdateBatch'Step 6, part A - Conclude the Update (Accept changes) mitTransExitTutorial:On Error GoTo 0rst.Closecnn.CloseExit Sub'Step 6, part B - Conclude the Update (Reject changes) ConflictHandler:rst.Filter = adFilterConflictingRecordsrst.MoveFirstDo While Not rst.EOFDebug.Print "Conflict: Name="; rst!au_fname;" ";rst!a u_lnamerst.MoveNextLoopcnn.RollbackTransResume ExitTutorialEnd Sub11.2 ADO程序设计根据ADO程序设计模型，详细实现步骤和相关对象如下。