Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P12

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

77
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'programming microsoft sql server 2000 with microsoft visual basic .net - p12', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P12

For m 2 r eappears aft er t he user replies t o t he prom pt , t he form shows t he fir st t wo t ext boxes wit h t heir prior v alues as well as t he fourt h t ex t box for display ing a colum n value. The specific value in t he bot t om t ext box is t he one m at ching r ow specified by t he user’s reply t o t he prom pt . Figu re 1 3 - 1 1 . Th e clien t a pp lica t ion form for t h e Se rvice 1 W e b ser vice in t h e Ta b leProce ssor folde r . The processing of t he r et ur n values fr om t he Colum nValues Web m et hod illust r at es a t ypical scenar io. A developer engineer s an applicat ion so t hat it can accom m odat e any of several scenarios. For exam ple, a client applicat ion m akes a select ion from t he t ot al set of colum n values t o show t he colum n value for j ust one r ow inst ead of t he whole set of colum n values as in Figure 13- 10. Figur e 13- 12 t r acks t he pr ocess fr om designat ing database and t able nam es t o capt uring t he r eply t o t he I nput Box prom pt t o showing t he specific colum n value t hat a user want s t o v iew. I n t he t op window, t he user designat es t hat t hey w ant result s fr om t he Cust om ers t able in t he Nor t hwind dat abase before click ing t he but t on labeled Get Colum n Value. The m iddle window shows t he user indicat ed t hat t he applicat ion should show t he colum n value for t he fift h row. By t he way, t he prom pt adj ust s aut om at ically t o show t he m axim um num ber of r ows. The applicat ion does t his by r unning t he RowCount Web m et hod when pr ocessing a request t o show a specific r ow value fr om t he fir st colum n. The bot t om window in t he figure rev eals BERGS as t he colum n v alue for t he fift h row in t he fir st colum n. You can easily confirm t his out com e for yourself by exam ining t he out put in Figur e 13- 10, which shows all t he colum n values for t he fir st colum n in t he Cust om ers t able from t he Nor t hwind dat abase.
Figu re 1 3 - 1 2 . Th e clien t applica t ion for t h e Se rvice 1 W e b ser vice in t h e Tab le Pr oce ssor folde r de m on st ra t ing h ow it h a nd le s a re qu e st t o sh ow a pa r t icu la r colu m n va lu e from t h e first colu m n of t h e Cu st om e rs t a ble in t h e N or t h w in d d at a ba se . The following list ing shows t he code behind For m 2 t hat m anages t he behavior of t he client applicat ion for t he Web service in t he TableProcessor folder . The list ing st ar t s wit h t he inst ant iat ion of a
m odule- lev el v ar iable, x ws1, for t he proxy Web serv ice. Not ice how Visual Basic .NET sy st em at ically nam es t he second- level reference in t he pr oxy obj ect . The proxy for t he first Web service uses localhost as it s second nam e. The proxy for t he deployed ver sion used localhost 1 as it s second nam e. This pr oxy variable, which is t he t hird one in t he chapt er, has localhost 2 as it s second nam e. I n all t hr ee cases, t he fir st nam e for a pr oxy denot es t he client applicat ion’s pr oj ect — nam ely, XMLWebSer viceClient s. Also, t he nam e for t he prox y obj ect in each case r efer s t o t he .asm x file in t he Web service, which has t he nam e Service1 in all t hr ee inst ances. The body of t he list ing includes t hr ee event pr ocedures. One is a form Load event procedure. This ev ent pr ocedure m erely readies t he init ial look of t he for m . I n part icular , it m akes t he t hird and fourt h t ex t boxes, along wit h t heir m at ching labels, invisible. The applicat ion also includes a Click event procedure for each but t on on t he form . These event procedur es inv oke t he RowCount and Colum nValues Web m et hods as well as processing t heir r et urn values. As you can see, t he x ws1 pr oxy variable appear s in bot h Click event procedur es, which is why t he list ing st art s by inst ant iat ing t he v ariable at t he m odule level. The But t on1_Click event procedur e inv ok es t he RowCount Web m et hod and displays it s result in Text Box3. This pr ocedure act ually st ar t s by m ak ing sure Text Box4 and it s m at ching label are invisible. These t wo cont rols are for displaying a colum n v alue and labeling t he ret ur n value, but a click of t he Row Count but t on ( But t on1) doesn’t show any colum n values. Nex t t he procedure copies t he Tex t pr opert y v alues of Text Box1 and Text Box2 t o m em ory var iables in t he client applicat ion. These variables st ore t he nam e of t he dat abase and t he t able for t he Web serv ice t o ex am ine. Aft er saving t he local m em or y v ariables, t he pr ocedure uses t hem as argum ent s while invoking t he RowCount Web m et hod. The argum ent s specify for which t able in which dat abase t o ret ur n a r ow count . The final group of lines in t he ev ent procedur e m akes t he t ext box and label ( Text Box 3 and Label3) for t he row count value visible on t he for m . The procedur e’s final line passes t he convert ed value t ype of t he r et urn v alue fr om t he RowCount Web m et hod t o t he Tex t propert y of Tex t Box3. The Click event pr ocedure for But t on2 is slight ly m or e sophist icat ed t han t he one for But t on1. Ther e ar e t hree r easons for t his. First , t he But t on2_Click event procedure invokes t wo Web m et hods inst ead of one. Second, t he Click event pr ocedure for But t on2 pr esent s a prom pt t o gat her user feedback. Third, t he event procedur e st ores t he ret ur n value from t he Colum nValues Web m et hod as an ar ray and t hen uses t he r eply t o t he pr om pt t o pick a value from t he arr ay and display it on t he form .
Like t he event procedure for But t on1, t he But t on2_Click event procedure st art s by m aking a t ex t box and label inv isible. I n t his case, t he t ext box and label are for t he Row Count Web m et hod’s ret ur n value, which a click t o But t on2 doesn’t show. Just because t he procedur e doesn’t direct ly show t he ret ur n value fr om t he RowCount Web m et hod doesn’t m ean t he Web m et hod is unused in t he procedur e. On t he cont rary, t he RowCount Web m et hod’s r et ur n value is used early and oft en t hroughout t he procedure. I n fact , t he nex t t hree lines save argum ent s for t he Web m et hod, inv oke it , and save t he ret ur n value in a m em ory v ariable, m y RowCount . Next t he procedure pr om pt s t he user for which r ow in t he first colum n t o show a colum n v alue. The procedur e uses an I nput Box funct ion for t his w it h t he default value 1. Aft er obt aining a reply t o t he I nput Box funct ion prom pt , t he procedure concludes it s dat a input phase fr om t he user. All t he dat a it needs is in m em or y or available via a Web m et hod call. Next t he procedure invok es t he Colum nValues Web m et hod and saves it s result as a st r ing. Then t he procedur e st rips off t he leading st r ing ( "Values in column 1 are: " ) fr om t he r et urn value and sav es t he result ing st ring ( st r1) . This leav es st r 1 wit h j ust t he colum n values from t he t able nam ed in Tex t Box2. Per haps t he m ost int er est ing aspect of t he procedure is t he par sing of st r 1 t o ext ract indiv idual colum n v alues t hat go int o cells in t he m yVect or arr ay . The arr ay is dim ensioned based on t he row count fr om t he t able nam ed in Text Box 2. This v alue is av ailable v ia a m em ory v ar iable ( m yRow Count ) from t he inv ocat ion of t he RowCount Web m et hod. The pr ocedure t hen opens a loop t hat it erat es t hrough t he colum n values in st r1. On each pass t hr ough t he loop, t he code reads t he fir st colum n v alue in st r1, which is a subst r ing up t o but not including t he fir st com m a. I t t hen sav es t his value in t he fir st em pt y cell in t he m y Vect or arr ay and r em oves t he value, it s t r ailing com m a, and t he blank space aft er t he com m a fr om t he st r1 v ar iable. Ther efore, successiv e passes always have a fr esh v alue as t he first colum n v alue in st r 1. The pr ocedure concludes by m aking Text Box4 wit h it s m at ching label v isible and by select ing a cell fr om t he m yVect or arr ay t o show based on t he user’s response t o t he I nput Box funct ion prom pt . ’Use cabinc_NthRoot Web Service. Dim xws1 As New XMLWebServiceClients.localhost2.Service1( ) Private Sub Form2_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ’Hide RowCount text box and label. TextBox3.Visible = False Label3.Visible = False
’Hide ColumnValue text box and label. TextBox4.Visible = False Label4.Visible = False End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click ’Hide ColumnValue text box and label. Label4.Visible = False TextBox4.Visible = False ’Pass database name and table name from text boxes on ’the form to the RowCount Web method. Dim adbname As String = TextBox1.Text Dim atablename As String = TextBox2.Text Dim myRowCount As Integer = _ xws1.RowCount(adbname, atablename) ’Make the RowCount label and text box visible ’before populating the text box with a value ’from the RowCount Web method. Label3.Visible = True TextBox3.Visible = True TextBox3.Text = myRowCount.ToString End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click ’Hide RowCount text box and label. Label3.Visible = False TextBox3.Visible = False ’Pass database name and table name from text boxes on ’the form to the RowCount Web method. Dim adbname As String = TextBox1.Text Dim atablename As String = TextBox2.Text Dim myRowCount As Integer = _ xws1.RowCount(adbname, atablename) ’Print out the maximum number of rows as part of a pr ompt ’for a selected row from a user. Dim strInputMsg = _ "What row to max. of " & myRowCount.ToString & "? "
Dim intReturnedRow As Integer = _ CInt(InputBox(strInputMsg, "", "1")) ’Pass database name and table name memory values to t he ’ColumnValues Web method and strip off leading string ’for column values. Dim myColumnValues As String = _ xws1.ColumnValues(adbname, atablename) Dim intToColon = InStr(myColumnValues, ":") Dim str1 = Mid(myColumnValues, intToColon + 2, _ Len(myColumnValues)) ’Dimension array and integer variable for loop. Dim myVector(myRowCount - 1) As String Dim intRow As Integer ’Pass string of column values to an array. For intRow = 0 To myRowCount - 1 myVector(intRow) = _ str1.substring(0, InStr(str1, ",") - 1) str1 = Mid(str1, InStr(str1, ",") + 2, Len(str1)) Next ’Make ColumnValue label and text box visible before ’passing array value corresponding to user selection in ’the text box. Label4.Visible = True TextBox4.Visible = True TextBox4.Text = myVector(intReturnedRow - 1) End Sub Th e SQL Se r ve r 2 0 0 0 W eb Ser vice s Toolk it The Web Services Toolkit sim plifies t he cr eat ion of Web serv ices based on SQL Ser ver 2000 dat abase obj ect s and t em plat es in I I S vir t ual direct ories. Microsoft built on an ear lier appr oach for delivering XML funct ionalit y from SQL Serv er wit h t he Web Serv ices Toolkit — nam ely, by ext ending t he capabilit y of t he I I S v irt ual dir ect ory so t hat it can host a Web serv ice. The Web serv ice fr om an I I S virt ual dir ect ory exposes indiv idual dat abase obj ect s and t em plat es as Web m et hods. Aft er t he creat ion of a Web serv ice based on an I I S virt ual dir ect ory , y ou st ill use t he sam e basic approach dem onst rat ed in t he preceding t wo sect ions for developing a client applicat ion for your Web serv ice. This sect ion st ar t s by revealing how t o design an
I I S v ir t ual direct or y t o offer a Web ser vice. The design of t he vir t ual dir ect ory specifies t he Web serv ice based on a st or ed pr ocedure. The r ev iew of a core client applicat ion and a sim ple ext ension of it equip you wit h t he skills t o build your own solut ions for capt ur ing XML fragm ent s r et urned from Web m et hods based on dat abase obj ect s and t em plat es. Scr ipt ing a SQL Se r ve r Use r f or a Vir t u a l D ir e ct or y Alt hough it isn’t essent ial t o designat e a SQL Ser ver user when specify ing an I I S vir t ual direct ory, it can be useful— especially when t he vir t ual direct ory host s a Web service. Any Web ser vice em anat ing fr om an I I S v ir t ual direct ory can hav e a pot ent ially lar ge num ber of users. By using a special SQL Server user, you can set t he per m issions for t he special SQL Serv er user and be sure t hat any one who connect s t o t he Web service will have perm ission t o per form t he t ask s enabled t hrough t he exposed Web m et hods. You can also lim it t he abilit y t o perform t asks t hr ough t he Web service by lim it ing t he per m ission for it s special SQL Serv er user. N ot e The .NET Fram ework cont ains st andard securit y convent ions, including t echniques for m anaging t he use of encrypt ion t hat your applicat ions m ay require for prot ect ing a user ’s ident it y, m anaging dat a during t r ansm ission, and aut hent icat ing dat a fr om designat ed client s. See t he “Crypt ography Overv iew” t opic in t he Visual St udio .NET docum ent at ion for m ore det ail on t his t opic. This t opic is a m aj or sect ion wit hin t he “Secur it y Applicat ions” t opic, which you m ight also want t o rev iew. The following T- SQL script is m eant for y ou t o r un from Query Analy zer for t he SQL Ser ver 2000 inst ance t hat y ou use for t he rem aining sam ples t hr oughout t his chapt er. The scr ipt is av ailable am ong t he book’s sam ple files as ScriptsFor 13.sql. The sam ple is built around t he not ion t hat t his is t he local SQL Ser ver 2000 inst ance. I f t his isn’t t he case, y ou’ll need t o adj ust t he sam ple accordingly. The script dr ops any pr ior SQL Serv er login for t he connect ed SQL Server inst ance and a prior user for t he Nor t hwind dat abase nam ed vbdot net 1. I f you incur error m essages because t he user doesn’t exist , sim ply ignor e t hem because t he purpose of t he scr ipt is t o rem ov e a login or user only if it does exist . Aft er m ak ing sur e vbdot net 1 is fr ee for assignm ent , t he scr ipt adds a new user nam ed vbdot net 1 and grant s access t o t he Nort hwind dat abase. Recall t hat t he Nort hwind dat abase is one of t he SQL Server sam ple dat abases. The dat abase’s public role grant s any user access t o m ost dat abase obj ect s t hat ship as par t of t he dat abase. For exam ple, vbdot net 1 has aut om at ic perm ission t o run
all st or ed procedures, such as t he Ten Most Expensive Product s st or ed procedure, which is one of t he built - in user- defined st ored procedures for t he dat abase. Not ice t hat t his scr ipt uses “/ * ” t o m ark t he beginning of t he code com m ent t hat st ret ches over m ult iple lines, and “* / ” t o end it . /*Run from member of sysadmin fixed server role. Ignore errors if user does not already exist. */ USE Northwind EXEC sp_revokedbaccess ’vbdotnet1’ EXEC sp_droplogin @loginame = ’vbdotnet1’ GO --Add vbdotnet1 user with known permissions. EXEC sp_addlogin @loginame = ’vbdotnet1’, @passwd = ’passvbdotnet1’, @defdb = ’Northwind’ EXEC sp_grantdbaccess ’vbdotnet1’ GO Bu ildin g a W e b Se r vice in a n I I S V ir t u a l D ir e ct or y Now t hat we hav e a SQL Ser ver user , we can proceed t hrough t he st eps for creat ing an I I S virt ual dir ect ory . This direct ory will cont ain t he cont ract for a Web serv ice. You can creat e a new I I S vir t ual dir ect ory by choosing Program s from t he Windows St art m enu, t hen SQLXML 3.0, and t hen Configure I I S Suppor t . This opens t he I I S Virt ual Direct or y Managem ent ut ilit y for SQLXML 3.0. I n or der t o open t he ut ilit y , you m ust , of course, have alr eady inst alled Web Release 3 ( SQLXML 3.0) . See t he “Web Serv ices from t he Web Serv ices Toolkit ” sect ion for a URL t o download Web Release 3 along wit h t he Web Ser vices Toolkit . Wit h t he I I S Virt ual Direct or y Managem ent ut ilit y open, ex pand t he folder for t he local Web server . Then r ight - click Default Web Sit e wit hin t he local Web server, choose New, and t hen choose Vir t ual Dir ect ory . This opens a m ult i- t abbed dialog box t hat let s y ou set t he proper t ies of a new virt ual dir ect ory . You can use t he New Vir t ual Dir ect ory Pr opert ies dialog box t o creat e t he v irt ual direct or y by following t hese inst r uct ions: 1. On t he Gener al t ab, nam e t he dir ect or y Chapt er13, and give t he vir t ual direct ory t he pat h c: \ inet pub\ wwwroot \ Chapt er 13. You can t y pe t he pat h or use t he Browse but t on t o navigat e t o t he folder. Alt hough t he ut ilit y allows you t o cr eat e a new
folder from wit hin t he ut ilit y , som e m ay find it easier t o cr eat e t he folder before opening t he ut ilit y. 2. On t he Securit y t ab, select t he SQL Serv er radio but t on. Then ent er vbdot net 1 in t he User Nam e t ex t box and passvbdot net 1 in t he Password t ex t box. Confirm t he password before m ov ing off t he t ab. 3. On t he Dat a Sour ce t ab, accept t he default set t ings of t he local SQL Serv er and default dat abase for t he current login. 4. On t he Set t ings t ab, leav e Allow Tem plat e Queries select ed and also select Allow POST. 5. On t he Vir t ual Nam es t ab, y ou set up t he vir t ual direct ory t hr ough which y ou can deliver Web services. Wit h < New vir t ual nam e> highlight ed in t he Defined Virt ual Nam es list box , ent er SoapFor13 in t he Nam e t ext box . Then select soap fr om t he Type list . Next , in t he Pat h t ext box , ent er t he pat h for your v ir t ual direct ory, nam ely c: \ inet put \ wwwr oot \ Chapt er13. Finally click Sav e t o enable t he configurat ion of y our Web service associat ed wit h t he SoapFor 13 virt ual nam e. Once y ou’ve clicked Sav e, t he Configure but t on is enabled. 6. While st ill in t he Vir t ual Nam es t ab, click Configur e ( see Figur e 13- 13) t o select SQL Ser ver st ored procedur es and user - defined funct ions t o expose as Web m ethods. You can also expose t em plat es t hr ough t he Web service. Alt hough your dat abase obj ect s and t em plat es m ust ex ist before you can expose t hem , t he Web Services Toolk it doesn’t expose t hem unt il y ou explicit ly configure it t o m ake t he Web service offer Web m et hods based on a st ored procedure, user- defined funct ion, or t em plat e. Figu re 1 3 - 1 3 . Th e V irt u a l N a m e t a b for t h e N e w V irt u a l D ire ct or y Pr op er t ie s dialog box for t h e Soa p For1 3 W e b se r vice in t h e Ch ap t e r 1 3 virt u al dire ct or y.
7. Aft er you click Configur e, t he Soap Vir t ual Nam e Configurat ion dialog box opens so t hat y ou can specify it em s t o ex pose as Web m et hods. I f you ar e going t o expose a st or ed pr ocedure or a user- defined funct ion, designat e SP as t he Type; ot her wise, select Tem plat e t o designat e a t em plat e as t he source for a Web m et hod. You can designat e an it em by using t he Browse but t on ( …) t o browse sources for a Web m et hod in t he Web service host ed by t he v ir t ual direct ory. By clicking t he Browse but t on wit h SP select ed as t he Type, I was able t o pick Ten Most Ex pensive Product s as t he source for a Web m et hod. I accept ed t he default select ion t o r et urn t he result set fr om t he st or ed pr ocedure as XML obj ect s. Wit h t his select ion, you can r et r ieve m ult iple r esult s ( or j ust one) fr om a st ored procedure. Figur e 13- 14 shows t he dialog box j ust befor e I click Save t o expose t he st ored pr ocedure as a Web m et hod. 8. Click OK t o sav e t he configurat ion of t he Web Serv ice and close t he Soap Virt ual Nam e Configurat ion dialog box.
Figu r e 1 3 - 1 4 . Th e Soa p V ir t u a l N a m e Con figu ra t ion d ia log b ox displa ying t h e se t t in gs for t h e Te n _ M ost _ Ex pe n sive_ Pr od u ct s W eb m e t h od j u st be fore sa vin g t h e m . You can im pr ove your debugging process by disabling var ious caching opt ions. 9. Click t he Advanced t ab in t he New Vir t ual Direct or y Propert ies dialog box. Consider select ing all t hree opt ions for disabling differ ent t ypes of caching. These select ions im prove t he operat ion of your Web ser vice, but t he caching can be dist ract ing in som e debugging and code updat ing oper at ions. 10. Click OK t o sav e t he set t ings you’ve chosen and close t he New Virt ual Direct or y Pr opert ies dialog box. Now y ou’re ready t o t est t he Web serv ice. Aft er y ou finish debugging and refining your Web serv ice, r est ore t he caching feat ur es because t hey speed up t he oper at ion of a Web serv ice in nor m al operat ion.
N ot e The book’s sam ple files include t he Chapt er 13 v irt ual dir ect ory folder for reference’s sake. I n or der t o cr eat e t he vir t ual direct ory so t hat y ou can m anage it and use it , you’ll need t o follow t he inst r uct ions for it s cr eat ion in t his sect ion. When a subsequent sect ion edit s t he SoapFor13 Web service host ed by t he Chapt er13 v ir t ual direct ory, you’ll need t o follow t he st eps for t hat as w ell. Bu ildin g a Clie n t Applica t ion t o Show a n XM L Fr a gm e n t Web services creat ed wit h t he SQL Server 2000 Web Serv ices Toolkit don’t have a built - in t est int er face. I n addit ion, you connect t hem t o a client applicat ion slight ly differ ent ly t han Web serv ices, which y ou build direct ly wit h Visual St udio .NET. Nev er t heless, t he broad out line of t he t est ing pr ocess wit h a client applicat ion is sim ilar. I n bot h cases, a .wsdl file form ally defines t he Web ser vice and specifies any input and out put s associat ed wit h individual Web m et hods. I n addit ion, y ou m ust creat e a Web reference in t he client applicat ion t hat point s at t he Web service. Cr eat e a new form nam ed For m 3 in t he XMLWebServiceClient s proj ect . Add t wo label cont r ols. Size t he form and cont r ols about as t hey appear in Figure 13- 15 lat er in t his sect ion. ( The form is also available in t he XMLWebServ iceClient s pr oj ect am ong t he book ’s sam ple files.) The arr angem ent and sizing of t he form and it s cont rols are int ended t o accom m odat e t he display of t he ent ire XML fr agm ent ret urned by t he Ten_Most _Expensiv e_Product s Web m et hod. Make For m 3 t he st art up obj ect for t he XMLWebSer viceClient s pr oj ect so t hat t he for m opens when you st ar t t he proj ect . I n t he m odule behind For m 3, add a Web reference t o t he SoapFor 13 Web serv ice by choosing Add Web Reference from t he Proj ect m enu. I n t he address box of t he Add Web Reference dialog box , t ype t he follow ing URL wit h it s t railing param et er: ht t p: / / localhost / Chapt er 13/ SoapFor13?wsdl Then press Ent er. This populat es t he left pane of t he Add Web Refer ence dialog box wit h a represent at ion of t he .wsdl file for t he SoapFor 13 Web serv ice. The right pane includes a single link wit h t he t ext View Cont r act . Click t he Add Refer ence but t on t o cr eat e a Web reference for use wit h a pr oxy variable. I f you hav e been cr eat ing t he sam ples t hr oughout t he chapt er, t he nam e for t his Web reference in t he Web References folder of Solut ion Ex plorer is localhost 3. No m at t er what it s nam e, t he r eference should include an it em nam ed SoapFor13.wsdl. This .wsdl file cont ains t he form al descript ion for t he Web service. Any proxy var iable based on t his Web reference will enable you t o r un t he
Ten_Most _Expensive_Product s Web m et hod and display t he result set r et urned as an XML fr agm ent . The r esult set from t he st ored procedure is available as an XML docum ent fr agm ent because t he exam ple select ed t his out put for m at in Figure 13- 14. The next list ing shows t he code behind t he for m in Figur e 13- 15. As you can see, it consist s of a single for m Load event pr ocedure. When t he form opens, t he pr ocedure connect s t o t he SoapFor13 Web service and invokes t he Ten_Most _Ex pensive_Pr oduct s Web m et hod. I t collect s t he XML fr agm ent ret urned by t he m et hod in an arr ay of Response obj ect s. The Response obj ect is t he m ost basic kind of obj ect in Visual St udio .NET; t his t ype of obj ect can accom m odat e any ot her k ind of obj ect or t y pe. Since t he Web serv ice can present eit her an XML docum ent or a SqlMessage obj ect , t he applicat ion needs Response obj ect s t o accom m odat e eit her out com e. The SqlMessage obj ect can ret urn SQL Ser ver er ror m essages and warnings t o an applicat ion. Using an arr ay of obj ect s accom m odat es t he possibilit y of m ult iple result set s from a single st or ed procedur e or t em plat e file. Alt hough t his sam ple has a single result set , t he sam ple’s design illust r at es t he For loop synt ax for it erat ing t hr ough t he m em bers of a Response obj ect ar ray. A Select …Case st at em ent sends t he Response obj ect t o t he appropriat e code for processing. Because t his is a very sim ple applicat ion, t he code j ust processes an obj ect cont aining an XML fragm ent . The processing consist s of a pair of st at em ent s t hat copy t he XML fragm ent in t he Response obj ect t o t he Tex t propert y of t he second label on Form 3. Whenever you choose t o out put t he result set or set s from a Web m et hod as XML obj ect s, you’ll hav e t o process t he out put in t his st y le— t hat is, w it h a Select …Case st at em ent nest ed wit hin a For loop t hat it erat es t hr ough t he obj ect s r et urned from t he Web m et hod. Private Sub Form3_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ’Declare Web service xws1 as type named soap in ’localhost4 Web reference. Dim xws1 As New XMLWebServiceClients.localhost3.SoapF or13() ’Declare object for return from Web service method. Dim response As New Object() Dim result As System.Xml.XmlElement ’Declare integer for iterating through multiple ’result sets that the Web service method can return. Dim int1 As Integer ’Save return from Web service method as an object. response = xws1.Ten_Most_Expensive_Products
’Iterate through result sets. For int1 = 0 To UBound(response) Select Case response(int1).GetType().ToString() Case "System.Xml.XmlElement" ’Pass int1 result set to result and displ ay ’in list box and Output window. result = response(int1) Label2.Text = result.OuterXml Case Else ’Handles end of result sets and other ’special returns. End Select Next End Sub Figure 13- 15 shows Form 3 open fr om t he XMLWebServ iceClient proj ect . The for m cont ains t wo labels. The t op label has a fix ed Tex t proper t y assignm ent . I t alway s shows “XML fr agm ent : ”. The cont ent s of t he bot t om t ex t box can change if t he t en m ost expensive product s change because of a pr ice r ev ision, t he addit ion of new pr oduct s, or t he dropping of exist ing product s. The pr oduct nam e and unit price values are delim it ed by opening and closing t ags. Alt hough t his form at m ay be convenient for com put er s t o process and is r eadable by hum ans, it is verbose. That ’s because every value has a pair of t ags, and t her e ar e addit ional t ags t o m ar k t he beginning and ending of each r ow ( < r ow> and < / r ow> ) as well as t he beginning and ending of t he XML fr agm ent ( < SQLXML> and < / SQLXML > ) . Figu r e 1 3 - 1 5 . Form 3 from t h e X M LW eb Se rvice Clie n t s p roj e ct sh ow in g t h e ou t pu t from t h e Ten _ M ost _ Exp e n sive _ Produ ct s W eb m e t h od in t h e Soap For 1 3 W e b ser vice .
Popu la t ing a List Box Cont r ol w it h a n X M L Se r vice Figure 13- 15 is int er est ing, but it ’s unlik ely t hat you’ll want t o show m any client s of y our Web services an XML fragm ent . I t ’s m ore lik ely t hat t hey will want t o view t he t ag values in a cont rol t han a ver bose XML fragm ent including t ags. I n t he case of t he display in Figure 13- 15, t hey m ight prefer t o see a List Box cont r ol wit h 10 it em s, showing pr oduct nam e and price in descending order. The sam ple in t his sect ion ex t ends t he pr eceding one t o achieve t his goal. You can im plem ent t his sam ple by creat ing a new form , For m 4, in t he XMLWebServiceClient s proj ect . Add a list box cont rol and t he code list ing in t his sect ion. Make For m 4 t he st art up obj ect for t he proj ect . I f y ou have t he Web r eference defined as in t he preceding sam ple, pressing F5 will cause t he for m t o open and show t he t en m ost expensiv e pr oduct s in a list box . ( See Figure 13- 16.) By adding cust om code, such as we will show lat er in t his chapt er , y ou can enable int eract iv it y wit h dat a ret ur ned by a Web ser vice from wit hin a Windows form cont r ol. Once y ou under st and t hat your applicat ions can capt ure t he result set fr om a st or ed procedure as a Response obj ect cont aining an XML fragm ent , it ’s easy t o craft a solut ion like t he one in Figure 13- 16. All y our applicat ion has t o do is par se t he XML fr agm ent t o ext ract t he t ag values, com bine t he product nam e and price in a form suit able for t he List Box cont rol, and t hen add t he com bined it em t o t he cont r ol.
Figu re 1 3 - 1 6 . A List Box con t r ol on For m 4 sh ow in g t h e p rocesse d ou t pu t from t h e Ten _ M ost _ Exp e n sive _ Produ ct s W e b m e t h od in t h e Soa p For 1 3 W e b se r vice . The following list ing illust r at es how y ou can achieve t his. The solut ion relies on t w o pr ocedures. The fir st is an ex t ension of t he form Load event procedure fr om t he preceding sam ple. The second procedure is new t o t his chapt er ( but you m ay recall it fr om Chapt er 12) . The new code for t his procedure as well as t he new procedur e in t his chapt er appear in bold t y pe. The pr im ary feat ure of t he ext ension is t he inser t ion of a Do…While loop wit hin t he For loop t hat ext ract s t he XML fr agm ent as a r esponse obj ect . The Do…While loop uses t wo st r ing var iables, st r1 and st r2, t o m anipulat e t he XML r et urned fr om t he response obj ect r et urned by t he Ten_Most _Expensive_Product s Web m et hod. For exam ple, st r1 st or es t he cur rent work ing v er sion of t he ret ur n value fr om t he Web m et hod. I n successiv e passes t hr ough t he Do…While loop, t he procedure ext ract s t he fir st values for pr oduct nam e and price from t he XML fragm ent . The for m Load event procedur e uses t he My TagValue funct ion procedur e t o ext ract t he pr oduct nam e and price values. The for m Load event procedure t hen com bines t he t wo values as a new com m a- delim it ed st ring in st r 2, which t he procedure adds as an it em t o List Box1. Befor e it erat ing t hr ough t he loop again, t he code uses t he Right funct ion t o r em ove fr om st r1 t he XML associat ed wit h t he added it em . When t here are no rem aining t ags in st r 1 wit h pr ice inform at ion, t he Do…While loop releases cont rol t o t he out er For loop, which leads, in t urn, t o t he opening of For m 4 wit h t he populat ed list box . As indicat ed earlier , t he My TagValue funct ion procedur e init ially appear ed in Chapt er 12. This procedure uses fam iliar st r ing m anipulat ion t echniques t o ext ract t ag v alues from an XML fr agm ent . The calling pr ocedure sim ply passes t he t ag nam e and
t he XML fragm ent . The funct ion ret ur ns t he fir st t ag v alue in t he st ring wit h st ar t ing and ending t ags w it hin t he fr agm ent m at ching t he t ag nam e passed t o it . The r euse of such a sim ple procedure fr om Chapt er 12 wit hout any m odificat ion illust rat es t he power of t hinking of XML fragm ent s as st r ings. Private Sub Form3_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ’Declare Web service xws1 as type named soap in ’localhost4 Web reference. Dim xws1 As New XMLWebServiceClients.localhost3.SoapFor13 () ’Declare object for return from Web service method. Dim response As New Object() Dim result As System.Xml.XmlElement ’Declare integer for iterating through multiple ’result sets that the Web service method can return. Dim int1 As Integer ’Save return from Web service method as an object. response = xws1.Ten_Most_Expensive_Products ’Declare variables for processing XML fragment. Dim str1 As String Dim str2 As String ’Iterate through result sets. For int1 = 0 To UBound(response) Select Case response(int1).GetType().ToString() Case "System.Xml.XmlElement" ’Pass int1 result set to result and display ’in list box and Output window. result = response(int1) str1 = result.OuterXml ’Iterate through column values in XML string ’within Web service method output. Do While InStr(str1, "") > 0 str2 = MyTagValue("TenMostExpensiveProduc ts", str1) str2 = str2 & ", " & MyTagValue("UnitPric e", str1) ListBox1.Items.Add(str2) str1 = Microsoft.VisualBasic.Right(str1, _ Len(str1) - (InStr(str1, "")) - _
Len("") + 1) Loop Case Else ’Handles end of result sets and other ’special returns. End Select Next End Sub Function MyTagValue(ByVal TagName As String, _ ByVal strXML As String) ’Declare and compute constants for this tag. Dim str1 = "" Dim str2 = "" Dim int1, int2 As Integer int1 = InStr(strXML, str1) + Len(str1) int2 = InStr(strXML, str2) ’Compute tag value and return it. ’strXML is string with XML to parse. ’int1 is start position. ’int2 - int1 calculates number of characters. Dim TagValue As String = Mid(strXML, _ int1, int2 - int1) Return TagValue End Function Mor e on Popu la t in g Con t r ols w it h W eb Se r vices This sect ion drills deeper int o building Web serv ices wit h t he SQL Server 2000 Web Services Toolkit and client applicat ions w it h Visual St udio .NET. I n part icular , t he em phasis is on populat ing cont rols on Windows Form s. Client applicat ions creat ed wit h Windows For m s can int eroperat e freely wit h Web serv ices over t he Web j ust as if t he client w ere available locally. I n addit ion t o show ing how t o populat e cont r ols, t his sect ion also illust r at es how t o prov ide int er act ivit y wit h Web serv ices t hrough Window s For m s. These client feat ures w ork equally well whet her you cr eat e y our Web serv ice applicat ion wit h t he Web Serv ices Toolkit or Visual St udio .NET. Addin g Cust om D a t a ba se Obj e ct s a s W e b M e t h ods
The sam ples in t he pr eceding sect ion cr eat ed Web ser vices for an exist ing st ored procedure in t he Nort hw ind dat abase. Som et im es our applicat ions benefit from , or ev en r equire, t he creat ion of cust om dat abase obj ect s. When t his is t he case for a Web services solut ion, you need t o add t he cust om dat abase obj ect s t o t he dat abase. Then, in a separ at e st ep, you m ust ex pose y our new obj ect s t hrough t he Web service. Typically, t his will require edit ing a previously exist ing Web serv ice. The st eps descr ibed in t his sect ion illust r at e how t o per form t hese act ions. T- SQL for Cre a t in g Sa m ple D a t ab ase Ob j e ct s The next t wo code sam ples for client applicat ions w ill work wit h result set s r et urned by t wo cust om dat abase obj ect s for t he Nor t hwind dat abase. One of t hese obj ect s is a st ored procedur e, and t he ot her is a user - defined funct ion. The st ored procedure ret ur ns all t he Cat egoryI D and Cat egory Nam e colum n v alues from t he Cat egories t able. The user- defined funct ion ret ur ns Product Nam e colum n v alues from t he Product s t able. A WHERE clause in t he user- defined funct ion causes t he funct ion t o ret urn j ust pr oduct nam es t hat ar e from a specified cat egory as designat ed by a Cat egoryI D param et er. Ther efor e, one of t he benefit s of t his dat abase obj ect is t hat it illust rat es t he synt ax and procedur es for using param et er s wit h Web serv ices. The following T- SQL list ing is t o be r un from Query Analy zer. The list ing is available am ong t he book ’s sam ple files as Scr ipt sFor13.sql. I f you want , you can adapt t he sam ples for running direct ly from Visual St udio .NET. ( See Chapt er 11 for sam ples dem onst rat ing t his approach.) Howev er , it is m uch easier t o r un t he scr ipt s fr om Quer y Analyzer if you’r e fam iliar w it h it . ( See t he last sect ion in Chapt er 1 if y ou don’t already have t his background.) Use a login for Query Analyzer t hat belongs t o t he sy sadm in gr oup. The scr ipt s for creat ing t he st ored procedur e and t he user- defined funct ion follow parallel pat hs. Fir st t hey drop any pr ior ver sion of t he dat abase obj ect . Nex t t hey creat e a new version of t he obj ect . Finally t hey explicit ly gr ant t he v bdot net 1 user perm ission t o use t he obj ect . Recall t hat vbdot net 1 is t he user for t he Chapt er13 vir t ual direct ory. Wit hout grant ing t he user perm ission for t he obj ect , you won’t be able t o ex pose t he obj ect s as Web m et hods for t he SoapFor13 Web ser vice originat ing fr om t he Chapt er13 vir t ual dir ect ory . /* Run from member of sysadmin fixed server role. Ignore errors if user does not already exist. */ USE Northwind
--Drop udpListOfCategoryNames if it exists. IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ’udpListOfCategoryNames’) DROP PROCEDURE udpListOfCategoryNames GO --Then, create udpListOfCategoryNames, and give -- vbdotnet1 permission to execute it. CREATE PROCEDURE udpListOfCategoryNames AS SELECT CategoryID, CategoryName FROM Categories GO GRANT EXEC ON udpListOfCategoryNames TO vbdotnet1 GO --Drop udfProductsInACategory if it exists. IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ’udfProductsInACategory’) DROP FUNCTION udfProductsInACategory GO --Then, create it, give vbdotnet1 --permission to select from it. CREATE FUNCTION udfProductsInACategory(@MyCategoryID int) RETURNS TABLE AS RETURN( SELECT ProductName FROM Products WHERE CategoryID = @MyCategoryID ) GO GRANT SELECT ON udfProductsInACategory TO vbdotnet1 GO Upda t in g a W eb Ser vice w it h t h e W e b Se rvice s Toolkit Aft er adding cust om dat abase obj ect s t o a dat abase, you can expose t he obj ect s as Web m et hods t hrough a vir t ual direct ory point ing t o t he dat abase. The v irt ual direct or y m ust have a soap vir t ual nam e, such as SoapFor 13, which is t he vir t ual soap nam e for t he Chapt er 13 v irt ual dir ect or y. I f you have an ex ist ing soap nam e,