数据库系统应用程序开发

1、第11章 数据库系统应用程序开发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 featur

2、es 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 filea cluster of different types of databasesor something yet

3、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 se

4、t of Component Object Model (COM) interfaces that provide uniform access to data stored in diverse information sources. However, because the OLE DB application programming interface is designed to provide optimal functionality in a wide variety of applications; it does not meet the requirement for s

5、implicity.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 modelt

6、he 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.二、A

7、DO程序设计模型1、ADO程序设计基本模型ADO 提供进行下列任务的方法: 连接到数据源指定对数据源访问的命令执行命令如果命令返回数据行，把它们存储在缓存中如果需要，从缓存中的数据更新数据源提供检测错误的方法 一般情况下，可按上述步骤进行处理，但不一定使用全部，可灵活使用。2、模型中的主要元素ADO程序模型中包括下面的元素： 连接（connection）：连接是一个交换数据的环境，应用程序通过一个连接访问数据源。命令(command)：命令对数据源中的数据进行操作,如增加、修改、删除、查询等，命令通过连接发送。参数（Parameter）：命令所需的变化部分。记录集（Recordset）：记录集

8、是存放查询结果的一个存储区，可实现按行访问。字段（Field）：记录集中的一个列，对数据源的操作通过字段进行。错误（Error）：不适当的操作可能产生一个错误。属性（Property）：每个对象有一组属性描述或控制对象的行为。集合（Collection）：集合是一种对象，包含有若干特定的对象。事件（Event）： 事件是某种操作完成或即将发生的通知。3、ADO对象模型为了支持ADO程序模型, ADO对象模型中设计了若干对象来完成相应的功能。ADO主要对象对象主要功能Connection建立与数据库的连接Command指定、执行SQL语句Recordset创建、保存结果集，访问数据行Parame

9、ter为Command对象提供一个参数Field用于访问Recordset的指定字段Error包含一个错误信息Property访问一个ADO对象属性ADO的集合集合主要功能Errors包含操作过程中产生的所有错误Fields包含Recordset的所有字段Parametes包含Command对象的所有参数Properties包含一个ADO对象的所有属性 ADO具有三个独立的对象：Connection对象：建立与数据源的连接Command对象：指定一个SQL语句,并可执行此语句。Recordset对象：创建、保存结果集，访问数据行。其余对象以集合的形式归独立对象所有：Errors集合：属Conn

10、ection对象所有，提供与单个操作有关的所有错误信息。由Error对象组成，每个Error对象代表一个错误信息。Parameters集合：属Command对象所有，提供所需得参数。由Parameter对象组成，每个Parameter对象提供一个参数。Fields集合：属Recordset对象所有，包含Recordset对象的所有字段。由Field对象组成，每个Field对象代表一个字段。Properties集合：每个Connection、Recordset、Command或Fields对象都包含一个Properties集合，代表ADO对象得动态特征。由Property对象组成，每个Prope

11、rty对象代表一个动态特征。4、使用ADO对象的程序设计模型 CONTROL Internet.HHCtrl.1 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: Mak

12、e a connection to a data source (Connection). Optionally, begin a 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,

13、 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 d

14、ata 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访问数据库示例SubMain()Step1-OpenaConnectionDSN=Pubs;uid=sa;pwd=;Step2-CreateaCommandection=cnn=SELECT*fromAuthorsStep3-Execute

15、theCommandrst.CursorLocation=adUseClientcmd,adOpenStatic,adLockBatchOptimisticStep4-ManipulatetheDatarst!au_lname.Properties(Optimize)=Truerst.Sort=au_lnamerst.Filter=phoneLIKE4155*Debug.PrintName=;rst!au_fname;rst!au_lname;_,Phone=;rst!phonerst!phone=777&Mid(rst!phone,4)Looprst.Filter = adFilterNon

16、eStep5-UpdatetheDataOnErrorGoToConflictHandlerStep6,partA-ConcludetheUpdate(Acceptchanges)ExitTutorial:OnErrorGoTo0ExitSubStep6,partB-ConcludetheUpdate(Rejectchanges)ConflictHandler:rst.Filter=adFilterConflictingRecords Debug.PrintConflict:Name=;rst!au_fname;rst!au_lname LoopResumeExitTutorialEndSub

17、11.2 ADO程序设计根据ADO程序设计模型，详细实现步骤和相关对象如下。一、建立连接访问数据源必须通过一个连接，连接建立了数据交换必须的环境，对数据源的任何操作都是通过连接对象进行的。被连接资源可为：数据库ODBC资源OLE DB提供者资源1、连接字符串：ConnectingString连接到数据源的信息用一个字符串表示，对于不同的提供者和数据源，其内容可能有所不同。OLE DB Provider for Microsoft JetConnectingString =Provider=Microsoft.Jet.OLEDB.4.0; Data Source= databaseName;Us

18、er ID=userName;Password=userPassword;OLE DB Provider for ODBC句法 with a DSN or FileDSN:ConnectingString =DSN=dsnNameFileDSN=fileName; DATABASE=databseName；UID=userName;PWD=userPassword; 句法without a DSN :ConnectingString =DRIVER=SQL Server;Server=serverName;DATABASE=databseName；UID=userName; PWD=userP

19、assword;OLE DB Provider for SQL ServerConnectingString = Provider=SQLOLEDB;Data Source=serverName; Initial Catalog =databaseName; User ID=userName; Password=userPassword;关键词说明ProviderSpecifies the OLE DB Provider for SQL Server. Data Source or ServerSpecifies the name of a server. Initial Catalog or

20、 DatabaseSpecifies the name of a database on the server.User ID or uidSpecifies the user name (for SQL Server Authentication).Password or pwdSpecifies the user password (for SQL Server Authentication).2、建立连接ADO 建立连接的主要方法是使用Connection对象的Open方法：格式：connection.Open ConnectionString, UserID, Password, Op

21、tions参数：ConnectionString：Optional. A String value that contains connection information. UserID：Optional. A String value that contains a user name to use Password：Optional. A String value that contains a password to use Options：Optional. determines whether this method should return after or before th

22、e connection is established.若不提供ConnectionString，则使用connection对象的ConnectionString属性值。UserID、Password将覆盖ConnectionString中的值。例如：DimcnnAsNewcnn.OpenDSN=Pubs;uid=sa;pwd=;成功建立一个连接后，则可通过连接发送命令，处理结果。连接也可通过Recordset和Command对象建立。二、创建命令一个命令是数据提供者的可识别的指令，用于对数据源的操作。指定命令：命令文本，或包含命令文本的变量命令对象，其CommandText属性设置为命令文本

23、。命令可以通过不同的对象调用：Command对象Connection对象Recordset对象命令对象指定对提供者操作的特定命令：CommandText属性中指定命令：可为一个表名称、SQL语句、存储过程等，或提供者可识别的命令，如调用存储过程。CommandType属性指定CommandText的类型。Execute方法执行指定的命令如果命令中包含参数，需使用命令对象的Parameters集合提供。例如：DimcmdAsNewSetcmd.ActiveConnection=cnncmd.CommandText=SELECT*fromAuthors三、执行命令执行命令的三种方法：Connect

24、ion.ExecuteCommand.Executepen有些命令可能返回一个Recordset对象，句法如下：connection.Execute(CommandText, RecordsAffected, Options)command.Execute(RecordsAffected, Parameters, Options)recordset.Open Source, ActiveConnection, CursorType, LockType, Options指定连接： The Connection.Execute method uses the connection embodied

25、 by the Connection object itself.The Command.Execute method uses the Connection object set in its ActiveConnection property.The method uses a connection string, its ActiveConnection parameter, or the Connection object set in its ActiveConnection property. 指定命令：In the method, the command is command t

26、ext.In the method, the command is not visibleit is specified in the Command.CommandText property. Furthermore, the command can be parameterized.In the method, the command is the Source argument, which can be command text or a Command object.特点：The Execute methods are intended for, but not limited to

27、, executing commands that dont return data.Both Execute methods return read-only, forward-only Recordset objects.The Command.Execute method allows you to use parameterized commands that can be reused efficiently.The Open method allows you to specify the CursorType (strategy and object used to access

28、 the data); and LockType (specify the degree of isolation from other users, and whether the cursor should support updates in immediate or batch modes. 的格式recordset.Open Source, ActiveConnection, CursorType, LockType, Options参数：Source：Optional. A Variant that evaluates toa table namean SQL statementa

29、 valid Command objecta stored procedure call,moreActiveConnection:Optional. Can be：a Variant that evaluates to a valid Connection object variable name, a String that contains ConnectionString parameters.CursorType:Optional. A constant value determines the type of cursor that the provider should use

30、when opening the Recordset. ConstantValueDescriptionadOpenDynamic2Uses a dynamic cursor. adOpenForwardOnly0Default.Uses a forward-only cursor. adOpenKeyset1Uses a keyset cursor. adOpenStatic3Uses a static cursor. LockType:Optional. A LockType value that determines what type of locking (concurrency)

31、the provider should use when opening the Recordset. ConstantValueDescriptionadLockBatchOptimistic4Indicates optimistic batch updates. adLockOptimistic3Indicates optimistic locking, locking records only when you call the Update method.adLockPessimistic2Indicates pessimistic locking, locking records a

32、t the data source immediately after editing.adLockReadOnly1Indicates read-only records. You cannot alter the data.Options：:Optional. A Long value that indicates command typeConstantValue Evaluates CommandText asadCmdText1a command or stored procedure call.adCmdTable2a table name adCmdStoredProc4a st

33、ored procedure name.adCmdUnknown8Default. the type is not known.例如：rst.CursorLocation=adUseClientrst.Opencmd,adOpenStatic,adLockBatchOptimistic四、操作数据对数据源中数据的操作通过Recordset对象进行：Recordset对象是一个由多行组成的缓冲区任何时刻只能访问其中的一行正在访问的行称当前行移动到另一行后，该行变为当前行Recordset对象提供许多方法和属性用于对记录集中的数据进行操作。1、移动记录：(1)Move方法:Moves the po

34、sition of the current record in a Recordset object.句法：recordset.Move NumRecords, StartNumRecords:A signed Long expression that specifies the number of records that the current record position moves.Start：Optional.specifies the start position(2)moveFirst、MoveLast、MoveNext、MovePrevious 方法： CONTROL Int

35、ernet.HHCtrl.1 CONTROL Internet.HHCtrl.1 CONTROL Internet.HHCtrl.1 Moves to the first, last, next, or previous record in a specified Recordset object and makes that record the current record.(3)其它属性：其它一些属性的设置也可改变记录的位置。2、越界检查：使用BOF和EOF properties 检查Recordset对象是否含有记录或超出Recordset对象的范围。BOF property: ret

36、urns True (-1) if the current record position is before the first record ,otherwise False (0).EOF property ：returns True if the current record position is after the last record ，otherwise False If either the BOF or EOF property is True, there is no current record.3、过滤、排序：Filter property： controls th

37、e rows you can access (that is, which rows are visible to you). Sort property： controls the order in which you navigate the rows of the Recordset.4、数据行操作：AddNew：create new rowsDelete：delete existing rowsUpdate: save the changes to Data surce 5、字段引用：Recordset(index)Recordset(field name)例如：roperties(O

38、ptimize)=Truerst.Sort=au_lnamerst.Filter=phoneLIKE4155*rst.MoveFirstDoWhileNotrst.EOFDebug.PrintName=;rst!au_fname;rst!au_lname;_,Phone=;rst!phonerst!phone=777&Mid(rst!phone,4)rst.MoveNextLooprst.Filter = adFilterNone五、更新数据对Recordset对象中数据的修改只有通过Update或UpdateBatch才能保存到数据源。Recordset对象支持两种类型的更新：立即更新：通过

39、Update方法保存对当前行的修改recordset.Update Fields, ValuesFields：Optional. A Variant that represents a single name, or a Variant array that represents names or ordinal positions of the field or fields you wish to modify.Values：Optional. A Variant that represents a single value, or a Variant array that represe

40、nts values for the field or fields in the new record.批更新：对Recordset对象中多行数据的修改缓存在缓冲区中，UpdateBatch方法一次写入到磁盘Batch事务支持connection对象提供支持事务的BeginTrans、CommitTrans和RollbackTrans方法和Attributes属性管理事务。用BeginTrans指定事务的开始，之后进行数据更新操作例如：cnn.BeginTransrst.UpdateBatch事务完成后，使用CommitTrans进行提交，数据永久保存在数据库中。例如：cnn.CommitT

41、rans如果在事务中出现错误，使用RollbackTrans回滚事务例如：rst.Filter=adFilterConflictingRecordsDebug.PrintConflict:Name=;rst!au_fname;rst!au_lnameLoopcnn.RollbackTrans例：通过ODBC访问SQL Server数据源 Dim cmd As New ADODB.Commandcnn_str = driver=SQL Server;server=.; database=test; uid=sa;pwd=; cnn.Open cnn_str cmd.ActiveConnectio

42、n = cnn cmd.CommandText = Select * from t1 On Error GoTo error_handle rst.Open cmd, , adOpenDynamic, adLockPessimistic For i = 0 To rst.Fields.Count - 1 Debug.Print rst(i), Next Loop Exit Suberror_handle:11.3 ADO编程示例设计一个窗口，用于对部门信息表进行操作，包括输入、修改、删除等功能。1、使用ADO数据控件与DataGrid控件使用Adodc控件指定数据源使用DataGrid访问数据

43、（1）创建工程并添加ADO部件到工具箱（2）设计窗口，具有如下的控件：（3）设计代码Private Sub Form_Load() 建立数据源的连接 Adodc1.ConnectionString = driver=SQL Server;server=.; database = master; uid=sa; pid=; 查询所有记录，并按部门编号排序 Adodc1.RecordSource = select * from 部门信息表 order by 部门编号 End SubPrivate Sub CmdExit_Click() Unload MeEnd Sub2、使用ADO数据控件与数据绑

44、定控件使用Adodc控件指定数据源使用EdiBox控件的数据绑定功能访问数据（1）创建工程并添加ADO部件到工具箱，同上。（2）设计窗口，使其具有如下的控件：其中的输入控件的DataSurce属性设置为ADODC1，DataField属性设置为相应的字段。（3）设计代码Private Sub Form_Load() 建立数据源的连接 Adodc1.ConnectionString = driver=SQL Server;server=.; database=master;uid=sa;pid=; 查询所有记录，并按部门编号排序 Adodc1.RecordSource = select * fr

45、om 部门信息表 order by 部门编号 For i = 0 To 4 Text1(i).Enabled = False Next i CmdSave.Enabled = FalseEnd SubPrivate Sub Text1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer) Select Case Index Case Is = Index If KeyCode = vbKeyReturn And Index 1 Then Text1(Index - 1).SetFocus End SelectEnd Su

46、bPrivate Sub CmdAdd_Click() 添加记录 For i = 0 To 4 Text1(i).Enabled = True Text1(i).Text = Next i Text1(0).SetFocus CmdAdd.Enabled = False CmdDelete.Enabled = False CmdModify.Enabled = False CmdSave.Enabled = TrueEnd SubPrivate Sub CmdModify_Click() 修改记录 If Adodc1.Recordset.RecordCount 0 Then For i = 0

47、 To 4 Text1(i).Enabled = True Next i CmdSave.Enabled = True CmdAdd.Enabled = False CmdModify.Enabled = False CmdDelete.Enabled = False Else MsgBox (没有要修改的数据!) End IfEnd SubPrivate Sub CmdDelete_Click() 删除记录 Dim myval As String myval = MsgBox(是否要删除该记录？, vbYesNo) If myval = vbYes Then For i = 0 To 4 T

48、ext1(i).Enabled = False Next i End IfEnd SubPrivate Sub CmdSave_Click() If Text1(0).Text = Then MsgBox 部门编号不允许为空！ Exit Sub End If If Text1(1).Text = Then MsgBox 部门名称不允许为空！ Exit Sub End If If Text1(2).Text = Then MsgBox 负责人不允许为空！ Exit Sub End If If Text1(3).Text = Then MsgBox 部门 不允许为空！ Exit Sub End I

49、f If Text1(4).Text = Then MsgBox 部门职能不允许为空！ Exit Sub End If Adodc1.Recordset.Update 更新记录 设置控件不可用 For i = 0 To 4 Text1(i).Enabled = False Next i CmdSave.Enabled = False CmdAdd.Enabled = True CmdModify.Enabled = True CmdDelete.Enabled = TrueEnd SubPrivate Sub CmdExit_Click() Unload MeEnd Sub3、使用非数据绑定控

50、件使用ADO对象访问数据源使用EditBox控件显示、输入数据（1）创建工程，并添加ActiveX Data Objects 2.0 library到工程中：（2）设计窗口，具有如下的控件：3、代码设计Option ExplicitDim myConnection As ConnectionDim myRecordset As RecordsetDim myBookmark As Variant 储存当前记录指针 Private Sub inibt() 设置按钮状态 cmdadd.Enabled = True cmdcnladd.Enabled = False cmdsave.Enabled

51、= False If myRecordset.RecordCount = 0 Then 数据库为空 cmdprev.Enabled = False cmdnext.Enabled = False cmddel.Enabled = False Exit Sub End If If cmdprev.Enabled = False Then cmdprev.Enabled = True cmdnext.Enabled = True cmdsave.Enabled = True cmddel.Enabled = True End IfEnd SubPrivate Sub initxt() Dim i

52、As Integer For i = 0 To 4 Text1(i).Text = Next iEnd SubPrivate Sub showfld() Dim i As Integer On Error Resume Next For i = 0 To 4 Text1(i).Text = myRecordset(i) Next iEnd SubPrivate Sub cmdclose_Click() Unload MeEnd SubPrivate Sub cmdcnladd_Click() If myBookmark Null Then myRecordset.Bookmark = myBookmark End If Call inibt Call showfldEnd SubPrivate Sub cmddel_Click() On Error GoTo DeleteErr cmdsave.Enabled = False With myRecordset .Delete If .RecordCount 0 The