Active Data Objects

1. Active Data Objects By CSJue

2. Microsoft Database Tech. • DAO (Data Access Objects) • 由C++寫成, 架構在JET database engine上, 可以直接存取和控制database。 • ODBC(Open Database Connectivity) • 以C寫成的API, 為MS定的一個Open standard 。 • RDO(Remote Data Objects) • 由C++寫成, 架構在ODBC上, 其特點為充分支援網路。

3. Microsoft Database Tech.(cont.) • OLE DB • 由C++寫成, 可處理多種資料(如ODBC, 試算表, E-Mail, text, file…..), 並支援網路。 Client Server Server RDO IIS Server ODBC

4. Microsoft Database Tech.(cont.) • ADO(Active Data Objects) • 由C寫成, 目的在使OLE DB用起來方便, 它用ActiveX技術把OLE DB包起來。 Client/Data Consumer DAO RDO ADO OLE DB ODBC Provider ODBC Relational data Text Data Streams

5. The ADO Object Model ActiveConnection

6. The ADO Object Model(Cont.) • Connection object: Create a connection to a data provider. • Recordset object: Create a set of records from a query. • Command object: Execute SQL strings, stored procedures, or action queries • Fields Collection:Recordset objects contain a collection of Field Objects that gives you access to column metadata

7. The ADO Object Model(Cont.) • ParametersCollection: Contains all of the parameters associated with the Command object. • Errors Collection:Connection objects contain an Errors collection. • Properties Collection:Connection, Command, Recordset, and Field objects all include a Properties collection.

8. Connection object • 主要在建立和維護Command object, 使用它來管理Recordset object. • 建立Connection Object Set objConn = Server.CreateObject(“ADODB.Connection”) ObjConn.Open =”DSN=myDSN;UID=master;PWD=slave” Set objRst = objConn.Execute(CommandText, RecordsAffected, Options) • Connection pooling - 60 sec(default) • Connection timeout - 15 sec(default)

9. Connection object (cont.) • Transactions ObjConn.BeginTrans(); ………………….. If(ObjConn.Errors.Count=0)Then ObjConn.CommitTrans Else ObjConn.RollbackTrans End If • Execute method • 直接建立recordset object, 但只能得到一個唯讀, 向前的游標

10. Connection object (cont.) • Set objRst = objConn.Execute(CommandText, RecordsAffected, Options);

11. Connection object (cont.) • IsolationLevel • 解決Dirty Reads, Nonrepeatable Reads, Phantom Records等同時性控制的問題 • adXactUnspecified(-1), adXactChaos(16), adXactBrowse (256), adXactReadUncommited (256), adXactCursorStability (4096), adXactReadCommitted (4096), adXactIsolated(1048576), adXactSerializable (1048576)

12. Command object • 主要在達成query • 建立Command object way1: Set objCmd = Server.CreateObject(“ADODB.Command”) Set objCmd.ActiveConnection = objConn ObjCmd.CommandText=”qryPhoneMessagesFor” way2:objCmd.ActiveConnection = “dsn=intranet ; database= intranet; uid=sa;pwd=..;” Or objCmd.ActiveConnection = ”DRIVER={Microsoft Access Driver(*.mdb)}; DBQ=c:\data\…..\XXX.mdb”

13. Command object(cont.) • Execute method • pCommand->Execute(RecordsAffcted, Parameters, Options) • CreateParameter & Parameters Collection

14. Recordset Object • 完成資料庫管理, 查詢的工作 • 建立Recordset object • 設定好ActiveConnection, CursorType, LockType, Source properties後, 呼叫Open method, 或直接把它們傳給Open method--pRecordset->Open(source, activeconnection, cursortype, LockType, options)

15. Recordset Object(Cont.) • CursorType:

16. Recordset Object(Cont.) • Options:

17. Recordset Object(Cont.) • Bookmark(用Rst.supports(adBookmark)測有無支援) • Page • 相關properties:AbsolutePage, PageSize, PageCount…

18. Recordset Object(Cont.) • Filter • way1:使用判斷標準常數~adFilterNone(0), adFilterPendingRecords (1), adFilterAffectedRecords (2), adFilterFetchedRecords(3) • 使用比較字串來過濾: • objRst.Filter = “for = ‘mpc’ AND on = ‘Date’ ” • GetRows: Fetches a block of rows into an array.

19. Recordset Object(Cont.) • Adding Data Rst.Addnew Rst(“field1”) = value1 Rst(“field2”) = value2 …………. Rst.Update (EX. Addcust1.html) • Updating data Rst(“field1”) = value1 …… rst.Update(EX. Update.asp)

20. Using ADO Objects in VC++ • Using #import #import “C:\ProgramFiles\Common Files\System\ADO\MSADO10.DLL” no_namespace rename(“EOF”, “ADOEOF”) • Initializing COM CoInitialize(NULL); CoUninitialize(); • Creating ADO objects _ConnectionPtr pConn; hr = pConn.CreateInstance( “ADODB.Connection”); • The ADO License Key

21. Reference • Http://www.microsoft/accessdev/articles/moves202.htm ~ ADO Programming(ASP) • http://www.microsoft.com/data/ado/sams/ch08.htm • http://www.microsoft.com/data/ado • Visual C++5 by David Bennett, part V • 深入Active Server Pages ~ ch15, 16, 17