Connect to SQLite with Dapper using C#

In this tutorial, we will demonstrate how to use Dapper to manage data operations in .NET applications.

With dotConnect for SQLite, you can build a console application, define Dapper models, and implement CRUD operations using a robust, reusable connection class for efficient interaction with a SQLite database.

Why dotConnect for SQLite?

dotConnect for SQLite is the ideal data provider for all SQLite-related operations. It offers on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.

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 also get it.
dotConnect for SQLite: A high-performance ADO.NET data provider for SQLite with enhanced ORM support and database connectivity features.
Sakila database: A sample database for learning and testing. Download the folder and unzip the file to use it.

Download and activate dotConnect for SQLite

30-day free trial version

Download and install dotConnect for SQLite directly on your machine, or install the Devart.Data.SQLite NuGet package.

No license key is required, and you can start exploring the product immediately.

Full version

After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.

License details and the activation key

To activate a connection in your application, add the License Key to your connection string.

Create a .NET project

1. In Visual Studio, click Create a new project.

2. Search for Console App (.NET Framework), click Next.

3. Name the project, specify the folder path where it will be stored, and click Create. In this tutorial, we'll use the project name SQLiteDapper.

4. Right-click your project in the Solution Explorer and select Manage NuGet Packages.

5. Find and install the Dapper and Devart.Data.SQLite packages.

Check SQLite database objects

1. To connect to SQLite using the built-in Data Explorer, move the sakila database to the project folder and select Tools > Connect to Database.

2. Then, choose SQLite as the data source and click Continue.

3. Click Browse to select the database file and click OK.

Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.

Connect to SQLite and retrieve data

In this section, we're going to show how the application will connect to the SQLite database, execute the query, and display the data from the Actor table in the console.

1. Create a connection class to provide a single, reusable database connection string. In the Solution Explorer, right-click the project name and select Add > New Item. Then, enter DatabaseConfig.cs into the field and click Add.

2. Copy and paste this code into the newly created class.

using System;
using Devart.Data.SQLite;
using System.Data;

namespace DapperSQLiteExample {
  public static class DatabaseConfig {
    private static readonly string connectionString = "DataSource=myDatabase.db;LicenseKey=**********";

    public static IDbConnection GetConnection() {
      try {
        var connection = new SQLiteConnection(connectionString);
        connection.Open();
        Console.WriteLine("Connected to SQLite database successfully!");
        return connection;
      } catch (Exception ex) {
        Console.WriteLine("Error connecting to database: " + ex.Message);
        throw;
      }
    }
  }
}

3. Define a class to map the Actor table. Create the Actor.cs class and insert this code into it.

using System;
using System.Data;
using Dapper;
using Devart.Data.SQLite;

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

4. Create Program.cs and add the following code into it.

using System;
using System.Collections.Generic;
using System.Data;
using Dapper;

namespace DapperSQLiteExample {
  class Program {
    static void Main(string[] args) {
      // Get database connection
      using(IDbConnection db = DatabaseConfig.GetConnection()) {
        // Query the first 10 rows from the Actor table
        string sql = "SELECT actor_id AS ActorId, first_name AS FirstName, last_name AS LastName, last_update AS LastUpdate FROM actor LIMIT 10;";

        IEnumerable < Actor > actors = db.Query < Actor > (sql);

        Console.WriteLine("\nFirst 10 rows from the Actor table:");
        foreach(var actor in actors) {
          Console.WriteLine($"{actor.ActorId}: {actor.FirstName} {actor.LastName}, Last Update: {actor.LastUpdate}");
        }
      }

      Console.WriteLine("\nPress any key to exit...");
      Console.ReadKey();
    }
  }
}

Update the connection string

Replace the placeholders in the connection string with your actual SQLite database credentials.

Note

If you're using a purchased dotConnect for SQLite license, include the license key in the connection strings.

Run the application

Build and run your application by pressing F5 or selecting Start from the toolbar.

Batch insert new records using Dapper

This example demonstrates how to insert multiple records in a single transaction to improve performance and ensure atomicity. Delete the contents of Program.cs, paste this code into it, and press F5.

using System;
using System.Collections.Generic;
using System.Data;
using Dapper;

namespace DapperSQLiteExample {
  class Program {
    static void Main(string[] args) {
      using(IDbConnection db = DatabaseConfig.GetConnection()) {
        // Insert 10 new actors first
        List < Actor > newActors = GetNewActors();
        InsertActors(db, newActors);

        // Display the first 10 rows after insertion
        Console.WriteLine("\nActors after insertion:");
        DisplayActors(db);
      }

      Console.WriteLine("\nPress any key to exit...");
      Console.ReadKey();
    }

    /// 
    /// Inserts a list of actors into the database.
    /// 
    private static void InsertActors(IDbConnection db, List < Actor > actors) {
      string sql = "INSERT INTO actor (actor_id, first_name, last_name, last_update) VALUES (:ActorId, :FirstName, :LastName, :LastUpdate);";

      int rowsInserted = db.Execute(sql, actors);
      Console.WriteLine($"\nInserted {rowsInserted} rows successfully.");
    }

    /// 
    /// Fetch and display the latest 10 actors from the database.
    /// 
    private static void DisplayActors(IDbConnection db) {
      string sql = "SELECT actor_id AS ActorId, first_name AS FirstName, last_name AS LastName, last_update AS LastUpdate FROM actor ORDER BY actor_id DESC LIMIT 10;";
      IEnumerable < Actor > actors = db.Query < Actor > (sql);

      Console.WriteLine("\nFirst 10 rows from the Actor table:");
      foreach(var actor in actors) {
        Console.WriteLine($"{actor.ActorId}: {actor.FirstName} {actor.LastName}, Last Update: {actor.LastUpdate}");
      }
    }

    /// 
    /// Generates a list of 10 new actors.
    /// 
    private static List < Actor > GetNewActors() {
      return new List < Actor > {
        new Actor {
          ActorId = 221,
            FirstName = "Thor",
            LastName = "Odinson",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 222,
            FirstName = "Steve",
            LastName = "Rogers",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 223,
            FirstName = "Bruce",
            LastName = "Banner",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 224,
            FirstName = "Stephen",
            LastName = "Strange",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 225,
            FirstName = "Wanda",
            LastName = "Maximoff",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 226,
            FirstName = "T'Challa",
            LastName = "Black Panther",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 227,
            FirstName = "Scott",
            LastName = "Lang",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 228,
            FirstName = "Clint",
            LastName = "Barton",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 229,
            FirstName = "Carol",
            LastName = "Danvers",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 230,
            FirstName = "Nick",
            LastName = "Fury",
            LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        }
      };
    }
  }
}

The result is as follows.

Update SQLite data using Dapper

Let's batch update actors with actor_id between 221 and 230, modify their first and last names, and then display the updated rows. Replace the contents of Program.cs with this code and press 5.

using System;
using System.Collections.Generic;
using System.Data;
using Dapper;

namespace DapperSQLiteExample {
  class Program {
    static void Main(string[] args) {
      using(IDbConnection db = DatabaseConfig.GetConnection()) {
        // Update names for actors with ID 221-230 (only existing ones)
        List < Actor > updatedActors = GetUpdatedActors();
        UpdateActors(db, updatedActors);

        // Display actors after update
        Console.WriteLine("\nActors after update:");
        DisplayActors(db);
      }

      Console.WriteLine("\nPress any key to exit...");
      Console.ReadKey();
    }

    /// 
    /// Updates the names of actors with IDs between 221 and 230.
    /// Only updates existing records, without inserting new ones.
    /// 

    private static void UpdateActors(IDbConnection db, List < Actor > actors) {
      string sql = "UPDATE actor SET first_name = :FirstName, last_name = :LastName, last_update = :LastUpdate WHERE actor_id = :ActorId;";
      int rowsUpdated = db.Execute(sql, actors);
      Console.WriteLine($"\nUpdated {rowsUpdated} rows successfully.");
    }

    /// 
    /// Fetch and display updated actors from the database.
    /// 

    private static void DisplayActors(IDbConnection db) {
      string sql = "SELECT actor_id AS ActorId, first_name AS FirstName, last_name AS LastName, last_update AS LastUpdate FROM actor WHERE actor_id BETWEEN 221 AND 230 ORDER BY actor_id;";
      IEnumerable < Actor > actors = db.Query < Actor > (sql);

      Console.WriteLine("\nFirst 10 rows from the Actor table (After Update):");
      foreach(var actor in actors) {
        Console.WriteLine($"{actor.ActorId}: {actor.FirstName} {actor.LastName}, Last Update: {actor.LastUpdate}");
      }
    }

    /// 
    /// Generates an updated list of actors with modified names.
    /// 

    private static List < Actor > GetUpdatedActors() {
      return new List < Actor > {
        new Actor {
          ActorId = 221, FirstName = "Jason", LastName = "Bourne", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 222, FirstName = "James", LastName = "Bond", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 223, FirstName = "John", LastName = "Wick", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 224, FirstName = "Lara", LastName = "Croft", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 225, FirstName = "Ellen", LastName = "Ripley", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 226, FirstName = "Neo", LastName = "Anderson", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 227, FirstName = "Max", LastName = "Rockatansky", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 228, FirstName = "Sarah", LastName = "Connor", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 229, FirstName = "Rick", LastName = "Deckard", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        },
        new Actor {
          ActorId = 230, FirstName = "Jack", LastName = "Sparrow", LastUpdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
        }
      };
    }
  }
}

Test the application.

Delete SQLite data using Dapper

Let's batch delete actors where actor_id is between 221 and 230. Update Program.cs with this new code and press F5.

using System;
using System.Collections.Generic;
using System.Data;
using Dapper;

namespace DapperSQLiteExample {
  class Program {
    static void Main(string[] args) {
      using(IDbConnection db = DatabaseConfig.GetConnection()) {
        // Delete actors with ID 221-230
        DeleteActors(db);
      }
    }

    /// 
    /// Deletes actors with IDs between 221 and 230.
    /// 
    private static void DeleteActors(IDbConnection db) {
      string sql = "DELETE FROM actor WHERE actor_id BETWEEN 221 AND 230;";
      int rowsDeleted = db.Execute(sql);
      Console.WriteLine($"Deleted {rowsDeleted} rows successfully.");
      Console.ReadKey();
    }
  }
}

Here's what we get.

Conclusion

In this tutorial, we showed how to integrate Dapper with SQLite using dotConnect for SQLite to streamline data operations in .NET applications. The powerful features of dotConnect for SQLite, combined with the simplicity and efficiency of Dapper, offer a fast, flexible, and reliable approach for managing database interactions in SQLite-based applications. With these tools, you can confidently build scalable, maintainable, and high-performance .NET solutions that integrate seamlessly with SQLite databases.