Importing and Exporting XML from a DataSet with C#

XML is the backbone of data communication. Here's how to read and write XML from a DataSet and SqlDataAdapter using C# and .Net

The .Net Framework and ADO.Net both contain strong XML support; in fact, XML forms the backbone of data communication in .Net. One of the features you will almost certainly need to use when dealing with 3rd party data providers is to import or export XML data to and from SQL Server and/or managed code.

.Net provides a DataSet object which is used to query SQL Server databases, but it also has the functionality to read and write XML data, either in the form of a string, text reader object, and XML reader object or an IO stream. In each instance, you will need to call the ReadXml method of the DataSet object or the WriteXml method.

Reading XML from a DataSet

For this example I am going to use the sample CD catalogue data from W3Schools, and display it using an ASP.Net grid view.

Once you have added a Grid View to an ASP document:

<asp:GridView ID="GridView1" runat="server"></asp:GridView>

You only need a small amount of C# code to import the CD catalogue and output the data.

DataSet ds = new DataSet();
ds.ReadXml("c:cd_catalog.xml");

GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

Writing SQL Records to XML from a DataSet

The DataSet object contains a method for writing XML data to disk and can be used to serialise SQL Server data. This example uses the Products table of the Northwind sample database.

string connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="C:SQL Server 2000Sample DatabasesNORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True";
string commandString = "SELECT * FROM Products";

SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString);
DataSet ds = new DataSet();
da.Fill(ds);
ds.WriteXml("c:products.xml");

Of course, in both examples, you can customise the data by sorting, filtering and so on.