Lecture6_ASP_ADO.ppt

1,WhatisADO?,ADOstandsforActiveXDataObjectsADOisautomaticallyinstalledwithMicrosoftIISADOisaprogramminginterfacetoaccessdatainadatabaseADOisacollectionofobjectsthatallowASPpagestoconnecttoyourdatabaseontheserver.ADOallowsyoutoadd,removeandeditdatainsideofyourdatabase.,2,ADOObjectModel,TheADOobjectmodelprovidesaquick,yetpowerful,interfacetoaccessdatafromadatabase.,Therearethreemajorobjectsthatletyoudirectlyinterfacewithyourdata:Connection,Command,andRecordset.YoucanthinkoftheConnectionObjectasthepathwaytoyourdatasource.WithouttheConnectionObjectyouwouldhavenowaytoconnecttoyourdatasource.TheConnectionObjectcontainstheErrorsObject,whichlogsalltheerrorsthatoccurinyourConnectionObject.TheCommandObjectisoptional,butisstillimportanttoADO.TheRecordsetObjectexpandsouraccesstoourdata.,ADODB,Connection,Command,RecordSet,Errors,Error,Parameters,Parameter,Fields,Field,3,AccessingaDatabasefromanASPPage,ThecommonwaytoaccessadatabasefrominsideanASPpageisto:,CreateanADOconnectiontoadatabaseOpenthedatabaseconnectionCreateanADOrecordsetOpentherecordsetExtractthedatayouneedfromtherecordsetClosetherecordsetClosetheconnection,4,ADODatabaseConnection,Beforeadatabasecanbeaccessedfromawebpage,adatabaseconnectionhastobeestablished.,CreateaDSN-lessDatabaseConnection,TheeasiestwaytoconnecttoadatabaseistouseaDSN-lessconnection.ADSN-lessconnectioncanbeusedagainstanyMicrosoftAccessdatabaseonyourwebsite.Ifyouhaveadatabasecallednorthwind.mdblocatedinawebdirectorylikec:/webdata/,youcanconnecttothedatabasewiththefollowingASPcode:,Fromtheexampleabove,youhavetospecifytheMicrosoftAccessdatabasedriver(Provider)andthephysicalpathtothedatabaseonyourcomputer.,5,CreateanODBCDatabaseConnection,IfyouhaveanODBCdatabasecallednorthwindyoucanconnecttothedatabasewiththefollowingASPcode:,HereishowtocreateaconnectiontoaMSAccessDatabase:,OpentheODBCiconinyourControlPanel.ChoosetheSystemDSNtab.ClickonAddintheSystemDSNtab.SelecttheMicrosoftAccessDriver.ClickFinish.Inthenextscreen,clickSelecttolocatethedatabase.GivethedatabaseaDataSourceName(DSN).ClickOK.,ADODatabaseConnection,6,ADOConnectionObject,TheADOConnectionObjectisusedtocreateanopenconnectiontoadatasource.Throughthisconnection,youcanaccessandmanipulateadatabase.Ifyouwanttoaccessadatabasemultipletimes,youshouldestablishaconnectionusingtheConnectionobject.YoucanalsomakeaconnectiontoadatabasebypassingaconnectionstringviaaCommandorRecordsetobject.,ProgID,setobjConnection=Server.CreateObject(ADODB.connection),Collections,7,Properties,ADOConnectionObject,8,ADOConnectionObject,Methods,9,Events,ADOConnectionObject,10,ADOIsolationLevel,Chaos:Alltransactionscanoverwriteeachotherschangesbeforethechangesarecommitted.Withthissetting,itislikenothavinganytransactionsatall.ReadUncommitted(Browse):AtransactionoperatingattheReadUncommittedlevelcanseeuncommittedchangesmadebyothertransactions.Atthislevelofisolation,dirtyreads,non-repeatablereads,andphantomsareallpossible.ReadCommitted(CursorStability):AtransactionoperatingattheReadCommittedlevelcannotseechangesmadebyothertransactionsuntilthosetransactionsarecommitted.Atthislevelofisolation,dirtyreadsarenotpossible,butnon-repeatablereadsandphantomsarepossible.RepeatableRead:AtransactionoperatingattheRepeatableReadlevelisguaranteednottoseeanychangesmadebyothertransactionsinvaluesithasalreadyread.Atthislevelofisolation,dirtyreadsandnon-repeatablereadsarenotpossible,butphantomsarepossible.Serializable(Isolated):AtransactionoperatingattheSerializablelevelguaranteesthatallconcurrenttransactionswillinteractonlyinwaysthatproducethesameeffectasifeachtransactionwereentirelyexecutedoneaftertheother.Atthisisolationlevel,dirtyreads,non-repeatablereads,andphantomsarenotpossible.,Thelevelofisolationdeterminesifachangetothedatabasewilloccurbeforeorattheendofthetransaction.Italsodetermineswhatdegreeofvisibilityyoucurrentlyhavetodatachangedbyothertransactions.Afullyisolatedtransactionhasnooverlapwithanothertransactionoccurringatthesametime.,11,ADOIsolationLevel,Thetablebelowshowsthenameofthevalue,thenumericvalue,andtherelatedphenomenafromabove.,12,ADOConnectionMode,TheModesettingcanbeusedtoconstrictconcurrentaccesstothesamedatasource,guaranteeingtheaccuracyofthedata.,13,WhyADOUsesCursors,CursorTypes,LockTypes,andCursorLocations(ADO),First,thecursorlocationdetermineswheretostoretherowsetwhilethecursorisopen.Second,thecursortypedeterminesmovementwithinthecursorandwhethertherowsetwillreflectuserschanges.Third,thecursorslockingtypespecifieshowSQLServerwilllocktherowsontheserverwhenyouwanttomakechanges.,TheADODB.ConnectionobjecthasapropertyknownasCursorLocationwhichisusedtoset/retrievethecursorlocationthatwillbeusedbyanyrecordsetobjectsthataccesstheirdatathroughtheconnectionobject.TheCursorLocationpropertycanonlybesetwhiletheconnectionisclosed,andthepropertywillbeinheritedbyanyrecordsetobjectsthataccesstheirdatathroughthegivenconnectionobject.Recordsetobjectscanalsoexplicitlysetacursorlocationdifferentthantheconnectionobjectscursorlocationaslongasitissetbeforetherecordsetisopen.ThetwooptionsavailableforthispropertyareadUseClientandadUseServer,withadUseServerbeingthedefaultproperty.,CursorLocation,WithinADO,cursorshavethreefunctions.,14,CursorTypes,LockTypes,andCursorLocations(ADO),WhenusingtheadUseServerserver-sidecursorlocation,responsibilityforhandlingthedatageneratedbyaquerylieswiththedatabaseserver.Thebenefitofserver-sidecursorsisthatwegainaccesstothedynamiccursortype.Thisallowsustoseeanychangestothedatathataremadebyotherusersinthedataourapplicationisaccessing.Withaserver-sidecursor(adUseServer),wehaveaccesstotheadOpenDynamicandadOpenForwardOnlycursortypes,andallfouroftherecordsetlocktypes.,adUseServer,Client-sidecursors,specifiedwiththeadUseClientkeyword,arehandledinternallybyADO.Thesecursorsoffermorefunctionalitythantheirserver-sidecounterparts,andalsoresultinlessloadbeingplacedontheserver.Whenusingaclient-sideadUseClientcursor,onlytheadOpenStaticcursorisavailable,andwecannotusetheadLockPessimisticlocktype.Client-sidecursorsalsohelpdecreaseloadonserver,sincewithastaticcursordataissenttotheclientandthentheserverhasnofurthercommunicationswiththeclient.Thisallowsyourservertoscalealotbetterthanwithserver-sidecursors.,adUseClient,15,CursorTypes,LockTypes,andCursorLocations(ADO),Client-Hostedvs.Server-HostedCursors,Client-HostedCursors,Server-HostedCursors,EntireresultsetmustbesenttotheclientTheresultsetisstoredinvirtualmemoryLocalADOprovidermanagesthecursorChangesmadebyusersnotvisibleNotresourcesusedontheserverwhilecursorisopen,OnlytherequestedrowsaresenttotheclientTheresultisstoredontheserverTheservermanagesthecursorChangesmadebyusersarevisibleMinimalresourcesusedontheclientwhilecursorisopen,16,Whenyouexecuteaquerythatreturnsrowsofdata,suchasSELECT*FROMmytable,theresultingdataishandledusingacursor.AcursorcanbelocatedeitherontheclientwiththeadUseClientargument,orontheserverwiththeadUseServerargument.Inaddition,thereare4typesofcursor:,CursorTypes,CursorTypes,LockTypes,andCursorLocations(ADO),adOpenForwardOnly,adOpenStatic,adOpenDynamic,andadOpenKeyset.,AllowableCursorTypesBasedonCursorLocation,17,CursorTypes,LockTypes,andCursorLocations(ADO),Staticcursorsaresonamedbecauseadditionsanddeletionsofrowsdontchangethelistofrows.Furthermore,changestoexistingrecordsdontappear.Anychangethecursorownermakesthroughthecursorappearsimmediately,butthestaticcursorignoresonlymodificationsbyotherusersuntiltheapplicationrefreshesthecursor.Ingeneral,youcanthinkofaclient-hosted,staticcursorasalocalcopyofthetablethatsisolatedfromtherestofthedatabasesystem.Client-hostedandserver-hostedstaticcursorsletyouusetheMoveFirst,MoveNext,MovePrevious,MoveLast,andMovemethodsoftheRecordsetobjecttomovetoanyrowinthecursor.Inaddition,client-hostedstaticcursorsletyoumovetoaspecificrowbysettingtheAbsolutePositionpropertyequaltotherownumber.Thevalidvaluesforthepropertyare1tothenumberofrowsstoredintheRowcountproperty.AnimportantconsiderationwhenyouusetheAbsolutePositionpropertyisthatarowspositionwithintherowsetmaychangedependingoninsertsanddeletes.Forexample,ifyoudeleterow4,thenrow5becomesthenewrow4.Therefore,SQLServerdocumentationrecommendsusingBookmarksinsteadofrownumbers,butabsolutepositioningworkswellforread-onlycursors.,StaticCursors.,18,CursorTypes,LockTypes,andCursorLocations(ADO),Forward-onlycursorsletyouscanonlyfromthefirsttothelastrowsinthecursor.OnlytheMoveNextmethodworksfortheforward-onlycursor.Youcanupdaterows,insertnewrows,anddeleterows,butyoucantmovebackward.,Forward-OnlyCursors.,Ifyourapplicationrequiresimmediateaccesstoallchanges,regardlessofwhomakesthechanges,youneedtousedynamiccursors.Choosedynamiccursorsifmultipleusersinsert,update,anddeleterowsinthedatabaseatthesametime.Dynamiccursorsletyourapplicationmoveinanydirectionthroughthecursor,andshowalluserschangestoallrows.Dynamiccursorsareflexible,buttheydontsupportabsolutepositioning.Becausedynamiccursorsrespondtoallmembershipchanges,theydontprovideawayforyoutoidentify,forexample,the10throwinthecursor.SQLServermaintainsonerowinitsbuffer,anditre-executespartofthequerytofindthenextrow.Thus,dynamiccursorsusemoreserverresourcesthanothercursors.Althoughdynamiccursorsarepowerfulandextremelyflexible,usethemonlywhenyouneedto.,DynamicCursors.,19,CursorTypes,LockTypes,andCursorLocations(ADO),Withkeysetcursors,therowmembershipandroworderarefixedwhenyouopenthecursor.Aswithastaticcursor,yourapplicationcanmovebackandforthbetweenrows.Unlikestaticcursors,however,keysetcursorsletyouseechangesotherprogramsmakeandchangesyourprogrammakes.Rememberthatclient-hostedcursorsareessentiallylocalcopiesofthedata;therefore,aclient-hostedkeysetcursorisntuseful.ADOalwayschangesthesecursorsintoclient-hostedstaticcursors.Keysetcursorspresentthreespecialcases.,KeysetCursors.,First,whenauserdeletesarow,SQLServermarkstherowasdeletedanditbecomesinaccessible.Second,ifauserchangesarowsothatitnolongermatchesthecursorsWHEREclausecriteria,SQLServerineffectremovestherowfromthecursorasiftheuserhaddeletedit.Therowstillexistsinthetable,butitisinaccessible.Third,ifanotheruserinsertsarowintothetable,SQLServerdoesntaddittothecursor,butifyouaddarowthroughthecursor,itappearsattheendofthecursor.,20,CursorTypes,LockTypes,andCursorLocations(ADO),adLockReadOnly,youcannotalterthedata.,adLockPessimistic,Pessimisticlockingisthestrongesttypeoflock.Recordswithpessimisticlockingareunavailabletootherusers.Pessimisticlocksoccurwhentheserverdeliverstherecord.TherecordremainslockeduntilyouclosetheRecordsetobject.,adLockOptimistic,AdLockBatchOptimistic,Optimisticbatchlocksactlikeoptimisticlocks,excepttheyworkforbatchupdatesdeferringimmediateupdatesinfavorofupdatingmanyrecordsatonetimeratherthanupdatingeachrecordimmediatelyaswithadLockOptimisticlocking.Itsyourcallwhetherbatchupdatesorimmediateupdatesarebetterforyourapplication,inpart,itdependsonthelevelofinteractivityyourapplicationdemandsandhowpeopleexpecttousetheapplication.,Optimisticlockinglocksrecordsjustbeforeanupdateoccurs,andunlocksthemimmediatelyafterward.Otheruserscanaccessdataduringthetimeyoureupdatingtherecord,whichmeanstheymaypotentiallybeviewingoutdateddata.Similarly,withoptimisticlocking,multipleusersmaysimultaneouslytrytoupdatethesamedata,leadingtoproblems.Optimisticlocking,recordbyrecordtheproviderusesoptimisticlocking,lockingrecordsonlywhenyoucalltheUpdatemethod.,21,CursorTypes,LockTypes,andCursorLocations(ADO),Apessimisticapproach,withlocks,isusuallyseenasgoodfordataintegrity,althoughitcanbebadforconcurrency,especiallythelongeralockisheld.Inparticular,itguaranteesagainstlostupdates-definedasanupdateperformedbyoneprocessbetweenthetimeofaccessandupdatebyanotherprocess,whichoverwritestheinterimupdate.However,otherusersareblockedfromupdatingthedataandpossiblyreadingitaswellifthereadaccessalsotriestoacquirealock.Anoptimsticapproachcanalleviatelockconcurrencyproblems,butrequiresmorecodeandcareforintegrity.Thebasicsarethatanychangesbetweentimeofaccessandtimeofupdatemustbedetectedandtakenintoaccount.Dependingonthetypeofoptimisticconcurrencychosen,ADOcompareseithertherowID,therowdatavalues,TimeStampcolumnsorcombinationsoftheseoptionswithexistingdatatodetermineifarowhaschangedsincelastfetched.Ifnochangeshavetakenplacesincethelastfetch,theupdateismade.Otherwise,yourapplicationtriggersatrappableerror.,22,CursorTypes,LockTypes,andCursorLocations(ADO),Optimisticusingvalues,Usefulinconditionswherethereisonlyasmallchancethatasecondusermayupdatearowintheintervalbetweenwhenacursorisopenedandtherowisfinallyupdated.Thecurrentvaluesintherowarecomparedwiththevaluesretrievedwhentherowwaslastfetched.Ifanyofthevalueshavechanged,anerrorisreturned.Performanceisgoodbecauselocksarenotheldontherowsthatmakeuptheresultset.,Optimisticusingrows,Thisisfortableswheretherowshaveamodificationtimestampcolumn.Thecursorenginecompareseachrowscurrenttimestampvaluewiththetimestampvaluethatwasstoredwhentherowwaslastfetchedtodeterminewhethertherowwasupdated.Becauseonlythetimestampneedscomparison,thisisfasterthancheckingallofthevalues.,23,ADOConnectionObject:OtherProperties,CommandTimeoutYoucanusethispropertytosettheduration,inseconds,thataSQLqueryisallowedtoexecute.Ifthequerytakeslongerthenthespecifiedtimeinseconds,thequeryisabortedandtheexecutemethodfails.Thispropertyisusefulinslownetworkconnections,backloggedservers,orlargedatabaseaccess.DefaultDatabaseThispropertytellstheconnectionwhichdatabasetoconnectto.WhenODBCconnectionsaremade,wespecifythedefaultdatabasetotheonethatwascreatedforyou,makingthispropertyunnecessary.WhenusingaDSN-lessconnection,wecaneitherusethispropertyorspecifythedatabaseintheOpenmethodcall.VersionThispropertyallowsyoutodisplaythecurrentversionoftheADOthattheserverisrunning.,24,ADOConnectionObjectOtherProperties,ConnectionStringTheConnectionStringPropertyallowsyoutospecifythedatabaseyouwishtoconnectto.WecanconnecteitherthroughODBCorDSN-lessconnectionstoourdatabase.TheConnectionStringPropertyismadeupofaseriesofassociations.Eachpairwithintheassociationhasanameandavalueassignedtoit.SemicolonsseparateeachpairinsidetheConnectionString.,25,ADOConnectionObjectOtherProperties,Thisexampleopensaconnectiontothepubsdatabaseonthebigsmileserverusingtheusernameofsaandablankpassword.,26,ADOConnectionObjectOtherProperties,ConnectionTimeoutThispropertysetshowlong,inseconds,theopenmethodwilltrytoopenthedatabase.Ifthedatabasedoesnotopenwithinthespecifiedtime,theoperationisconsideredafailure.CursorLocationThispropertyletsADOknowwherethedatafromtheOpencommandwillbekept.TheConnectionObjectdoesnotusethevaluesetinthisproperty,buttheRecordsetObjectinheritsitsvalue.,Thefirstlocation,theClient,isaspecialcursorlocation.Theclientlocationwillallowthedatatobequeriedfromtheserverandthentransmittedbacktotheclient.Theclientthenmakesthemodificationstothedata.Oncethemodificationsaremade,themodifiedsetofdata,orRecordset,isthentransmittedbacktotheserverforupdating.Theclientbatchisamethodwherethedataistransmittedinbatchesbackandforthtotheserverandclient.Theclientmaymodify20recordsandthensendbackthatRecordsetforabatchupdate.,27,ADOConnectionObject:Methods,BeginTransWhenyoucallthismethodinsidetheConnectionObject,anewtransactionbegins.Wecansetthemodeandisolationpropertiestodefinehowourtransactionshouldwork.CommitTransWhentheCommitTransmethodiscalled,thecurrenttransactioniscommittedtothedatabase,meaningthatanychangesinsidethetransactionarewrittentothedatabase.CloseWecanusetheClosemethodtobreaktheConnectionObjectslink.Ifyouforgettocalltheclosemethodattheendofyourscripts,ASPautomaticallyclosesanydatabaseconnections.RollbackTransRollbackTranscancelsanychangesmadeduringthecurrenttransactionandendsthetransaction.OpenUsingtheOpenmethodonaConnectionObjectestablishesaphysicalconnectiontoadatasource.Afterthismethodsuccessfullycompletes,theconnectionisliveandyoucanissuecommandsagainstitandprocessresults.Therefore,thisisthemostimportantmethodforaccessingdatafromyourdatasources.Again,wecanusetheoptionalConnectionStringargumenttospecifyourconnectionstring.Wecanoverridethispropertybyspecifyingourconnectionstringasaparametertotheopencall.,28,ADOConnectionObject:Methods,ExecuteWecallExecutemethodtoexecuteaqueryagainstthedatasource.TheexecutemethodreturnsaRecordset.Ifthequerythatwesubmitdoesnotreturnanydata,thentheexecutemethodreturnsanemptyRecordset.Setrs=Connection.ExecuteQuery,Count,OptionsWhenwecalltheExecutemethod,wemustpasstheQueryparameter,whichcontainsastandardSQLquery.Theotherparameters,CountandOptions,areoptional.TheCountParametercontainsthenumberofrecordsaffectedbythequery.Thisparametermaybehelpfulifwewanttoknowhowmanyrecordsareinatable,orhowmanyrecordsweneedtoloopthroughtodisplayalloftherecordsintheRecordset.TheOptionsparametersthatwecanpass.,29,ADORecordsetObject,TheADORecordsetobjectisusedtoholdasetofrecordsfromadatabasetable.ARecordsetobjectconsistofrecordsandcolumns(fields).InADO,thisobjectisthemostimportantandthemostusedobjecttomanipulatedatafromadatabase.,setobjRecordset=Server.CreateObject(ADODB.recordset),WhenyoufirstopenaRecordset,thecurrentrecordpointerwillpointtothefirstrecordandtheBOFandEOFpropertiesareFalse.Iftherearenorecords,theBOFandEOFpropertyareTrue.Recordsetobjectscansupporttwotypesofupdating:,Immediateupdating-allchangesarewrittenimmediatelytothedatabaseonceyoucalltheUpdatemethod.Batchupdating-theproviderwillcachemultiplechangesandthensendthemtothedatabasewiththeUpdateBatchmethod.,ProgID,30,InADOthereare4differentcursortypesdefined:,ADORecordsetObject,Dynamiccursor-Allowsyoutoseeadditions,changes,anddeletionsbyotherusers.Keysetcursor-Likeadynamiccursor,exceptthatyoucannotseeadditionsbyotherusers,anditpreventsaccesstorecordsthatotherusershavedeleted.Datachangesbyotheruserswillstillbevisible.Staticcursor-Providesastaticcopyofarecordsetforyoutousetofinddataorgeneratereports.Additions,changes,ordeletionsbyotheruserswillnotbevisible.Thisistheonlytypeofcursorallowedwhenyouopenaclient-sideRecordsetobject.Forward-onlycursor-AllowsyoutoonlyscrollforwardthroughtheRecordset.Additions,changes,ordeletionsbyotheruserswillnotbevisible.,ThecursortypecanbesetbytheCursorTypepropertyorbytheCursorTypeparamet