C# and Oracle – Importing Data (JSON)

In order to import data into an Oracle 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 number 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. It also has an integrated package manager console, where this package can be added using the following command.

Install-Package Newtonsoft.Json

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. It should be noted that this command will also work in Linux, through a Terminal window.

dotnet add package Newtonsoft.Json

Once added, a ‘using‘ statement for the ‘Newtonsoft.Json.Linq’ namespace needs to be included.

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


    // Connect to database.

catch (Exception e)

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


// 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.


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

// Record count.
int recordCount = 0;

// SQL variables.
string sqlPersonInfo;
OracleCommand sqlInsert;


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

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

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

                    // Bind the parameters to the query.
                    sqlInsert.Parameters.Add("firstname", personFName);
                    sqlInsert.Parameters.Add("lastname", personLName);
                    sqlInsert.Parameters.Add("title", personTitle);

                    // Execute SQL.

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


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


catch (Exception e)

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


    // Close the database connection.


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