Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
ASP :資料庫應用 PowerPoint Presentation
Download Presentation
ASP :資料庫應用

ASP :資料庫應用

162 Views Download Presentation
Download Presentation

ASP :資料庫應用

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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