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

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

0
51
lượt xem
11
download

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

Mô tả tài liệu
  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 - p10', 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ủ đề:
Lưu

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

  1. The follow ing shows t he code for t he RunSQLParam et er Query Wit hPassedParam s procedur e. The lines t hat change from t he RunSQLParam et erQuer y pr ocedur e in t he preceding sect ion appear in bold. Not ice j ust four lines change. These are m ost ly for receiving and using t he passed st ring v ariables t hat specify t he query synt ax and t he param et er value. The Root Tag propert y assignm ent changes t o m ake it appropr iat e for any SQL query st r ing. Aside fr om t hese m inor changes, t here is not hing m or e t o updat ing t he ear lier pr ocedur e so t hat it can accom m odat e any SQL query st r ing. Sub RunSQLParameterQueryWithPassedParams( _ ByVal strSQL As String, _ ByVal strPrm1Value As String) ’Specify connection string for SqlXmlCommand. Dim cnn1String As String = _ "Provider=SQLOLEDB;Server=(local);" & _ "database=Northwind;" & _ "Integrated Security=SSPI" ’Specify connection for cmd1 SqlXmlCommand object Dim cmd1 As SqlXmlCommand = _ New Microsoft.Data.SqlXml.SqlXmlCommand(cnn1String) ’Designate data source for cmd1 with a parameter. cmd1.RootTag = "MyRoot" cmd1.CommandType = SqlXmlCommandType.Sql cmd1.CommandText = strSQL ’Create a parameter for cmd1 and assign it a value. Dim prm1 As SqlXmlParameter prm1 = (cmd1.CreateParameter()) prm1.Value = strPrm1Value ’Declare and instantiate a stream in memory and ’populate it with the XML result set from cmd1. Dim stm1 As New System.IO.MemoryStream() stm1 = cmd1.ExecuteStream() ’Copy result set in stream to a stream reader ’to display stream contents in a message box. Dim srd1 As New System.IO.StreamReader(stm1) MsgBox(srd1.ReadToEnd) srd1.Close() End Sub Th e I n t er pla y Bet w e en XM L a nd D a t a Se t s XML docum ent s and ADO.NET dat a set s int eract wit h one anot her in m ult iple way s. Underst anding t hese int eract ions and k now ing how t o put t hem t o use can help you quer y and m anipulat e dat a bot h locally on a client ’s w or kst at ion and on a dat abase ser ver. This sect ion pr ov ides a select ion of sam ples t o show how t o use XML docum ent s wit h dat a set s for t hese purposes. As w it h m any t opics addressed by t his book, t he pr esent at ion isn’t m eant t o prov ide ex haust ive coverage of ev ery possible feat ur e on a t opic. I nst ead, t he sect ion aim s t o prov ide a firm foundat ion t hat w ill equip you t o go on and learn m ore in what ev er dir ect ions y our needs dict at e. Cr e a t in g H ie r a r ch ica l X M L D ocum e n t s Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. One of t he really valuable aspect s of t he Dat aSet obj ect in ADO.NET is t hat it is XML- based. What t his m eans is t hat you can m anipulat e t he elem ent s wit hin a dat a set and indir ect ly m odify XML st r uct ures. This feat ure is part icular ly beneficial w hen w ork ing wit h m ult it able row sources t hat hav e parent - child relat ionships because it reliev es dev elopers fr om r epresent ing t hese com plex relat ionships in XSD schem as. Alt hough ADO.NET and XML are relat iv ely new t o m any Visual Basic developers, t he obj ect m odel for dat a set s in ADO.NET m akes it relat iv ely m ore fam iliar t o t hose w it h any background in m anipulat ing obj ect s. See Chapt er 10 for a general rev iew of ADO.NET obj ect s. Figure 10- 1 prov ides an ov erv iew of t he Dat aSet obj ect m odel, and num erous code sam ples t hr oughout Chapt er 10 dem onst rat e ADO.NET pr ogram m ing t opics, including t he Dat aSet obj ect and it s hierarchically dependent obj ect s. A Dat aSet obj ect and it s associat ed XML docum ent are lik e t w o sides of t he sam e coin. Wit h t he Wr it eXm l m et hod for a Dat aSet obj ect , you can persist bot h t he cont ent s of an XML docum ent and t he under ly ing schem a for t he docum ent . I n addit ion, when a dat a set has changes not com m it t ed t o a r em ot e dat abase, y ou can generat e v ia t he Wr it eXm l m et hod t he DiffGram represent ing t he dat a set wit h it s uncom m it t ed changes. Recall t hat a DiffGram cont ains cur rent v alues as well as prev ious values. The DiffGram is readily available because ADO.NET conv eys changes from a client t o a SQL Ser ver inst ance v ia DiffGram s. The sam ple in t his sect ion dem onst rat es how t o creat e a t hr ee- t ier ed dat a set based on t hr ee t ables fr om t he Nort hw ind dat abase. These t ables are t he Cust om ers, Orders, and Order Det ails t ables. I ndiv idual cust om ers ar e parent s of indiv idual orders, and orders, in t ur n, are par ent s of order det ails, or line it em s wit hin an order. This pair of nest ed r elat ions is t he k ind of st r uct ur e t hat XML docum ent s r epr esent especially w ell because t he docum ent shows t he act ual nest ing inst ead of a single flat rowset . The sam ple r elies on t w o pr ocedur es. The first procedur e, Sav eThreeTierDasAsXm lDocum ent , calls a second procedure t hat generat es a dat a set and t hen persist s t he dat a set as an XML docum ent . By using t he Writ eXm l m et hod, t he SaveThreeTierDasAsXm lDocum ent procedure av oids a reliance on SQLXML Managed Classes. This m eans t he t echniques dem onst rat ed in t his chapt er ar e relat ively r obust in t hat t hey can work w it h any dat a source t o which ADO.NET can connect . I n addit ion, t he pr ocedur es dem onst rat ed for t he Dat aSet obj ect don’t r equir e t he inst allat ion of eit her Web Release 2 or Web Release 3, as is necessary for t he use of Managed Classes. The second procedur e, Creat eThreeTierDat aSet , is a funct ion procedur e t hat ret ur ns a Dat aSet obj ect t o t he procedure t hat calls it . I t is t his r et ur ned dat a set t hat t he first pr ocedur e persist s as an XML docum ent in a file. The Sav eThreeTierDasAsXm lDocum ent pr ocedure st art s by inst ant iat ing a Dat aSet obj ect and populat ing it wit h t he dat a set ret urned by t he Cr eat e- ThreeTierDat aSet funct ion procedure. Aft er populat ing t he dat a set , t he procedur e prepar es t o persist it as a file w it h Unicode charact ers. These act ions t ake several st eps. The procedur e st art s t he process by assigning t he nam e of t he XML docum ent t o a st ring var iable ( st r1) . Next t he pr ocedur e inst ant iat es a FileSt ream obj ect ( fst 1) t o hold t he file cont aining t he XML docum ent . Then t he procedur e inst ant iat es an Xm lText Wr it er obj ect ( t xw1) t o copy t he XML wit hin t he dat a set t o t he FileSt r eam obj ect . The Wr it eXm l m et hod uses t xw1 as one of it s t wo argum ent s for copy ing t he XML fr om t he dat a set t o t he file. The ot her argum ent , which is Xm lWr it eMode.Writ eSchem a in t his case, det erm ines how t he Writ eXm l m et hod conv eys cont ent fr om t he dat a set t o t he file. The Xm lWr it eMode.Wr it eSchem a argum ent dir ect s t he Writ eXm l m et hod t o st art by copying t he schem a for t he docum ent and t hen follow t he schem a w it h t he cont ent s of t he XML docum ent . Aft er w r it ing t he docum ent , t he procedure fr ees resources and ret urns cont rol t o t he pr ocedur e by closing bot h t he Xm lText Wr it er and FileSt ream obj ect s. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. The Creat eThreeTierDat aSet pr ocedur e st art s by inst ant iat ing a connect ion obj ect and opening it so t hat t he connect ion point s t o t he Nort hw ind dat abase. The procedur e next inst ant iat es a Dat aSet obj ect ( das1) and uses t he connect ion obj ect t o connect a SqlDat aAdapt er obj ect ( dap1) wit h t he Cust om ers t able in t he Nort hwind dat abase. Then t he procedure copies t he Cust om ers t able rows int o a dat a t able nam ed Cust om ers w it hin das1 by inv ok ing t he Fill m et hod for t he dap1 obj ect . Aft er adding t he Cust om ers t able from t he Nort hw ind dat abase t o t he das1 dat a set , t he pr ocedur e point s dap1 t o t he Orders t able in t he Nort hw ind dat abase. Then it adds t he Orders t able t o das1. I t repeat s t he pr ocess a t hird and final t im e t o creat e an OrderDet ails dat a t able in das1 wit h t he colum n values from t he Order Det ails t able in t he Nort hw ind dat abase. At t he end of t hese t hr ee invocat ions of t he Fill m et hod, t he das1 dat a set cont ains t hr ee unrelat ed t ables. How ev er, w e need Dat aRelat ion obj ect s t o specify t he hierarchical relat ionship bet w een t ables. I n fact , das1 needs t wo Dat aRelat ion obj ect s. One Dat aRelat ion obj ect expr esses t he r elat ionship bet ween t he Cust om ers and Orders dat a t ables. A second Dat aRelat ion obj ect represent s t he r elat ionship bet ween t he Orders and Order Det ails dat a t ables. The procedur e builds t he first Dat aRelat ion obj ect by inv ok ing t he Add m et hod for t he Relat ions collect ion of t he das1 dat a set . The first argum ent , w hich is a st r ing w it h t he value “ Cust Orders”, nam es t he Dat aRelat ion obj ect . The next t w o argum ent s ident ify t he colum ns used t o j oin t he t w o dat a t ables. By set t ing t he Nest ed propert y for t he Dat aRelat ion obj ect t o Tr ue, you cause t he XML docum ent t o show orders nest ed wit hin cust om ers. The default value for t he Nest ed propert y is False. I n t his case, t he Wr it eXm l m et hod shows t wo set s of colum n values wit hout any nest ing of colum n v alues fr om one dat a t able w it hin t hose of anot her dat a t able. By invok ing t he Add m et hod a second t im e for t he Relat ions collect ion in t he das1 dat a set , t he pr ocedur e cr eat es a second dat a r elat ionship expr essing t he parent - child st r uct ure bet w een t he Orders and Order Det ails dat a t ables. Finally t he Cr eat eThr eeTierDat aSet pr ocedur e concludes by inv ok ing t he Ret urn st at em ent t o pass t he das1 dat a set back t o t he procedure t hat called it . Sub SaveThreeTierDasAsXmlDocument() ’Declare and instantiate the das1 data set and ’populate it with the return data set from ’the CreateThreeTierDataSet function procedure. Dim das1 As New DataSet() das1 = CreateThreeTierDataSet() ’Declare string for filename to hold file stream ’based on XmlTextWriter with contents of das1 data set. Dim str1 As String = _ "c:\SQL Server Development with VBDotNet\" & _ "Chapter12\myCustomersSchema.xml" Dim fst1 As New System.IO.FileStream _ (str1, System.IO.FileMode.Create) Dim txw1 As New System.Xml.XmlTextWriter _ (fst1, System.Text.Encoding.Unicode) ’Write from das1 the XML along with schema. das1.WriteXml(txw1, XmlWriteMode.WriteSchema) ’Close TextWriter and FileStream. txw1.Close() fst1.Close() End Sub Function CreateThreeTierDataSet() ’Open connection to northwind database. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. Dim cnn1 As SqlConnection = _ New SqlConnection( _ "Data Source=localhost;" & _ "Initial Catalog=northwind;" & _ "Integrated Security=SSPI") cnn1.Open() ’Declare and instantiate a data set (das1) Dim das1 As DataSet = New DataSet("CustomerOrders") ’Declare and instantiate a data adapter (dap1) to fill ’the Customers data table in das1. Dim dap1 As SqlDataAdapter = _ New SqlDataAdapter( _ "SELECT CustomerID, CompanyName, ContactName, Phone " & _ "FROM Customers", cnn1) dap1.Fill(das1, "Customers") ’Re-use dap1 to fill the Orders data table in das1. dap1.SelectCommand.CommandText = _ "SELECT OrderID, OrderDate, CustomerID FROM Orders" dap1.Fill(das1, "Orders") ’Re-use dap1 to fill the OrderDetails data table in das1. dap1.SelectCommand.CommandText = _ "SELECT * FROM [Order Details]" dap1.Fill(das1, "OrderDetails") ’Close the connection. cnn1.Close() ’Specify a relationship between Customers and Orders ’data tables with orders elements nesting within ’customers elements. das1.Relations.Add("CustOrders", _ das1.Tables("Customers").Columns("CustomerID"), _ das1.Tables("Orders").Columns("CustomerID")). _ Nested = True ’Specify a relationship between Orders and ’OrderDetails data tables with OrderDetails elements ’nesting within orders elements. das1.Relations.Add("OrderDetail", _ das1.Tables("Orders").Columns("OrderID"), _ das1.Tables("OrderDetails").Columns("OrderID"), _ False).Nested = True Return das1 End Function When t he SaveThr eeTierDasAsXm lDocum ent pr ocedur e invokes t he Wr ileXxm l m et hod w it h it s second argum ent equal t o Xm lWr it eMode.Wr it eSchem a, t he m et hod act ually wr it es t wo docum ent s in one. The XSD schem a for t he XML argum ent appears befor e t he act ual dat a. The .NET docum ent at ion refers t o t his kind of schem a as an inline schem a because it appears in line w it h t he XML dat a t hat follows it . The schem a for t he XML docum ent cor responding t o das1 is reasonably com plex because it specifies colum ns from t hree t ables, t wo dat a relat ionship specificat ions, and support ing elem ent s, such as const raint s t o enable t he Dat aRelat ion obj ect s. Figur es 12- 6 and 12- 7 show port ions of t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. schem a in br owser w indows; t he schem a is t oo long t o fit in one w indow. This schem a appears at t he beginning of t he XML docum ent nam ed in t he Save- ThreeTierDasAsXm lDocum ent procedure. The XML docum ent ’s filenam e is m y Cust om ersSchem a.x m l in t he c: \ SQL Ser ver Dev elopm ent w it h VBDot Net \ Chapt er12 folder. I n t est ing t he applicat ion on your syst em , you m ay care t o change t he dest inat ion folder for t he XML docum ent t o a folder t hat y ou alr eady have on your w orkst at ion. Figu re 1 2 - 6 . Th e first p a rt of t h e in lin e sche m a for t h e X M L d ocum e n t in t h e m yCu st om e rsSch e m a. xm l file . Figu r e 1 2 - 7 . Th e se con d pa rt of t h e inlin e sch em a for t h e X M L docu m e n t in t h e m yCu st om er sSch em a .x m l file . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. As y ou can see fr om t he schem a’s lengt h and com plex it y, it is of value t o be able t o wr it e t he schem a aut om at ically. Cr eat ing a dat a set in code should be fair ly st raight forward by t his point in t he book. I n any event , if y ou ar e building solut ions wit h ADO.NET, it is highly likely t hat y ou w ill gain a com fort level wit h building dat a set s pr ogr am m at ically . Therefore, using a pr ogram m at ically cr eat ed dat a set as t he basis for a schem a m ay be a useful pr ocess if you ar en’t handy at specify ing XSD schem as fr om scrat ch. I n fact , writ ing out t he schem as and corr elat ing t hem w it h t he design of y our dat a set s m ay be a way t o hav e Visual Basic .NET t each you XSD sy nt ax so t hat you can ev ent ually wr it e y our ow n com plex schem as from scrat ch. Figure 12- 8 shows an excerpt from t he beginning of t he XML dat a in m y Cust om ersSchem a.xm l. You can see all of t he first order ( OrderI D 10643) and t he beginning of t he second order ( OrderI D 10692) for t he cust om er w it h a Cust om erI D value of ALFKI . Not ice how orders nest wit hin cust om ers. Also, t he line it em s, or order det ails, for an order nest w it hin an order . Figu r e 1 2 - 8 . An e x cer pt fr om t h e b egin nin g of t h e X M L d at a in t he m yCu st om e r sSch em a .x m l file . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. Qu e r yin g D e sce nd a n t s in a D a t a Se t w it h X Pa t h The hierarchical design of t he das1 dat a set in t he pr eceding sam ple pr ov ides a source t hat is suit able for dem onst rat ing how t o query descendant s w it h XPat h query sy nt ax . Recall t hat t he dat a set has order det ails t hat are t he children of orders t hat in t ur n ar e t he childr en of cust om er s. I n Figur e 12- 8, t he fir st Unit Pr ice value of 45.6 is a descendant of t he fir st order wit h an Order I D value of 10643. This OrderI D is a child of t he cust om er wit h t he Cust om erI D value ALFKI . XPat h query sy nt ax perm it s you t o creat e a result set of cust om ers based on any of t heir descendant values, such as Unit Price. The sam ple in t his sect ion illust rat es how t o const r uct such an XPat h query , and t he sam ple also reveals how t o enum erat e t he nodes of t he r esult set . Alt hough XPat h quer ies r et ur n a collect ion of nodes in an Xm lNodeList obj ect , t he enum erat ion r eport s indiv idual values wit hout t he clut t er of t he XML t ags t hat delim it values in an XML docum ent . The RunXPat hQueryFor ThreeTier Xm lDocum ent procedur e, w hich im plem ent s t he sam ple for t his sect ion, st art s by inst ant iat ing a new dat a set nam ed das1 and t hen populat ing it w it h t he t hree- t ier ed dat a set creat ed by t he Creat eThreeTierDat aSet funct ion. ( See t he preceding sect ion for t he list ing w it h t his funct ion procedure. ) Because ADO.NET aut om at ically cr eat es an XML Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. docum ent behind each dat a set , y ou can query eit her t he dat a set or it s underly ing XML docum ent and obt ain ident ical r esult set s. The RunXPat hQueryFor ThreeTier Xm lDocum ent procedur e present s one approach t o pr ocessing t he XML docum ent behind a dat a set . Aft er populat ing t he dat a set , t he procedure inst ant iat es a new Xm lDat aDocum ent obj ect ( xdc1) based on t he das1 dat a set . The Xm lDat aDocum ent class is an ext ension of t he Xm lDocum ent class t hat enables .NET applicat ions t o load t he XML behind a dat a set int o an XML docum ent . Xm lDat aDocum ent obj ect s per m it t he applicat ion W3C processing t echniques for XML docum ent s, such as XPat h quer ies. The pr ocedure dem onst rat es t his capabilit y by specify ing an XPat h query t hat select s all cust om er nodes t hat cont ain any descendant s wit h a Unit Price value of m ore t han 100. The XPat h expression cr eat es an Xm lNodeList obj ect ( x nl1) based on t he st ruct ur e of t he associat ed dat a set for t he Xm lDat aDocum ent obj ect t hat it quer ies. The associat ion bet w een t he Xm lDat aDocum ent obj ect and t he das1 dat a set m akes it possible t o select indiv idual values from each node in t he Xm l- NodeList obj ect as colum n values in a Dat aRow obj ect from t he Dat aSet obj ect m odel. The procedur e prepares t o im plem ent t his approach by declar ing a Dat aRow obj ect ( m y Row) . Befor e st art ing a loop, t he procedure r et urns a count of t he num ber of nodes wit hin t he x nl1 node list . The loop uses a For Each st at em ent t o successively pass t hrough each node w it hin xnl1. The Get RowFr om Elem ent m et hod t ransfers individual values from t he curr ent node t o t he m y Row Dat aRow obj ect . The m et hod t ransfers values st ripped of any XML t ags. Once t he values of a node ar e available as colum n values wit hin t he m y Row obj ect , t he procedure const r uct s a st ring for t he first four colum n values. The schem a in Figure 12- 6 confirm s t hat t hese colum ns cor respond t o Cust om erI D, Com panyNam e, Cont act Nam e, and Phone. The last st at em ent w it hin t he loop print s t he four colum n v alues t o t he Out put w indow. Sub RunXPathQueryForThreeTierXmlDocument() ’Declare and instantiate the das1 data set and ’populate it with the return data set from ’the CreateThreeTierDataSet function procedure. Dim das1 As New DataSet() das1 = CreateThreeTierDataSet() ’Declare and instantiate an XmlDataDocument based ’on the contents of das1. Dim xdc1 As System.Xml.XmlDataDocument = _ New XmlDataDocument(das1) ’Generate a result set with all Customers ordering ’products with a UnitPrice greater than 100. Dim xnl1 As XmlNodeList = _ xdc1.DocumentElement.SelectNodes( _ "descendant::Customers" & _ "[Orders/OrderDetails/UnitPrice>100]") ’Declare objects for a loop through result set. Dim myRow As DataRow Dim xnd1 As XmlNode Dim str1 As String ’Loop through result set and print values ’in Output window. Debug.WriteLine("There are " & _ xnl1.Count.ToString & " in the result set.") For Each xnd1 In xnl1 myRow = xdc1.GetRowFromElement(CType(xnd1, XmlElement)) str1 = myRow(0) & ", " & myRow(1) & _ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. ", " & myRow(2) & ", " & myRow(3) Debug.WriteLine(str1) Next End Sub Figur e 12- 9 present s an excerpt from t he Out put window show ing v alues generat ed by t he RunXPat hQueryFor Thr eeTier Xm lDocum ent procedure. The first line in t he excerpt r epor t s t he num ber of cust om ers purchasing any it em wit h a Unit Pr ice value of m ore t han 100. Then t he w indow shows a list of t he indiv idual cust om ers m eet ing t his crit er ion. For each cust om er , t he list shows t he associat ed Cust om erI D, Com panyNam e, Cont act Nam e, and Phone values. Figu re 1 2 - 9 . An e x cer pt disp la yin g t h e in it ia l ou t p u t from t h e Ru n X Pa t h Qu e ryFor Th r ee Tie rX m lD ocu m e n t pr oced u r e. Qu e r yin g D e sce nd a n t s in a n XM L D ocu m e n t w it h X Pa t h The sam ple in t he preceding sect ion creat ed a fr esh dat a set by calling t he Creat eThreeTierDat aSet procedure t o generat e a new dat a set . For applicat ions in which t he dat a changes slow ly or at regular int ervals, y ou m ay be able t o im pr ov e per form ance by using a pr ev iously saved copy of t he XML docum ent behind a dat a set . Using a prev iously sav ed XML docum ent can reduce t he load on a dat abase serv er and im prove applicat ion responsiv eness. The Sav eThree- TierDasAsXm lDocum ent procedure, descr ibed previously , saves an XML docum ent based on t he sam e t hr ee- t ied dat a st r uct ur e generat ed by t he Creat eThreeTierDat aSet procedure. The file cont aining t he XML docum ent is m y Cust om ersSchem a.x m l, and it s pat h is c: \ SQL Ser ver Dev elopm ent wit h VBDot Net \ Chapt er12. I f y ou updat ed eit her t he docum ent ’s filenam e or it s pat h for t est ing on y our syst em , you will need t o r ev ise t hem for t he sam ple in t his sect ion as well. The sam ple for t his sect ion r elies on t w o procedur es. The first procedure, RunXPat hQueryFor Sav edThreeTier Xm lDocum ent , processes t he sav ed XML docum ent in m y Cust om ersSchem a.xm l. The second pr ocedur e, MyTagValue, ext ract s t ag values from a st ring cont aining values delim it ed by XML t ags. The st ring v alues passed t o t he MyTagValue procedure ar e t he nodes ret ur ned fr om an XPat h quer y. The RunXPat hQueryFor Sav edThr eeTier Xm lDocum ent procedure st art s by inst ant iat ing an XML docum ent , xdc1, and t hen loading t he previously sav ed m y Cust om ersSchem a.x m l. The pr ocedur e uses an Xm lText Reader t o connect w it h t he XML docum ent in m y Cust om ersSchem a.xm l, nav igat e t o t he r oot node, and load t he dat a fr om t he file int o xdc1. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. Aft er loading t he prev iously saved XML docum ent , t he sam ple ex ecut es t he sam e XPat h query as in t he pr eceding sam ple. Alt hough t he sy nt ax for t he XPat h query is ident ical in t his sam ple and t he pr eceding one, t he source for t he query is different in a couple of im port ant way s. First , t he source for t his sam ple doesn’t requir e a t r ip t o t he dat abase serv er because it wor ks wit h a locally sav ed file cont aining an XML docum ent . I f t he dat abase serv er or t he connect ion t o it is down t em porar ily, t his local resource can subst ant ially im pr ov e t he r obust ness of an applicat ion. Second, t here is no dat a set under ly ing t he XML docum ent . This m eans t he XML nodes r et ur ned by t he XPat h query ar e st r ings wit h no associat ed row st ruct ure. As a consequence, t his pr ocedur e processes elem ent s in nodes different ly t han in t he preceding sam ple. This pr ocedur e generat es ident ical out put t o t hat which appears in Figure 12- 9, but it ar r iv es at t hat out put v ia a differ ent pat h t han t he preceding sam ple. The alt er nat ive approach t o ext ract ing t ag values is necessary because t here is no underly ing r ow st r uct ure from a dat a set t o facilit at e t he ext ract ion of v alues. Each node in t he XPat h query ’s r esult set for t his sam ple is a st ring. Tags delim it t ag values w it hin each st ring. From Figur e 12- 8, you can see t hat t he < Cust om er - I D> and < / Cust om erI D> t ags bound t he ALFKI t ag value. Ther efor e, y ou can ext ract any t ag v alue by specifying it s opening and closing t ags. Wit h t he Mid funct ion, y ou can ext ract t he t ag value cont ained wit hin any t ag. The RunXPat hQueryFor Sav edThreeTier Xm lDocum ent and MyTagValue procedur es wor k t oget her t o ext ract t he first four t ag values for each successive node in t he XPat h query ’s r esult set . The RunXPat hQueryForSavedThr eeTier Xm lDocum ent procedur e passes t he t ag nam e for each of t he first four t ags in a node, and t he MyTagValue funct ion pr ocedur e ret ur ns a st r ing wit h t he cor r esponding t ag’s value. Then t he RunXPat hQueryFor Sav edThr eeTierXm lDocum ent procedur e concat enat es t he t ag values and w r it es t hem t o t he Out put w indow. Sub RunXPathQueryForSavedThreeTierXmlDocument() ’Procedure works from saved document instead of ’re-creating the document from a new data set. ’Declare and instantiate an XML document. Dim xdc1 As New System.Xml.XmlDocument() ’Declare and instantiate reader based on ’previously saved XML document; move to root ’node of document and load into xdc1. Dim xrd1 As XmlTextReader = _ New XmlTextReader _ ("c:\SQL Server Development with VBDotNet\" & _ "Chapter12\myCustomersSchema.xml") xrd1.MoveToContent() xdc1.Load(xrd1) ’Close the XmlTextReader. xrd1.Close() ’Generate a result set with all Customers ordering ’products with a UnitPrice greater than 100. Dim xnl1 As XmlNodeList = _ xdc1.DocumentElement.SelectNodes( _ "descendant::Customers" & _ "[Orders/OrderDetails/UnitPrice>100]") ’Declare objects for a loop through result set. Dim xnd1 As XmlNode Dim str1, str2 As String ’Loop through result set and print values ’in Output window. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. Debug.WriteLine("There are " & _ xnl1.Count.ToString & " in the result set.") For Each xnd1 In xnl1 ’Saver node’s inner XML. str1 = xnd1.OuterXml ’Get CustomerID tag value. str2 = MyTagValue("CustomerID", str1) ’Get CompanyName tag value. str2 = str2 & ", " & MyTagValue("CompanyName", str1) ’Get ContactName tag value. str2 = str2 & ", " & MyTagValue("ContactName", str1) ’Get Phone tag value. str2 = str2 & ", " & MyTagValue("Phone", str1) ’Write first four tag values. Debug.WriteLine(str2) 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 is number of characters. Dim TagValue As String = Mid(strXML, _ int1, int2 - int1) Return TagValue End Function Usin g D a t a Se t s t o Upda t e D a t a ba se s via D iff Gr a m s By now you should be get t ing t he idea t hat you can perform dat abase operat ions t o obt ain ident ical r esult s wit h dat a set s or t he XML docum ent s associat ed w it h t hem . This general rule applies t o dat abase updat es as well. Recall from earlier in t his chapt er t hat ADO.NET updat es a dat abase via a DiffGram , which is an XML docum ent t hat can separat ely specify current values and prior colum n v alues in a dat a t able wit hin a dat a set . When an ADO.NET applicat ion invokes t he Updat e m et hod for a dat a adapt er and specifies a dat a set , t he applicat ion sends t he DiffGram t o t he .NET Fr am ew ork r unning on a ser ver. The .NET Fram ework , in t ur n, at t em pt s t o per form t he updat e wit h t he dat abase serv er and passes back any necessary feedback t o t he client , such as an ident it y value or a m essage t hat Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. t he dat abase r ej ect s t he updat es because t he prior value changed from t he t im e t he dat a set was init ially populat ed. The sam ple in t his sect ion int eract s wit h XML in t wo different ways. First , it uses an annot at ed schem a t o specify which colum n v alues t o r et urn fr om a r em ot e dat a source. Aft er ret rieving v alues fr om a rem ot e dat a source, t he sam ple fills a dat a t able in a dat a set on t he client . Second t he sam ple updat es a colum n value in t he local dat a t able. Then t he pr ocedur e w r it es t he DiffGram t hat cont ains t he change before calling t he Updat e m et hod for a dat a adapt er t o send t he DiffGram t o a dat abase serv er . Alt hough it is possible t o wor k w it h DiffGram s direct ly, j ust lik e Updat egram s ( see Chapt er 6) , Visual Basic dev elopers m ight generally find it m or e conv enient t o m anipulat e t he ADO.NET obj ect m odel t o updat e v alues bot h locally and on a rem ot e serv er. The follow ing schem a list ing shows t he cont ent s of an Em ploy eesFirst Last Nam es.xsd file used by t he sam ple w it hin t his sect ion. The file resides in t he root folder of t he XMLSam ples solut ion. ( The lines for t he Fnam e and LNam e elem ent s w rap ont o a second line because t hey are t oo long t o fit on one line.) Aft er t he nam espace declarat ions for a W3C xsd schem a and Micr osoft m apping at t r ibut es, t he list ing declares Em p as t he nam e for t he Em ployees obj ect in a dat abase connect ion. The sql: relat ion at t r ibut e set s t he corr espondence bet ween Em p and Em ploy ees. Because t he sam ple connect s t o t he Nort hw ind dat abase, Em p is t he nam e for t he collect ion of ret riev ed values from t he Em ployees t able. The schem a designat es FNam e and LNam e as m at ching nam es w it hin t he local dat a set for t he First Nam e and Last Nam e colum n values in t he Em ploy ees t able on t he dat abase server. The sql: field at t r ibut e indicat es t he ser ver - based colum ns t o w hich t he local dat a set colum ns point . The follow ing Populat eModifyUpdat eWit hDiffGram procedure st art s by specify ing a connect ion st r ing and t hen using t he st r ing t o const r uct a SqlXm lCom m and obj ect . The cont ent s of t he st ring point t o t he Nort hw ind dat abase on t he default local SQL Server inst ance. Next t he procedure creat es a local dat a set ( das1) w it h a dat a t able nam ed Em p based on t he Em ploy eesFirst Last Nam es.xsd schem a file. This dat a set com plet es t he set up for t he sam ple’s dat a envir onm ent . Sub PopulateModifyUpdateWithDiffGram() ’Specify connection for cmd1 SqlXmlCommand object; ’connection specification must include ’provider designation (sqloledb). Dim cmd1 As New SqlXmlCommand("Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=northwind;Integrated Security=SSPI") ’Specify SQLXmlCommand to return first and last ’names based on an XPath query. cmd1.RootTag = "ROOT" cmd1.CommandText = "Emp" Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. cmd1.CommandType = SqlXmlCommandType.XPath cmd1.SchemaPath = "..\EmployeesFirstLastNames.xsd" ’Instantiate a SqlXmlAdapter object using the ’SqlXmlCommand object . Dim dap1 As SqlXmlAdapter dap1 = New SqlXmlAdapter(cmd1) ’Instantiate a new DataSet object (das1) and ’fill via dap1. Dim das1 As DataSet = New DataSet() dap1.Fill(das1) ’Edit the value in the first row’s first column ’of Emp data table. das1.Tables("Emp").Rows(0)(0) = "Nancie" ’Write the XML as a DiffGram before committing ’change to server. Dim str1 As String = _ "c:\SQL Server Development with VBDotNet\" & _ "Chapter12\myDiffGram.xml" Dim myFileStream As New System.IO.FileStream _ (str1, System.IO.FileMode.Create) Dim xtw1 As New System.Xml.XmlTextWriter _ (myFileStream, System.Text.Encoding.Unicode) das1.WriteXml(xtw1, XmlWriteMode.DiffGram) ’Perform update to server-based data source for ’the das1 data set; don’t specify a specific 'data table within the data set. dap1.Update(das1) End Sub Aft er set t ing up t he dat a env ir onm ent , t he procedur e assigns a new value, “Nancie”, t o t he first colum n in t he first row of t he Em p dat a t able. The Rows collect ion for t he Em p dat a t able exposes t he colum n values for indiv idual rows wit hin t he dat a t able. I n t he follow ing line, das1.Tables("Emp").Rows(0)(0) = "Nancie" t he first num ber in parent heses aft er Rows designat es t he r ow and t he second num ber in par ent heses point s t o a colum n w it hin a r ow. ( The Rows collect ion is zer o- based; t he first colum n and r ow ar e bot h num ber ed 0.) Befor e t ransfer r ing t he updat e t o t he Nort hwind dat abase w it h t he Updat e m et hod for a dat a adapt er , t he procedure copies t he dat a set in DiffGram form at t o a file nam ed C: \ SQL Server Dev elopm ent Wit h VBDot Net \ Chapt er12\ My Diff- Gram .xm l on t he local com put er ’s C dr iv e. Change t he nam e and dest inat ion t o fit your com put er env ironm ent . Figur es 12- 10 and 12- 11 show t he DiffGram creat ed in m yDiffGram .x m l by t he sam ple for t his sect ion. Figur e 12- 10 is a br owser w indow display ing t he t op half of t he DiffGram , and Figur e 12- 11 present s t he bot t om half of t he DiffGram in a browser w indow. As Figur e 12- 10 r ev eals, t he em ploy ee whose Em ploy eeI D value is 1 has t he FNam e t ag value Nancie. ( See t oward t he t op of t he w indow.) I n Figur e 12- 11, t he befor e sect ion of t he DiffGram ( see t oward t he bot t om of t he browser w indow ) shows t he init ial value for any changes in t he dat a set uncom m it t ed on t he rem ot e dat abase source. I n t his inst ance, you can see t hat t he init ial value for t he FNam e t ag is Nancy for t he em ploy ee whose Em ployeeI D value is 1. I m m ediat ely aft er invoking t he Updat e m et hod in t he final line of t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. Populat eModifyUpdat eWit hDiffGram procedure, t he DiffGram for das1 will change. I n part icular, t he befor e sect ion w ill drop because t he dat a set w ill cont ain only curr ent values unt il t her e is a m odificat ion of t he local Em p dat a t able. Figu r e 1 2 - 1 0 . Th e be gin n in g p ar t of t h e m yD iffGr am .x m l file ge n e ra t ed b y t h e Popu la t eM od ifyUpda t e W it h D iffGr a m p roce du re . Figu r e 1 2 - 1 1 . Th e en ding pa r t of t h e m yD iffGr a m .x m l file g en er a t e d by t h e Popu la t eM od ifyUpd at e W it h D iffGr a m pr oce du re . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. You pr obably want t o rest ore your Em ploy ees t able in t he Nort hw ind dat abase so t hat t he first nam e for Em ployeeI D 1 is Nancy inst ead of Nancie. You can do t hat by changing Nancie t o Nancy in t he Populat eModifyUpdat eWit hDiffGram procedur e and r e- r unning t he pr ocedure. N ot e As I m ent ioned, m any Visual Basic .NET developer s m ight find it m ore convenient t o enable dat a m anipulat ion t hr ough Dat aSet obj ect s t han by direct ly coding DiffGr am s or Updat egr am s. This book’s sam ple files include an addit ional sam ple pr ocedure, List AndEdit Wit hDat aset , t o fur t her illust r at e t he flexibilit y and ease of t his approach. For t he sake of brevit y , t he procedure’s list ing doesn’t appear in t he book . Usin g D iffGr a m s on t h e W e b W it hou t Vir t ua l D ir e ct or ie s One of t he best feat ur es about t he preceding sam ple is how robust it is. For exam ple, v ery near ly t he ident ical code works in an ASP.NET applicat ion. Furt herm or e, t hat ASP.NET applicat ion perm it s updat es t o t he Web w it hout t he necessit y of a v irt ual dir ect ory for a dat abase. This sim plifies adm inist rat ion of your Web solut ions. The follow ing five st eps build an ASP.NET Web Applicat ion solut ion nam ed XMLWebSam ple. These st eps adapt t he sam ple from t he preceding sect ion t o run in an ASP.NET solut ion. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. 1. St art a new ASP.NET solut ion nam ed XMLWebSam ple, and add a r efer ence t o t he Micr osoft .Dat a.SqlXm l nam espace as described ear lier in t his chapt er . 2. Select t he default WebForm 1.aspx file in Design view , and open t he m odule behind t he Web page by r ight - clicking t he page and choosing View Code. At t he t op of t he m odule for t he page, insert I m port s Micr osoft .Dat a.SqlXm l. 3. Copy t he code from t he Populat eModifyUpdat eWit hDiffGram procedure in t he preceding solut ion t o t he Page_Load ev ent for t he XMLWebSam ple solut ion. 4. Creat e in t he r oot Web folder of t he XMLWebSolut ion a schem a j ust lik e Em ploy eesFirst Last Nam es.xsd. You can use t he XML Designer for t his t ask as described ear lier in t he chapt er. ( I t ’s probably easiest t o open t he schem a in XML Source view and r eplace t he ex ist ing XML wit h t he XML from t he Em ployeesFirst Last Nam es.xsd in t his book ’s sam ple files.) Nam e t he schem a Em ployeesFirst Last Nam es.xsd. 5. Change t he set t ing for t he Schem aPat h pr opert y set t ing of t he SqlXm lCom m and obj ect in t he Page_Load event code fr om ".. \ Em ploy eesFirst Last Nam es.xsd" t o MapPat h( " Em ployeesFir st Last Nam es.xsd" ) . Aft er com plet ing t he abov e st eps, y ou can r ight - click t he WebForm 1.aspx page in t he Solut ion Explor er w indow and choose Build And Browse. This pr ocess will set t he First Nam e field for t he r ow in t he Em ploy ees t able wit h t he Em ploy eeI D value 1 t o Nancie. You can rest ore t he or iginal first nam e by changing Nancie t o Nancy in t he Page_Load ev ent procedur e and choosing Build And Browse a second t im e. For your easy r eference, t he Page_Load ev ent procedur e list ing appears her e. The t wo lines t hat changed from t he Populat eModify Updat eWit hDiffGram pr ocedur e appear in bold. The im port ant point t o grasp is t hat alt hough t he follow ing list ing is for ASP.NET, it w or ks near ly ident ically t o t he prior Windows applicat ion solut ion. The MapPat h funct ion ret urns t he full pat h t o a file t hat serv es as it s argum ent . This Web t echnique enables dev elopers t o r efer ence t he pat h t o a file wit hout explicit ly including it in t heir applicat ion. I n addit ion, t he MapPat h funct ion im pr oves your code’s port abilit y because t he funct ion dynam ically com put es t he pat h t o t he file ev en if y ou change t he folder for t he solut ion. Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ’Put user code to initialize the page here. ’Specify connection for cmd1 SqlXmlCommand object; ’connection specification must include ’provider designation (sqloledb). Dim cmd1 As New SqlXmlCommand("Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=northwind;Integrated Security=SSPI") ’Specify SQLXmlCommand to return first and last ’names based on an XPath query. cmd1.RootTag = "ROOT" cmd1.CommandText = "Emp" cmd1.CommandType = SqlXmlCommandType.XPath cmd1.SchemaPath = MapPath("EmployeesFirstLastNames.xsd") ’Instantiate a SqlXmlAdapter object using the ’SqlXmlCommand object. Dim dap1 As SqlXmlAdapter dap1 = New SqlXmlAdapter(cmd1) ’Instantiate a new DataSet object (das1) and Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. ’fill via dap1. Dim das1 As DataSet = New DataSet() dap1.Fill(das1) ’Edit the value in the first row’s first column ’of Emp data table. das1.Tables("Emp").Rows(0)(0) = "Nancie" ’Write the XML as a DiffGram before committing ’change to server. Dim str1 As String = _ "c:\SQL Server Development with VBDotNet\" & _ "Chapter12\myDiffGram.xml" Dim myFileStream As New System.IO.FileStream _ (str1, System.IO.FileMode.Create) Dim xtw1 As New System.Xml.XmlTextWriter _ (myFileStream, System.Text.Encoding.Unicode) das1.WriteXml(xtw1, XmlWriteMode.DiffGram) ’Perform update to server-based data source for ’the das1 data set; don’t specify a specific 'data table within the data set. dap1.Update(das1) End Sub Cr e a t in g H TML Pa ges w it h XSLT As y ou st art t o work wit h Visual Basic .NET, m ost of y our Web dev elopm ent work should focus ar ound ASP.NET. ( See Chapt er 11.) This t echnology is especially craft ed t o m ak e Visual Basic dev elopers feel right at hom e when building Web solut ions. As you can see from t he preceding pair of sam ples, it ’s easy t o adapt Visual Basic code is t o ASP.NET. How ev er, y ou m ight occasionally want t o generat e out put for a Web environm ent using XSLT. I n m y ex per ience, one of t he m ost popular uses for XSLT is t he t ransform at ion of XML docum ent s int o t ables on HTML pages. The chapt er up unt il t his point aim ed t o convey a w ork ing know ledge of how t o cr eat e and consum e XML docum ent s in .NET solut ions. The rem ainder of t his chapt er helps you prepare XML docum ent s for display on HTML pages via XSLT. When y ou’r e using XSLT t o t ransform XML docum ent s int o HTML pages, it ’s useful t o have a w or k ing k now ledge of HTML form at t ing synt ax as w ell as cascading st yle sheet s. You, of course, also need som e fam iliar it y wit h how t o select t ags from XML docum ent s t o display in y our HTML pages. Many Visual Basic dev elopers hav e lit t le or no HTML pr ogram m ing exper ience. I f t his is your sit uat ion, I recom m end a couple of st rat egies. First , use a graphic Web page designer, such as t he one built int o .NET or t he one in Front Page. Wit h a graphic Web page designer, you can graphically creat e pages and t hen look at t he HTML behind t he code. You can t hen incorporat e t hat code int o your XSLT t ransform at ion file. Second, if you belong t o a proj ect t eam t hat includes Web specialist s, plan t he pr oj ect so t hat t he Web specialist s creat e general XSLT files t hat can fit m any sit uat ions or be easily adapt ed. Then t he Visual Basic dev elopers can r efer ence t he XSLT t ransform at ion files as is or w it h m inor edit ing. The Visual St udio .NET docum ent at ion includes several sam ples illust rat ing how t o load XML docum ent s and t ransform t hem w it h XSLT. ( For exam ple, see t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. “XslTransform .Load Met hod ( Xm lReader ) ” t opic in t he Visual Basic .NET docum ent at ion.) This sect ion in t he book includes a couple of sam ples t o com plem ent t hose from t he Visual Basic .NET docum ent at ion t hat w ork wit h t he SqlXm lCom m and class. Recall t hat y ou can use t his SQLXML Managed Class t o generat e XML docum ent s from SQL st at em ent s. The SQLXML Managed Classes are t here t o m ak e life sim ple for SQL Serv er developers. For exam ple, t he Schem aPat h propert y facilit at es referencing annot at ed schem a for filt er ing t he ret ur n set fr om a dat abase obj ect . Sim ilar ly, t he XslPat h pr opert y for a SqlXm lCom m and obj ect r eferences an XSLT file. When you specify t his at t ribut e, your pr ocedur es can r et ur n HTML pages inst ead of raw , unform at t ed XML t ags and values in a docum ent file. The refer enced XSLT t ransform file m ust synchr onize w it h t he XML docum ent t hat w ould hav e ret urned from t he SqlXm lCom m and obj ect . Two sam ple XSLT t ransform at ion files illust rat e how t o im plem ent t his sy nchr onizat ion. For m a t t ing Tw o Colu m n s fr om t h e Em ploye e s Ta ble When y ou use t he XslPat h pr opert y w it h a SqlXm lCom m and obj ect , y ou don’t get t o see t he under ly ing XML docum ent . The int er nal code in t he SqlXm lCom m and class aut om at ically conv ert s it s XML docum ent t o HTML code according t o t he inst ruct ions in t he file t o which t he XslPat h pr opert y point s. The following sam ple t ransform s an XML docum ent based on t he Em ployees t able in t he Nort hw ind dat abase. I nst ead of j ust saving t he final HTML page, t he pr ocedur e first saves t he XML docum ent w it hout set t ing t he XslPat h propert y . Then t he procedur e assigns a st ring v alue t o t he XslPat h propert y t hat point s t o an XSLT file and saves a second docum ent in HTML form at . The SQLToXMLToHTMLFor Em ployees pr ocedur e st art s creat ing an XML docum ent wit h a SqlXm lCom m and obj ect point ing t o t he Nort hw ind dat abase. The SQL st ring for t he obj ect ext ract s t he Em ployeeI D, First Nam e, and Last Nam e colum ns from t he Em ployees t able by using a SELECT st at em ent w it h a FOR XML clause. Recall t hat t his process ret ur ns an XML fragm ent wit hout a unique out er t ag for t he docum ent . Ther efor e, t he pr ocedur e assigns a st ring value ( “My Root ” ) t o t he Root Tag pr opert y for t he SqlXm lCom m and obj ect . Next t he pr ocedur e set s up t o save t he XML docum ent in a file nam ed Unfor m at t edEm ployees.xm l befor e inv ok ing t he Ex ecut eToSt ream m et hod t o sav e t he XML docum ent . The set up process enables t he Ex ecut eToSt ream m et hod t o pass t he docum ent dir ect ly from t he SqlXm lCom m and obj ect t o a file. Aft er saving t he XML docum ent , t he pr ocedur e assigns t he XslPat h propert y for t he SqlXm lCom m and obj ect . The propert y point s t o t he My XSL.xslt file in t he r oot folder of t he XMLSam ples solut ion folder . Then, t he pr ocedur e inv ok es t he Ex ecut eSt ream m et hod for t he SqlXm lCom m and obj ect t o r epr esent t he HTML page wit h an in- m em or y st ream obj ect . Aft er capt ur ing t he HTML as a st ream obj ect , t he procedure m ov es on t o r ead t he st ream and t hen wr it e it t o an ext er nal file nam ed For m at t edEm ployees.ht m l. Sub SQLToXMLToHTMLForEmployees() ’Specify SqlXmlCommand. Dim cmd1 As New SqlXmlCommand("Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=northwind;Integrated Security=SSPI") cmd1.CommandText = _ "SELECT EmployeeID, FirstName, LastName " & _ "FROM Employees FOR XML AUTO" cmd1.CommandType = SqlXmlCommandType.Sql cmd1.RootTag = "MyRoot" ’Name the path and file for the Xml result set, then ’instantiate a Stream object for the file’s contents. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. Dim myXMLfile As String = _ "c:\SQL Server Development with VBDotNet\" & _ "Chapter12\UnFormattedEmployees.xml" Dim myFileStream As New System.IO.FileStream _ (myXMLfile, System.IO.FileMode.Create) ’Execute cmd1 and store the result set in the stream. cmd1.ExecuteToStream(myFileStream) ’Close the file stream to recover the resource. myFileStream.Close() ’Set the XslPath property to specify the name of ’the XSLT style sheet. cmd1.XslPath = "..\MyXSL.xslt" ’Return the HTML from cmd1 as an in-memory stream ’object; then, create a stream reader to read the ’contents of the stream. Dim stm1 As Stream stm1 = cmd1.ExecuteStream Dim srd1 As New StreamReader(stm1) ’Declare and instantiate a string for the name of ’the file pointing at the FileStream with the ’HTML content. Dim str1 As String = _ "c:\SQL Server Development with VBDotNet\" & _ "Chapter12\FormattedEmployees.html" Dim fst1 As New FileStream(str1, FileMode.OpenOrCreate) ’Declare and instantiate a StreamWriter to populate ’the file holding the HTML content; then, read the ’StreamReader’s contents into a string and write the ’string to fst1. Dim swt1 As New StreamWriter(fst1) Dim str2 As String = srd1.ReadToEnd swt1.Write(str2) ’Close the file. swt1.Close() End Sub Figur e 12- 12 shows t he UnForm at t edEm ployees.xm l file. Not ice t hat it cont ains nine Em ployees elem ent s. Each elem ent has t hree at t ribut es wit h v alues for Em ploy eeI D, First Nam e, and Last Nam e. The cont ent and layout follow direct ly from t he Com m andText propert y set t ing for t he SqlXm lCom m and obj ect in t he SQLToXMLToHTMLFor Em ployees pr ocedur e. I t is t he UnForm at t edEm ploy ees.xm l file t hat t he My XSL.xslt file t ransform s. Figu r e 1 2 - 1 2 . Th e Un for m at t e dEm p loye e s.x m l file con t en t s g en e ra t ed by t h e SQLToX M LToH TM LFor Em p loye e s pr oce d u r e. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. Figur e 12- 13 shows t he t ransform ed XML docum ent saved as Form at t ed- Em ploy ees.ht m l. The file in Figure 12- 13 appears as a t able inst ead of a raw list ing of elem ent s. I n addit ion, t he second colum n display ing last nam e appears in it alics and bold. Ther e’s addit ional form at t ing as well, such as a t able header wit h a background color . The My XSL.xslt file facilit at ed all t he lay out and for m at t ing changes bet ween Figure 12- 12 and Figur e 12- 13. There’s one m or e difference bet w een t he t wo figur es. Figur e 12- 13 has only t wo colum ns, but t he init ial XML docum ent has t hr ee at t r ibut es for ev er y Em ploy ees elem ent wit hin t he docum ent . This difference result s fr om t he fact t hat t he My XSL.xslt file select s only t wo of t he t hree at t ribut es for display . Figu r e 1 2 - 1 3 . Th e For m a t t e dEm ploye es.h t m l file con t en t s g en e ra t ed by t h e SQLToX M LToH TM LFor Em p loye e s pr oce d u r e. The list ing for My XSL.xslt appears next . I t com m ences wit h it s declarat ion as an XML docum ent and a r eference t o t he Wor ld Wide Web Consort ium nam espace for XSLT files. The design of t he t ransform has t w o m ain part s denot ed w it hin t wo xsl: t em plat e elem ent s. The first elem ent m at ches t he Em ploy ees elem ent in t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

CÓ THỂ BẠN MUỐN DOWNLOAD

Đồng bộ tài khoản