orbetterperformance.
Prerequisites
Youshouldhaveanunderstandingof:
*FundamentalADO.NETdataconcepts,includingdatasetsanddataadapters.Formoreinformation,seeIntroductiontoDataAccesswithADO.NET.
*ConcurrencycontrolbasicsandtheoptionsavailableinVisualStudio.NET.Formoreinformation,seeIntroductiontoDataConcurrencyinADO.NET.
WhereAretheSQLStatements?
SQLstatementsarelocatedintheCommandTextpropertyofcommandobjects.SQLcommandsareautomaticallygeneratedatdesigntimewhenconfiguringdataadapters,andatruntimewhenusingcommandbuilderobjects.Formoreinformation,seeConcurrencyandCommandBuilderObjects.
ConfiguringDataAdapters
*DragadataadapterfromtheDatataboftheToolbox
*DragatablefromServerExplorer
*Modifyinganexistingadapter,byselectingadataadapterandclickingtheConfigureDataAdapterlinkatthebottomofthePropertieswindow.
CommandBuilderobjects
*Commandbuilderobjectsarecreatedprogrammaticallyatruntime.Formoreinformation,see(SqlCommandBuilderorOleDbCommandBuilder)
ConcurrencyandDataAdapters
WhenconfiguringdataadapterswiththeDataAdapterConfigurationWizard,youcandecidewhethertouseoptimisticconcurrencyforthegeneratedUpdateandDeletestatements.
ConsiderationsandCaveats
*YourdatasourcemusthaveaprimarykeyinorderfortheSQLstatementstobegeneratedtouseoptimisticconcurrency.
*WhencreatingdataadaptersbydraggingtablesfromServerExplorer,thedataadaptercreatesUpdateandDeletestatementsthatareautomaticallyconfiguredforoptimisticconcurrency.Ifyoudonotwanttouseoptimisticconcurrency,youcanreconfigurethedataadapter:Right-clicktheadapterandselectConfigureDataAdapterfromtheshortcutmenu,thencleartheUseoptimisticconcurrencyoptionoftheAdvancedSQLGenerationOptionsDialogBox.Thewizardwillrecreatethestatementswithouttheadditionalcodetocheckforconcurrencyviolations.
*Whenreconfiguringanexistingdataadapter,notethattheadvancedsettingsallreverttotheirdefaultstate.Forexample,ifyouclearedtheUseoptimisticconcurrencyoptionwhentheadapterwasoriginallyconfigured,itwillautomaticallybeselectedifyoureconfigureit,evenifyoudonotaccesstheAdvancedSQLGenerationOptionsdialogbox.
*IfyouselecttheUseexistingstoredproceduresoptionintheChooseaQueryTypesectionoftheDataAdapterConfigurationWizard,theoptiontouseoptimisticconcurrencyisnotavailable.Thestoredprocedureswillexecuteasis,andanydesiredconcurrencycheckingmustbedonewithinthestoredprocedure,orprogrammaticallybuiltintoyourapplication.
*Whencommandsaregeneratedtouseoptimisticconcurrency,noverificationwillbeperformedonbinarycolumnstodeterminewhetherconcurrentchangeshavebeenmade.Theresourcestoperformabit-by-bitcomparisonofalargebinaryrecordwouldbeextremelyinefficient.
SQLStatementsGeneratedbytheWizard
TounderstandhowVisualStudio.NETconstructsSQLstatementsthatuseoptimisticconcurrency,letusinspecttheUpdatestatementgeneratedbytheDataAdapterConfigurationWizard.WewilllookatthesamestatementgeneratedbothwithandwithouttheUseoptimisticconcurrencyoptionselectedintheAdvancedSQLGenerationOptionsdialogboxofthewizard.
YouwillnoticethedifferencesbetweenstatementsthateitheruseoptimisticconcurrencyornotarelocatedintheWhereclause.
NoteThefollowingexamplesusetheUpdatecommandthatisgeneratedbyrunningtheDataAdapterConfigurationWizard,andselectingseveralcolumnsfromtheCustomerstableintheNorthwindsampledatabase.
UpdateStatementUsingOptimisticConcurrency
ThisexampleusesthedefaultsettingsoftheDataAdapterConfigurationWizard,whichhastheUseoptimisticconcurrencyoptionselected.
NoteWhenusingoptimisticconcurrency,thecommandsaregeneratedwithasecondsetofparameters.Thissecondsetofparameters(theoneswiththe@Original_prefix)storethevaluesthatareinitiallyreadfromthedatasource.
ExaminingtheWhereclauseinthefollowingstatementrevealsthatallfieldsareinspectedtomakesurethecurrentvalueforeachfieldinthedatabaseisequaltothevaluethatwasoriginallyreadintothedataset(forexample,WHERECity=@Original_City).Bycomparingeachfieldinthedatabasewiththeoriginalvalue,itiseasytodetermineifaconcurrentuserhasmodifiedafield.IftheWhereclauseisnotsatisfied,norecordsareupdatedandaDBConcurrencyExceptionisraised.Ifafieldinthedatasourcecontainsanullvalue,thestatementalsoverifiestheoriginalrecordcontainedanullvalue.
UPDATECustomers
SETCustomerID=@CustomerID,CompanyName=@CompanyName,ContactName
=@ContactName,ContactTitle=@ContactTitle,City=@City
WHERE(CustomerID=@Original_CustomerID)AND(City=@Original_City
OR@Original_CityISNULLANDCityISNULL)AND(CompanyName=
@Original_CompanyName)AND(ContactName=@Original_ContactNameOR
@Original_ContactNameISNULLANDContactNameISNULL)AND(ContactTitle=
@Original_ContactTitleOR@Original_ContactTitleISNULLAND
ContactTitleISNULL);
SELECTCustomerID,CompanyName,ContactName,ContactTitle,City
FROMCustomersWHERE(CustomerID=@CustomerID)
UpdateStatementWithoutOptimisticConcurrency
ThisexamplemodifiestheadvancedsettingsoftheDataAdapterConfigurationWizardandclearstheUseoptimisticconcurrencyoption.
ExaminingthefollowingstatementrevealsthatallfieldswillbeupdatedaslongasarecordexistsinthedatabasewhereCustomerID=@Original_CustomerID.Nomatterwhatvaluesexistinthisrecord,theywillallbesettothevaluespassedthroughthisstatement.Thereisnoverificationtocheckifaconcurrentuserhasmodifiedtherecord.Thisiscalledthe"last-inwins"approach,becausenomatterwhatmodificationshavebeenperformedontherecord,theupdatewillstillbeperformed.
UPDATECustomers
SETCustomerID=@CustomerID,CompanyName=@CompanyName,
ContactName=@ContactName,ContactTitle=@ContactTitle,City=@City
WHERE(CustomerID=@Original_CustomerID);
SELECTCustomerID,CompanyName,ContactName,ContactTitle,City
FROMCustomersWHERE(CustomerID=@CustomerID)
OptimizingtheGeneratedSQLStatement
VisualStudio.NETgeneratesSQLstatementsthatusethe"checkallvalues"approachtooptimisticconcurrency.Althoughthismaynotgeneratethemostefficientstatement,itdoescreateastatementthatcancheckforconcurrencyviolationsonanydatasourcecontainingaprimarykey.
Ifthe"checkallvalues"approachtooptimisticconcurrencyprovesinefficient
上一篇:
ASP_net010电子购物商城系统+论文(论文和程序)
下一篇:
[博士论文]青春期叛逆现象发生的生理基础