SQLite C#: Connecting to SQLite databases

Summary: in this tutorial, you will learn how to connect to an SQLite database using ADO.NET in a C# program.

Creating a new project

First, launch Visual Studio and create a new Console App project with a specific name for example SQLiteDemo.

Second, right-click the project name and select the Manage Nuget Packages... menu item

Third, search for Microsoft.Data.Sqlite and click the Install button to download it to your project.

Connecting to an SQLite database

If you open a connection to an SQLite database file that does not exist, the program creates the database file automatically and opens a new connection.

If the SQLite file already exists, the program creates a new connection to the SQLite database file.

Here are the steps for connecting to an SQLite database file using ADO.NET:

First, create a new instance of the SqliteConnection class:

var connection = new SqliteConnection("Data Source=databaseFile");Code language: C# (cs)

Replace the databaseFile with the actual path to the SQLite database file.

Second, open a connection by calling the Open() method of the SqliteConnection object:

connection.Open();Code language: C# (cs)

Third, after completing interacting with the SQLite database, close the database connection by calling the Close() method:

connection.Close();Code language: C# (cs)

The following shows the complete code:

var connection = new SqliteConnection("Data Source=databaseFile");
connection.Open();

// interacting with the database 
// ...

connection.Close();Code language: C# (cs)

If you use the using statement, you do not need to call the Close() method explicitly to close the database connection:

using var connection = new SqliteConnection("Data Source=databaseFile");
connection.Open();

// interacting with the database 
// ...

// automatically close the database connection hereCode language: C# (cs)

When you connect to an SQLite database, exceptions may occur during the connection process. To handle the exceptions properly, you need to wrap the code inside a try-catch block:

try
{
    using var connection = new SqliteConnection("Data Source=databaseFile");
    connection.Open();

    // Interacting with the database 
    // ...

} catch (SqliteException e) {
    // Display the exception
    Console.WriteLine(e.Message);
}Code language: C# (cs)

The following program shows how to connect to the C:\db\pub.db database file:

using Microsoft.Data.Sqlite;

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

    Console.WriteLine("Connected to the SQLite database!");

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

Notice that the directory C:\db must exist before you run the program or you’ll encounter an error.

When you run the program and no exception occurs, you’ll see the following message:

Connected to the SQLite database!Code language: C# (cs)

Summary

• Create a new instance of the SqliteConnection class to represent a connection to an SQLite database file.
• Call the Open() method of the SqliteConnection object to open a new connection to the database.
• Always call the Close() method to close the database connection if you no longer use it.
• To properly close the database connection, create the SqliteConnection object in a using statement.