ASP ：資料庫應用

1. ASP：資料庫應用 鄧姚文 http://www.ywdeng.idv.tw

2. Ways of Data Access • Open DataBase Connectivity (ODBC) • API to allow access to relational databases • Remote Data Objects (RDO) • ActiveX objects that sits on top of ODBC, giving all of the facilities of ODBC, but in an easy to use form

3. ActiveX Data Objects (ADO) • You should only have one way to access data • OLE DB is the underlying technology that interfaces between our programs and the source of the data. • ADO • ActiveX objects that provides easy access to the OLE DB functionality

4. ActiveX and COM • ActiveX is a cross-platform standard for components • based on the COM architecture • Common Object Model (COM) • Windows specific

5. OLE DB and ADO Architecture

6. OLE DB Providers • Jet OLE DB 4.0 – For Microsoft Access databases • OLAP Services – For the Microsoft OLAP server • Oracle – For Oracle databases • SQL Server – For Microsoft SQL Server databases • Microsoft Directory Services – For the Windows 2000 Directory Services • ODBC Drivers – For ODBC Data Sources • Simple Provider, for simple text files

7. OLE DB Providers -1 • Internet Publishing – For access to Web servers • Indexing Service – For Index Catalogs • Site Server Search – For the Site Server search catalog • MSDataShape – For hierarchical data • DTS Packages – For the SQL Server Data Transformation Services • DTS Flat File – For the SQL Server Data Transformation Services flat file manager

8. The ADO 2.5 Object Model

9. The Connection Object • To connect to data stores • Specify which OLE DB Provider we wish to use • If you are going to be running several commands against a Provider, you should explicitly create a Connection • It's more efficient than letting ADO create one each time you run a command

10. The Command Object • For running commands against a data store • Structured Query Language (SQL) • 增 INSERT • 刪 DELETE • 查 SELECT • 改 UPDATE • 參考資料：ppt1ppt2

11. The Recordset Object • Contains the sets of data we extract from the data stores • It allows us to change the data (additions, updates and deletions), move around the records, filter the records so that only a subset are shown

12. The Record Object • A collection is mapped onto a recordset, and an individual file is mapped to a record, with the properties of the file being mapped into the Fields collection

13. The Stream Object • Used to access the contents of a node • Email message • Web page • XML • BLOB

14. Connecting to Data Stores • Create a connection • Connection string • Issue Commands (SQL) • Retrieve RecordSet • Manipulate RecordSet

15. ADO Constants • ADO 相關常數的定義 • C:\Program Files\Common Files\System\ado\adovbs.inc • 把這個檔案複製到網頁目錄中 <!-- #INCLUDE FILE="adovbs.inc" --> • 或 <!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

16. Connection Strings • Microsoft Access Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\MyWeb\database_name.mdb • Microsoft SQL Server Provider=SQLOLEDB; Data Source=server_name; Initial Catalog=database_name; User Id=user_name; Password=user_password

17. Using Include Files 在 Connection.asp 裡： 在 ASP 網頁裡：

18. Using Connection State 在 global.asa 裡： 在 ASP 網頁裡：

19. Connection Syntax • 建立資料庫連線

20. Connection Examples

21. Connection Pooling • A pool of non-active connections • Connection.close 之後，並不銷毀 Connection • 提高效能

22. Housekeeping • Open the connection as late as possible • Close the connection as soon as possible • The connection is open for the shortest period of time possible

23. 練習：列出表格內容 • 資料庫：MyDB • 資料表：tWorker

24. 練習：列出表格內容 • 匯入內容： • 以ASP網頁列出資料表內容 INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('陳怡心',20,'女','A223456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('林惠玲',21,'女','B223456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('李家銘',22,'男','C123456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('吳建宏',23,'男','D123456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('王雅雯',24,'女','E223456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('邱志豪',25,'男','F123456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('張雅惠',26,'女','G223456789'); INSERT INTO tWorker ([name],[age],[sex],[soc_id]) VALUES ('許蓋功',27,'男','H123456789');

25. Recordsets • The ones that contain the data • Set of records • 背後的機制 • Recordset Object • Cursor

26. Cursors • A cursor is what manages the set of records and the current location within the recordset, the latter being handled by the current record pointer.

27. Cursor Types • Static (adOpenStatic) • Forward Only (adOpenForwardOnly) • Dynamic (adOpenDynamic) • Keyset (adOpenKeyset)

28. Cursor Types • Static (adOpenStatic) • Contain a static copy of the records • The contents of the recordset are fixed at the time the recordset is created • Movement through the recordset is allowed both forwards and backwards • Forward Only (adOpenForwardOnly) • The default cursor type • Only move forwards

29. Cursor Types • Dynamic (adOpenDynamic) • Doesn't have a fixed set of records • Any changes, additions or deletions by other users will be visible in the recordset • Movement through the recordset is allowed both forwards and backwards

30. Cursor Types • Keyset (adOpenKeyset) • Similar to Dynamic cursors, except that the set of records is fixed • You can see changes by other users, but new records are not visible. • If other users delete records, then these will be inaccessible in the recordset • This functionality is achieved by the set of records being identified by their keys – so the keys remain, even if the records change or are deleted

31. Cursor Location • Microsoft SQL Server, have a cursor service of their own • Microsoft Access don't have a cursor service • OLE DB has its own cursor service • adUseServer – To let the data store manipulate the cursor • adUseClient – To let ADO manipulate the cursor

32. Cursor Location

33. Locking • Ensure the integrity of our data • Types of locking • Read Only (adLockReadOnly) • Pessimistic (adLockPessimistic) • Optimistic (adLockOptimistic) • Batch Optimistic (adLockBatchOptimistic)

34. Locking • Read Only (adLockReadOnly) • The default locking type • The recordset is read-only • Pessimistic (adLockPessimistic) • Locking the record as soon as editing takes place

35. Locking • Optimistic (adLockOptimistic) • The record is not locked until the changes to the record are committed to the data store • Batch Optimistic (adLockBatchOptimistic) • Allows multiple records to be modified • The records are only locked when the UpdateBatch method is called

36. Creating Recordsets • Source: • The source of the data, it can be • The name of a table from a database • A stored query or procedure • A SQL string • A Command object, or any other command applicable to the Provider

37. Creating Recordsets

38. Creating Recordsets

39. Creating RecordsetsTest Empty Recordset

40. Creating RecordsetsThe Options Argument • adCmdText – a text command, such as a SQL string • adCmdTable or adCmdTableDirect – the name of a table • adCmdStoredProc – the name of a stored procedure • adCmdFile – the file name of a saved recordset • adCmdURLBind – To indicate a URL

41. Moving Through the Recordset

42. Moving Through the Recordset

43. Using the Fields Collection

44. Bookmarks • To use a bookmark you simple assign the Bookmark property to a variable: • You can then move about the recordset, and later return to the bookmark record by performing the reverse command:

45. Using Bookmarks to Save Your Position

46. Adding Records

47. Adding Records This method doesn't require a call to the Update method.

48. Editing Records

49. Deleting Records • Call the Delete method • Which records are deleted depends on: • adAffectCurrent – only the current record is deleted. This is the default action. • adAffectGroup – all records matching the current filter • adAffectAll – all records in the recordset • adAffectAllChapters – records in all chapters are deleted.

50. Deleting Records