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这个名字至今依然是个迷，包括开发者在内也不知道。

仓酷云

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。

不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?