Connecting and Using SQLite Databases from C#: A Practical Guide

There are two primary methods for connecting to SQLite databases in C#:

1. ADO.NET (System.Data.SQLite):

   • This is the most common approach, providing a familiar interface for database interactions.

2. Dapper:

   • A lightweight object mapper (ORM) that simplifies database interactions.

Using SQLite in C#

Once connected, you can perform various operations on your SQLite database:

• Creating Tables:

  string sql = @"CREATE TABLE Customers (
      CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
      Name TEXT NOT NULL,
      Email TEXT UNIQUE
  )";
  using (var cmd = conn.CreateCommand())
  {
      cmd.CommandText = sql;
      cmd.ExecuteNonQuery();
  }
  

• Inserting Data:

  string sql = @"INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
  using (var cmd = conn.CreateCommand())
  {
      cmd.CommandText = sql;
      cmd.Parameters.AddWithValue("@Name", "John Doe");
      cmd.Parameters.AddWithValue("@Email", "[email protected]");
      cmd.ExecuteNonQuery();
  }
  

• string sql = @"SELECT * FROM Customers WHERE Name = @Name";
  using (var cmd = conn.CreateCommand())
  {
      cmd.CommandText = sql;
      cmd.Parameters.AddWithValue("@Name", "John Doe");
      using (var reader = cmd.ExecuteReader())
      {
          if (reader.Read())
          {
              int id = reader.GetInt32(0);
              string name = reader.GetString(1);
              string email = reader.GetString(2);
              // Process retrieved data
          }
      }
  }
  

• Updating Data:

  string sql = @"UPDATE Customers SET Email = @NewEmail WHERE CustomerID = @ID";
  using (var cmd = conn.CreateCommand())
  {
      cmd.CommandText = sql;
      cmd.Parameters.AddWithValue("@NewEmail", "[email protected]");
      cmd.Parameters.AddWithValue("@ID", 1); // Replace with actual ID
      cmd.ExecuteNonQuery();
  }
  

• string sql = @"DELETE FROM Customers WHERE CustomerID = @ID";
  using (var cmd = conn.CreateCommand())
  {
      cmd.CommandText = sql;
      cmd.Parameters.AddWithValue("@ID", 1); // Replace with actual ID
      cmd.ExecuteNonQuery();
  }
  

Choosing the Right Approach

• ADO.NET offers a more granular control over database interactions but requires more code.
• Dapper provides a simpler syntax and object mapping capabilities, making it suitable for rapid development.

using System.Data.SQLite;

public class SqliteExampleADO
{
    private const string ConnectionString = @"Data Source=mydatabase.db";

    public void CreateCustomersTable()
    {
        using (var conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();

            string sql = @"CREATE TABLE Customers (
                CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                Email TEXT UNIQUE
            )";

            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }

            conn.Close();
        }
    }

    public void InsertCustomer(string name, string email)
    {
        using (var conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();

            string sql = @"INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Email", email);
                cmd.ExecuteNonQuery();
            }

            conn.Close();
        }
    }

    public List<Customer> GetCustomers()
    {
        List<Customer> customers = new List<Customer>();

        using (var conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();

            string sql = @"SELECT * FROM Customers";
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        string email = reader.GetString(2);

                        customers.Add(new Customer { CustomerID = id, Name = name, Email = email });
                    }
                }
            }

            conn.Close();
        }

        return customers;
    }
}

public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

Dapper Example:

using Dapper;
using System.Data.SQLite;

public class SqliteExampleDapper
{
    private const string ConnectionString = @"Data Source=mydatabase.db";

    public void CreateCustomersTable()
    {
        using (var conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();
            string sql = @"CREATE TABLE IF NOT EXISTS Customers (
                CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                Email TEXT UNIQUE
            )";
            conn.Execute(sql);
        }
    }

    public void InsertCustomer(string name, string email)
    {
        using (var conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();
            string sql = @"INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
            conn.Execute(sql, new { Name = name, Email = email });
        }
    }

    public List<Customer> GetCustomers()
    {
        using (var conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();
            string sql = @"SELECT * FROM Customers";
            return conn.Query<Customer>(sql).ToList();
        }
    }
}

public class Customer
{
    public int CustomerID { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

1. Entity Framework Core (EF Core):

   • If your project already utilizes EF Core for data access, you can leverage it for SQLite as well.
   • Requires installing the Microsoft.EntityFrameworkCore.Sqlite NuGet package.
   • Offers a higher-level abstraction for database interactions, mapping C# classes to database tables.

2. Dapper.Contrib:

   • An extension library for Dapper that provides additional functionality, including:
     • Automatic object mapping from database results.
     • Transaction management.
     • Command caching.
   • Useful if you want the simplicity of Dapper with some additional features.
   • Requires installing the Dapper.Contrib NuGet package.

3. SQLitePCL.raw:

   • A low-level, managed code interface directly interacting with the native SQLite library.
   • Offers maximum performance and control but requires more development effort.
   • Suitable for advanced scenarios where raw performance is critical.
   • Requires building the library from source or using a pre-built package.

• EF Core: If your project already uses EF Core and you value a high-level abstraction.
• Dapper.Contrib: If you're using Dapper and want some additional features like automatic object mapping.
• SQLitePCL.raw: For experienced developers needing maximum performance and control over SQLite interactions.