C# XML data export from MySQL

I originally wrote this in C# at work under Visual Studio to connect to a Microsoft SQL Server on my workstation. One of the tables contained 117 records of archived articles for the web site. We needed to extract the data and output it into an XML file. Once I got it working I decided to see how well it would compile and run under Mono, an open source development platform based on the .NET framework, and have it connect instead to a MySQL database.

In order to have my .NET application connect to MySQL instead of the Microsoft SQL Server I needed to download and install the MySQL Connector/Net driver. The MySQL Connector/Net is a fully-managed ADO.NET driver written in 100% pure C#. It was then simply a case of adding the new MySQL Namespace to the code and substituting the different Microsoft specific classes for the MySQL ones.

This is the resulting code,

// Filename: ExportXMLData.cs

using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ExportXMLData
{
  class ExportXML
  {
    static void Main(string[] args)
    {
      ExportXML exportXML = new ExportXML();
      exportXML.Run();
    }
                
    private void Run()
    {
      // Change the variables to reflect values needed for
      // your computer and database properties.
      string Database = "";
      string Server = "localhost";
      string User = "";
      string Pass = "";
      string TableName = "";
      string XMLRootNodeName = "Root";
      string OutputFileName = "output.xml";

      string conn = 
	"Database=" + Database + ";" + 
	"Server=" + Server + ";" +
	"Uid=" + User + ";" +
	"Pwd=" + Pass;

      MySqlConnection connection = new MySqlConnection(conn);
      MySqlDataAdapter adapter = new MySqlDataAdapter();
      adapter.TableMappings.Add("Table", TableName);
      connection.Open();
      MySqlCommand query = new MySqlCommand("SELECT * FROM "
					    + TableName, connection);
      query.CommandType = CommandType.Text;
      adapter.SelectCommand = query;
      DataSet ds = new DataSet(XMLRootNodeName);
      adapter.Fill(ds);
      connection.Close();

      ds.WriteXml(OutputFileName, XmlWriteMode.WriteSchema);
    }
  }
}

You will need to compile the code as follows so it finds the necessary libraries,

mcs ExportXMLData.cs -r System.Data -r MySql.Data

You can then run the C# program with,

mono ExportXMLData.exe

The result should be an XML file in your working directory containing your database table structure and data.

In a later post I will show you how you can use XSL Transformations (XSLT) to apply a stylesheet and change the display formatting of the XML file itself.