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

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

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

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

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

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

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

var rowsUpdated = command.ExecuteNonQuery();

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

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

Console.WriteLine(ex.Message);

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

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

Third, retrieve data from the authors table:

SELECT * FROM authors;

Output:

id  first_name  last_name
--  ----------  ---------
1   Jane        Smith

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.

Was this tutorial helpful?