Ӧ
Computer Applications
1999 19 7  Vol.19 No.7 1999



ADOݴȡ
ƽ
ժҪĽ΢˾ActiveXΪADOݴȡADOĹԭVB˵ADOݷʼʵַ
ؼʡADOݴȡӣ¼

ADO DATA ACCESS TECHNOLOGY

Zhou Zhiping
Department of Information and Control Engineering, Wuxi Jiangnan Institute, Jiangsu*Wuxi 214063

AbstractThis paper introduces the Microsofts newest data access technologies based on the ActiveX , discusses their principles, and illustrates how to use the ADO programming model to query and update a data source in VB .
KeywordsADO Data access, Connection, Recordset,Objects

1
ADOActiveX Data Objects΢˾ƳActiveXΪݴȡADOҪصʹøףٶȸ죬Դ̺ʹ洢ҪСADOֽֿ֧ͻ/ģʽWebӦó򣬾ԶݷRDSRemote Data Service, ͨRDSܹһн˵ݴ͵ͻ˵ӦóWebҳУڿͻ˶ݽд·˵ݡADOڵOLE DBԶԵʼıļݱȸͨͳһĽӿAPIApplication Programming InterfaceӿڽдȡԶݴȡһҪչ

2ADOԭ
ݿķʣҪһ򵥶ͳһӦóӿ(API) Ӧóͨʺ޸ĸָݡ͵Դ֧ODBCOpen Database Connectivity׼Ĺϵݿ⣬ͨһϵSQLݲԴһ򵥵ıļҲԲͬرݿһϡңʺ޸ݵķԤȷķOLE DBһϵͳݿ̽ӿڣṩһϵйݿϵͳͳһCOMComponent Object ModelӿڡͨOLE DBԴȡ칹ĹͷǹϢʼļϵͳıͼΡͼλԼͻƶADOOLE DBӦóݲӿڡ
ADOһԭͣиһϵнݴȡ͸Ϊ˵ԭͱʾһģͣʵԭӦĶ󼯣ݲĶ󷽷ʾݻ󷽷ΪƵԵȡص¼˵˽ҪѾݲ̡
2.1ADOԭ˳
* Դ;
* ָԴĴȡ;
* ;
* صݴļ¼бڼ޸;
* ݵı仯
ͨõǱģADOʹԭеһֽй磬ֱӴһļаݴ浽ļ¼ADOݡ
2.2ADO ԭͽṹ
ADOԭмҪ֣
ӣConnectionConnectionһݽӦóͨConnectionȡݡӦóֱӻͨIISInternet Information ServerϵͳӴȡԴݡConnectionڹ涨ضṩߺ͸ӲConnectionһϵݴȡγһ̣ADOȷ̳ɹĽԴĳʧܻûȡ̣Դеݾάԭȵ״̬ģͲȷؾʾһ̣ͨһConnection󷽷ʵ֡
CommandCommandͨѽConnection͵Commandӡɾ͸£ָеļ¼ȡ
ParameterParameterĸӿɱ䲿֣ͨıParameterͬܵ
¼RecordsetģRecordsetѯصĽǼ޸ļ¼ҪֶΣRecordsetпԼ¼ڼ¼¼Ĵ޸Ļɾ¼¼¼ѱ仯Դȡ
ֶΣFieldRecordsetÿ¼һFieldɣÿFieldơͺֵ޸Դʱͨ޸RecordsetеField ֵմݵԴСʹConnectionȷݱͬ


ͼ1ADOԭ͡ͼ2ADO
ErrorӦóκʱ򶼿ܳError罨ӡȡκδ󶼻һErrorµError滻ǰError
ԣPropertyÿADOһΨһıʾƶΪProperty͵Property̶ĺͶ̬ġ̶PropertyADOһ֣ʼЧ̬PropertyṩӵADOPropertyУʹøṩʱЧ
ϣCollectionADOṩ͵CollectionConnectionErrorϡCommand ParameterϡRecordsetFieldϣ⣬ǰFieldPropertyϡ
¼Event ģͨEvents̱ʾEventsConnectionEventsRecordsetEventsEvents

3ADOʹ÷
VB˵ADOʹù̡
3.1һ
ADOConnection.OpenRecordset.Openӣ﷨£
connection.Open ConnectionString, UserID, Password, 
OpenOptions
recordset.Open Source, ActiveConnection, CursorType, 
LockType, Options
ConnectionStringָ׼ӵԴ磬ΪMS SQL ServerиODBCݿpubs󷽷дɣ
Connection.Open "DSN=pubs;uid=sa;pwd=pa;
database=pubs"
uidpwd ؼָConnectionUserIDûţPassword
3.2
͵SQLɣѯĽΪݵļ¼磬ҪгpubsݿڱauthorsеȫݵSQLΪSELECT * from authorsCommandCommandTextַ£
Command cmd = New ADODB.Command;
cmd.CommandText = "SELECT * from authors"
SQLַǲģԴһͨ?ʱ滻ͨ?䡣ͬʹôPreparedԵĲıеĲɡִһгΪRingerĲѯ
Command cmd = New ADODB.Command
cmd.CommandText = "SELECT * from authors WHERE aulname = ?"
ÿͨ?CommandParameterӦParameterֵ滻УȴParameter󲢸ֵΪRingerȻӵParametersϡ
Parameter prm = New ADODB.Parameter
prm.Name = "aulname"
prm.Type = adVarChar
prm.Direction = adInput
prm.Size = 40
prm.Value = "Ringer"
cmd.Parameters.Append prm
ҲCreateParameteròһParameter󣬶Parametersϡ
cmd.Parameters.Append cmd.CreateParameter 
"aulname", adVarChar, adInput, 40, "Ringer"
3.3
ADOConnection.ExecuteCommand.ExecuteRecordset.OpenַÿַӦڲͬӹ̡﷨£
connection.Execute(CommandText, RecordsAffected, Options)
command.Execute(RecordsAffected, Parameters, Options)
recordset.Open Source, ActiveConnection, CursorType, 
LockType, Options

Recordset rs = New ADODB.Recordset
rs.Open cmd, conn, adOpenDymanic, adLockBatchOptimistic
3.4
¼һ¼MoveFindڼ¼ƶBookmarkҲԸı¼λãFilterڹ˽дȡļ¼SortԾ¼ƶĴУΪ415,ԡ5ֿʼĵ绰Ϊ777
rs!aulname.Optimize = TRUE
rs.Sort = "aulname ASCENDING"
rs.Filter = "phone LIKE 415 5*"
rs.MoveFirst
Do While Not rs.EOF
Debug.Print "Name: " & rs!aufname & " " rs!aulname 
& "Phone: " rs!phone & vbCr
rs!phone = "777" & Mid(rs!phone, 5, 11)
rs.MoveNext
Loop

3.5
ADO֧ݸ·һǼӵģݱ仯һڲУȷµʱŽݱ仯д¼һֱӵģûȷݴʱֱӰѵ¼һϵм¼ĸ´͸ԴͨCursorLocationԺ LockTypeԽйֱӸµģʽУǰ¼ƶUpdate޸ĵ¼UpdateBatch´͸Դ򿪼¼ķʽйءͬʱȷĽͬĴдɣ
conn.BeginTrans
rs.UpdateBatch
...
3.6ݸ
ADOܹⲢݸ¹пֵܳĴǴĴ̣
...
conn.CommitTrans
...
On Error
rs.Filter = adFilterConflictingRecords
rs.MoveFirst
Do While Not rs.EOF
Debug.Print "Conflict: Name: " & rs!aufname " " 
& rs!aulname rs.MoveNext
Loop
conn.Rollback
Resume Next
ϵĲԴ˽ADOȡݵʹ÷ԲͬĳԣƵĹ̡
4
ADOһµݴȡϵDAOData Access ObjectsرRDORemote Data ObjectsݷʽӿڣΪµԶݷʷADOʹãܸãADOṩݴȡýӿڣ绷ݿӦÿṩ˼ıˣ˽ⲢADOݴȡԶ塣ʹDAORDOݷʷɵADOһƣݲ̽֡
ƽʦ¹ҵԶƺͼӦõĽѧй
ߵλƽѧԺϢƹϵգ 214063
ո:1999-02-28
