C# and PostgreSQL – Exporting Data (JSON)
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. JSON or JavaScript Object Notation 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 a JSON file called ‘personexport.json’, from a PostgreSQL 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, text and XML).
Firstly, a connection to the database is established, the JSON 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 JSON 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 JSON 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.json', 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, the package 'Newtonsoft.Json' needs to be added to the project. This can be done in a couple of different ways, depending on what Integrated Development Environment (IDE) is being used. Visual Studio incorporates NuGet Package Manager, which allows for packages to be searched for and installed. For IDEs that don't have a built in package manager, PowerShell can be used. The following command can be used to install the above mentioned package. Before running this command it is necessary to navigate to the folder where the project resides.
dotnet add package Newtonsoft.Json
Once added, a ‘using‘ statement for the ‘Newtonsoft.Json’ namespace needs to be included, along with 'System.Data'.
// 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 exportJson = "personexport.json"; // Stream writer for JSON file. StreamWriter jsonFile = 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(); // If data has been returned, do the export. if (reader.HasRows) { // Stream writer for JSON file. jsonFile = new StreamWriter(@exportPath + exportJson); // Add reader to data table object. var dataTable = new DataTable(); dataTable.Load(reader); // String for JSON. string jsonString = string.Empty; // Wrapper object for JSON. var collectionWrapper = new { person = dataTable }; // Convert to JSON. jsonString = JsonConvert.SerializeObject(collectionWrapper, Formatting.Indented); // Add JSON to the file. jsonFile.Write(jsonString); // Flush the internal buffer. jsonFile.Flush(); // Today's date. DateTime today = DateTime.Now; // Construct the backup file name. string exportBackupJson = exportJson.Substring(0, exportJson.Length - 5) + "-" + (int)today.DayOfWeek + "-" + today.DayOfWeek.ToString().ToLower() + ".json"; // 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, exportBackupJson)) || (File.Exists(Path.Combine(exportPath, exportBackupJson)) && File.GetLastWriteTime( Path.Combine(exportPath, exportBackupJson)).Date != today.Date)) { // Copy the JSON export. File.Copy(Path.Combine(exportPath, exportJson), Path.Combine(exportPath, exportBackupJson), 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 and JSON file. connect.Close(); jsonFile.Close(); }
The JSON file produced contains the following data.
{
"person": [
{
"id": 1,
"firstname": "Bob",
"lastname": "Smith",
"title": "Mr",
"dob": "1980-01-20T00:00:00"
},
{
"id": 3,
"firstname": "Fred",
"lastname": "Bloggs",
"title": "Mr",
"dob": "1975-05-07T00:00:00"
},
{
"id": 4,
"firstname": "Alan",
"lastname": "White",
"title": "Mr",
"dob": "1989-03-20T00:00:00"
},
{
"id": 5,
"firstname": "Fiona",
"lastname": "Bloggs",
"title": "Mrs",
"dob": "1985-05-19T00:00:00"
},
{
"id": 6,
"firstname": "Zoe",
"lastname": "Davis",
"title": "Miss",
"dob": "1979-07-11T00:00:00"
},
{
"id": 7,
"firstname": "Tom",
"lastname": "Ingram",
"title": "Mr",
"dob": "1971-10-04T00:00:00"
},
{
"id": 8,
"firstname": "Karen",
"lastname": "Thomas",
"title": "Mrs",
"dob": "1969-03-08T00:00:00"
},
{
"id": 9,
"firstname": "Samantha",
"lastname": "Yates",
"title": "Miss",
"dob": "1995-08-27T00:00:00"
}
]
}