ADO.NET and XML: double swords, powerful
Release Date: 4/1/2004
| Update Date: 4/1/2004
Rockford Lhotka
MAGENIC TECHNOLOGIES
June 17, 2002
Please download VBADOXML.EXE.
There are many ways to view and use data today. Traditionally, we use data access technology such as ADO.NET, which provides us with a powerful feature mainly targeting relational data. With ADO and ADO.NET, you can implement interactions with non-relational data, but true focus is a traditional table format data view. Recently, we have seen the rise of XML, and the core of this technology is a layered data representation.
Many years ago, before I contacted the relational database, I used the hierarchical database. In many ways, XML can be said to be an open expression of the hierarchical database, but because XML is based on text, it is possible to use XSL application conversion, plus other standards related to XML, which is far from scalability and performance. Far beyond the hierarchical database.
With ADO.NET, Microsoft not only provides support for traditional table format data views in relational data sources, but also integrates powerful support for XML. The relationship between ADO.NET and XML exists in a certain level in an infrastructure, so we can use these two data technology based on their own application requirements.
In this article, I will discuss the basic knowledge of ADO.NET and XML.
ADO.NET and XML structure
Before writing the code, I want to quickly review the way ADO.NET and XML integration. Then we use the test applications that these two techniques use, apply the concept.
ADO.NET itself can be divided into two parts. A part is a managed provider, responsible for processing communication with data sources. Another part is a dataset, providing a memory area in memory for table format data. Data sets can load data from the managed provider, or you can load directly from our code. In addition, the data set can also load data using XMLReader. The above is shown in Figure 1.
Figure 1. Three ways to load data to data sets.
Regardless of the way it is used, the final result is a data set containing the data table, and the data table contains the data we have loaded. The data table is a table format structure, so the data is represented as a group of rows and columns.
With data sets, we can manipulate data by code, or we can use data binding to connect data to the controls in the user interface (UI). Once the data is filled into the dataset, regardless of the data from Microsoft SQL Server? Or the XML document, there is no difference in behavior. This feature can achieve very powerful features, because it means, we can load data from the table, then save the data to the XML file, we can also load data from the XML file, then store the data to the database in the database.
When using the XMLReader method with an XML to populate the data set, it will automatically generate a definition for XML data, ie XSD. XSD is derived from its structure when the XML document is read. This is very useful because it brings another benefit that you can use the dataset to quickly generate XSD derived from existing XML documents. I really like this benefits, because manual writing XSD is one of the things I don't like to do.
On the other hand, if you want to control the fields you read from the XML file to the data set, you can create your own XSD, use it before reading XML data, to initialize the data set. Then, when XML data is read, only the data matched to the XSD field is read into the data set. Any other data in the XML document will be ignored. It should be noted - if you use a custom XSD when you read data, the dataset will only contain data defined by the XSD. If you subsequently save the data set as XML, only the data defined by this XSD is retained. Any other data in the original XML file will not be included in the output.
It is also important to note that when using XML directly from the data set, the data set does not retain the format setting of the XML document whether there is a predefined XSD. Any spaces, comments, or other non-data elements within the XML document will be lost when the data set is written.
In order to solve data loss, format settings and comments lost these issues, ADO.NET provides another mechanism for using XML.
If you used to use XML in .NET, you must be familiar with the XMLDocument object. XMLDocument reads the XML document, allowing us to do it, then save it back. This object can reserve format settings, comments, and all data.
Microsoft .NET also includes an XMLDATADOCUMENT object. XMLDATADOCUMENT has all functions of XMLDocument, in addition, it understands how to integrate with the ADO.NET dataset. as shown in picture 2.
Figure 2. XMLDATADOCUMENT integration with ADO.NET data set.
This means we can use the data set at the same time as a table format and view data in a hierarchical format through xmldataDocument. Since XSD defines specific data that can use the data set, we can accurately control which data in XMLDATADOCUMENT can be provided through the dataset.
With a data set view, we can use the data binding function to enable the majority of developers who are familiar with the use of table formats to access XML data. The XMLDATADOCUMENT view allows us to operate all XML data, including any data not provided in the data set. In addition, XMLDATADOCUMENT continues to keep spaces, format settings, and comments, so when we save your documents back, we will get files with the same category compared to the reading.
Integration between XMLDATADOCUMENT and datasets is two-way. Once they are lin together, any update to data in the data set will be immediately reflected in XmlDATADOCUMENT. Similarly, any changes to the data in XMLDATADOCUMEN will also be immediately reflected in the data set.
In addition to being able to read XML directly, write XML to the data set, and the XMLDATADOCUMENT is linked together, ADO.NET also provides powerful XML support. After understanding the above background, we can study and write some code to illustrate how ADO.NET is used with XML.
We will build applications based on column articles Ado.Net and You. You can download the code from MSDN.
The application already has a DataGrid, a connection to the PUBS database, and can retrieve and update the data in the Authors table. There are also some buttons that you can use to write data in the table to the file and read the file back to the database table. The files in this example are not XML, but simple text files. We will expand the app to use the XML file.
Back to top
Use XML directly in the data set
DataSet objects include methods that directly read and write XML files. One advantage of these methods is that we can load data from the database and write the data to the XML file. Now let's enhance the app to use these methods. Save an XML to a file
Add a button to Form1 called BTNTOXML. We will write the code behind this button, save any data currently in the DSPUBS data set as an XML file:
Private sub btntoxml_click (Byval Sender as system.Object, _
Byval e as system.eventargs) Handles BTNTOXML.CLICK
DSPUBS.WRITEXML ("c: /authors.xml")
End Sub
XML will automatically generate based on the column names in the data table and will include all data in the data set. If there is more than one DataTable object, the data will be included in the output. Suppose we have loaded the data in the Authors table to the dataset, click this button to generate its contents as follows:
All data in the data set will be included. From this XML, it can be roughly seen in the output mode. XML is simple and straightforward, and there is no information or XSD specific to the data set. If we need the XSD of the data, you can specify the XSD by the following changes:
Private sub btntoxml_click (Byval Sender as system.Object, _
Byval e as system.eventargs) Handles BTNTOXML.CLICK
DSPUBS.WRITEXML ("c: /authors.xml")
DSPUBS.WRITEXML ("C: / Authors with Schema.xml", _
XMLWRITEMODE.WRITESCHEMA
End Sub
Now we have created two files. The first file contains data in a simple XML format, and the second file contains the same data presented in the same format, but the front band has an XSD information defined in the data structure.
Typically, the data set automatically generates XSD. On the other hand, we can also define our XSD and load it into the data set before loading the data. In this case, this XSD will reflect our predetermined definition. Later will then load your own XSD.
In our example, DSPUBS's XSD is automatically generated by the DataAdapter wizard when you create a DataSet object. The following is the specific content of XSD (some text has been wrapped):
Targetnamespace = "http://www.tempuri.org/dspubs.xsd" XMLns: mstns = "http://www.tempuri.org/dspubs.xsd" XMLns = "http://www.tempuri.org/dspubs.xsd" XMLns: xs = "http://www.w3.org/2001/xmlschema" xmlns: msdata = "URN: Schemas Microsoft-Com: XML-MSData "AttributeFormDefault =" Qualified " ElementFormDefault = "Qualified"> The Author data is followed behind the XSD. Note that XSD knows the unique main key value. This is because the DataAdapter wizard is based on the database definition to generate XSD. The XSD derived from the original data will not include such advanced concepts. For example, if you load XML from Authors.xml to a normal DataSet object, then write XML (do not enter this code) by the following code: DIM DSXML AS New DataSet () DSXML.Readxml ("c: /authors.xml") DSXml.writexml ("c: / authors with schema.xml", _ XMLWRITEMODE.WRITESCHEMA) The XSD gets is simpler (some text has been wrapped): Targetnamespace = "http://www.tempuri.org/dspubs.xsd" XMLns: mstns = "http://www.tempuri.org/dspubs.xsd" XMLns = "http://www.tempuri.org/dspubs.xsd" XMLns: xs = "http://www.w3.org/2001/xmlschema" xmlns: msdata = "URN: Schemas Microsoft-Com: XML-MSData "AttributeFormDefault =" Qualified " ElementFormDefault = "Qualified"> Minoccurs = "0" /> Minoccurs = "0" /> Here again, the Author data is followed behind the XSD. Note that the primary key is not mentioned. This type of information is unable to derive from a simple XML data. This XSD only includes basic information that can be inferred directly from the data itself. We can enhance the functionality of the code, not only can write XML and the code with the architecture, you can also write the code itself: private sub btntoxml_click (Byval Sender as system.object, _ Byval e as system.eventargs) Handles BTNTOXML.CLICK DSPUBS.WRITEXML ("c: /authors.xml") DSPUBS.WRITEXML ("C: / Authors with Schema.xml", _ XMLWRITEMODE.WRITESCHEMA) DSPUBS.WRITEXMLSCHEMA ("c: /authors.xsd") End Sub It is also necessary to remember that both the XML and XSD written by the data set do not include a comment or special format setting, nor contain any data outside the data contained in the data set. Read XML from a file The process of reading the XML file to the data set is also simple. You can add another button to Form1 called btnfromxml by using the following code behind the form. Private sub btnfromxml_click (Byval Sender as system.Object, _ Byval e as system.eventargs) Handles btnfromxml.click Dspubs.clear () DSPUBS.READXML ("c: /authors.xml) End Sub The READXML method reads the data from the file and then loads the data to the data set. The Clear method clears any data existing in DSPUBS, which is important because XML reads the data set as the added set of new lines. Since we use the type data set (such as DSPUBS) that uses the DataAdapter wizard (such as DSPUBS), the data set will force the rules such as the unique main key. Therefore, if we try to load data loaded from the XML files to existing data in the data set, it will cause an error in runtime. If our DataSet object is a general data set, its performance will vary. For example, we can write the following code (do not enter this code): DIM DSXML AS New DataSet () DSXML.Readxml ("c: /authors.xml") DSXML.Readxml ("c: /authors.xml") The result is a data set containing all data twice. The dataset will automatically generate the xSD of the data, but the generated XSD does not know the unique main key value. This allows the same data line multiple times. Use the correct XSD We have seen the XML definition (or XSD) used by the data set differently different in behavior. Our control has exceeded whether the data set knows the unique primary key. By predefining the XSD of the data set, we can control which data fields can be read from the source XML file to determine which fields will be written when we save XML. We have been using the DTAPUBS that DSPUBS is built using the DataAPter wizard in the article "ADO.NET and YOU". This means it is a type of data set with predefined XSD. Can we edit this XSD in Visual Studio .NET. Use its built-in XSD designer. DSPUBS.XSD is a file in the Solution Explorer. By double-click open designer, as shown in Figure 3. Figure 3. XSD designer in Visual Studio .NET. The designer displays a key icon next to the AU_ID field, indicating that it is the unique main key. The designer also lists field names and data types that can be loaded into all data in the data set. You can use this designer to add additional fields, delete fields or change the data type of fields. One thing to note is that when we load data from SqlDataAdapter to the data set, the XSD shown here will be ignored. For example, even if we delete a Phone field by editing it, when we re-load data, the Phone field will restore because XSD will be recreated based on SQL definition. However, when we load data from an XML file to a data set, the XSD shown here will be ignored. To learn the specific process, right click on the left side of the Phone field and select Delete from the pop-up menu. This will remove the Phone field from the XSD. Run the application now, then click the from XML button to load the Authors.xml file to the dataset. Note that the Phone field is not displayed in the grid and is not loaded into the data set. This field does not match the XSD, so it is ignored. Now return to BTNTOML code, where there is a line of code written XSD from the data set to a file called Authors.xsd. Visual Studio .NET also uses the XSD designer to edit this file. Select File | Open | File, then select Authors.xsd. This file will open in the same type of designer, we can edit XSD with graphical mode, or edit directly by clicking the XML tab at the bottom of the designer. To make this demonstration, select Delete by right-click on the left side of this field, delete the Phone field. You may have to click the Phone field with a left click to select this field, otherwise the delete operation will delete the entire Authors entity from XSD. After that, save the file. We can create a general data set and load XSD into the data set from files such as Authors.xsd. Add a new button to Form1 named btnxsd and add the following window size variable declaration for the general data set: Private DSXML AS Dataset Then add the following code behind the new button: Private sub btnxsd_click (byval sender as system.object, _ Byval e as system.eventargs) Handles btnxsd.click 'Create a generic dataset DSXML = New Dataset () 'Loading the xsd in otto the dataset DSXML.Readxmlschema ("c: /authors.xsd") 'Loading the xml data 通o the dataset DSXML.Readxml ("c: /authors.xml") 'Bind the grid to this new dataset DataGrid1.datasource = dsxml.tables (0) End Sub The first thing we did is to create a general DataSet object. Unlike the type DSPUBs with associated XSD, this new data set is completely undefined and is empty, no XSD. Then we load the XSD file authors.xsd to the dataset. This makes the data set have the definition of data. In this case, this definition knows the primary key field, but because we delete the Phone field, it does not know the data. Then we load the XML data itself from Authors.xml. This file does include Phone information, but this information is ignored because it does not match the XSD. Next, the DataGrid is bound to this new DataSet object, so the result will be displayed. Figure 4. Data display when using custom XSD to load data. Figure 4 illustrates why we know that we use an automatic derived XSD (ie the XSD created in Visual Studio .NET), or is very important and useful to manually loaded XSD. By controlling the architecture, we can control the loaded data, as well as the data after loading. Back to top Use XMLDATADOCUMENT and Data Sets So far we have been using XML from the data set. Although this is very convenient, it is quite powerful, but there is also a shortcoming. When we mix using XML data sources and non-XML data sources, those techniques are most effective. But if we read and update an existing XML file, we will encounter restrictions. In particular, the data set only loads the XSD architecture as the XML data defined by the data set, ignores all other data in the XML source. When writing xml from the data set, only the data in the data set is written, and any other data in the original XML file will be lost. It may be worse, read the data into the dataset, and then save any comments or special format settings in the original XML file from the data set to the XML file. In order to overcome these limits, we can use XMLDATADOCUMENT with the data set. The XML data will be loaded into the XMLDATADOCUMENT object, which will link to a dataset. We can use XML data directly through XMLDATADOCUMENT, or you can use the data set to use XML data in a table view. Read XML data In Form1, add an Imports statement for XML namespace: Imports system.xml Then add XMLDATADOCUMENT form-level variable declaration: Private xmldoc as xmldatadocument Now, add a button to Form1 named btnxmldoc, which will trigger the process of loading an XML document into XmlDATADOCUMENT through the associated dataset: Private sub btnxmldoc_click (byval sender as system.object, _ Byval e as system.eventargs) Handles btnxmldoc.click Dspubs.clear () XMLDoc = New XMLDATADOCUMENT (DSPUBS) XMLDoc.Load ("C: / Authors By Hand.xml) End Sub First we have to clear the data set to make sure it does not contain any data. Then link the DataSet object to a new XMLDATADOCUMENT object. At this point, any data we are loaded into XmldATADOCUMENT will be reflected in the data set. That is, any data is matched to the XSD defined the data set. Since we use DSPUBS, we know that it has a associated XSD, which covers the Author data we have been using. In order to understand the specific process, we need an XML document that includes some extra data and a comment so that this type of information is reserved. Use Visual Studio .NET to open the Authors.xml file, then edit the file, as shown below: Save this file with the name authorscript.xml. Note that in this new file, each author has a Notes element, there is a comment in XML. If we click the button just added after running the application, the dataset will be linked to new XMLDATADOCUMENT will also reflect these changes immediately. Similarly, any changes to the data within XMLDATADOCUMENT will be reflected by the data set. Write XML data Once we use the links of the data to load data to XmlDATADOCUMENT, you can use any of these two objects. After operating the data, we are likely to save it back to the XML file. If we use the WriteXML method of the dataset object to save the data, we lose any data that is not displayed in the data set, as well as any comments in the document. Therefore, we can save data from XMLDATADOCUMENT back to files, reserve all data, comments, and format settings of the document. Add a button to Form1 named btntoxmldoc with the following code: Private sub btntoxmldoc_click (Byval Sender as system.object, _ Byval e as system.eventargs) Handles BTNTOXMLDOC.CLICK XMLDoc.save ("c: / authors by hand.xml) End Sub This code is designed to run after we click BTNXMLDoc, load data to XMLDATADOCUMENT and its links. The content of XMLDATADOCUMENT will save back to read the XML files from it. If we run the application, load data, edit the fields in the DataGrid, then click the button, the changed data will write back the file. The following XML is the name of the author in the DataGrid, and then updates the result of the XML file: Note, notes and Back to top summary In this article, I discussed basic knowledge using XML in ADO.NET. In the past, traditional data access technology and XML have always had a broken relationship. Using ADO.NET and XMLDATADOCUMENT, we combine these both. We can perform basic operations directly from data intended to write XML, when XML becomes more complicated, we can load it into XmlDATADocument through a link, so you can get the xml and table views of data at the same time.
