SQLite C#: Updating Data

Summary: in this tutorial, you will learn how to update data in an SQLite table from a C# program using ADO.NET.

Steps for updating data into an SQLite table using ADO.NET

Step 1. Open a new database connection.

Step 2. Construct an UPDATE  statement that updates one or more rows in a table.

Step 3. Create an SqliteCommand object that accepts the UPDATE statement.

Step 4. Bind values to the parameters of the UPDATE statement.

Step 5. Execute the statement by calling the ExecuteNonQuery() method of the SqliteCommand object.

Step 6. Close the database connection.

Updating data in a table

The following program updates the last name of the author id 2 to Smith:

using Microsoft.Data.Sqlite;


var sql = "UPDATE authors SET last_name = @last_name WHERE id = @id";

var lastName = "Smith";
var authorId = 1;

try
{
    // Open a new database connection
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();


    // Bind parameters values
    using var command = new SqliteCommand(sql, connection);

    command.Parameters.AddWithValue("@last_name", lastName);
    command.Parameters.AddWithValue("@id", authorId);


    // Execute the UPDATE statement
    var rowInserted = command.ExecuteNonQuery();

    Console.WriteLine($"The author has been updated successfully.");


}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

How it works.

First, construct an UPDATE statement that updates the last name of the author:

var sql = "UPDATE authors SET last_name = @last_name WHERE id = @id";Code language: C# (cs)

Second, declare and initialize variables for storing the author’s last name and id:

var lastName = "Smith";
var authorId = 1;Code language: C# (cs)

Third, open a database connection to the C:\db\pub.db file:

using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
connection.Open();Code language: C# (cs)

Fourth, create a new SqliteCommand object and bind values to the corresponding parameters:

using var command = new SqliteCommand(sql, connection);

command.Parameters.AddWithValue("@last_name", lastName);
command.Parameters.AddWithValue("@id", authorId);Code language: C# (cs)

Fifth, execute the UPDATE statement by calling the ExecuteNonQuery() method of the SqliteCommand object:

var rowsUpdated = command.ExecuteNonQuery();Code language: C# (cs)

The ExecuteNonQuery() method returns the number of rows updated.

Sixth, display a message indicating that the program has updated the author successfully:

Console.WriteLine($"The author has been updated successfully.");Code language: C# (cs)

Finally, show the error message if there is any exception occurred during the insertion:

Console.WriteLine(ex.Message);Code language: C# (cs)

Verifying the update

First, open the terminal on the Unix-like systems or Command Prompt on Windows.

Second, launch the sqlite3 command to connect to the pub.db file located in the C:\db directory (or any directory of your choice)

sqlite3 c:\db\pub.dbCode language: C# (cs)

Note that sqlite3 program needs to be in the PATH environment variable:

Third, retrieve data from the authors table:

SELECT * FROM authors;Code language: C# (cs)

Output:

id  first_name  last_name
--  ----------  ---------
1   Jane        SmithCode language: C# (cs)

The output indicates that the author has been updated successfully.

Summary

• Call the ExecuteNonQuery() method of the SqliteConnection object to execute an UPDATE statement that updates data in a table.