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

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.

To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.

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.

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.

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

Entity Developer allows you to visually design and generate EF Core models, making database application development faster, easier, and more efficient. If you don't have it already installed, close your Visual Studio instance, download Entity Developer, and install it following the on-screen instructions.

Follow the detailed illustrated guide to create your database model using Entity Developer. When this process is complete, the model you created 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.

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!