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

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

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

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

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

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

var rowInserted = command.ExecuteNonQuery();

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

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

Console.WriteLine(ex.Message);

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

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.

Third, retrieve data from the authors table:

SELECT * FROM authors;

Output:

id  first_name  last_name
--  ----------  ---------
1   Jane        Doe

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.

Was this tutorial helpful?