數(shù)據(jù)庫系統(tǒng)應(yīng)用程序開發(fā)

1、第11章 數(shù)據(jù)庫系統(tǒng)應(yīng)用程序開發(fā)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.一、數(shù)據(jù)訪問解決方案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程序設(shè)計模型1、ADO程序設(shè)計基本模型ADO 提供進行下列任務(wù)的方法: 連接到數(shù)據(jù)源指定對數(shù)據(jù)源訪問的命令執(zhí)行命令如果命令返回數(shù)據(jù)行，把它們存儲在緩存中如果需要，從緩存中的數(shù)據(jù)更新數(shù)據(jù)源提供檢測錯誤的方法 一般情況下，可按上述步驟進行處理，但不一定使用全部，可靈活使用。2、模型中的主要元素ADO程序模型中包括下面的元素： 連接（connection）：連接是一個交換數(shù)據(jù)的環(huán)境，應(yīng)用程序通過一個連接訪問數(shù)據(jù)源。命令(command)：命令對數(shù)據(jù)源中的數(shù)據(jù)進行操作,如增加、修改、刪除、查詢等，命令通過連接發(fā)送。參數(shù)（Parameter）：命令所需的變化部分。記錄集（Recordset）：記錄集

8、是存放查詢結(jié)果的一個存儲區(qū)，可實現(xiàn)按行訪問。字段（Field）：記錄集中的一個列，對數(shù)據(jù)源的操作通過字段進行。錯誤（Error）：不適當?shù)牟僮骺赡墚a(chǎn)生一個錯誤。屬性（Property）：每個對象有一組屬性描述或控制對象的行為。集合（Collection）：集合是一種對象，包含有若干特定的對象。事件（Event）： 事件是某種操作完成或即將發(fā)生的通知。3、ADO對象模型為了支持ADO程序模型, ADO對象模型中設(shè)計了若干對象來完成相應(yīng)的功能。ADO主要對象對象主要功能Connection建立與數(shù)據(jù)庫的連接Command指定、執(zhí)行SQL語句Recordset創(chuàng)建、保存結(jié)果集，訪問數(shù)據(jù)行Parame

9、ter為Command對象提供一個參數(shù)Field用于訪問Recordset的指定字段Error包含一個錯誤信息Property訪問一個ADO對象屬性ADO的集合集合主要功能Errors包含操作過程中產(chǎn)生的所有錯誤Fields包含Recordset的所有字段Parametes包含Command對象的所有參數(shù)Properties包含一個ADO對象的所有屬性 ADO具有三個獨立的對象：Connection對象：建立與數(shù)據(jù)源的連接Command對象：指定一個SQL語句,并可執(zhí)行此語句。Recordset對象：創(chuàng)建、保存結(jié)果集，訪問數(shù)據(jù)行。其余對象以集合的形式歸獨立對象所有：Errors集合：屬Conn

10、ection對象所有，提供與單個操作有關(guān)的所有錯誤信息。由Error對象組成，每個Error對象代表一個錯誤信息。Parameters集合：屬Command對象所有，提供所需得參數(shù)。由Parameter對象組成，每個Parameter對象提供一個參數(shù)。Fields集合：屬Recordset對象所有，包含Recordset對象的所有字段。由Field對象組成，每個Field對象代表一個字段。Properties集合：每個Connection、Recordset、Command或Fields對象都包含一個Properties集合，代表ADO對象得動態(tài)特征。由Property對象組成，每個Prope

11、rty對象代表一個動態(tài)特征。4、使用ADO對象的程序設(shè)計模型 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訪問數(shù)據(jù)庫示例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程序設(shè)計根據(jù)ADO程序設(shè)計模型，詳細實現(xiàn)步驟和相關(guān)對象如下。一、建立連接訪問數(shù)據(jù)源必須通過一個連接，連接建立了數(shù)據(jù)交換必須的環(huán)境，對數(shù)據(jù)源的任何操作都是通過連接對象進行的。被連接資源可為：數(shù)據(jù)庫ODBC資源OLE DB提供者資源1、連接字符串：ConnectingString連接到數(shù)據(jù)源的信息用一個字符串表示，對于不同的提供者和數(shù)據(jù)源，其內(nèi)容可能有所不同。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;關(guān)鍵詞說明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參數(shù)：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=;成功建立一個連接后，則可通過連接發(fā)送命令，處理結(jié)果。連接也可通過Recordset和Command對象建立。二、創(chuàng)建命令一個命令是數(shù)據(jù)提供者的可識別的指令，用于對數(shù)據(jù)源的操作。指定命令：命令文本，或包含命令文本的變量命令對象，其CommandText屬性設(shè)置為命令文本

23、。命令可以通過不同的對象調(diào)用：Command對象Connection對象Recordset對象命令對象指定對提供者操作的特定命令：CommandText屬性中指定命令：可為一個表名稱、SQL語句、存儲過程等，或提供者可識別的命令，如調(diào)用存儲過程。CommandType屬性指定CommandText的類型。Execute方法執(zhí)行指定的命令如果命令中包含參數(shù)，需使用命令對象的Parameters集合提供。例如：DimcmdAsNewSetcmd.ActiveConnection=cnncmd.CommandText=SELECT*fromAuthors三、執(zhí)行命令執(zhí)行命令的三種方法：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參數(shù)：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四、操作數(shù)據(jù)對數(shù)據(jù)源中數(shù)據(jù)的操作通過Recordset對象進行：Recordset對象是一個由多行組成的緩沖區(qū)任何時刻只能訪問其中的一行正在訪問的行稱當前行移動到另一行后，該行變?yōu)楫斍靶蠷ecordset對象提供許多方法和屬性用于對記錄集中的數(shù)據(jù)進行操作。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)其它屬性：其它一些屬性的設(shè)置也可改變記錄的位置。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、數(shù)據(jù)行操作：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五、更新數(shù)據(jù)對Recordset對象中數(shù)據(jù)的修改只有通過Update或UpdateBatch才能保存到數(shù)據(jù)源。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對象中多行數(shù)據(jù)的修改緩存在緩沖區(qū)中，UpdateBatch方法一次寫入到磁盤Batch事務(wù)支持connection對象提供支持事務(wù)的BeginTrans、CommitTrans和RollbackTrans方法和Attributes屬性管理事務(wù)。用BeginTrans指定事務(wù)的開始，之后進行數(shù)據(jù)更新操作例如：cnn.BeginTransrst.UpdateBatch事務(wù)完成后，使用CommitTrans進行提交，數(shù)據(jù)永久保存在數(shù)據(jù)庫中。例如：cnn.CommitT

41、rans如果在事務(wù)中出現(xiàn)錯誤，使用RollbackTrans回滾事務(wù)例如：rst.Filter=adFilterConflictingRecordsDebug.PrintConflict:Name=;rst!au_fname;rst!au_lnameLoopcnn.RollbackTrans例：通過ODBC訪問SQL Server數(shù)據(jù)源 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編程示例設(shè)計一個窗口，用于對部門信息表進行操作，包括輸入、修改、刪除等功能。1、使用ADO數(shù)據(jù)控件與DataGrid控件使用Adodc控件指定數(shù)據(jù)源使用DataGrid訪問數(shù)據(jù)

43、（1）創(chuàng)建工程并添加ADO部件到工具箱（2）設(shè)計窗口，具有如下的控件：（3）設(shè)計代碼Private Sub Form_Load() 建立數(shù)據(jù)源的連接 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數(shù)據(jù)控件與數(shù)據(jù)綁

44、定控件使用Adodc控件指定數(shù)據(jù)源使用EdiBox控件的數(shù)據(jù)綁定功能訪問數(shù)據(jù)（1）創(chuàng)建工程并添加ADO部件到工具箱，同上。（2）設(shè)計窗口，使其具有如下的控件：其中的輸入控件的DataSurce屬性設(shè)置為ADODC1，DataField屬性設(shè)置為相應(yīng)的字段。（3）設(shè)計代碼Private Sub Form_Load() 建立數(shù)據(jù)源的連接 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 (沒有要修改的數(shù)據(jù)!) 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 更新記錄 設(shè)置控件不可用 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、使用非數(shù)據(jù)綁定控

50、件使用ADO對象訪問數(shù)據(jù)源使用EditBox控件顯示、輸入數(shù)據(jù)（1）創(chuàng)建工程，并添加ActiveX Data Objects 2.0 library到工程中：（2）設(shè)計窗口，具有如下的控件：3、代碼設(shè)計Option ExplicitDim myConnection As ConnectionDim myRecordset As RecordsetDim myBookmark As Variant 儲存當前記錄指針 Private Sub inibt() 設(shè)置按鈕狀態(tài) cmdadd.Enabled = True cmdcnladd.Enabled = False cmdsave.Enabled

51、= False If myRecordset.RecordCount = 0 Then 數(shù)據(jù)庫為空 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