How to Connect to SQLite in .NET With C#

This tutorial provides step-by-step instructions on how to connect a .NET application to an SQLite database. Establishing reliable database connectivity is essential, as most applications depend on a data store to insert, retrieve, and manage information efficiently.

In this tutorial, we will use SQLite, a small, fast, and easy-to-use database engine. Thanks to its serverless architecture, it is one of the most popular SQL engines found in nearly every mobile device and many desktop applications.

Why dotConnect for SQLite?

dotConnect for SQLite is a robust data provider designed for all SQLite-related operations. It supports dynamic connector creation, flexible configuration, seamless integration with Visual Studio, and advanced ORM capabilities.

Requirements

What you will need for this tutorial:

Visual Studio 2022: The IDE used in the tutorial. If you do not have it installed, visit the official Microsoft website to download the Community edition, which is free and available to everyone.
dotConnect for SQLite: A high-performance ADO.NET data provider for SQLite with enhanced ORM support and database connectivity features.
Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation.
Sakila database: A sample database used for testing purposes. To use the same database, simply download it and unzip the folder.

Download and activate dotConnect for SQLite

Free 30-day trial version

You can start using dotConnect for SQLite immediately with a free 30-day trial. Choose one of the following installation options:

Installer: Download and install dotConnect for SQLite directly on your machine.
NuGet: Add the Devart.Data.SQLite package to your project via NuGet.

No license key is required for the trial version. You can start exploring the product right away.

Full version

Once you've purchased the full version:

Log in to your Devart profile and navigate to the Products section.
Locate your product and either click its name or hover over it and select Details.
On the License details page, copy your activation key and add it to your connection string using the License Key parameter.

Your dotConnect for SQLite application is now successfully activated and ready for use.

Create a .NET project

To get started with dotConnect for SQLite, we’ll first need to set up a .NET project in Visual Studio. In this tutorial, we'll create a simple console application that connects to a SQLite database. The setup process involves initializing a new project and installing the required NuGet packages.

Let us walk you through the process step by step.

1. Open Visual Studio and select Create a new project.

2. Choose Console App (.NET Core) or Console App (.NET Framework), depending on your target framework, and click Next.

3. Name your project and click Create. For this tutorial, we'll use SqliteTest.

4. In Solution Explorer, right-click the project and select Manage NuGet Packages.

5. Navigate to the Browse tab, enter Devart.Data.SQLite in the search box, and then download and install the package.

Connect using C#

This example demonstrates how to establish a basic connection to a SQLite database using C#. Please note, depending on your requirements, you may need to extend this example by adding functionality such as executing SQL queries or handling various types of exceptions.

static void Main(string[] args) { 
  // Path to the Sakila database file 
  string databasePath = @"path\to\your\sakila.db"; 
 
  // Connection string 
  string connectionString = $"Data Source={databasePath};Version=3;"; 
 
  // Create a new SQLiteConnection object 
  using(SQLiteConnection connection = new SQLiteConnection(connectionString))
{ 
	try { 
  	// Open the connection 
  	connection.Open(); 
  	Console.WriteLine("Connection successful!"); 
	} catch (Exception ex) { 
  	Console.WriteLine($"Connection failed: {ex.Message}"); 
	} 
  } 
 
  Console.WriteLine("Press any key to exit..."); 
  Console.ReadKey(); 
}

Connect using the SQLiteCrypt encryption

The dotConnect for SQLite data provider offers robust support for connecting to encrypted SQLite databases. While the data provider itself does not include a built-in encryption extension, it is fully compatible with databases encrypted using third-party solutions.

To connect to an encrypted SQLite database using dotConnect for SQLite, you need to configure the connection string with the appropriate encryption parameters, such as encryption and password parameters.

If you need to change your database password, use the ChangePassword method. To decrypt the database, simply specify an empty password.

The following example demonstrates how to use the SQLiteCrypt encryption with dotConnect for SQLite to ensure your SQLite database remains protected.

static void Main(string[] args) { 
  // Path to the Sakila database file 
  string databasePath = @"path\to\your\sakila.db"; 
 
  // Connection string with SQLiteCrypt encryption parameters 
  string connectionString = $"Data Source={databasePath};Encryption=SQLiteCrypt;EncryptionLicenseKey=00000-000-0000000-00000;FailIfMissing=false;Password=yourpassword"; 
 
  // Create a new SQLiteConnection object 
  using(SQLiteConnection connection = new SQLiteConnection(connectionString)) { 
	try { 
  	// Open the connection 
  	connection.Open(); 
  	Console.WriteLine("Connection successful!"); 
	} catch (Exception ex) { 
  	Console.WriteLine($"Connection failed: {ex.Message}"); 
	} 
  } 
 
  Console.WriteLine("Press any key to exit..."); 
  Console.ReadKey(); 
}

Connect using the AES256 encryption

Let’s explore the example below to learn how to establish a secure, AES256-encrypted connection to an SQLite database via dotConnect for SQLite.

static void Main(string[] args) { 
  // Path to the Sakila database file 
  string databasePath = @"path\to\your\sakila.db"; 
 
  // Connection string with AES256 encryption parameters 
  string connectionString = $"Data Source={databasePath};Encryption=AES256;FailIfMissing=false;Password=best"; 
 
  // Create a new SQLiteConnection object 
  using(SQLiteConnection connection = new SQLiteConnection(connectionString)) { 
	try { 
  	// Open the connection 
  	connection.Open(); 
  	Console.WriteLine("Connection successful!"); 
	} catch (Exception ex) { 
  	Console.WriteLine($"Connection failed: {ex.Message}"); 
	} 
  } 
 
  Console.WriteLine("Press any key to exit..."); 
  Console.ReadKey(); 
}

Connect using Server Explorer

To connect to your SQLite database using Visual Studio's built-in Server Explorer:

1. Right-click Data Connections and select Add Connection.

2. In the Add Connection dialog that appears, choose SQLite Database as the data source.

3. Enter the path to the database you want to connect to, then click OK.

Once the connection is established, you can use Server Explorer in Visual Studio to browse tables, run queries, and manage data directly within the IDE.

Connect to EF Core using Entity Developer

To connect to an SQLite database via Entity Developer, follow these steps:

1. In Solution Explorer, right-click the project and select Add > New Item.

2. In the Add New Item dialog, go to Installed > C# items > Data, then select Devart EF Core Model, and click Add.

3. In the Create Model Wizard, select Database First and click Next to continue.

4. Next, enter the path to the database you want to connect to and click Next.

5. Select Generate From Database and click Next.

6. Choose the database objects you want to scaffold.

7. Define the naming convention for the property names in the database object. We suggest keeping the default settings this time.

8. On the next page, specify the model properties, including the namespace for the generated classes and the entity container that will manage your data entities.

9. After that, choose the contents of the model diagram. You can use all entities, organize them by database, or select specific items manually. On selecting, click Next.

10. Pick a template that defines how your objects will be generated. Let's use the default settings for this tutorial. Click Next.

11. Your model is ready. Click Finish to close the wizard.

The model opens. You can work with the diagram and edit its fields and relations. It also allows you to generate SQL code for altering the database, or you can update the database directly from the model.

This guideline was tailored for Visual Studio. However, if you prefer using VS Code, JetBrains Rider, or another IDE, consider working with Entity Developer as a standalone application to manage your models efficiently.

Finally, open a C# Console application and enter the following code. In our example, the database connection properties are defined within the DbContext class.

static void Main(string[] args) { 
  // Create an instance of your DbContext 
  using(var context = new SakilaContext()) { 
	try { 
  	// Attempt to open the connection 
  	context.Database.OpenConnection(); 
  	Console.WriteLine("Connection successful!"); 
	} catch (Exception ex) { 
  	Console.WriteLine($"Connection failed: {ex.Message}"); 
	} finally { 
  	// Close the connection 
  	context.Database.CloseConnection(); 
	} 
  } 
 
  Console.WriteLine("Press any key to exit..."); 
  Console.ReadKey(); 
}

Connect to EF Core using Scaffold-DbContext

1. Ensure that EF Core tools are installed in your development environment. If they are not already installed, you can add them globally using .NET CLI with the following command:

dotnet tool install --global dotnet-ef

2. Install Scaffold-DbContext using the Package Manager Console in Visual Studio.

Install-Package Microsoft.EntityFrameworkCore.Tools

3. Once installed, you can use the Scaffold-DbContext command to generate the DbContext and entity classes for your SQLite database. Run the following command in the Package Manager Console:

Scaffold-DbContext "DataSource=sakila.db;LicenseKey=**********" -provider 
Devart.Data.SQLite.EFCore -OutputDir Models

After running the command, Entity Framework Core will generate a DbContext class and the corresponding entity classes in the specified output directory (e.g., Models).

Be advised to review the generated code to ensure it accurately reflects your database schema.

Video tutorial: How to connect a .NET console application to an SQLite database

Conclusion

This article enables you to harness dotConnect for SQLite's powerful integration with SQLite databases. Discover features like on-the-fly connector creation and flexible configuration with or without Entity Framework Core and Entity Developer. But wait, there's more! Unleash advanced ADO.NET integration through Visual Studio and enhanced ORM support. Experience dotConnect for SQLite's capabilities firsthand — download your free trial today!