[Link] [Link]://[Link]/learn/dataaccess/[Link].
WorkingwithDatainASP.NET2.0::UsingExisting StoredProceduresfortheTypedDataSets TableAdapters Introduction
Inthe precedingtutorialwesawhowtheTypedDataSetsTableAdapterscouldbeconfiguredtousestored [Link],weexaminedhowtohavethe [Link] 2.0orwhenbuildinganASP.NET2.0websitearoundanexistingdatamodel,chancesarethatthedatabasealready [Link],youmayprefertocreateyourstoredproceduresbyhandor throughsometoolotherthantheTableAdapterwizardthatautogeneratesyourstoredprocedures. [Link] Northwinddatabaseonlyhasasmallsetofbuiltinstoredprocedures,wewillalsolookatthestepsneededto [Link]! Note:IntheWrappingDatabaseModificationswithinaTransaction tutorialweaddedmethodstothe TableAdaptertosupporttransactions(BeginTransaction,CommitTransaction,andsoon).Alternatively, transactionscanbemanagedentirelywithinastoredprocedure,whichrequiresnomodificationstotheData [Link] statementswithinthescopeofatransaction.
Step1:AddingStoredProcedurestotheNorthwindDatabase
[Link] NorthwinddatabasethatreturnsallcolumnsfromtheProducts tableforthosethathaveaparticularCategoryID [Link],expandtheNorthwinddatabasesothatitsfolders DatabaseDiagrams, Tables,Views,andsoforth [Link],theStoredProceduresfolder [Link],simplyrightclicktheStored ProceduresfolderandchoosetheAddNewStoredProcedureoptionfromthecontextmenu.
1 of20
Figure1:RightClicktheStoredProceduresFolderandAddaNewStoredProcedure
AsFigure1shows,selectingtheAddNewStoredProcedureoptionopensascriptwindowinVisualStudiowith [Link] 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+[Link],theStoredProceduresfolderrefreshes,showingthenewlycreatedstored
2 of20
[Link],thescriptinthewindowwillchangesubtletyfromCREATEPROCEDURE dbo.Products_SelectProductByCategoryIDtoALTERPROCEDURE dbo.Products_SelectProductByCategoryID.CREATEPROCEDURE addsanewstoredproceduretothedatabase, whileALTERPROCEDURE [Link], changingthestoredproceduresinputparametersorSQLstatementsandclickingtheSaveiconwillupdatethe storedprocedurewiththesechanges. Figure2showsVisualStudioaftertheProducts_SelectByCategoryID storedprocedurehasbeensaved.
Figure2:TheStoredProcedureProducts_SelectByCategoryID HasBeenAddedtotheDatabase
Step2:ConfiguringtheTableAdaptertoUseanExistingStored Procedure
NowthattheProducts_SelectByCategoryID storedprocedurehasbeenaddedtothedatabase,wecanconfigure [Link],wewilladda GetProducstByCategoryID(categoryID) methodtotheProductsTableAdapter inthe NorthwindWithSprocs TypedDataSetthatcallstheProducts_SelectByCategoryID storedprocedurewejustcreated. StartbyopeningtheNorthwindWithSprocs [Link] andchooseAdd [Link] weoptedtohavethe [Link],however,wewanttowirethenew TableAdaptermethodtotheexistingProducts_SelectByCategoryID [Link],choosethe Useexistingstoredprocedure optionfromthewizardsfirststepandthenclickNext.
3 of20
Figure3:ChoosetheUseexistingstoredprocedure Option
[Link] procedurelistsitsinputparametersontheleftandthedatafieldsreturned(ifany)[Link] Products_SelectByCategoryID storedprocedurefromthelistandclickNext.
4 of20
Figure4:PicktheProducts_SelectByCategoryID StoredProcedure
Thenextscreenasksuswhatkindofdataisreturnedbythestoredprocedureandouranswerheredeterminesthe [Link],ifweindicatethattabulardataisreturned,themethod willreturnaProductsDataTable [Link], ifweindicatethatthisstoredprocedurereturnsasinglevaluetheTableAdapterwillreturnanObject thatis assignedthevalueinthefirstcolumnofthefirstrecordreturnedbythestoredprocedure. SincetheProducts_SelectByCategoryID storedprocedurereturnsallproductsthatbelongtoaparticular category,choosethefirstanswer Tabulardata andclickNext.
5 of20
Figure5:IndicatethattheStoredProcedureReturnsTabularData
[Link] theFillaDataTableandReturnaDataTable optionschecked,butrenamethemethodstoFillByCategoryID [Link] everythinglookscorrect,clickFinish.
6 of20
Figure6:NametheMethods FillByCategoryID andGetProductsByCategoryID
Note:TheTableAdaptermethodswejustcreated,FillByCategoryID andGetProductsByCategoryID, [Link] viaits@CategoryID parameter.IfyoumodifytheProducts_SelectByCategory storedprocedures parameters,[Link] previoustutorial,thiscanbedoneinoneoftwoways:bymanuallyaddingorremovingparametersfromthe parameterscollectionorbyrerunningtheTableAdapterwizard.
Step3:AddingaGetProductsByCategoryID(categoryID) Methodtothe BLL
WiththeGetProductsByCategoryID DALmethodcomplete,thenextstepistoprovideaccesstothismethodin [Link] classfileandaddthefollowingmethod:
<[Link].DataObjectMethodAttribute_ ([Link],False)>_ PublicFunctionGetProductsByCategoryID(ByValcategoryIDAsInteger)_ [Link] [Link](categoryID) EndFunction
ThisBLLmethodsimplyreturnstheProductsDataTable returnedfromtheProductsTableAdapters GetProductsByCategoryID [Link] attributeprovidesmetadatausedbythe [Link],thismethodwillappearintheSELECTtabs 7 of20
dropdownlist.
Step4:DisplayingProductsbyCategory
TotestthenewlyaddedProducts_SelectByCategoryID storedprocedureandthecorrespondingDALandBLL methods,[Link] listallofthecategoriesinthedatabasewhiletheGridViewwilldisplaytheproductsbelongingtotheselected category. Note:Wevecreatedmaster/[Link] lookatimplementingsuchamaster/detailreport,refertotheMaster/DetailFilteringWithaDropDownList tutorial. [Link] pageintheAdvancedDAL folderanddragaDropDownListfromtheToolbox [Link] propertytoCategories anditsAutoPostBack propertytoTrue. Next,fromitssmarttag,bindtheDropDownListtoanewObjectDataSourcenamedCategoriesDataSource. ConfiguretheObjectDataSourcesothatitretrievesitsdatafromtheCategoriesBLL classsGetCategories [Link],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,[Link] to ProductsByCategory and,fromitssmarttag,bindittoanewObjectDataSourcenamed [Link] ObjectDataSourcetousethe ProductsBLLWithSprocs class,havingitretrieveitsdatausingtheGetProductsByCategoryID(categoryID) [Link],setthedropdownlistsintheUPDATE,INSERT, andDELETEtabsto (None)andclickNext.
9 of20
Figure9:ConfiguretheObjectDataSourcetoUsetheProductsBLLWithSprocs Class
10 of20
Figure10:RetrieveDatafromtheGetProductsByCategoryID(categoryID) Method
ThemethodchosenintheSELECTtabexpectsaparameter,sothefinalstepofthewizardpromptsusforthe [Link] controlfrom [Link].
11 of20
Figure11:UsetheCategories DropDownListastheSourceofthecategoryID Parameter
UponcompletingtheObjectDataSourcewizard,VisualStudiowilladdBoundFieldsandaCheckBoxFieldforeach [Link]. [Link] [Link],asFigure12shows,causesa postbackandreloadsthegridwiththeproductsofthenewlyselectedcategory.
12 of20
Figure12:TheProductsintheProduceCategoryareDisplayed
Step5:WrappingaStoredProceduresStatementsWithintheScopeof aTransaction
Inthe WrappingDatabaseModificationswithinaTransactiontutorialwediscussedtechniquesforperforminga [Link] performedundertheumbrellaofatransactioneitherallsucceedorallfail,[Link] usingtransactionsinclude:
l l l
[Link] namespace, [Link],and AddingtheTSQLtransactioncommandsdirectlywithinthestoredprocedure
TheWrappingDatabaseModificationswithinaTransaction [Link] remainderofthistutorialexamineshowtomanageatransactionusingTSQLcommandsfromwithinastored procedure. ThethreekeySQLcommandsformanuallystarting,committing,androllingbackatransactionareBEGIN TRANSACTION,COMMITTRANSACTION,andROLLBACKTRANSACTION,[Link] 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,[Link] Try...Catch blocksinVisualBasic,theSQLTRY...CATCH blockexecutesthestatementsintheTRY [Link] statementraisesanerror,controlisimmediatelytransferredtotheCATCH block. IftherearenoerrorsexecutingtheSQLstatementsthatmakeupthetransaction,theCOMMITTRANSACTION [Link],however,oneofthestatementsresultsinanerror, theROLLBACKTRANSACTION intheCATCH blockreturnsthedatabasetoitsstatepriortothestartofthetransaction. Thestoredprocedurealsoraisesanerrorusingthe RAISERRORcommand,whichcausesaSqlException tobe raisedintheapplication. Note:SincetheTRY...CATCH blockisnewtoSQLServer2005,theabovetemplatewillnotworkifyouare usingolderversionsofMicrosoftSQLServer.IfyouarenotusingSQLServer2005,consultManaging TransactionsinSQLServerStoredProceduresforatemplatethatwillworkwithotherversionsofSQL Server. [Link] andProducts tables, meaningthateachCategoryID fieldintheProducts tablemustmaptoaCategoryID valueintheCategories [Link],suchasattemptingtodeleteacategorythathasassociated products,[Link],revisittheUpdatingandDeletingExisting BinaryDataexampleintheWorkingwithBinaryDatasection(~/BinaryData/[Link]). ThispagelistseachcategoryinthesystemalongwithEditandDeletebuttons(seeFigure13),butifyouattemptto deleteacategorythathasassociatedproducts suchasBeverages thedeletefailsduetoaforeignkeyconstraint violation(seeFigure14).
14 of20
Figure13:EachCategoryisDisplayedinaGridViewwithEditandDeleteButtons
15 of20
Figure14:YouCannotDeleteaCategorythathasExistingProducts
Imagine,though,thatwewanttoallowcategoriestobedeletedregardlessofwhethertheyhaveassociated [Link],imaginethatwewanttoalsodeleteitsexistingproducts (althoughanotheroptionwouldbetosimplysetitsproductsCategoryID valuesto NULL).Thisfunctionalitycould [Link],wecouldcreateastored procedurethatacceptsa@CategoryID inputparameterand,wheninvoked,explicitlydeletesalloftheassociated productsandthenthespecifiedcategory. Ourfirstattemptatsuchastoredproceduremightlooklikethefollowing:
CREATEPROCEDUREdbo.Categories_Delete ( @CategoryIDint ) AS First,deletetheassociatedproducts... DELETEFROMProducts WHERECategoryID=@CategoryID Nowdeletethecategory DELETEFROMCategories WHERECategoryID=@CategoryID
Whilethiswilldefinitelydeletetheassociatedproductsandcategory,itdoesnotdosoundertheumbrellaofa [Link] thatwouldprohibitthedeletion ofaparticular@CategoryID [Link] [Link],thisstoredprocedurewouldremoveallof
16 of20
itsproductswhilethecategoryremainedsinceitstillhasrelatedrecordsinsomeothertable. Ifthestoredprocedurewerewrappedwithinthescopeofatransaction,however,thedeletestotheProducts table [Link] 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 [Link] andinstructit tousetheCategories_Delete storedprocedureinstead. Note:EarlierinthistutorialwewereworkingwiththeNorthwindWithSprocs [Link] onlyhasasingleentity,ProductsDataTable,[Link],forthe remainderofthistutorialwhenItalkabouttheDataAccessLayerImreferringtotheNorthwind DataSet,
17 of20
theonethatwefirstcreatedintheCreatingaDataAccessLayer tutorial. OpentheNorthwindDataSet,selecttheCategoriesTableAdapter,[Link] PropertieswindowliststheInsertCommand,UpdateCommand,DeleteCommand,andSelectCommand usedbythe TableAdapter,[Link] propertytoseeits details.AsFigure15shows,theDeleteCommandsComamndType propertyissettoText,whichinstructsittosend thetextintheCommandText propertyasanadhocSQLquery.
Figure15:SelecttheCategoriesTableAdapter intheDesignertoViewItsPropertiesintheProperties Window
Tochangethesesettings,selectthe(DeleteCommand)textinthePropertieswindowandchoose(New)from [Link],CommandType,andParameters properties. Next,settheCommandType propertytoStoredProcedure andthentypeinthenameofthestoredprocedureforthe CommandText (dbo.Categories_Delete).Ifyoumakesuretoenterthepropertiesinthisorder firstthe CommandType andthentheCommandText [Link] youdonotenterthesepropertiesinthisorder,youwillhavetomanuallyaddtheparametersthroughthe [Link],itsprudenttoclickontheellipsesintheParameterspropertytobring uptheParametersCollectionEditortoverifythatthecorrectparametersettingschangeshavebeenmade(see Figure16).Ifyoudonotseeanyparametersinthedialogbox,addthe@CategoryID parametermanually(youdo notneedtoaddthe@RETURN_VALUE parameter).
18 of20
Figure16:EnsureThattheParametersSettingsareCorrect
OncetheDALhasbeenupdated,deletingacategorywillautomaticallydeleteallofitsassociatedproductsanddo [Link],returntotheUpdatingandDeletingExistingBinaryData [Link],thecategoryandall ofitsassociatedproductswillbedeleted. Note:BeforetestingtheCategories_Delete storedprocedure,whichwilldeleteanumberofproducts alongwiththeselectedcategory,[Link] [Link] databaseinApp_Data,simplycloseVisualStudioandcopytheMDFandLDFfiles inApp_Data [Link],youcanrestorethedatabasebyclosing VisualStudioandreplacingthecurrentMDFandLDFfilesinApp_Data withthebackupcopies.
Summary
WhiletheTableAdapterswizardwillautomaticallygeneratestoredproceduresforus,therearetimeswhenwe [Link] accommodatesuchscenarios,[Link] thistutorialwelookedathowtomanuallyaddstoredprocedurestoadatabasethroughtheVisualStudio [Link] SQLcommandsandscriptpatternusedforstarting,committing,androllingbacktransactionsfromwithinastored procedure. HappyProgramming!
AbouttheAuthor
19 of20
ScottMitchell,authorofsevenASP/[Link],hasbeenworkingwith [Link],trainer,[Link] [Link]@[Link]. or viahisblog,whichcanbefoundat [Link]
SpecialThanksTo
[Link] Geisenow,SrenJacobLauritsen,[Link]?Ifso, dropmealineat mitchell@[Link].
20 of20