XML and the .NET Dataset
As you know, the .NET Dataset object (from the System.Data namespace) is a key component of the ADO.NET technology. I introduced these managed objects in Chapter 11. You have seen how flexible this in-memory data store can be. You can easily use the .NET Dataset as a disconnected relational database that allows you to query the Dataset in much the same way as you would an actual database. But that is not all that the .NET Dataset can do.
Microsoft designed the .NET Dataset (or just Dataset ) to support a relational view and a hierarchical view simultaneously . This has significantly increased the usefulness and flexibility of the Dataset. You can easily use the Dataset as a container for your relational data or XML ”your hierarchical data. Let's now explore how you can go about writing and reading XML directly to and from a .NET Dataset.
| Note | For those of you reformed mainframe programmers who have had the pleasure of programming with IBM's Information Management System (IMS) on the mainframe, the word "hierarchical" may send shivers up your spine. Fear not. As you work with XML, experiencing again the parent/child structures, you will find that the .NET platform provides for a pleasurable and productive programming experience. As the mainframe IMS "data store" had its value, XML certainly has its value ”and then some. Yes, the two technologies represent data in a hierarchical model. That is where the similarity ends. XML extends on from there, earning the "X" in its name . |
Exploring the Dataset Class
In order to write XML to and read XML from the System.Data.Dataset class, you will first need to drill down into the Dataset class to identify the exposed methods. For that reason, I have executed the Class Viewer (WinCV.exe) tool from the Visual Studio .NET (VS .NET) command prompt and captured the "member" information for the Dataset class. The partial Class Viewer display in Listing 12-1 shows most of the methods exposed by the Dataset class.
 |
//from module 'c:\winnt\assembly\gac\system.data.0.3300.0__b77a5c561934e089\system.data.dll' public class System.Data.DataSet : . . . // Fields . . . // Constructors . . . // Properties . . . // Events . . . // Methods public void AcceptChanges(); public virtual void BeginInit(); public void Clear(); public virtual System.Data.DataSet Clone(); public System.Data.DataSet Copy(); public virtual void Dispose(); public virtual void EndInit(); public virtual bool Equals(object obj); public System.Data.DataSet GetChanges(); public System.Data.DataSet GetChanges(System.Data.DataRowState rowStates); public virtual int GetHashCode(); public virtual object GetService(Type service); public Type GetType(); public string GetXml(); public string GetXmlSchema(); public bool HasChanges(); public bool HasChanges(System.Data.DataRowState rowStates); public void InferXmlSchema(string fileName, string[] nsArray); public void InferXmlSchema(System.Xml.XmlReader reader, string[] nsArray); public void InferXmlSchema(System.IO.TextReader reader, string[] nsArray); public void InferXmlSchema(System.IO.Stream stream, string[] nsArray); . . . public System.Data.XmlReadMode ReadXml(string fileName); public System.Data.XmlReadMode ReadXml(string fileName, XmlReadMode mode); public System.Data.XmlReadMode ReadXml(System.IO.TextReader reader); public System.Data.XmlReadMode ReadXml(System.Xml.XmlReader reader); public System.Data.XmlReadMode ReadXml(System.Xml.XmlReader reader, XmlReadMode) public System.Data.XmlReadMode ReadXml(System.IO.TextReader reader, XmlReadMode) public System.Data.XmlReadMode ReadXml(System.IO.Stream stream); public System.Data.XmlReadMode ReadXml(System.IO.Stream stream, XmlReadMode mode); public void ReadXmlSchema(string fileName); public void ReadXmlSchema(System.IO.TextReader reader); public void ReadXmlSchema(System.Xml.XmlReader reader); public void ReadXmlSchema(System.IO.Stream stream); public virtual void RejectChanges(); public virtual void Reset(); public virtual string ToString(); public void WriteXml(string fileName); public void WriteXml(string fileName, System.Data.XmlWriteMode mode); public void WriteXml(System.IO.Stream stream); public void WriteXml(System.IO.Stream stream, System.Data.XmlWriteMode mode); public void WriteXml(System.IO.TextWriter writer); public void WriteXml(System.Xml.XmlWriter writer); public void WriteXml(System.IO.TextWriter writer, System.Data.XmlWriteMode mode); public void WriteXml(System.Xml.XmlWriter writer, System.Data.XmlWriteMode mode); public void WriteXmlSchema(string fileName); public void WriteXmlSchema(System.IO.Stream stream); public void WriteXmlSchema(System.Xml.XmlWriter writer); public void WriteXmlSchema(System.IO.TextWriter writer); } // end of System.Data.DataSet
| |
Looking at the Class Viewer display in Listing 12-1, you can decipher from the method names alone that the vast majority of the Dataset methods are provided for XML support. From the available methods, you will focus on the following three XML- related Dataset methods:
-
System.Data.Dataset.ReadXML
-
System.Data.Dataset.GetXML
-
System.Data.Dataset.WriteXML
Using the ReadXML, GetXML, and WriteXML Methods
For basic XML/Dataset reading and writing, you will be using the ReadXML, GetXML, and WriteXML Dataset methods. You will find that they are extremely simple to use. Basically, you can use the ReadXML method to load an XML file (a file typically ending with the .xml suffix) into a Dataset. The GetXML method and the WriteXML method are similar. They both support the extraction of Dataset contents in XML format. However, the GetXML method outputs to a String variable, whereas the WriteXML method expects to output to a disk-based XML file.
| Tip | Both the ReadXML and WriteXML methods support an XML format called DiffGram. Generally speaking, the DiffGram XML format is used by the .NET Framework when it needs to serialize the contents of a Dataset. The ReadXML and WriteXML methods have overloaded constructors that allow a parameter to be passed in to indicate the Read or Write mode. The System.Data.XmlReadMode and System.Data.XmlWriteMode enumerations are used for this purpose. The value of DiffGram is listed among other enumerated Read and Write mode values. |
The following sample projects (both in COBOL .NET and Visual Basic .NET [VB .NET]) demonstrate the simplicity of writing XML to and reading XML from the .NET Dataset. In the samples, you will see the necessary ADO.NET coding to connect to the Northwind database and query the Customers table. Before you continue, consider launching one of the SQL Server client tools. Take this opportunity to review the structure and contents of the Northwind database and the Customers table (see Figure 12-1).
Figure 12-1: A subset of the Northwind.Customers table beginning with the ALFKI CustomerID and ending with the CACTU CustomerID
Each of the sample projects displays XML to the console window and creates an XML file. If you were to capture the XML displayed to the console [1] or actually open the saved XML file, you would see formatted XML as shown in Listing 12-2. (Notice that I have included only a snippet of the actual XML beginning with the ALFKI CustomerID and ending with the CACTU CustomerID.)
| |
<?xml version="1.0" standalone="yes"?> <NewDataSet> <myCustomers> <CustomerID>ALFKI</CustomerID> <CompanyName>Alfreds Futterkiste</CompanyName> <ContactName>Maria Anders</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>Obere Str. 57</Address> <City>Berlin</City> <PostalCode>12209</PostalCode> <Country>Germany</Country> <Phone>030-0074321</Phone> <Fax>030-0076545</Fax> </myCustomers> <myCustomers> <CustomerID>ANATR</CustomerID> <CompanyName>Ana Trujillo Emparedados y helados</CompanyName> <ContactName>Ana Trujillo</ContactName> <ContactTitle>Owner</ContactTitle> <Address>Avda. de la Constitucin 2222</Address> <City>Mxico D.F.</City> <PostalCode>05021</PostalCode> <Country>Mexico</Country> <Phone>(5) 555-4729</Phone> <Fax>(5) 555-3745</Fax> </myCustomers> <myCustomers> <CustomerID>ANTON</CustomerID> <CompanyName>Antonio Moreno Taquera</CompanyName> <ContactName>Antonio Moreno</ContactName> <ContactTitle>Owner</ContactTitle> <Address>Mataderos 2312</Address> <City>Mxico D.F.</City> <PostalCode>05023</PostalCode> <Country>Mexico</Country> <Phone>(5) 555-3932</Phone> </myCustomers> <myCustomers> <CustomerID>AROUT</CustomerID> <CompanyName>Around the Horn</CompanyName> <ContactName>Thomas Hardy</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>120 Hanover Sq.</Address> <City>London</City> <PostalCode>WA1 1DP</PostalCode> <Country>UK</Country> <Phone>(171) 555-7788</Phone> <Fax>(171) 555-6750</Fax> </myCustomers> <myCustomers> <CustomerID>BERGS</CustomerID> <CompanyName>Berglunds snabbkp</CompanyName> <ContactName>Christina Berglund</ContactName> <ContactTitle>Order Administrator</ContactTitle> <Address>Berguvsv gen 8</Address> <City>Lule</City> <PostalCode>S-958 22</PostalCode> <Country>Sweden</Country> <Phone>0921-12 34 65</Phone> <Fax>0921-12 34 67</Fax> </myCustomers> <myCustomers> <CustomerID>BLAUS</CustomerID> <CompanyName>Blauer See Delikatessen</CompanyName> <ContactName>Hanna Moos</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>Forsterstr. 57</Address> <City>Mannheim</City> <PostalCode>68306</PostalCode> <Country>Germany</Country> <Phone>0621-08460</Phone> <Fax>0621-08924</Fax> </myCustomers> <myCustomers> <CustomerID>BLONP</CustomerID> <CompanyName>Blondesddsl pre et fils</CompanyName> <ContactName>Frdrique Citeaux</ContactName> <ContactTitle>Marketing Manager</ContactTitle> <Address>24, place Klber</Address> <City>Strasbourg</City> <PostalCode>67000</PostalCode> <Country>France</Country> <Phone>88.60.15.31</Phone> <Fax>88.60.15.32</Fax> </myCustomers> <myCustomers> <CustomerID>BOLID</CustomerID> <CompanyName>Blido Comidas preparadas</CompanyName> <ContactName>Martn Sommer</ContactName> <ContactTitle>Owner</ContactTitle> <Address>C/ Araquil, 67</Address> <City>Madrid</City> <PostalCode>28023</PostalCode> <Country>Spain</Country> <Phone>(91) 555 22 82</Phone> <Fax>(91) 555 91 99</Fax> </myCustomers> <myCustomers> <CustomerID>BONAP</CustomerID> <CompanyName>Bon app'</CompanyName> <ContactName>Laurence Lebihan</ContactName> <ContactTitle>Owner</ContactTitle> <Address>12, rue des Bouchers</Address> <City>Marseille</City> <PostalCode>13008</PostalCode> <Country>France</Country> <Phone>91.24.45.40</Phone> <Fax>91.24.45.41</Fax> </myCustomers> <myCustomers> <CustomerID>BOTTM</CustomerID> <CompanyName>Bottom-Dollar Markets</CompanyName> <ContactName>Elizabeth Lincoln</ContactName> <ContactTitle>Accounting Manager</ContactTitle> <Address>23 Tsawassen Blvd.</Address> <City>Tsawassen</City> <Region>BC</Region> <PostalCode>T2F 8M4</PostalCode> <Country>Canada</Country> <Phone>(604) 555-4729</Phone> <Fax>(604) 555-3745</Fax> </myCustomers> <myCustomers> <CustomerID>BSBEV</CustomerID> <CompanyName>B's Beverages</CompanyName> <ContactName>Victoria Ashworth</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>Fauntleroy Circus</Address> <City>London</City> <PostalCode>EC2 5NT</PostalCode> <Country>UK</Country> <Phone>(171) 555-1212</Phone> </myCustomers> <myCustomers> <CustomerID>CACTU</CustomerID> <CompanyName>Cactus Comidas para llevar</CompanyName> <ContactName>Patricio Simpson</ContactName> <ContactTitle>Sales Agent</ContactTitle> <Address>Cerrito 333</Address> <City>Buenos Aires</City> <PostalCode>1010</PostalCode> <Country>Argentina</Country> <Phone>(1) 135-5555</Phone> <Fax>(1) 135-4892</Fax> </myCustomers> . . . </NewDataSet>
| |
In the sample code projects, I decided to first load the result set (relational data) into a Dataset and then write out the XML to a disk file named myCustomers . xml. The newly created XML file is then read into a second disconnected Dataset. The contents of that second Dataset are then displayed to the console window in XML format.
| Tip | When you choose to manually build your DataColumn using the System.Data.DataColumn namespace, you can use the System.Data.SqlTypes namespace to help set the DataType property. Mapping the native data types within SQL Server to the data types in your program can sometimes be tricky. Using the SqlTypes objects for type mapping will help prevent loss of data precision and integrity. |
COBOL .NET Sample Code
The sample code in Listing 12-3 is part of the ReadWriteXMLDatasetExample-Cobol project (a console project type). Please review the code. As usual, I have included many useful comments in the code. Feel free to experiment, change the code, and explore the flexibility of these ADO.NET Dataset methods (recall the various overloaded forms of the methods shown earlier in the section "Exploring the Dataset Class"). As demonstrated in this sample, working with XML can be rather simple. The WriteXML, ReadXML, and GetXML methods of the Dataset class are used.
| Note | The COBOL .NET project type requires that you manually add a reference to the ADO.NET assembly (System.Data.Dll). The steps for adding an assembly reference in VS .NET were introduced earlier (see the section "VS .NET Feature: The Object Browser" in Chapter 7). |
| |
000010* Sample Code demonstrating ADO.NET's support for XML Technology 000020 IDENTIFICATION DIVISION. 000030 PROGRAM-ID. MAIN. 000040 ENVIRONMENT DIVISION. 000050 CONFIGURATION SECTION. 000060 REPOSITORY. 000070* .NET Framework Classes 000080 CLASS SqlConnection AS "System.Data.SqlClient.SqlConnection" 000090 CLASS SqlDataAdapter As "System.Data.SqlClient.SqlDataAdapter" 000100 CLASS SqlCommand As "System.Data.SqlClient.SqlCommand" 000110 CLASS DataSet As "System.Data.DataSet" 000120 CLASS DataTable AS "System.Data.DataTable" 000130 CLASS DataRow As "System.Data.DataRow" 000140 CLASS DataColumn AS "System.Data.DataColumn" 000150 CLASS SystemType AS "System.Type" 000160 CLASS DataColumnArray AS "System.Data.DataColumn[]" 000170 000180 CLASS Sys-Integer AS "System.Int32" 000190 CLASS Sys-String AS "System.String" 000200 CLASS Sys-Object AS "System.Object" 000210 000220* .NET Framework Properties 000230 PROPERTY PROP-ConnectionString AS "ConnectionString" 000240 PROPERTY PROP-Connection AS "Connection" 000250 PROPERTY PROP-CommandText AS "CommandText" 000260 PROPERTY PROP-SelectCommand AS "SelectCommand" 000270 PROPERTY PROP-Columns AS "Columns" 000280 PROPERTY PROP-Tables AS "Tables" 000290 PROPERTY PROP-DataType AS "DataType" 000300 PROPERTY PROP-ColumnName AS "ColumnName" 000310 PROPERTY PROP-Item AS "Item" 000320 PROPERTY PROP-PrimaryKey AS "PrimaryKey" 000330 PROPERTY PROP-Unique AS "Unique" 000340 PROPERTY PROP-IgnoreSchema AS "IgnoreSchema" 000350 000360* .NET Framework Enumerations 000370 ENUM ENUM-XmlWriteMode AS "System.Data.XmlWriteMode". 000380 000390 DATA DIVISION. 000400 WORKING-STORAGE SECTION. 000410 77 mySqlConnection OBJECT REFERENCE SqlConnection. 000420 77 mySqlDataAdapter OBJECT REFERENCE SqlDataAdapter. 000430 77 mySqlCommand OBJECT REFERENCE SqlCommand. 000440 77 myDataSet1 OBJECT REFERENCE DataSet. 000450 77 myDataSet2 OBJECT REFERENCE DataSet. 000460 77 myDataTable OBJECT REFERENCE DataTable. 000470 77 myDataColumn OBJECT REFERENCE DataColumn. 000480 77 myPrimaryKeyColumn OBJECT REFERENCE DataColumn. 000490 77 myPrimaryKeyColumns OBJECT REFERENCE DataColumnArray. 000500 77 myENUM-XmlWriteMode OBJECT REFERENCE ENUM-XmlWriteMode. 000510 000520 77 mySys-String OBJECT REFERENCE Sys-String. 000530 77 mySys-Integer OBJECT REFERENCE Sys-Integer. 000540 77 mySys-Object OBJECT REFERENCE Sys-Object. 000550 77 myXmlFile OBJECT REFERENCE Sys-String. 000560 77 myDisplayString PIC x(38550). 000570 77 myInt PIC S9(9) COMP-5. 000580 77 myOtherInt PIC S9(9) COMP-5. 000590 01 NULL-X PIC X(1). 000600 PROCEDURE DIVISION. 000610 000620 Perform 0000-OptionalPreTableBuild. 000630 Perform 1000-UseSqlDataAdapter. 000640 Perform 2000-ReadWriteXML. 000650 DISPLAY " " 000660 000670 DISPLAY "Enter X and Press Enter to Exit.". 000680 ACCEPT NULL-X. 000690 Stop Run. 000700 000710************************************************ 000720 0000-OptionalPreTableBuild. 000730* It is possible to obtain the "schema" or table structure 000740* directly/automatically from the SQL Server Database 000750* This section is added for training purposes. 000760* The information found in this section would be critical 000770* in the case of building a disconnected .NET dataset 000780* that may have a non-SQL Server Data Source. 000790 000800* Create a new DataTable. 000810 INVOKE DataTable "NEW" USING BY VALUE "myCustomers" 000820 RETURNING myDataTable. 000830 000840* Create 1st myDataColumn. 000850 INVOKE DataColumn "NEW" RETURNING myDataColumn. 000860 SET PROP-DataType OF myDataColumn TO 000870 SystemType::"GetType"("System.String"). 000880 SET PROP-ColumnName OF myDataColumn TO "CustomerID". 000890 SET PROP-Unique OF myDataColumn TO B"1". 000900 INVOKE PROP-Columns OF myDataTable "Add" 000910 USING BY VALUE myDataColumn. 000920 000930* Create 2nd myDataColumn. 000940 INVOKE DataColumn "NEW" RETURNING myDataColumn. 000950 SET PROP-DataType OF myDataColumn TO 000960 SystemType::"GetType"("System.String"). 000970 SET PROP-ColumnName OF myDataColumn TO "CompanyName". 000980 INVOKE PROP-Columns OF myDataTable "Add" 000990 USING BY VALUE myDataColumn. 001000 001010* Create 3rd myDataColumn. 001020 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001030 SET PROP-DataType OF myDataColumn TO 001040 SystemType::"GetType"("System.String"). 001050 SET PROP-ColumnName OF myDataColumn TO "ContactName". 001060 INVOKE PROP-Columns OF myDataTable "Add" 001070 USING BY VALUE myDataColumn. 001080 001090* Create 4th myDataColumn. 001100 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001110 SET PROP-DataType OF myDataColumn TO 001120 SystemType::"GetType"("System.String"). 001130 SET PROP-ColumnName OF myDataColumn TO "ContactTitle". 001140 INVOKE PROP-Columns OF myDataTable "Add" 001150 USING BY VALUE myDataColumn. 001160 001170* Create 5th myDataColumn. 001180 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001190 SET PROP-DataType OF myDataColumn TO 001200 SystemType::"GetType"("System.String"). 001210 SET PROP-ColumnName OF myDataColumn TO "Address". 001220 INVOKE PROP-Columns OF myDataTable "Add" 001230 USING BY VALUE myDataColumn. 001240 001250* Create 6th myDataColumn. 001260 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001270 SET PROP-DataType OF myDataColumn TO 001280 SystemType::"GetType"("System.String"). 001290 SET PROP-ColumnName OF myDataColumn TO "City". 001300 INVOKE PROP-Columns OF myDataTable "Add" 001310 USING BY VALUE myDataColumn. 001320 001330* Create 7th myDataColumn. 001340 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001350 SET PROP-DataType OF myDataColumn TO 001360 SystemType::"GetType"("System.String"). 001370 SET PROP-ColumnName OF myDataColumn TO "Region". 001380 INVOKE PROP-Columns OF myDataTable "Add" 001390 USING BY VALUE myDataColumn. 001400 001410* Create 8th myDataColumn. 001420 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001430 SET PROP-DataType OF myDataColumn TO 001440 SystemType::"GetType"("System.String"). 001450 SET PROP-ColumnName OF myDataColumn TO "PostalCode". 001460 INVOKE PROP-Columns OF myDataTable "Add" 001470 USING BY VALUE myDataColumn. 001480 001490* Create 9th myDataColumn. 001500 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001510 SET PROP-DataType OF myDataColumn TO 001520 SystemType::"GetType"("System.String"). 001530 SET PROP-ColumnName OF myDataColumn TO "Country". 001540 INVOKE PROP-Columns OF myDataTable "Add" 001550 USING BY VALUE myDataColumn. 001560 001570* Create 10th myDataColumn. 001580 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001590 SET PROP-DataType OF myDataColumn TO 001600 SystemType::"GetType"("System.String"). 001610 SET PROP-ColumnName OF myDataColumn TO "Phone". 001620 INVOKE PROP-Columns OF myDataTable "Add" 001630 USING BY VALUE myDataColumn. 001640 001650* Create 11th myDataColumn. 001660 INVOKE DataColumn "NEW" RETURNING myDataColumn. 001670 SET PROP-DataType OF myDataColumn TO 001680 SystemType::"GetType"("System.String"). 001690 SET PROP-ColumnName OF myDataColumn TO "Fax". 001700 INVOKE PROP-Columns OF myDataTable "Add" 001710 USING BY VALUE myDataColumn. 001720 001730* Assign primary key column to "CustomerID" column. 001740 INVOKE DataColumnArray "NEW" USING BY VALUE 1 001750 RETURNING myPrimaryKeyColumns. 001760 INVOKE PROP-Columns OF myDataTable "get_Item" 001770 USING BY VALUE "CustomerID" 001780 RETURNING myPrimaryKeyColumn. 001790 INVOKE myPrimaryKeyColumns "Set" 001800 USING BY VALUE 0 myPrimaryKeyColumn. 001810 SET PROP-PrimaryKey OF myDataTable TO myPrimaryKeyColumns. 001820 001830* Reference the DataSet. 001840 INVOKE DataSet "NEW" RETURNING myDataSet1. 001850* Associate the Table with the Dataset. 001860 INVOKE PROP-Tables OF myDataSet1 "Add" 001870 USING BY VALUE myDataTable. 001880 001890************************************************ 001900 1000-UseSqlDataAdapter. 001910 001920* Reference Data Provider Objects 001930 INVOKE SqlConnection "NEW" RETURNING mySqlConnection 001940 INVOKE SqlDataAdapter "NEW" RETURNING mySqlDataAdapter 001950 INVOKE SqlCommand "NEW" RETURNING mySqlCommand 001960 001970* Prepare to Connect to SQL Server Database 001980* using Connection String 001990 SET PROP-ConnectionString OF mySqlConnection TO 002000 "user id=sa;pwd=;Database=northwind;Server=(LOCAL)" 002010 002020* Associate the Command Object with the Connection Object 002030 SET PROP-Connection OF mySqlCommand TO mySqlConnection 002040* Associate the Command Object with intended SQL Statement 002050 SET PROP-CommandText OF mySqlCommand TO "Select * from Customers" 002060* Associate the DataAdapter Object with the Command Object 002070 SET PROP-SelectCommand OF mySqlDataAdapter TO mySqlCommand 002080 002090* Have the DataAdapter Object Execute the SQL Statement and 002100* store the result set in a DataSet DataTable named myCustomers 002110 INVOKE mySqlDataAdapter "Fill" 002120 USING BY VALUE myDataSet1, "myCustomers" 002130 002140* Close the Database Connection 002150 INVOKE mySqlConnection "Close". 002160 002170 SET mySqlConnection TO NULL. 002180 SET mySqlDataAdapter TO NULL. 002190 SET mySqlCommand TO NULL. 002200 SET myDataTable TO NULL. 002210 002220************************************************ 002230 2000-ReadWriteXML. 002240 002250* The following XML file will be saved on your hard disk. 002260* You can locate it in the local application BIN folder 002270 SET myXmlFile TO "myCustomers.xml" 002280 002290* Demonstrate the usage of the WriteXML method 002300* Write out an XML file that originated as relational data 002310 SET myENUM-XmlWriteMode 002320 TO PROP-IgnoreSchema OF ENUM-XmlWriteMode 002330 INVOKE myDataSet1 "WriteXml" USING BY VALUE 002340 myXmlFile, myENUM-XmlWriteMode 002350 002360* Demonstrate the usage of the ReadXML method 002370* Load a 2nd Dataset from the saved XML file 002380 INVOKE DataSet "NEW" RETURNING myDataSet2 002390 INVOKE myDataSet2 "ReadXml" USING BY VALUE myXmlFile 002400 002410* Demonstrate the usage of the GetXML method 002420* Extract data from the Dataset in XML format 002430 INVOKE myDataSet2 "GetXml" RETURNING mySys-String 002440 SET myDisplayString TO mySys-String 002450 DISPLAY myDisplayString. 002460 002470 END PROGRAM MAIN. | |
Wow! You have got to admit, using COBOL (in .NET) to easily read and write XML from relational database data is great. Can you just imagine having been able to do something like this in some of the legacy mainframe COBOL applications that you have written in the past?
Of course, you've long been able to code legacy COBOL mainframe applications that extract database data and hold the data in a programmatically defined in-memory table. But when was the last time you output the in-memory table in a format similar to that of XML with such ease (you didn't even need any JCL)? And with so few lines of code? That's what I thought. Learning to leverage ADO.NET's support for XML has "programming productivity" written all over it.
Continuing your bilingual quest, you'll now take a look at the VB .NET sample code.
VB .NET Sample Code
I have included the relevant portion of the ReadWriteXMLDatasetExampleVBproject in Listing 12-4. This sample (a console project type) is constructed as such to exercise each targeted Dataset method. In your own application, you may typically use one Dataset method or the other, but perhaps not all of them together.
| |
'This sample program demonstrates how to Read 'and Write data from/to a ADO.NET Dataset in XML 'format. Module Module1 Sub Main() Call UseSqlDataAdapter(OptionalPreTableBuild) Console.WriteLine(" ") Console.ReadLine() End Sub Public Function OptionalPreTableBuild() As DataSet 'It is possible to obtain the "schema" or table structure 'directly/automatically from the SQL Server Database 'This section is added for training purposes. 'The information found in this section would be critical 'in the case of building a disconnected .NET dataset 'that may have a non-SQL Server Data Source. ' Create new DataTable. Dim myDataTable As DataTable = _ New System.Data.DataTable("myCustomers") ' Declare DataColumn and DataRow variables. Dim myDataColumn As System.Data.DataColumn Dim myDataRow As System.Data.DataRow ' Create 1st myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "CustomerID" myDataColumn.Unique = True myDataTable.Columns.Add(myDataColumn) ' Create 2nd myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "CompanyName" myDataTable.Columns.Add(myDataColumn) ' Create 3rd myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "ContactName" myDataTable.Columns.Add(myDataColumn) ' Create 4th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "ContactTitle" myDataTable.Columns.Add(myDataColumn) ' Create 5th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "Address" myDataTable.Columns.Add(myDataColumn) ' Create 6th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "City" myDataTable.Columns.Add(myDataColumn) ' Create 7th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "Region" myDataTable.Columns.Add(myDataColumn) ' Create 8th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "PostalCode" myDataTable.Columns.Add(myDataColumn) ' Create 9th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "Country" myDataTable.Columns.Add(myDataColumn) ' Create 10th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "Phone" myDataTable.Columns.Add(myDataColumn) ' Create 11th myDataColumn. myDataColumn = New System.Data.DataColumn() myDataColumn.DataType = Type.GetType("System.String") myDataColumn.ColumnName = "Fax" myDataTable.Columns.Add(myDataColumn) ' Assign primary key column to CustomerID column Dim PrimaryKeyColumns(0) As System.Data.DataColumn PrimaryKeyColumns(0) = myDataTable.Columns("CustomerID") myDataTable.PrimaryKey = PrimaryKeyColumns ' Reference the DataSet. Dim myDataSet As New System.Data.DataSet() ' Associate the Table with the Dataset. myDataSet.Tables.Add(myDataTable) myDataTable = Nothing Return myDataSet End Function Public Sub UseSqlDataAdapter(ByVal myDataset As DataSet) 'Reference Data Provider Objects Dim mySqlConnection As New System.Data.SqlClient.SqlConnection() Dim mySqlDataAdapter As New System.Data.SqlClient.SqlDataAdapter() Dim mySqlCommand As New System.Data.SqlClient.SqlCommand() 'Reference Dataset Objects Dim myDataRow As System.Data.DataRow 'Prepare to Connect to SQL Server Database 'using Connection String mySqlConnection.ConnectionString = _ "user id=sa;pwd=;Database=northwind;Server=(LOCAL)" 'Associate the Command Object with the Connection Object mySqlCommand.Connection = mySqlConnection 'Associate the Command Object with intended SQL Statement mySqlCommand.CommandText = "Select * from Customers" 'Associate the DataAdapter Object with the Command Object mySqlDataAdapter.SelectCommand = mySqlCommand 'Have the DataAdapter Object Execute the SQL Statement and 'store the result set in a DataSet DataTable named myCustomers mySqlDataAdapter.Fill(myDataset, "myCustomers") 'Close the Database Connection mySqlConnection.Close() mySqlConnection = Nothing mySqlDataAdapter = Nothing mySqlCommand = Nothing 'Pass the Disconnected Dataset to the called method Call ReadWriteXML(myDataset) End Sub Private Sub ReadWriteXML(ByVal ds1 As System.Data.DataSet) 'The following XML file will be saved on your hard disk. 'You can locate it in the local application BIN folder Dim myXmlFile As String = "myCustomers.xml" 'Demonstrate the usage of the WriteXML method 'Write out an XML file that originated as relational data ds1.WriteXml(myXmlFile, XmlWriteMode.IgnoreSchema) 'Demonstrate the usage of the ReadXML method 'Load a 2nd Dataset from the saved XML file Dim ds2 As New System.Data.DataSet() ds2.ReadXml(myXmlFile) 'Demonstrate the usage of the GetXML method 'Extract data from the Dataset in XML format Console.WriteLine(ds2.GetXml()) End Sub End Module | |
Please take a moment and read through the following sample program logic and comments.
In the UseSqlDataAdapter method in Listing 12-4, notice how the Dataset is completely disconnected from its original data provider class (DataAdapter). You will see that the second Dataset, created in the ReadWriteXML method, is not associated with a data provider at all. The discussed Dataset methods are used in the ReadWriteXML method.
Not bad, huh? Now that you've gotten your feet quite wet using these three Dataset methods (WriteXML, ReadXML, and GetXML), you'll move on to examine the topic of the XML Schema. After all, there will be times when you'll want to work with the "defined structure" of your XML data.
The Dataset and the XML Schema
For a moment, let's take a closer look at the COBOL copybook. Why? Please recall reading earlier (in the section "XML Learning Kick Start" in Chapter 4) that an XML Schema is loosely analogous to the mainframe COBOL copybook. Listing 12-5 should help illustrate this point. Basically, this pseudo-code snippet is an example of what a copybook might look like if you were to create one for the SQL Server Northwind.Customers sample table. A DB2 DCLGEN "copybook" would have been slightly different (e.g., DB2 data types, database constraints listed, and so forth).
| |
. . . 000060 05 myCustomer-Detail. 000070 10 CustomerID PIC X(05). 000080 10 CompanyName PIC X(40). 000090 10 ContactName PIC X(30). 000100 10 ContactTitle PIC X(30). 000110 10 FullAddress PIC X(60). 000120 10 City PIC X(15). 000130 10 Region PIC X(15). 000140 10 PostalCode PIC X(10). 000150 10 Country PIC X(15). 000160 10 Phone PIC X(24). 000170 10 Fax PIC X(24). 000180 05 myCustomers-TABLE REDEFINES myCustomer-Detail. 000190 10 myCustomers OCCURS 100 TIMES. 000200 15 myCustomers-ITEM PIC X(268). . . .
| |
Now, please compare the "copybook" example in Listing 12-5 to the XML Schema in Listing 12-6. This XML Schema corresponds to the Northwind.Customers sample SQL Server table.
| Note | I demonstrate how to create the XML Schema momentarily. |
| |
<?xml version="1.0"?> <xs:schema id="NewDataSet" targetNamespace="http://tempuri.org/myCustomers.xsd" xmlns:mstns="http://tempuri.org/myCustomers.xsd" xmlns="http://tempuri.org/myCustomers.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:EnforceConstraints="False"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="myCustomers"> <xs:complexType> <xs:sequence> <xs:element name="CustomerID" type="xs:string" minOccurs="0" /> <xs:element name="CompanyName" type="xs:string" minOccurs="0" /> <xs:element name="ContactName" type="xs:string" minOccurs="0" /> <xs:element name="ContactTitle" type="xs:string" minOccurs="0" /> <xs:element name="Address" type="xs:string" minOccurs="0" /> <xs:element name="City" type="xs:string" minOccurs="0" /> <xs:element name="PostalCode" type="xs:string" minOccurs="0" /> <xs:element name="Country" type="xs:string" minOccurs="0" /> <xs:element name="Phone" type="xs:string" minOccurs="0" /> <xs:element name="Fax" type="xs:string" minOccurs="0" /> <xs:element name="Region" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>
| |
Now do you see the similarity between the COBOL copybook and an XML Schema? Good. I want to show you a very easy way to create an XML Schema (typically a file ending with an .xsd suffix). One of the easiest ways to manually create an XML Schema (rather than type it out by hand) is to do it from within the VS .NET IDE.
Using one of the sample projects (ReadWriteXMLDatasetExampleVB), you can execute the Show All Files feature from within the Solution Explorer window to expose the targeted XML file: myCustomers.xml (see Figure 12-2).
Figure 12-2: The myCustomers.xml file (in the local application bin folder) created earlier in the sample code project ReadWriteXMLDatasetExampleVB
| Cross-Reference | The steps you take to use the Show All Files feature from the Solution Explorer window were first introduced in the section "Using the VS .NET Solution Explorer" in Chapter 6. |
After you select the XML file from the Solution Explorer window, you will be able to view and edit the actual XML from within the VS .NET IDE. You will also notice that there is an XML menu bar option (this only appears when you have the XML file selected). Figure 12-3 shows the option available to create the XML Schema file (using the XML menu bar and selecting XML Create Schema).
Figure 12-3: The Create Schema feature from within the VS .NET IDE
Using this Create Schema feature, you will create a file that has an .xsd suffix. This is your XML Schema file. As shown in Figure 12-4, the XML Schema file myCustomers.xsd has been created in the same folder location as the actual XML file.
Figure 12-4: The XML Schema file located in the local application bin folder
In comparison, using this VS .NET IDE Create Schema feature is certainly preferable than having to type a schema out by hand (remember all of those copy-books you manually created through the years ?).
| |
As great as this VS .NET Create Schema feature is, it too is considered a manual approach, albeit a modern one. You can programmatically create an XML schema using the Dataset method WriteXmlSchema . Two other Dataset methods provided for "accessing" the XML Schema are ReadXmlSchema and GetXmlSchema . These three Dataset methods are practically just as simple to use as the three "XML" Dataset methods used to the read/write the actual XML file. Choose the "schema creation" approach that fits your business need. It is good to be aware of your choices.
| |
I trust that the discussion in this section provided you with a better understanding of the XML Schema. When you work with the .NET Dataset and XML, it is inevitable that you will come across the need to also work with the corresponding XML Schema. I would like to point out that other XML Schema “related topics certainly await you (e.g., Schema inference, Schema annotations, and Schema/data validation concerns). As you continue your .NET retraining , you will find the references provided in the "To Learn More" section at the end of this chapter useful.
I have discussed XML and XML Schemas, both from the perspective of the .NET Dataset. At this point, you might have the impression that you will end up using the .NET Dataset for all of your XML needs. After all, the XML support provided in the .NET Dataset is impressive.
Well, as it turns out, that is not case. There are times when you will need more. For example, you may have a need for more structural support (strong typing, Schema validation, and so forth) for your XML, or perhaps XML query and transformation support (XPath, XSL, and so forth). These needs will exhaust and exceed the features of the .NET Dataset (when it is used alone). Fortunately, the .NET Framework has a full offering, an entire namespace of managed objects available for your advanced XML needs. Let's now switch gears to review the .NET namespace that will serve your extended XML scenarios.
[1] You may find it necessary to increase the value of the console window's Screen Buffer Size Height setting (select Properties Layout tab) to capture the extended output stream produced by these sample projects. I typically increase the console window's Screen Buffer Size Height setting from its low default setting (300) up to a much higher value (9999).
EAN: 2147483647
Pages: 204