MSSQL网页设计Build Data-Driven Web Services with ...

提供多语言支持，常见的编码如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。server|services|web|xmlDownloadthecodeforthisarticle:SQLXML3.exe(239KB)--->SUMMARYXMLisbecomingtheubiquitousdataformatontheWeb,andXMLsupportinSQLServerisevolvingtomeettheadditionaldemand.UsingXML,SOAP,HTTP,andSQLServer,youcannowbuildpowerfulWebServiceseasily.ToshowjusthowsimpleitiswithSQLXML3.0,thisarticlewalksthereaderthroughtheprocessstepbystep,fromsettingupavirtualdirectoryenablingdataaccessviaHTTPtoexecutingqueriesandbuildingWebServices.Finally,theauthorillustratesthecreationoftwoWebServicesclientsonewithC#thatworkswiththeMicrosoft.NETFrameworkandonewiththeSOAPToolkit2.0foranyonestillusingearlierdevelopmenttools.tshardtobelievethatXMLsupportinSQLServer?2000hasbeenaroundforovertwoyears.Inthesoftwareworld,thatsalifetime.SQLServer2000wasthefirstversiontoprovidenativesupport,andthiswaslimitedtothemorebasicXMLfeatureset(templatequeries,mappingschemas,andOPENXML).UsingsimpleHTTPqueriesyoucouldretrieveformattedrelationaldatainXMLformat.WithalittlehelpandsomeExtensibleStylesheetLanguage(XSL)magic,youcouldspitoutthedatainaformatted,HTML-friendlymanner.Later,withtheintroductionoffeatureslikeupdategrams,youcouldeasilysubmitanXML-basedSQLtemplatetoinsertorupdaterowsofinformationinSQLServerwithlittleeffort.
Initially,IthoughtthatsomewouldconsiderXMLsupportafrivolousadditiontoanalreadypowerfulproduct.IfadeveloperwasntdisplayingSQLdatainaWebpageorfeedingasystemthatonlyspeaksXML,werethesefeaturesallthatuseful?
Previously,theonlyviableapproachforaccessingdata,forthemiddle-tieranyway,wasthroughatraditionaldataaccesslayerbuiltwithODBC,OLEDB,orADO.NowwithSQLXML3.0,SQLServer2000,SOAP,BizTalk?andthe.NETFramework,XMLisnolongerafrivolousadditionitsthedatalanguageofchoice.

UsingSQLXML3.0forDataAccess
SQLXML3.0isthethirditerationofXMLsupportforSQLServer.ThebiggestdifferencebetweentheoldwayofrepresentingdataandthewayitsrepresentedwithXMLishowtherowsetiscreated,whereitiscreated(server-sideorclient-side),andhowitisformatted(raw,nested,element-based,orattribute-based).Formoreonrawandexplicitformats,refertotheinformationlistedinthearticlesummary.
Forthoseofyoualreadyworkingwithsomeofthe.NETserverproductssuchasBizTalk,managedclasses,andthelike,youalreadyknowhowimportantitistouseXMLasyourdataformat.IfusingXMLfordataaccessisnewtoyou,thismaytakesomegettingusedto.IfyouchoosetouseXMLasyourdataformat,youmusttakeintoaccountthesubtledifferencesbetweenrelationalandhierarchicalrepresentationandhowyoucanexploitthebenefitsofahierarchy.
Ifyouareupgradingfromapreviousversion,youcanstillrunSQLXML3.0sidebysidewithyourcurrentversion.(Seethesidebars"Side-by-sideSupport"and"EvolutionofXMLSupport"formoreinformation.)

QueryingSQLServerwithXML
ThefastestwaytobeginaccessingSQLServer2000usingXMListhroughyourbrowser.Thisisagreatwaytocheckwhetheryouhaveeverythingsetupcorrectly,andisalsoyourfirstmeansofdiagnosingproblemsshouldtheyappear.ToaccessSQLServerusingaURLviathebrowseroranyHTTPclient,youmustfirstsetupavirtualdirectoryforSQLServerusingtheMicrosoft?ManagementConsole(MMC)snap-inprovidedwithanyofthereleases.
Ifyouwanttosetupavirtualdirectorytoperformtemplatequeries,youcanstillusetheMMCsnap-inprovidedwiththeoriginalinstallationofSQLServer.ThiscanbefoundintheSQLServer2000programgroupunderConfigureSQLXMLSupportinIIS.However,totakeadvantageofSQLXML3.0features,IrecommendselectingtheMMCsnap-infoundintheSQLXML3.0programgroupunderConfigureIISSupport.Hereyoucanconfigureallfeaturesuptoandincludingthoseofversion3.0.
Tosetupavirtualdirectory,firstyouneedtosetupadirectorystructurewithamaindirectory(Icalledmineprojects)thathastwosubdirectories:templateandSOAP.ThetemplatedirectorywillcontainyourXMLtemplatefilesandwillbeusedforalltemplateoperations(forexample,file-basedSQL,XPath,updategrams,andsoon).TheSOAPdirectorywillcontainallfilesrequiredforaccessingSQLServerviaWebServices.Ifyouwanttoexperimentwithmappingschemas(viatheschematype)and/ordirectdatabaseobjectaccess(viathedbobjecttype),thenyoumayadddirectoriesforeachofthoseaswell.FollowthesestepsfortestingyourinstallationwithasimpleXMLquery.
TocreatethetemplatevirtualdirectoryintheMMC,selectDefaultWebSite,thenNewVirtualDirectory.
OntheGeneraltab,namethevirtualdirectorytomatchthedatabaseyouwillbeaccessing.IsimplyuseNorthwind(seeFigure1).ThisbecomesthevirtualdirectoryuponwhichyouwillaccessanyXMLfeature.Setthisrootdirectorytocontainalltemplates.


Figure1GoodOlNorthwind

OntheSecuritytab,selecttheauthenticationschemeyouwillusetoaccessthedatabase.
OntheDataSourcetab,selectyourdatasource.
OntheSettingstab,select"Allowsql=..."andselect"Allowtemplatequeries."Thesetwowillbeenoughtogetyougoing.Lateryouwillselect"AllowPost"toenablecallstoSQLServerasaWebService.
OntheVirtualNamestab(seeFigure2),select<Newvirtualname>,callit"template,"specifythetemplatetype,andpointittothetemplatesubdirectorythatshouldnowresideunderyourmaindirectory.


Figure2DefiningaNewVirtualName

Namethetemplate"Customers.xml"andsaveitunderyourtemplatesubdirectory.AnySQLcommandcanbeaddedtothisfile.Bothupdategramsandbulkloadingcanbeusedforupdatesorinsertsfromthetemplatedirectoryaswell.HereyoucanseeasampleXMLquerytemplateforretrievingallcustomersfromtheNorthwinddatabase:

<ROOTxmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:queryclient-side-xml="0">SELECT*FROMCustomersFORXMLAUTO</sql:query></ROOT>
Nowexecutethefollowinginyourbrowser:http://localhost/northwind/template/customers.xml.YoushouldseetheXMLqueryresultsshowninFigure3(notfancybutfunctional).Ifso,yourqueriesareworkingandnowyoucanproceedtothemoreadvancedfeaturesofSQLXML3.0.
GettingStartedwithSQLXMLWebServices
Ifyouarealreadydoing.NETdevelopment,thenyouknowthatbuildingWebServicesisquitesimple.ThroughVisualStudio?.NETandtheruntimesuseofattributessuchasWebServiceandWebMethod,youcanquicklyproducereliableWebServices.EvenmoreadvancedfunctionalitysuchaspassingSOAPheadersorhookingSOAPrequestspassedintoa.NETWebService(traceextensions)becomeslessdauntingwith.NET.
Ifyouarentusingthe.NETruntimeinyourenvironmentyet(sensemybias?),alittlemoreelbowgreasemayberequired.YoucanusetheSOAPToolkit2.0,butthatrequiresmorebackgroundinhowSOAPisusedtosendandreceivedatafromaWebService.Overall,however,buildinganon-.NETclientisverysimilartoworkingwithaWebServiceproxyin.NET.Ifyoudonthave.NET,oryoudontwanttobuildanentiredataaccess/WebServicesframework,SQLXML3.0isforyou.
SQLXML3.0providesaWebServicemiddletierintheformofanISAPIlibrary(sqlis3.dll).AllyouneedtodoisconfigureSQLXMLandprovideaWebServicesclient.WithSQLXMLyoucannowsendSOAPHTTPrequeststoaserverrunningSQLXML3.0toexecuteastoredprocedure,XMLtemplate,orUDFdirectly.TherequestedoperationisexecutedatthedatasourceandaSOAPresponseisreturnedtotheclient.TheWebServicesmagic,atleastontheserver,isalltakencareofbySQLXML.JustconfiguretheWebServiceusingthesameMMCsnap-inasIdemonstratedintheprevioussectionfortemplates.Theonlycoderequiredisontheclient.ThiscanbeanASPorASP.NETapplication,aMicrosoftWindows?application,aconsoleapplication,orwhatever.TheclientcanbebuiltusingC#,astandardSOAPclientusingstraightXML,oreventheSOAPToolkit2.0.InthisarticleIwilldemonstrateclientdevelopmentbybuildingasimpleC#client(usingtheVisualStudio-generatedWebServicesproxy)andaVisualBasic?client(usingtheSOAPToolkit).

SettingUpaSQLXMLWebService
Ifyouarefollowingalongwithmysample,usethesestepstoconfiguretheWebService:
SelecttheNorthwindvirtualdirectorythatyoucreatedintheprevioussectionanddisplayitsproperties.
SelecttheSettingstabandmakesureAllowPostischeckedsothatSOAPrequestscanbepostedfromtheclient.
SelecttheVirtualNamestabandselect<Newvirtualname>asyoudidtocreatethetemplatetype.
SelecttheSOAPtypeandgiveitaname.Icalledmine"soapprocedures."YoucannameitanythingyouwantandyoucanhaveasmanydefinedSOAPtypesasyoulike.ForeachdefinedSOAPtype,SQLXMLcreatesacorrespondingconfigurationfile(.ssc)andaWebServicesDescriptionLanguage(WSDL)filethatareusedtoaccesstheWebService.ItisimportanttonotethatthesefilesarenamedaftertheWebServiceyouprovided,nottheSOAPtype.TheSOAPtypesnameisusedtoretrievethegeneratedWSDLfile,whichdescribestheserviceandtheoperations(storedprocedures,UDFs,andtemplates)thataclientcanthenrequest.
SelectadirectorytomapthisSOAPtype.UsetheSOAPdirectorycreatedearlier.Thisiswherethe.sscandWSDLfileswillbecreated.SelectSave.
Finally,giveyourWebServiceaname.Icalledmine"procedures."ThisisthenameyouwillusefromyourclientcodetoinstantiatetheWebServiceusingtheproxyin.NET.Figure4showstheWSDLoutputforthisWebService.YoullnoticethatunderIIS,yourvirtualdirectory(Northwind)willhaveaSOAPdirectoryandtwofiles:procedures.sscandprocedures.wsdl.NotethatbydefaultyoucantselecttheWSDLfiledirectlyfromthebrowser.YouneedthisURI:http://localhost/northwind/SOAPprocedures?wsdl.
WhenthenewSOAPtypeisselected,selectConfigure.
UndertheSoapVirtualNameConfigurationdialog,select<Newmethodmapping>.
Selectamappingtype(SPforstoredproceduresanduser-definedfunctions).
Selectthestoredprocedureusingthebrowsebutton,andgiveitamethodname.ThenamewillbetheinvokableWebmethodyouwillusefromtheclient.Keeptheremainingdefaults.FormyexampleIselectedtwostoredproceduresfromtheNorthwinddatabase,SalesByCategoryandCustOrderHist,andkeptthedefault,whichsimplyusesthestoredprocedurename.
TesttheWSDLfilethatcontainstheWebmethodscreatedfromyourbrowserasyoudidinthesection"SettingUpaSQLXMLVirtualDirectory."Nowletsbuildtheclients.
ASQLXMLWebServicesClientUsingC#
ThequickestwaytogetupandrunningwithWebServicesistowriteyourclientusingthe.NETFramework.Asyouwillsee,itisnttheamountofcodesavedthatmakes.NETsimplertouse.MostofyoucangetawaywithoutknowingtheunderpinningsoftheSOAPprotocolsincetheproxygeneratedfromVisualStudiodoesallofthework.However,learningsomeofthebasicelementsofSOAPwouldbesmart.Formysimpleexample,IuseC#tocallthenewlyconfiguredWebService.
IcreatedanewclientapplicationusingVisualStudio.NET.Theclientcanbeanytypeofapplication.Forthisexample,IamusingaC#applicationforWindows.SQLXMLWebServicescanbecalledlikeanyotherWebService.AddaWebreferencefromtheAddWebReferencedialogtypeinthesameURLyouusedtotesttheWSDLfile(http://localhost/northwind/SOAPprocedures?wsdl).InFigure5theGetAllCustomerstemplatehasbeencalledasaWebServiceanditsXMLresultsusedinaDataSetgrid.Figure6showsthesampleclientusingASP.NET.
TheWSDLoutputshouldappearintheleftpaneoftheVisualStudioIDE.Fromhere,youcanaddthereferencetoyourproject.Iaddedtheprocedures.wsdlreference,allowingmetodeclareavariableofthisWebreferencetype.Oncedeclared,Itreatthistypelikeanyotherclasstypein.NETbyinstantiatingit.AftertheWebServiceobjectiscreated,Icaninvokeitsoperationsbycallinganyofitsexposedmethods.IntelliSense?shouldnowdisplayeachoftheseWebmethodsintheeditor.
ThefollowingC#codeshowshowtocallastoredprocedurewrappedasaWebService.IveomittedafewdetailsthatIwillexplainshortly.YoucanseethatcallingaWebServiceatthispointisverysimilartocallingintoanyotherobjecttype:

localhost.proceduresoWSProcs=newlocalhost.procedures();intnReturnValue;?=oWSProcs.CustOrderHist("ALFKI",outnReturnValue);
YoullnoticethatthiscalldiffersfromstandardcallstoWebServicesinthereturnvalues.WhenusingSQLXMLWebServices,thedatareturnedfromtheWebmethodtakestheformofanobjectarray,whichmustthenbecastintoaworkabletypelikeXMLElementorSqlMessage.
XMLElementobjectsincludetheresultthatissuccessfullyreturnedbySQLXMLafterexecutinganyoperations(storedprocedure,template,orUDF).IntheWSDLfilethisisdefinedashavingaSqlXMLcomplextype.ErrormessagesreturnedfromSQLXMLareoftypeSqlMessage.IfSQLServerreturnsoneormoreerrors,thisSqlMessagecomplextypeisreturnedaspartoftheobjectarrayandisalsodefinedintheWSDLfile.(Moreonthislater.)
TheSystem.XML.XMLElementcomplextypemapsdirectlyintoanXMLnodeclasstypefromthe.NETclasslibrary.Ifyouhaveworkedwith.NETandXMLyoushouldalreadybefamiliarwiththisstocktype.SqlMessageisacustomtypespecifictoSQLXMLandcontainsanyerrormessagesgeneratedduringtransport.TomakesenseofthereturnedobjectarrayfromaSQLXMLWebService,IcreatedtheXMLElementmethod.InFigure7youcanseehowtheobjectarrayishandled.
ThismethodtakesanyreturnedobjectarrayandeitherreturnsanarrayofXMLElementtypesorthrowsanexception,fillinginthevaluesfromtheSqlMessagetype.TodetermineiftheobjectarraycontainsanerrororXMLinstancedata,thetypeisdeterminedbyusingGetTypeandthevalueiscastappropriately.XMLElementsaresimplyreturnedtothecaller.Figure8showsthecallingcodeinitsentirety.(Thisisslightlydifferentfromthisarticlesdownloadablecodeforclarity.)
IhavenotyetmentionedtheSystem.Data.DataSettype.JustbecausedataisbeingtransportedviaXML,SOAP,andultimatelySQLXMLdoesntmeanyoucannotuseDataSetstoyouradvantage.DataSetsareterrificatprovidingtheperfectdatacontainer,nottomentionbeinghandyforpurposessuchasdisplayingdatainagrid.
ItseasytoreturnXMLinstancedatafromastoredprocedure(callablefromaSQLXMLWebService)andturnitintoanXMLschema-basedDataSet,readytobeconsumedasyouplease.ToperformthisconversionIcreatedamethodcalledGetDataSetFromXMLFragmentwhichtakesanyXMLfragment,infersanXMLschema,andhydratesitsdata.ThemanagedSQLXMLclassescanalsobeusedinsimilarfashion.
ThefollowingcodeshowshowtheSystem.XML.XMLReaderandtheDataSetsReadXMLworktogethertofillaDataSet:

publicstaticDataSetGetDataSetFromXmlFragment(XmlElementoXml){DataSetds=newDataSet();XmlTextReaderoReader=newXmlTextReader(oXml.OuterXml,XmlNodeType.Element,newXmlParserContext(null,null,null,XmlSpace.None));//nowletscreateaschemaoffoftheinstancedatads.ReadXml(oReader,XmlReadMode.InferSchema);returnds;}
Dontforget,valuetypessuchasintegerandfloatcannotbepassedorreturnedasanullvaluewhenusingtheproxyclassesthataregeneratedbyVisualStudio.NET.Todoso,youmustcreateyourownWebServiceproxyclass(whichisnotrecommended).Referencetypesandstringtypescanbenull.

CallingTemplatesandUDFsasWebServices
Alongwithstoredprocedures,SQLXMLalsoallowsWebServicestocallXMLtemplatesandUDFs.Configuringthesetypesisnotverydifferentfromworkingwithstoredprocedures.TheconfigurationprocessestablishesthenecessarymappinginaWSDLfileasbefore.Onceconfigured,themappingisusedtoexecutethecorrespondingtemplateorUDFjustlikeyoudowithstoredprocedures.Ifyouwanttoconfigureatemplatetousewithmysample,completethefollowingsteps:
GotothepropertiesdialogoftheNorthwindvirtualdirectory.
OntheVirtualNamestab,selectthesoapproceduresSOAPtypecreatedearlierandselectConfigure.
SelecttemplateastheEdit/Newmappingtype.
Selectthebrowsebutton.FromthereyoucanfindanypreviouslybuiltXMLtemplate.
Selectthecustomers.xmltemplatethatyouhaveusedalreadytotesttheinstallationofSQLXML3.0andcallitGetAllCustomers.
Thatsit.YoucannowcallGetAllCustomersasaWebServicejustlikeyoudcallthestoredprocedures.GetAllCustomerswillreturnalloftherecordsfromtheCustomerstableasXML,butinsteadofusingabrowserIcannowcapturethisincode.Ibelievethisiswherethisreleasereallyshines.ThoseofyouwhohaveinvokedtemplatesincodeviaHTTPorthroughoneoftheOLEDBprovidersasIdiscussedinmyarticle"BizTalkandXML:AddE-CommercetoYourAppwithXMLandSQLServer2000,"(MSDNMagazineJanuary2002)willnowappreciatethesimplicityofuniformlyinvokingalloperationsasWebServices.
InvokingaUDFisnodifferent.YoucanbuildtheUDFshowninFigure9byfollowingthesamestepsjustoutlinedandselectingSPastheEdit/Newmappingtypeasyoudidwhenconfiguringacallablestoredprocedure.AllUDFsandstoredproceduresshouldappearinthebrowsedialog.MakesureyouupdateyourWebreferencefromVisualStudio.NET.(IntelliSensewilltellyouwhenitisthere,oryoucanlookatthegeneratedWSDL.)

UsingtheSOAPToolkit2.0
Manyofyoumaynotyethavetheoptionofusing.NETtechnologyinyourdevelopmentenvironment.Ifthatsthecase,youcaninvokeanySQLXMLfeatureusingplainoldVisualBasic?6.0.TheonlyadditionalcomponentrequiredpriortorunningthefollowingsamplecodeistheSOAPToolkit2.0.IinvoketheexactsameoperationsIcreatedherealreadyexceptIwilldoitfromVisualBasic6.0.FamiliaritywiththeMSXMLDocumentObjectModel(DOM)wouldbehelpful,butitsnotrequired.TheonlytwointerfacesthatarerequiredaretheIXMLDOMNodeListandIXMLDOMNodeinterfacesfromMSXML4.0.
Figure10looksamazinglysimilartotheC#sample.ThemajordifferencehereisthatIamdoingthisfromaVisualBasic6.0-basedclientandIamusingthesoapclientcomponentfromtheSoapToolkit2.0fortheproxy.SoapclientisusedexactlylikethegeneratedproxyfromVisualStudio.NET.Insteadofbindingthereturnvaluesfromanobjectarraytoadatatype,youwillalwaysbeusinganIXMLDomNodeListfromMSXML4.0toiteratethrougheachreturnedIXMLDOMNode.HereyouaresimplyworkingwiththeMSXMLNodeinterfaces.Theoutputfromrunningthiscodeisnotquiteasneatasyousawinthe.NETexample.ItcouldbemuchimprovedwithalittleXSL.Illleavetherestuptoyou.

Conclusion
InthisarticleIintroducedSQLXML3.0anditsmostpowerfulapplication:WebServicesusingSOAP.Forenvironmentsnotreadyfor.NET,orthoseofyouwithouttheinclinationtobuildacustommiddletier,SQLXML3.0providesasimpleyeteffectivewaytoaccessSQLServeroverthewire.HierarchicaldataintheformofXMLhasbecomethedataformatofchoiceamongdevelopers.XMLandSOAPwillgiveyouanadvantageinthelooselycoupledworldofWebServices.TodownloadthelatestWebrelease(SQLXMLVersion3.0)ortofindmoreinformationonthenewfeaturesofferedintheXMLforSQLServerWebReleases,seehttp://msdn.microsoft.com/xml.
两个到底是哪一个给出了MySQL这个名字至今依然是个迷，包括开发者在内也不知道。