C# and PostgreSQL – Importing Data (JSON)

In order to import data into a PostgreSQL database, it must be in a suitable format. JSON or JavaScript Object Notation files are an alternative file format to CSV, that can sometimes be used for this purpose. JSON is an open standard file format, which uses human readable text for storing and exchanging data.

Below is some sample JSON that contains person information, the same as that used in the previous example for importing data from CSV.

{
  "person": [
    {
      "firstname": "Zoe",
      "lastname": "Davis",
      "title": "Miss",
      "dob": "1979-07-11"
    },
    {
      "firstname": "Tom",
      "lastname": "Ingram",
      "title": "Mr",
      "dob": "1971-10-04"
    },
    {
      "firstname": "Karen",
      "lastname": "Thomas",
      "title": "Mrs",
      "dob": "1969-03-08"
    },
    {
      "firstname": "Samantha",
      "lastname": "Yates",
      "title": "Miss",
      "dob": "1995-08-27"
    }
  ]
}

The following example shows how C# can be used to import this data, from a file called 'personimport.json', into a table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data (CSV, text and XML).

Firstly, a connection to the database is established, the JSON file path is set and a check is made to see if it actually exists. If it does, the JSON is extracted from the file into a variable and parsed, then each set of person information is processed one by one. The individual data elements for a person are extracted and a check is made to see if they contain data, before being inserted into the database, using an SQL ‘Insert‘ statement. Finally, feedback is given as to the number of records added.

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.Linq’ namespace needs to be included.

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

}

// File path.
string filePath = @"C:\demo\personimport.json";

// Check if the JSON file exists.
if (!File.Exists(filePath))
{

    // Message stating JSON file could not be located.
    Console.WriteLine("Could not locate the JSON file.");

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

}

// Variables for person details.
string personFName;
string personLName;
string personTitle;
string personDob;

// Record count.
int recordCount = 0;

// SQL variables.
string sqlPersonInfo;
NpgsqlCommand sqlInsert;

try
{

    // Extract the JSON from the file, then parse it.
    var json = System.IO.File.ReadAllText(filePath);
    var objects = JObject.Parse(json);

    // Process the objects in the JSON.
    foreach (var obj in objects)
    {

        // Check it is the 'person' object.
        if (obj.Key.ToString() == "person")
        {

            // Process the person information.
            foreach (var person in obj.Value)
            {

                // Extract the person information.
                personFName = person["firstname"].ToString();
                personLName = person["lastname"].ToString();
                personTitle = person["title"].ToString();
                personDob = person["dob"].ToString();
                            
                // Check that there is data to insert.
                if (!String.IsNullOrEmpty(personFName) &&
                    !String.IsNullOrEmpty(personLName) &&
                    !String.IsNullOrEmpty(personTitle) &&
                    !String.IsNullOrEmpty(personDob))
                {

                    // Construct the insert statement.
                    sqlPersonInfo = @"
                        INSERT INTO person 
                            (firstname, lastname, title, dob)
                        VALUES 
                            (@firstname, @lastname, @title, @dob)
                    ";

                    // Query text incorporated into SQL command.
                    sqlInsert = new NpgsqlCommand(sqlPersonInfo, connect);

                    // Bind the parameters to the query.
                    sqlInsert.Parameters.AddWithValue("@firstname", personFName);
                    sqlInsert.Parameters.AddWithValue("@lastname", personLName);
                    sqlInsert.Parameters.AddWithValue("@title", personTitle);
                    sqlInsert.Parameters.AddWithValue("@dob",
                        Convert.ToDateTime(personDob));
                    sqlInsert.Prepare();

                    // Execute SQL.
                    sqlInsert.ExecuteNonQuery();

                    // Increment the record count.
                    recordCount += 1;

                }

            }

        }

    }

    // Provide feedback on the number of records added.
    if (recordCount == 0)
    {

        Console.WriteLine("No new person records added.");

    }
    else if (recordCount == 1)
    {

        Console.WriteLine(recordCount + " person record added.");

    }
    else
    {

        Console.WriteLine(recordCount + " person records added.");

    }

}
catch (Exception e)
{

    // Confirm error adding person information and exit.
    Console.WriteLine("Error adding person information.");
    System.Environment.Exit(1);

}
finally
{

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

}

The contents of the ‘person’ table now looks as follows.

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

Further Resources