C# and Oracle – Exporting Data (XML)

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. XML or eXtensible Markup Language files are an alternative file format to CSV, that can sometimes be used for both of these scenarios.

Below is an example of how C# can be used to export data to an XML file called ‘personexport.xml’, from an Oracle database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting, importing (CSV, text, XML and JSON) and exporting data (CSV and text).

Firstly, a connection to the database is established, the XML file path and name are set and a check is made to see if the path actually exists. If it does, a query is executed to extract the data from the database and if data is returned, the XML file is opened for writing. The contents of the file is then constructed.

A rolling seven day backup is also included. This makes a copy of the XML file that has just been created, giving it a name that includes the index number for the day of the week, along with the day itself, for example, 'personexport-1-monday.xml', for the backup on a Monday. Here, Sunday is classed as the first day of the week, with an index value of zero. Note that the backup is only done for the first time that this is run in a given day. Backups are then overwritten each week.

Feedback is provided as to the success or failure of the task. It should be noted that in order for this to work, there must be a ‘using‘ statement for the ‘System.Xml’ namespace.

// Database connection variable.
OracleConnection connect = new OracleConnection(
    "Data Source=localhost:1521/Demo;" + 
    "User Id=DemoUN; Password=DemoPW");

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.");
    System.Environment.Exit(1);

}

// Export path and file.
string exportPath = @"C:\demo\";
string exportXml = "personexport.xml";

// Check to see if the file path exists.
if (!Directory.Exists(exportPath))
{

    // Display a message stating file path does not exist.
    Console.WriteLine("File path does not exist.");

    // Stop program execution.
    System.Environment.Exit(1);

}

try
{

    // Query text.
    string sqlText = @"
        SELECT id, firstname, lastname, title, dob 
        FROM person 
        ORDER BY id
    ";

    // Query text incorporated into SQL command.
    OracleCommand sqlSelect = new OracleCommand(sqlText, connect);

    // Execute SQL and place data in a reader object.
    OracleDataReader reader = sqlSelect.ExecuteReader();

    // If data has been returned, do the export.
    if (reader.HasRows)
    {

        // Create the XML file.
        using (XmlTextWriter writer = 
        new XmlTextWriter(exportPath + exportXml, System.Text.Encoding.UTF8))
        {

            // Set the formatting for the document.
            writer.Formatting = Formatting.Indented;
            writer.Indentation = 4;

            // Add the declaration for the document.
            writer.WriteStartDocument();

            // Open the root element.
            writer.WriteStartElement("people");

            // Process the rows of data.
            while (reader.Read())
            {

                // Open the person element.
                writer.WriteStartElement("person");

                // Add the person details.
                writer.WriteElementString("id", reader.GetValue(0).ToString());
                writer.WriteElementString("firstname", reader.GetValue(1).ToString());
                writer.WriteElementString("lastname", reader.GetValue(2).ToString());
                writer.WriteElementString("title", reader.GetValue(3).ToString());
                writer.WriteElementString("dob", reader.GetDateTime(4).ToString("dd/MM/yyyy"));

                // Close the person element.
                writer.WriteEndElement();

            }

            // Close the root element.
            writer.WriteEndElement();

            // Close the XML document.
            writer.WriteEndDocument();

        }

        // Today's date.
        DateTime today = DateTime.Now;

        // Construct the backup file name.
        string exportBackupXml = exportXml.Substring(0, exportXml.Length - 4) +
            "-" + (int)today.DayOfWeek + "-" +
            today.DayOfWeek.ToString().ToLower() + ".xml";

        // Check if the backup file does not exist, or if it does, check that
        // today's date is different from the last modified date.
        if (!File.Exists(Path.Combine(exportPath, exportBackupXml)) ||
            (File.Exists(Path.Combine(exportPath, exportBackupXml)) &&
            File.GetLastWriteTime(
                Path.Combine(exportPath, exportBackupXml)).Date !=
                today.Date))
        {

            // Copy the xml export.
            File.Copy(Path.Combine(exportPath, exportXml),
                Path.Combine(exportPath, exportBackupXml), true);

        }

    }
    else
    {

        // Message stating no data to export.
        Console.WriteLine("There is no data to export.");
        System.Environment.Exit(1);

    }

    // Message stating export successful.
    Console.WriteLine("Data export successful.");

}
catch (Exception e)
{

    // Message stating export unsuccessful.
    Console.WriteLine("Data export unsuccessful.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The XML file produced contains the following data.

<?xml version="1.0" encoding="utf-8"?>
<people>
    <person>
        <id>1</id>
        <firstname>Bob</firstname>
        <lastname>Smith</lastname>
        <title>Mr</title>
        <dob>20/01/1980</dob>
    </person>
    <person>
        <id>3</id>
        <firstname>Fred</firstname>
        <lastname>Bloggs</lastname>
        <title>Mr</title>
        <dob>07/05/1975</dob>
    </person>
    <person>
        <id>4</id>
        <firstname>Alan</firstname>
        <lastname>White</lastname>
        <title>Mr</title>
        <dob>20/03/1989</dob>
    </person>
    <person>
        <id>5</id>
        <firstname>Fiona</firstname>
        <lastname>Bloggs</lastname>
        <title>Mrs</title>
        <dob>19/05/1985</dob>
    </person>
    <person>
        <id>6</id>
        <firstname>Zoe</firstname>
        <lastname>Davis</lastname>
        <title>Miss</title>
        <dob>11/07/1979</dob>
    </person>
    <person>
        <id>7</id>
        <firstname>Tom</firstname>
        <lastname>Ingram</lastname>
        <title>Mr</title>
        <dob>04/10/1971</dob>
    </person>
    <person>
        <id>8</id>
        <firstname>Karen</firstname>
        <lastname>Thomas</lastname>
        <title>Mrs</title>
        <dob>08/03/1969</dob>
    </person>
    <person>
        <id>9</id>
        <firstname>Samantha</firstname>
        <lastname>Yates</lastname>
        <title>Miss</title>
        <dob>27/08/1995</dob>
    </person>
</people>

Further Resources