C# and Oracle – Inserting Data

In order to insert data into an Oracle database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in a ‘try-catch-finally’ block to catch any errors that may arise and close the database connection at the end.

// Database connection variable.
OracleConnection connect = new OracleConnection(
    "Data Source=localhost:1521/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(1);

}

try
{

    // Query parameters.
    string firstname = "Fiona";
    string lastname = "Jones";
    string title = "Miss";
    DateTime dob = new DateTime(1985, 5, 19);

    // Query text.
    string sqlText = @"
        INSERT INTO person 
               (firstname, lastname, title, dob) 
        VALUES (:firstname, :lastname, :title, :dob)
    ";

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

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

    // Execute SQL.
    sqlInsert.ExecuteNonQuery();

    // Confirm successful addition of person information.
    Console.WriteLine("Person information added successfully.");

}
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
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Jones Miss 1985-05-19

Further Resources