投影片 1

Transcript 投影片 1

ASP：資料庫應用
鄧姚文
http://www.ywdeng.idv.tw
1
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
2
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
3
ActiveX and COM
• ActiveX is a cross-platform standard for
components
– based on the COM architecture
• Common Object Model (COM)
– Windows specific
4
OLE DB and ADO Architecture
5
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
6
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
7
The ADO 2.5 Object Model
8
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
9
The Command Object
• For running commands against a data store
• Structured Query Language (SQL)
– 增 INSERT
– 刪 DELETE
– 查 SELECT
– 改 UPDATE
– 參考資料：ppt1 ppt2
10
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
11
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
12
The Stream Object
• Used to access the contents of a node
– Email message
– Web page
– XML
– BLOB
13
Connecting to Data Stores
• Create a connection
– Connection string
• Issue Commands (SQL)
• Retrieve RecordSet
• Manipulate RecordSet
14
ADO Constants
• ADO 相關常數的定義
• C:\Program Files\Common
Files\System\ado\adovbs.inc
– 把這個檔案複製到網頁目錄中

• 或

15
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
16
Using Include Files
在 Connection.asp 裡：
在 ASP 網頁裡：
17
Using Connection State
在 global.asa 裡：
在 ASP 網頁裡：
18
Connection Syntax
• 建立資料庫連線
19
Connection Examples
20
Connection Pooling
• A pool of non-active connections
• Connection.close 之後，並不銷毀
Connection
• 提高效能
21
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
22
練習：列出表格內容
• 資料庫：MyDB
• 資料表：tWorker
23
練習：列出表格內容
• 匯入內容：
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
INSERT INTO tWorker ([name],[age],[sex],[soc_id])
VALUES ('陳怡心',20,'女','A223456789');
VALUES ('林惠玲',21,'女','B223456789');
VALUES ('李家銘',22,'男','C123456789');
VALUES ('吳建宏',23,'男','D123456789');
VALUES ('王雅雯',24,'女','E223456789');
VALUES ('邱志豪',25,'男','F123456789');
VALUES ('張雅惠',26,'女','G223456789');
VALUES ('許蓋功',27,'男','H123456789');
• 以ASP網頁列出資料表內容
24
Recordsets
• The ones that contain the data
• Set of records
• 背後的機制
– Recordset Object
– Cursor
25
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.
26
Cursor Types
•
•
•
•
Static (adOpenStatic)
Forward Only (adOpenForwardOnly)
Dynamic (adOpenDynamic)
Keyset (adOpenKeyset)
27
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
28
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
29
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
30
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
31
Cursor Location
32
Locking
• Ensure the integrity of our data
• Types of locking
– Read Only (adLockReadOnly)
– Pessimistic (adLockPessimistic)
– Optimistic (adLockOptimistic)
– Batch Optimistic (adLockBatchOptimistic)
33
Locking
• Read Only (adLockReadOnly)
– The default locking type
– The recordset is read-only
• Pessimistic (adLockPessimistic)
– Locking the record as soon as editing takes place
34
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
35
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
36
Creating Recordsets
37
Creating Recordsets
38
Creating Recordsets
Test Empty Recordset
39
Creating Recordsets
The 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
40
Moving Through the Recordset
41
Moving Through the Recordset
42
Using the Fields Collection
43
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:
44
Using Bookmarks to Save Your
Position
45
Adding Records
46
Adding Records
This method doesn't require a call to the Update method.
47
Editing Records
48
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.
49
Deleting Records
50
Auto-Increment Fields
• ID fields
– auto-increment
– IDENTITY field in SQL Server
– AutoNumber in Access
51
Auto-Increment Fields
52
Auto-Increment Fields
• whether you can obtain this value after adding
a new record depends upon
– the cursor type
– the lock type
– whether or not the ID field is indexed
53
Auto-Increment Fields
54
Auto-Increment Fields
55
Auto-Increment Fields
56
Managing Errors
• The Errors Collection
57
ADO Errors in ASP Pages
58
ADO Errors in ASP Pages
59
ADO Errors in ASP Pages
60
ADO Errors in ASP Pages
61
The Connection Object
• To return a recordset from the Connection
object, use the Execute method
62
The Connection Object
63
Action Commands
64
No Recordset Returned
• If no recordset is being returned, as in the
example above, then it's also best to add
another option to the Execute statement:
• Using adExecuteNoRecords tells ADO that the
command being executed does not return any
records. ADO therefore doesn't bother
building a recordset.
65
The Command Object
• The Command object is designed specifically
to deal with commands of any sort, but
especially those that require parameters
66
Returning Recordsets
67
The Command Object Arguments
68
Changing the Cursor Type
69
Action Commands
Set cmdUpdate = Server.CreateObject("ADODB.Command")
strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
" WHERE Type='Business'"
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = sSQL
cmdUpdate.CommandType = adCmdText
cmdUpdate.Execute , , adExecuteNoRecords
70
Stored Procedures
71
Stored Procedures Parameters
72
Stored Procedures Parameters
73
Stored Procedures Parameters
74
Stored Procedures Parameters
75
練習：登入與 Session 管理
• 以資料庫儲存使用者帳號密碼
• pwdencrypt('明文密碼') 可以將密碼加密成
varbinary
• pwdcompare ('明文密碼', @password)
– 比較明文密碼與加密後的密碼是否相符
– 0 不相符
– 1 相符
76