How To Persist an ADO.NET DataSet as XML by Using Visual Basic .NET

Article translations Article translations
Article ID: 308064 - View products that this article applies to.
This article was previously published under Q308064
Expand all | Collapse all

On This Page

SUMMARY

This article demonstrates how to persist an ADO.NET DataSet object to XML.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual Studio .NET
  • ADO.NET fundamentals and syntax
  • XML fundamentals

Description of the Technique

You can use the WriteXml method to write Extensible Markup Language (XML) schema and data from the DataSet object. The XML data is written to a file, a Stream class, an XmlWriter class, or a TextWriter class. You can use one of two sets of overloaded methods for WriteXml, depending on your needs. The first set of four overloaded methods takes just one parameter, and the second set of four overloaded methods takes an additional parameter (XmlWriteMode) along with one of the above-mentioned parameters. Each of these methods is described in this section.

To write the current schema and data for the DataSet to the specified file, use the following code:
Overloads Public Sub WriteXml(String)
				
To write the current schema and data for the DataSet, use the specified TextWriter class. The TextWriter class is designed for character output.
Overloads Public Sub WriteXml(TextWriter)
				
To write the current schema and data for the DataSet, use the specified System.IO.Stream. The Stream class is designed for byte input and output.
Overloads Public Sub WriteXml(Stream)
				
To write the current schema and data for the DataSet to the specified XmlWriter, use the following code. This provides a fast, non-cached, forward-only method to generate streams or files that contain XML data to conform to the World Wide Web Consortium (W3C) XML 1.0 specification and the namespaces in the XML specification.
Overloads Public Sub WriteXml(XmlWriter)
				
The XmlWriteMode enumeration specifies how to write XML data and schema from the DataSet. XmlWriteMode includes the following options:
  • DiffGram: Writes the entire DataSet as a DiffGram.
  • IgnoreSchema: Writes the current contents of the DataSet as XML data, without an XML Schema Definition language (XSD) schema.
  • WriteSchema: Writes the current contents of the DataSet as XML data with the relational structure as inline XSD schema.

Create Project and Add Code

The following sample creates a DataSet from the customer table in the Northwind database and uses the WriteXml method to persist the DataSet into XML. This sample demonstrates how to use two of the commonly used overloaded versions of WriteXml. For other examples, refer to MSDN for individual overload topics of this method.
  1. Start Visual Studio .NET.
  2. Create a new Windows Application project in Visual Basic .NET.
  3. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
  4. Place two Command buttons on Form1. Change the Name property of the first button to btnWriter and its Text property to Writer.

    Change the Name property of the second button to btnFile and its Text property to File.
  5. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code.
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    					
  6. Copy and paste the following code in the code window after the "Windows Form Designer generated code" region:
    Private Sub btnWriter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnWriter.Click
        Dim myConnectionString As String _
            = "User ID=sa;password=sa;Initial Catalog=Northwind;Data Source=mySQLServer"
        Dim mySelectQuery As String _
            = "Select * From Customers"
        Dim myXMLfile As String = "c:\mySchema.xml"
     
            Dim con As New SqlConnection(myConnectionString)
            Dim daCust As New SqlDataAdapter(mySelectQuery, con)
            Dim ds As New DataSet()
            daCust.Fill(ds, "Cust")
     
            Dim myFileStream As New System.IO.FileStream _
               (myXMLfile, System.IO.FileMode.Create)
            Dim MyXmlTextWriter As New System.Xml.XmlTextWriter _
               (myFileStream, System.Text.Encoding.Unicode)
        Try
            'Write the XML along with the inline schema (default).
            ds.WriteXml(MyXmlTextWriter, XmlWriteMode.WriteSchema)
            'Write the XML only.
            'ds.WriteXml(MyXmlTextWriter, XmlWriteMode.IgnoreSchema)
            'Write the XML as a DiffGram.
            'ds.WriteXml(MyXmlTextWriter, XmlWriteMode.DiffGram)
            MsgBox("Save complete")
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            MyXmlTextWriter.Close()
            myFileStream.Close()
        End Try
    End Sub
     
    Private Sub btnFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnFile.Click
        Dim myConnectionString As String _
            = "User ID=sa;password=sa;Initial Catalog=Northwind;Data Source=mySQLServer"
        Dim mySelectQuery As String _
            = "Select * From Customers"
        Dim myXMLfile As String = "c:\mySchema.xml"
     
            Dim con As New SqlConnection(myConnectionString)
            Dim daCust As New SqlDataAdapter(mySelectQuery, con)
            Dim ds As New DataSet()
            daCust.Fill(ds, "Cust")
      
        Try
            'Write the XML along with the inline schema (default).
            ds.WriteXml(MyXmlFile, XmlWriteMode.WriteSchema)
            'Write the XML only.
            'ds.WriteXml(MyXmlFile, XmlWriteMode.IgnoreSchema)
            'Write the XML as a DiffGram.
            'ds.WriteXml(MyXmlFile, XmlWriteMode.DiffGram)
            MsgBox("Save complete")
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    					
  7. Modify the connection string (myConnectionString) and XML file path (myXMLfile) as appropriate for your environment.
  8. Save your project.
  9. From the Debug menu, click Start, and run your project.
  10. Click any of the buttons to persist the data into the file.
  11. Open your browser, and open the XML file. Notice that the data in the DataSet has been persisted successfully in XML format based on the XmlWriteMode that you specified.

Notes

  • To write only the XML schema, you can use the WriteXmlSchema method.
  • To get only the XML representation of the data in the DataSet, instead of persisting it onto a stream or file, you can use the GetXml method.

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
262450 How To A C++ Sample of ADO Recordset XML Persistence
309702 How To Read XML Data into a DataSet by Using Visual Basic .NET
For more information on ADO.NET objects and syntax, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation:
Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx

Properties

Article ID: 308064 - Last Review: May 13, 2007 - Revision: 2.3
APPLIES TO
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbhowtomaster kbio kbsqlclient kbsystemdata KB308064

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com