Thistutorialispartofaset.FindoutmoreaboutdataaccesswithASP.NETintheWorkingwithData inASP.NET2.0sectionoftheASP.NETsiteathttp://www.asp.net/learn/dataaccess/default.aspx.
WorkingwithDatainASP.NET2.0::UsingExisting StoredProceduresfortheTypedDataSets TableAdapters Introduction
Inthe precedingtutorialwesawhowtheTypedDataSetsTableAdapterscouldbeconfiguredtousestored procedurestoaccessdataratherthanadhocSQLstatements.Inparticular,weexaminedhowtohavethe TableAdapterwizardautomaticallycreatethesestoredprocedures.WhenportingalegacyapplicationtoASP.NET 2.0orwhenbuildinganASP.NET2.0websitearoundanexistingdatamodel,chancesarethatthedatabasealready containsthestoredproceduresweneed.Alternatively,youmayprefertocreateyourstoredproceduresbyhandor throughsometoolotherthantheTableAdapterwizardthatautogeneratesyourstoredprocedures. InthistutorialwewilllookathowtoconfiguretheTableAdaptertouseexistingstoredprocedures.Sincethe Northwinddatabaseonlyhasasmallsetofbuiltinstoredprocedures,wewillalsolookatthestepsneededto manuallyaddnewstoredprocedurestothedatabasethroughtheVisualStudioenvironment.Letsgetstarted! Note:IntheWrappingDatabaseModificationswithinaTransaction tutorialweaddedmethodstothe TableAdaptertosupporttransactions(BeginTransaction,CommitTransaction,andsoon).Alternatively, transactionscanbemanagedentirelywithinastoredprocedure,whichrequiresnomodificationstotheData AccessLayercode.InthistutorialwellexploretheTSQLcommandsusedtoexecuteastoredprocedures statementswithinthescopeofatransaction.
Step1:AddingStoredProcedurestotheNorthwindDatabase
VisualStudiomakesiteasytoaddnewstoredprocedurestoadatabase.Letsaddanewstoredproceduretothe NorthwinddatabasethatreturnsallcolumnsfromtheProducts tableforthosethathaveaparticularCategoryID value.FromtheServerExplorerwindow,expandtheNorthwinddatabasesothatitsfolders DatabaseDiagrams, Tables,Views,andsoforth aredisplayed.Aswesawintheprecedingtutorial,theStoredProceduresfolder containsthedatabasesexistingstoredprocedures.Toaddanewstoredprocedure,simplyrightclicktheStored ProceduresfolderandchoosetheAddNewStoredProcedureoptionfromthecontextmenu.
1 of20
Figure1:RightClicktheStoredProceduresFolderandAddaNewStoredProcedure
AsFigure1shows,selectingtheAddNewStoredProcedureoptionopensascriptwindowinVisualStudiowith theoutlineoftheSQLscriptneededtocreatethestoredprocedure.Itisourjobtofleshoutthisscriptandexecute it,atwhichpointthestoredprocedurewillbeaddedtothedatabase. Enterthefollowingscript:
CREATEPROCEDUREdbo.Products_SelectByCategoryID ( @CategoryIDint ) AS SELECTProductID,ProductName,SupplierID,CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder, ReorderLevel,Discontinued FROMProducts WHERECategoryID=@CategoryID
Thisscript,whenexecuted,willaddanewstoredproceduretotheNorthwinddatabasenamed Products_SelectByCategoryID.Thisstoredprocedureacceptsasingleinputparameter(@CategoryID,oftype int)anditreturnsallofthefieldsforthoseproductswithamatchingCategoryID value. ToexecutethisCREATEPROCEDURE scriptandaddthestoredproceduretothedatabase,clicktheSaveiconinthe toolbarorhitCtrl+S.Afterdoingso,theStoredProceduresfolderrefreshes,showingthenewlycreatedstored
2 of20
procedure.Also,thescriptinthewindowwillchangesubtletyfromCREATEPROCEDURE dbo.Products_SelectProductByCategoryIDtoALTERPROCEDURE dbo.Products_SelectProductByCategoryID.CREATEPROCEDURE addsanewstoredproceduretothedatabase, whileALTERPROCEDURE updatesanexistingone.SincethestartofthescripthaschangedtoALTERPROCEDURE, changingthestoredproceduresinputparametersorSQLstatementsandclickingtheSaveiconwillupdatethe storedprocedurewiththesechanges. Figure2showsVisualStudioaftertheProducts_SelectByCategoryID storedprocedurehasbeensaved.
Figure2:TheStoredProcedureProducts_SelectByCategoryID HasBeenAddedtotheDatabase
Step2:ConfiguringtheTableAdaptertoUseanExistingStored Procedure
NowthattheProducts_SelectByCategoryID storedprocedurehasbeenaddedtothedatabase,wecanconfigure ourDataAccessLayertousethisstoredprocedurewhenoneofitsmethodsisinvoked.Inparticular,wewilladda GetProducstByCategoryID(categoryID) methodtotheProductsTableAdapter inthe NorthwindWithSprocs TypedDataSetthatcallstheProducts_SelectByCategoryID storedprocedurewejustcreated. StartbyopeningtheNorthwindWithSprocs DataSet.RightclickontheProductsTableAdapter andchooseAdd QuerytolaunchtheTableAdapterQueryConfigurationwizard.Intheprecedingtutorial weoptedtohavethe TableAdaptercreateanewstoredprocedureforus.Forthistutorial,however,wewanttowirethenew TableAdaptermethodtotheexistingProducts_SelectByCategoryID storedprocedure.Therefore,choosethe Useexistingstoredprocedure optionfromthewizardsfirststepandthenclickNext.
3 of20
Figure3:ChoosetheUseexistingstoredprocedure Option
Thefollowingscreenprovidesadropdownlistpopulatedwiththedatabasesstoredprocedures.Selectingastored procedurelistsitsinputparametersontheleftandthedatafieldsreturned(ifany)ontheright.Choosethe Products_SelectByCategoryID storedprocedurefromthelistandclickNext.
4 of20
Figure4:PicktheProducts_SelectByCategoryID StoredProcedure
Thenextscreenasksuswhatkindofdataisreturnedbythestoredprocedureandouranswerheredeterminesthe typereturnedbytheTableAdaptersmethod.Forexample,ifweindicatethattabulardataisreturned,themethod willreturnaProductsDataTable instancepopulatedwiththerecordsreturnedbythestoredprocedure.Incontrast, ifweindicatethatthisstoredprocedurereturnsasinglevaluetheTableAdapterwillreturnanObject thatis assignedthevalueinthefirstcolumnofthefirstrecordreturnedbythestoredprocedure. SincetheProducts_SelectByCategoryID storedprocedurereturnsallproductsthatbelongtoaparticular category,choosethefirstanswer Tabulardata andclickNext.
5 of20
Figure5:IndicatethattheStoredProcedureReturnsTabularData
Allthatremainsistoindicatewhatmethodpatternstousefollowedbythenamesforthesemethods.Leaveboth theFillaDataTableandReturnaDataTable optionschecked,butrenamethemethodstoFillByCategoryID andGetProductsByCategoryID.ThenclickNexttoreviewasummaryofthetasksthewizardwillperform.If everythinglookscorrect,clickFinish.
6 of20
Figure6:NametheMethods FillByCategoryID andGetProductsByCategoryID
Note:TheTableAdaptermethodswejustcreated,FillByCategoryID andGetProductsByCategoryID, expectaninputparameteroftypeInteger.Thisinputparametervalueispassedintothestoredprocedure viaits@CategoryID parameter.IfyoumodifytheProducts_SelectByCategory storedprocedures parameters,youllneedtoalsoupdatetheparametersfortheseTableAdaptermethods.Asdiscussedinthe previoustutorial,thiscanbedoneinoneoftwoways:bymanuallyaddingorremovingparametersfromthe parameterscollectionorbyrerunningtheTableAdapterwizard.
Step3:AddingaGetProductsByCategoryID(categoryID) Methodtothe BLL
WiththeGetProductsByCategoryID DALmethodcomplete,thenextstepistoprovideaccesstothismethodin theBusinessLogicLayer.OpentheProductsBLLWithSprocs classfileandaddthefollowingmethod:
<System.ComponentModel.DataObjectMethodAttribute_ (System.ComponentModel.DataObjectMethodType.Select,False)>_ PublicFunctionGetProductsByCategoryID(ByValcategoryIDAsInteger)_ AsNorthwindWithSprocs.ProductsDataTable ReturnAdapter.GetProductsByCategoryID(categoryID) EndFunction
ThisBLLmethodsimplyreturnstheProductsDataTable returnedfromtheProductsTableAdapters GetProductsByCategoryID method.TheDataObjectMethodAttribute attributeprovidesmetadatausedbythe ObjectDataSourcesConfigureDataSourcewizard.Inparticular,thismethodwillappearintheSELECTtabs 7 of20
dropdownlist.
Step4:DisplayingProductsbyCategory
TotestthenewlyaddedProducts_SelectByCategoryID storedprocedureandthecorrespondingDALandBLL methods,letscreateanASP.NETpagethatcontainsaDropDownListandaGridView.TheDropDownListwill listallofthecategoriesinthedatabasewhiletheGridViewwilldisplaytheproductsbelongingtotheselected category. Note:Wevecreatedmaster/detailinterfacesusingDropDownListsinprevioustutorials.Foramoreindepth lookatimplementingsuchamaster/detailreport,refertotheMaster/DetailFilteringWithaDropDownList tutorial. OpentheExistingSprocs.aspx pageintheAdvancedDAL folderanddragaDropDownListfromtheToolbox ontotheDesigner.SettheDropDownListsID propertytoCategories anditsAutoPostBack propertytoTrue. Next,fromitssmarttag,bindtheDropDownListtoanewObjectDataSourcenamedCategoriesDataSource. ConfiguretheObjectDataSourcesothatitretrievesitsdatafromtheCategoriesBLL classsGetCategories method.SetthedropdownlistsintheUPDATE,INSERT,andDELETEtabsto(None).
Figure7:RetrieveDatafromtheCategoriesBLL ClasssGetCategories Method
8 of20
Figure8:SettheDropDownListsintheUPDATE,INSERT,andDELETETabsto(None)
AftercompletingtheObjectDataSourcewizard,configuretheDropDownListtodisplaytheCategoryName data fieldandtousetheCategoryID fieldastheValue foreachListItem. Atthispoint,theDropDownListandObjectDataSourcesdeclarativemarkupshouldsimilartothefollowing:
<asp:DropDownListID="Categories"runat="server"AutoPostBack="True" DataSourceID="CategoriesDataSource"DataTextField="CategoryName" DataValueField="CategoryID"> </asp:DropDownList> <asp:ObjectDataSourceID="CategoriesDataSource"runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetCategories"TypeName="CategoriesBLL"> </asp:ObjectDataSource>
Next,dragaGridViewontotheDesigner,placingitbeneaththeDropDownList.SettheGridViewsID to ProductsByCategory and,fromitssmarttag,bindittoanewObjectDataSourcenamed ProductsByCategoryDataSource.ConfiguretheProductsByCategoryDataSource ObjectDataSourcetousethe ProductsBLLWithSprocs class,havingitretrieveitsdatausingtheGetProductsByCategoryID(categoryID) method.SincethisGridViewwillonlybeusedtodisplaydata,setthedropdownlistsintheUPDATE,INSERT, andDELETEtabsto (None)andclickNext.
9 of20
Figure9:ConfiguretheObjectDataSourcetoUsetheProductsBLLWithSprocs Class
10 of20
Figure10:RetrieveDatafromtheGetProductsByCategoryID(categoryID) Method
ThemethodchosenintheSELECTtabexpectsaparameter,sothefinalstepofthewizardpromptsusforthe parameterssource.SettheParametersourcedropdownlisttoControlandchoosetheCategories controlfrom theControlIDdropdownlist.ClickFinishtocompletethewizard.
11 of20
Figure11:UsetheCategories DropDownListastheSourceofthecategoryID Parameter
UponcompletingtheObjectDataSourcewizard,VisualStudiowilladdBoundFieldsandaCheckBoxFieldforeach oftheproductdatafields.Feelfreetocustomizethesefieldsasyouseefit. Visitthepagethroughabrowser.WhenvisitingthepagetheBeveragescategoryisselectedandthecorresponding productslistedinthegrid.Changingthedropdownlisttoanalternativecategory,asFigure12shows,causesa postbackandreloadsthegridwiththeproductsofthenewlyselectedcategory.
12 of20
Figure12:TheProductsintheProduceCategoryareDisplayed
Step5:WrappingaStoredProceduresStatementsWithintheScopeof aTransaction
Inthe WrappingDatabaseModificationswithinaTransactiontutorialwediscussedtechniquesforperforminga seriesofdatabasemodificationstatementswithinthescopeofatransaction.Recallthatthemodifications performedundertheumbrellaofatransactioneitherallsucceedorallfail,guaranteeingatomicity.Techniquesfor usingtransactionsinclude:
l l l
UsingtheclassesintheSystem.Transactions namespace, HavingtheDataAccessLayeruseADO.NETclasseslikeSqlTransaction,and AddingtheTSQLtransactioncommandsdirectlywithinthestoredprocedure
TheWrappingDatabaseModificationswithinaTransaction tutorialusedtheADO.NETclassesintheDAL.The remainderofthistutorialexamineshowtomanageatransactionusingTSQLcommandsfromwithinastored procedure. ThethreekeySQLcommandsformanuallystarting,committing,androllingbackatransactionareBEGIN TRANSACTION,COMMITTRANSACTION,andROLLBACKTRANSACTION,respectively.LikewiththeADO.NET approach,whenusingtransactionsfromwithinastoredprocedureweneedtoapplythefollowingpattern: 1. 2. 3. 4. Indicatethestartofatransaction. ExecutetheSQLstatementsthatcomprisethetransaction. IfthereisanerrorinanyoneofthestatementsfromStep2,rollbackthetransaction. IfallofthestatementsfromStep2completewithouterror,committhetransaction.
ThispatterncanbeimplementedinTSQLsyntaxusingthefollowingtemplate:
13 of20
BEGINTRY BEGINTRANSACTIONStartthetransaction ...PerformtheSQLstatementsthatmakeupthetransaction... Ifwereachhere,success! COMMITTRANSACTION ENDTRY BEGINCATCH Whoops,therewasanerror ROLLBACKTRANSACTION Raiseanerrorwiththe detailsoftheexception DECLARE@ErrMsgnvarchar(4000), @ErrSeverityint SELECT@ErrMsg=ERROR_MESSAGE(), @ErrSeverity=ERROR_SEVERITY() RAISERROR(@ErrMsg,@ErrSeverity,1) ENDCATCH
ThetemplatestartsbydefiningaTRY...CATCH block,aconstructnewtoSQLServer2005.Likewith Try...Catch blocksinVisualBasic,theSQLTRY...CATCH blockexecutesthestatementsintheTRY block.Ifany statementraisesanerror,controlisimmediatelytransferredtotheCATCH block. IftherearenoerrorsexecutingtheSQLstatementsthatmakeupthetransaction,theCOMMITTRANSACTION statementcommitsthechangesandcompletesthetransaction.If,however,oneofthestatementsresultsinanerror, theROLLBACKTRANSACTION intheCATCH blockreturnsthedatabasetoitsstatepriortothestartofthetransaction. Thestoredprocedurealsoraisesanerrorusingthe RAISERRORcommand,whichcausesaSqlException tobe raisedintheapplication. Note:SincetheTRY...CATCH blockisnewtoSQLServer2005,theabovetemplatewillnotworkifyouare usingolderversionsofMicrosoftSQLServer.IfyouarenotusingSQLServer2005,consultManaging TransactionsinSQLServerStoredProceduresforatemplatethatwillworkwithotherversionsofSQL Server. Letslookataconcreteexample.AforeignkeyconstraintexistsbetweentheCategories andProducts tables, meaningthateachCategoryID fieldintheProducts tablemustmaptoaCategoryID valueintheCategories table.Anyactionthatwouldviolatethisconstraint,suchasattemptingtodeleteacategorythathasassociated products,resultsinaforeignkeyconstraintviolation.Toverifythis,revisittheUpdatingandDeletingExisting BinaryDataexampleintheWorkingwithBinaryDatasection(~/BinaryData/UpdatingAndDeleting.aspx). ThispagelistseachcategoryinthesystemalongwithEditandDeletebuttons(seeFigure13),butifyouattemptto deleteacategorythathasassociatedproducts suchasBeverages thedeletefailsduetoaforeignkeyconstraint violation(seeFigure14).
14 of20
Figure13:EachCategoryisDisplayedinaGridViewwithEditandDeleteButtons
15 of20
Figure14:YouCannotDeleteaCategorythathasExistingProducts
Imagine,though,thatwewanttoallowcategoriestobedeletedregardlessofwhethertheyhaveassociated products.Shouldacategorywithproductsbedeleted,imaginethatwewanttoalsodeleteitsexistingproducts (althoughanotheroptionwouldbetosimplysetitsproductsCategoryID valuesto NULL).Thisfunctionalitycould beimplementedthroughthecascaderulesoftheforeignkeyconstraint.Alternatively,wecouldcreateastored procedurethatacceptsa@CategoryID inputparameterand,wheninvoked,explicitlydeletesalloftheassociated productsandthenthespecifiedcategory. Ourfirstattemptatsuchastoredproceduremightlooklikethefollowing:
CREATEPROCEDUREdbo.Categories_Delete ( @CategoryIDint ) AS First,deletetheassociatedproducts... DELETEFROMProducts WHERECategoryID=@CategoryID Nowdeletethecategory DELETEFROMCategories WHERECategoryID=@CategoryID
Whilethiswilldefinitelydeletetheassociatedproductsandcategory,itdoesnotdosoundertheumbrellaofa transaction.ImaginethatthereissomeotherforeignkeyconstraintonCategories thatwouldprohibitthedeletion ofaparticular@CategoryID value.Theproblemisthatinsuchacasealloftheproductswillbedeletedbeforewe attempttodeletethecategory.Thenetresultisthatforsuchacategory,thisstoredprocedurewouldremoveallof
16 of20
itsproductswhilethecategoryremainedsinceitstillhasrelatedrecordsinsomeothertable. Ifthestoredprocedurewerewrappedwithinthescopeofatransaction,however,thedeletestotheProducts table wouldberolledbackinthefaceofafaileddeleteonCategories.Thefollowingstoredprocedurescriptusesa transactiontoassureatomicitybetweenthetwoDELETE statements:
CREATEPROCEDUREdbo.Categories_Delete ( @CategoryIDint ) AS BEGINTRY BEGINTRANSACTIONStartthetransaction First,deletetheassociatedproducts... DELETEFROMProducts WHERECategoryID=@CategoryID
Nowdeletethecategory DELETEFROMCategories WHERECategoryID=@CategoryID Ifwereachhere,success! COMMITTRANSACTION ENDTRY BEGINCATCH Whoops,therewasanerror ROLLBACKTRANSACTION Raiseanerrorwiththe detailsoftheexception DECLARE@ErrMsgnvarchar(4000), @ErrSeverityint SELECT@ErrMsg=ERROR_MESSAGE(), @ErrSeverity=ERROR_SEVERITY() RAISERROR(@ErrMsg,@ErrSeverity,1) ENDCATCH
TakeamomenttoaddtheCategories_Delete storedproceduretotheNorthwinddatabase.ReferbacktoStep1 forinstructionsonaddingstoredprocedurestoadatabase.
Step6:UpdatingtheCategoriesTableAdapter
WhileweveaddedtheCategories_Delete storedproceduretothedatabase,theDALiscurrentlyconfiguredto useadhocSQLstatementstoperformthedelete.WeneedtoupdatetheCategoriesTableAdapter andinstructit tousetheCategories_Delete storedprocedureinstead. Note:EarlierinthistutorialwewereworkingwiththeNorthwindWithSprocs DataSet.ButthatDataSet onlyhasasingleentity,ProductsDataTable,andweneedtoworkwithcategories.Therefore,forthe remainderofthistutorialwhenItalkabouttheDataAccessLayerImreferringtotheNorthwind DataSet,
17 of20
theonethatwefirstcreatedintheCreatingaDataAccessLayer tutorial. OpentheNorthwindDataSet,selecttheCategoriesTableAdapter,andgotothePropertieswindow.The PropertieswindowliststheInsertCommand,UpdateCommand,DeleteCommand,andSelectCommand usedbythe TableAdapter,aswellasitsnameandconnectioninformation.ExpandtheDeleteCommand propertytoseeits details.AsFigure15shows,theDeleteCommandsComamndType propertyissettoText,whichinstructsittosend thetextintheCommandText propertyasanadhocSQLquery.
Figure15:SelecttheCategoriesTableAdapter intheDesignertoViewItsPropertiesintheProperties Window
Tochangethesesettings,selectthe(DeleteCommand)textinthePropertieswindowandchoose(New)from thedropdownlist.ThiswillclearoutthesettingsfortheCommandText,CommandType,andParameters properties. Next,settheCommandType propertytoStoredProcedure andthentypeinthenameofthestoredprocedureforthe CommandText (dbo.Categories_Delete).Ifyoumakesuretoenterthepropertiesinthisorder firstthe CommandType andthentheCommandText VisualStudiowillautomaticallypopulatetheParameterscollection.If youdonotenterthesepropertiesinthisorder,youwillhavetomanuallyaddtheparametersthroughthe ParametersCollectionEditor.Ineithercase,itsprudenttoclickontheellipsesintheParameterspropertytobring uptheParametersCollectionEditortoverifythatthecorrectparametersettingschangeshavebeenmade(see Figure16).Ifyoudonotseeanyparametersinthedialogbox,addthe@CategoryID parametermanually(youdo notneedtoaddthe@RETURN_VALUE parameter).
18 of20
Figure16:EnsureThattheParametersSettingsareCorrect
OncetheDALhasbeenupdated,deletingacategorywillautomaticallydeleteallofitsassociatedproductsanddo soundertheumbrellaofatransaction.Toverifythis,returntotheUpdatingandDeletingExistingBinaryData pageandclicktheDeletebuttonforoneofthecategories.Withonesingleclickofthemouse,thecategoryandall ofitsassociatedproductswillbedeleted. Note:BeforetestingtheCategories_Delete storedprocedure,whichwilldeleteanumberofproducts alongwiththeselectedcategory,itmightbeprudenttomakeabackupcopyofyourdatabase.Ifyouare usingtheNORTHWND.MDF databaseinApp_Data,simplycloseVisualStudioandcopytheMDFandLDFfiles inApp_Data tosomeotherfolder.Aftertestingthefunctionality,youcanrestorethedatabasebyclosing VisualStudioandreplacingthecurrentMDFandLDFfilesinApp_Data withthebackupcopies.
Summary
WhiletheTableAdapterswizardwillautomaticallygeneratestoredproceduresforus,therearetimeswhenwe mightalreadyhavesuchstoredprocedurescreatedorwanttocreatethemmanuallyorwithothertoolsinstead.To accommodatesuchscenarios,theTableAdaptercanalsobeconfiguredtopointtoanexistingstoredprocedure.In thistutorialwelookedathowtomanuallyaddstoredprocedurestoadatabasethroughtheVisualStudio environmentandhowtowiretheTableAdaptersmethodstothesestoredprocedures.WealsoexaminedtheT SQLcommandsandscriptpatternusedforstarting,committing,androllingbacktransactionsfromwithinastored procedure. HappyProgramming!
AbouttheAuthor
19 of20
ScottMitchell,authorofsevenASP/ASP.NETbooksandfounderof4GuysFromRolla.com,hasbeenworkingwith MicrosoftWebtechnologiessince1998.Scottworksasanindependentconsultant,trainer,andwriter.Hislatest bookisSamsTeachYourselfASP.NET2.0in24Hours.Hecanbereachedatmitchell@4GuysFromRolla.com. or viahisblog,whichcanbefoundat http://ScottOnWriting.NET.
SpecialThanksTo
Thistutorialserieswasreviewedbymanyhelpfulreviewers.LeadreviewersforthistutorialwereHilton Geisenow,SrenJacobLauritsen,andTeresaMurphy.InterestedinreviewingmyupcomingMSDNarticles?Ifso, dropmealineat mitchell@4GuysFromRolla.com.
20 of20
