C# and SQL Server – 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 an SQL Server database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting, importing and exporting data to CSV  and XML formats.

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. Feedback is provided as to the success or failure of the task. It should be noted that for this to work the third party library JSON.NET needs to be used and a ‘using’ directive must be added for ‘Newtonsoft.Json’.

// Database connection variable.
SqlConnection connect = new SqlConnection(
      "Server=localhost\\MSSQLSERVERDEMO; Database=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(0);

}

// 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))
{

   try
   {

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

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

      // Execute SQL and place data in a reader object.
      SqlDataReader 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();

      }
      else
      {

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

      }

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

   }
   catch (Exception e)
   {

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

   }
   finally
   {

      // Close the database connection and JSON file.
      connect.Close();
      jsonFile.Close();

   }

}
else
{

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

}

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"
    }
  ]
}

 

Navigation