C# and SQLite – Updating Data

In order to update data in an SQLite database, the SQL ‘Update’ statement needs to be used. The following example updates the record in the ‘person’ table that was added in the example for inserting data.

Updating data follows the same pattern as inserting data. Firstly, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the SQL statement. One of the parameters is for an ‘id’, so that only the specified record is updated, in this case, the record with an ‘id’ of five. The other parameters are for updating the ‘lastname’ and ‘title’ fields against the record. 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.
string database = @"C:\Demo\DemoDB.db";
string password = @"DemoPW";
SqliteConnection connect;

// Check if the database exists.
if (!File.Exists(database))
{

    // Message confirming incorrect database location.
    Console.WriteLine("Error locating database.");

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

}

// Database connection.
connect = new SqliteConnection("Data Source=" + database + ";" +
                                "Password=" + password);

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

}

try
{

    // Query parameters.
    string lastname = "Bloggs";
    string title = "Mrs";
    int id = 5;

    // Query text incorporated into SQL command.
    var sqlUpdate = connect.CreateCommand();
    sqlUpdate.CommandText = @"
        UPDATE person 
        SET lastname = $lastname, 
            title = $title
        WHERE id = $id
    ";

    // Bind the parameters to the query.
    sqlUpdate.Parameters.AddWithValue("$lastname", lastname);
    sqlUpdate.Parameters.AddWithValue("$title", title);
    sqlUpdate.Parameters.AddWithValue("$id", id);

    // Execute SQL.
    sqlUpdate.ExecuteNonQuery();

    // Confirm successful updating of person information.
    Console.WriteLine("Person information updated successfully.");

}
catch (Exception e)
{

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

}
finally
{

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

}

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

Further Resources

• Environment Class.
• File Class.