How to connect SQLite database in .NET application

This tutorial provides a deep dive into connecting your .NET application to an SQLite database.

We will be using SQLite, a small, fast, self-contained database engine. Due to its serverless nature, it is the most popular SQL engine and is used on all mobile phones and most computers. Database connectivity is important because no application or solution is complete without some sort of data store, so we can insert and retrieve information as we see fit.

Requirements

What you will need for this tutorial:

• Visual Studio 2022: our IDE of choice. If you do not have it on your machine, go to the official website to download and install it. We will be using the community version, so you can get it as well.
• dotConnect for SQLite: a feature-rich ADO.NET provider with Entity Framework, NHibernate, and LinqConnect support.
• Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation.
• Sakila database: a sample database that for learning and testing. Please download the folder and unzip the file to use it.

Installation and configuration of dotConnect for SQLite

The first thing we will do is set up a new .NET Application project.

1. Open the Visual Studio you installed and click Create New Project. In the Search Template field, type ASP.NET Core Web Application and click the corresponding search result.

2. Give your project a name. For example, ours is going to be called SQLite_ASP.NET.

3. Select .NET 8 as the target framework and click Create.

4. The next step is to install dotConnect for SQLite in our project. To do that, click Tools, point to NuGet Package Manager, and click Manage NuGet Packages for Solution.

5. The NuGet Package Manager page opens. Click Browse, search for Devart.Data.Sqlite, and select it once found. Then, select your project and click Install.

6. After installing the NuGet package, proceed to activate the trial version or enter your license:

• Visit the dotConnect for SQLite download page.
• Download the dcsqlite.exe installer.
• Execute the downloaded file and follow the instructions to install dotConnect for SQLite on your machine.

This process not only installs dotConnect for SQLite but also ensures that all features are activated and available for use in your project. Also, please note that registered users download the full version from their account, not the trial one.

7. Run the application by pressing F5 to open the scaffolded web page.

8. Find the sqlite-sakila-db folder that we have previously downloaded at the requirements phase. Move it into our project to make it look like this:

9. Right-click your project, point to Add and select Class. This way, we can set up the database connection class.

10. We are going to name it DatabaseConnectionClass. We will use this very class to simulate connecting to our application using a SQLite database.

For this tutorial, we will use the Actor table from the sakila database. The table has the following schema:

| ACTOR_ID: number(22)
| FIRST_NAME: varchar(45)
| LAST_NAME: varchar(45)
| LAST_UPDATE: timestamp

We can create a class based on this information. Thus, right-click your solution again and add a class named Actor.cs. Copy the code below and paste it to the newly created class:

public class Actor
{
    public int ActorId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime LastUpdate { get; set; }
}

Connect to SQLite

There are two ways we can connect to SQLite:

1. Run-time creation

Let us add a method to our DatabaseConnectionClass. To do that, copy this code and paste it into your class:

public static List SQLite_RunTime_Connection()
{
    List actors = new List();
    try
    {
        // Open a connection to the SQLite database
        string connectionString = @"Data Source= ..\SQLite_ASP.NET\sqlite-sakila-db\sakila.db";
        Devart.Data.SQLite.SQLiteConnection sqLiteConnection = new Devart.Data.SQLite.SQLiteConnection();
        sqLiteConnection.ConnectionString = connectionString;
        sqLiteConnection.Open();
        var command = "SELECT * FROM actor LIMIT 10";
        var results = new Devart.Data.SQLite.SQLiteCommand(command, sqLiteConnection);
        using (Devart.Data.SQLite.SQLiteDataReader reader = results.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string firstname = reader.GetString(1);
                    string lastname = reader.GetString(2);
                    DateTime lastupdate = reader.GetDateTime(3);
                    Actor actor = new Actor();
                    actor.ActorId = id;
                    actor.FirstName = firstname;    
                    actor.LastName = lastname;
                    actor.LastUpdate = lastupdate;
                    actors.Add(actor);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    return actors;
}

We have introduced a method that connects to our sample database, runs a query to return the first 10 records from the Actors table, and then returns a list of those records.

Don't forget to add the using Devart.Data.SQLite statement at the top of your file.

2. Using connection string builder

You can set up a connection by assigning values to several properties. We will use the SQLiteConnectionStringBuilder class for this. In your DatabaseConnectionClass, add the following code:

public static List SQLite_String_Builder()
{
    List actors = new List();
    try
    {
        // Open a connection to the SQLite database
        Devart.Data.SQLite.SQLiteConnectionStringBuilder connSB = new Devart.Data.SQLite.SQLiteConnectionStringBuilder();
        connSB.DataSource = @"..\SQLite_ASP.NET\sqlite-sakila-db\sakila.db";
        Devart.Data.SQLite.SQLiteConnection sqLiteConnection = new Devart.Data.SQLite.SQLiteConnection(connSB.ToString());
        sqLiteConnection.Open();
        var command = "SELECT * FROM actor LIMIT 10";
        var results = new Devart.Data.SQLite.SQLiteCommand(command, sqLiteConnection);
        using (Devart.Data.SQLite.SQLiteDataReader reader = results.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string firstname = reader.GetString(1);
                    string lastname = reader.GetString(2);
                    DateTime lastupdate = reader.GetDateTime(3);
                    Actor actor = new Actor();
                    actor.ActorId = id;
                    actor.FirstName = firstname;
                    actor.LastName = lastname;
                    actor.LastUpdate = lastupdate;
                    actors.Add(actor);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    return actors;
}

We have essentially implemented the same functionality, but this time, we specified different parameters for our connection, including Locking and ConnectionTimeout. Now, we need to visualize the data we have just retrieved. To do that, click Controllers in Solution Explorer. Then, click Add New Class, and name it ActorsController. Inside the SQLite_ASP.NET.Controllers namespace, modify the class using the code below:

[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
    [HttpGet]
    public IEnumerable GetActors()
    {
        var actors = DatabaseConnectionClass.SQLite_RunTime_Connection();
        return actors;
    }
}

After completing this, you should see the following when you run your project:

Select Try it now from the GetActors dropdown. Then, click Execute.

Upon execution, we see the list we were looking for. Great! We've successfully established a connection to our demo SQLite database.

Establish an encrypted connection to an SQLite Database

The dotConnect for SQLite library offers robust support for connecting to encrypted SQLite databases. Even though the library doesn't provide its encryption extension, it fully accommodates connections to databases encrypted with any 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. In case you need to change the database password, you can use the ChangePassword method. To decrypt the database, simply specify an empty password.

Here is an example of how to establish an encrypted connection to a database using AES256 encryption in dotConnect for SQLite:

using System;
using Devart.Data.SQLite;

namespace SQLiteAES256Example
{
    class Program
    {
        static void Main(string[] args)
        {
            // Open/create an unencrypted database
            SQLiteConnection conn = new SQLiteConnection("DataSource=sakila.db;Encryption=AES256;FailIfMissing=false;");
            conn.Open();
            conn.ChangePassword("best"); // Encrypt the database with the password "best"
            conn.Close();

            // Open the encrypted database with AES256 encryption
            conn = new SQLiteConnection("DataSource=sakila.db;Encryption=AES256;Password=best; FailIfMissing=false;");
            conn.Open();
            Console.WriteLine("Encrypted database opened successfully.");
            conn.Close();
        }
    }
}

Connect to SQLite using Visual ORM Designer

Another way of connecting to an SQLite database is through Entity Developer.

1. Download and install Entity Developer.

2. Click File and select New Model.

3. Click Entity Model under Devart Entity Framework.

4. In the Entity Developer: Create Model Wizard that opens, select Database First and click Next.

5. The Setup data connection properties window opens. Remember where you saved the Sakila database and enter the path to that folder:

6. Choose the modal contents by selecting Generate From Database, and click Next.

7. Select database objects you wish to scaffold. You can choose all of them, but since we have been using the Actors table all this time, let's stick to that.

8. On clicking next, you will be asked to setup naming rules. This will define the naming convention you want the property names in the database object to follow. We will leave it unchanged.

9. The next page will ask us to set up model properties like namespace and entity container. The only thing will change on this page is Target Framework. Since we are using .NET 8, we are going to select it. However, in case your project uses a different framework, you need to specify it instead. Once done, click Next.

10. When asked to choose the model diagram content, you can decide to use all entities, split the entities by database, or do a custom selection. For this tutorial, we will use All Entities and click Next.

11. In this tutorial, we will use the default settings as the code generation template for our objects. You can define different parameters for the object to follow.

12. Finally, as the creation is over, click Finish.

The created model will look like this:

We did it! We connected to our database using Entity Developer. From here, you can do a lot of things, like create new models, add properties to existing models, and even create mappings. Head over to our webpage to see a full Entity Developer feature list and the powerful tools used in model creation.

Advantages of Entity Developer

• Increased productivity and reduced errors remain entirely relevant for SQLite. Automating code generation and leveraging pre-built templates benefit any development environment.
• Enhanced development experience can be adjusted to highlight seamless Visual Studio integration and support for both Model-First and Database-First approaches, still applicable for SQLite.
• Powerful visual design and code generation might require minor adjustments. Since SQLite doesn't support some features like user-defined functions, you can mention support for common mapping types and efficient model handling.
• Flexibility and customization stay relevant for SQLite. You can emphasize features like background model validation, custom code generation templates, and direct query execution within the designer.

Connection using EF Core

We can also connect a .NET application to our SQLite database through EF (Entity Framework) Core. Let's see how we can do that by adding a separate project to our solution.

1. Right-click your solution in the Solution Explorer. Point to Add and select New project. Don't forget to move your sample database folder sqlite-sakila-db to this project as well.

As you can see, a second project named SQLite_EF_Core has been created.

2. Now, right-click SQLite_EF_Core and set it as a startup project.

At this point, we need to make sure we the EF Core Tools are installed in your development environment. You can install them globally using the .NET CLI by running the command:

dotnet tool install --global dotnet-ef

3. Once done, open the NuGet package manager for the project and add Devart.Data.SQLite.EFCore. Click Install.

4. Next, we need to install Scaffold-DbContext using the Package Manager Console. To do this, open Visual Studio first. Then, make sure the selected project is SQLite_EF_Core.

The Package Manager Console opens. Go ahead and run this command:

Install-Package Microsoft.EntityFrameworkCore.Tools

5. Once installed, we can use Scaffold-DbContext to generate DbContext and entity classes for our SQLite database. Go ahead and run this command in the Package Manager Console:

Scaffold-DbContext "Data Source=..\SQLite_EF_Core\sqlite-sakila-db\sakila.db" -provider Devart.Data.SQLite.Entity.EFCore -OutputDir Models

After executing this command, the SakilaContext and the Models folder containing the table entity classes should be generated. You should see them in your Solution Explorer.

As you can see, the Actors class has also been generated, so let's use it once again. Now, we will query this entity table using LINQ.

6. Right-click the SQLite_EF_Core project, select Add Class, and add DatabaseConnectionClass like we did in the project earlier. Then, copy this code into the class:

public class DatabaseConnectionClass
{
    private readonly SakilaContext _context;

    public DatabaseConnectionClass()
    {
        _context = new SakilaContext();
    }

    public IEnumerable GetActors()
    {
        return _context.Actors.Take(10).ToList();
    }
}

7. Just like before, we need a way to visualize our data. In the Controllers folder, add a new class called ActorsController.

8. Now, inside the SQLite_EF_Core.Controllers namespace, modify the class to use the code below:

[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
    private readonly DatabaseConnectionClass _context;

    public ActorsController()
    {
        _context = new DatabaseConnectionClass();
    }

    [HttpGet]
    public IEnumerable GetActors()
    {
        return _context.GetActors();
    }
}

Your class should look like this:

When you run your application using F5 and go to the GetActors Endpoint, click Try it out and Execute.

You should see your query results in the response body.

Advantages of dotConnect for SQLite

Built-in SQLite Encryption

• Supports robust encryption for your SQLite databases, eliminating the need for costly third-party solutions.
• Includes range of powerful encryption algorithms: TripleDES, Blowfish, AES128, AES192, AES256, Cast128, RC4

Comprehensive .NET integration

• Enables seamless use of SQLite within various .NET development environments (ASP.NET, Console, WinForms, .NET MAUI).
• Integrates with popular .NET data access technologies like ADO.NET, Entity Framework, and Entity Framework Core.

Highly performant and feature-rich

• Leverages ADO.NET technology for efficient database interactions.
• Supports a wide range of SQLite-specific features (user-defined functions, encryption extensions).

Improved developer productivity

• Introduces new approaches to application design, potentially simplifying development.
• Offers the bundled Entity Developer tool for visual ORM model design and code generation.

Streamlined development for SQLite applications

• Provides a complete solution for building SQLite-based .NET applications.
• Reduces development complexity by offering a single data provider for various .NET projects.

Enhanced ORM experience (with Entity Developer)

• Provides a visual designer for building and managing ORM models for SQLite data access.
• Simplifies ORM development by automating code generation and model management tasks.

Conclusion

This exploration equipped 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!