C# and PostgreSQL – Exporting Data (Text)

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. Text files are one such file format that allows for both of these scenarios. These text files are often called delimited text files because each piece of data is separated by a delimiter.

Below is an example of how C# can be used to export data to a text file called ‘personexport.txt’, from a PostgreSQL database table called ‘person’, which was used in the examples for selectinginserting, updating, deleting, importing (CSV, text, XML and JSON) and exporting data in CSV format. The delimiter used to separate each piece of data in this example is the pipe (|) symbol.

Firstly, a connection to the database is established, the text 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 the text file is opened for writing. The table headers are then added to the text file, followed by the rows of data, one by one.

A rolling seven day backup is also included. This makes a copy of the text 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.txt', 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.

Finally, confirmation of a successful export is provided. A ‘try-catch-finally’ block is used to catch any errors that may occur, as well as tidy up at the end, regardless of whether the export is successful or not.

// Database connection variable.
NpgsqlConnection connect = new NpgsqlConnection(
    "Server=localhost;" +
    "Database=Demo;" +
    "User Id=DemoUN;" +
    "Password=DemoPW");

try
{

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

}
catch (Exception e)
{

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

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

}

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

// Stream writer for text file.
StreamWriter textFile = null;

// 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.
    NpgsqlCommand sqlSelect = new NpgsqlCommand(sqlText, connect);

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

    // Stream writer for text file.
    textFile = new StreamWriter(@exportPath + exportText);

    // Add the headers to the text file.
    textFile.WriteLine(String.Format("{0}|{1}|{2}|{3}|{4}",
        reader.GetName(0), reader.GetName(1), reader.GetName(2),
        reader.GetName(3), reader.GetName(4)));

    // Construct text file data rows.
    while (reader.Read())
    {

        // Add line from reader object to new text file.
        textFile.WriteLine(String.Format("{0}|{1}|{2}|{3}|{4}",
            reader[0], reader[1], reader[2], reader[3], reader[4]));

    }

    // Close the file.
    textFile.Close();

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

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

    // 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, exportBackupText)) ||
        (File.Exists(Path.Combine(exportPath, exportBackupText)) &&
        File.GetLastWriteTime(
            Path.Combine(exportPath, exportBackupText)).Date !=
            today.Date))
    {

        // Copy the text export.
        File.Copy(Path.Combine(exportPath, exportText),
            Path.Combine(exportPath, exportBackupText), true);

    }

    // 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 and text file.
    connect.Close();
    textFile.Close();

}

The text file produced contains the following data. Note that, although dates in a PostgreSQL database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day), they are output to the text file in a day, month, year and time format.

id|firstname|lastname|title|dob
1|Bob|Smith|Mr|20/01/1980 00:00:00
3|Fred|Bloggs|Mr|07/05/1975 00:00:00
4|Alan|White|Mr|20/03/1989 00:00:00
5|Fiona|Bloggs|Mrs|19/05/1985 00:00:00
6|Zoe|Davis|Miss|11/07/1979 00:00:00
7|Tom|Ingram|Mr|04/10/1971 00:00:00
8|Karen|Thomas|Mrs|08/03/1969 00:00:00
9|Samantha|Yates|Miss|27/08/1995 00:00:00

Further Resources