SQLite C#: Inserting Data

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

Steps for inserting data into a table using ADO.NET

First, open a new database connection.

Second, construct an INSERT statement that inserts a row into a table.

Next, create a SqliteCommand object that accepts the INSERT statement.

Then, bind values to the parameters of the INSERT statement.

After that, execute the statement by calling the ExecuteNonQuery() method of the SqliteCommand object.

Finally, close the database connection.

Inserting one row into a table

The following program inserts one row into the authors table:

using Microsoft.Data.Sqlite;

var sql = "INSERT INTO authors (first_name, last_name) " +
    "VALUES (@first_name, @last_name)";

var firstName = "Jane";
var lastName = "Doe";

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("@first_name", firstName);
    command.Parameters.AddWithValue("@last_name", lastName);


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

    Console.WriteLine($"The author '{firstName} {lastName}' has been created successfully.");


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

How it works.

First, construct an INSERT statement that inserts a row into the authors table:

var sql = "INSERT INTO authors (first_name, last_name) " +
    "VALUES (@first_name, @last_name)";Code language: C# (cs)

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

var firstName = "Jane";
var lastName = "Doe";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("@first_name", firstName);
command.Parameters.AddWithValue("@last_name", lastName);Code language: C# (cs)

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

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

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

Sixth, display a message indicating that the program has inserted a new row into the authors table successfully:

Console.WriteLine($"The author '{firstName} {lastName}' has been created 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 insert

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 or you’ll encounter an error:

'sqlx' is not recognized as an internal or external command,
operable program or batch file.Code language: C# (cs)

Third, retrieve data from the authors table:

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

Output:

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

The output indicates that the author has been inserted successfully.

Summary

• Call the ExecuteNonQuery() method of the SqliteConnection object to execute an INSERT statement that inserts a new row into a table.