How can i use dapper to connect to a sqlite database?

Question

How can I use dapper to connect and get data from a sqlite database?

Answer 1

There is nothing magical you need to do. Just add:

using Dapper;

And run queries on your open SqliteConnection

cnn.Query("select 'hello world' from Table")

Answer 2

Here is a complete working example with an in-memory database. Requires C# 8.0.

using System;
using System.Data.SQLite;
using Dapper;
    
namespace First
{
   // dotnet add package System.Data.SQLite.Core
   // dotnet add package Dapper
   class Program
   {
      static void Main(string[] args)
      {
         string constr = "Data Source=:memory:";
    
         using var con = new SQLiteConnection(constr);
         con.Open();
    
         var res = con.QueryFirst("select SQLITE_VERSION() AS Version");
    
         Console.WriteLine(res.Version);
      }
   }
}

Running the example:

$ dotnet run
3.30.1

Answer 3

To have a full working example you can take a look at this roslynpad snippet

#r "nuget: Dapper, 2.0.151"
#r "nuget: System.Data.SQLite.Core, 1.0.118"
 // dotnet add package System.Data.SQLite.Core   // dotnet add package Dapper
using System.Data.SQLite;
using Dapper;
// for this to work the file mySQLite.db must already exist
using var con = new SQLiteConnection(
            @"Data Source=C:\dev\RoslynPad\mySQLite.db;Version=3;"); 
            // mySQLite.db above must already exist
            // using var con = new SQLiteConnection("Data Source=:memory:");
con.Open();

var created = con.Execute(@"CREATE TABLE IF NOT EXISTS MyTable('ID' INTEGER, 'Comment' TEXT)"); 
var inserted = con.Execute(@"Insert INTO MyTable(ID, Comment) values (@a, @b)",
    new[] { new { a=1, b="Foo" }, new { a=2, b="Bar" }, new { a=3, b="FooBar" } }
  );
var selected = con.Query("Select * FROM MyTable WHERE ID in (1,3)");
selected.Dump("selected dump");

Screenshot of Roslynpad snippet from above

The result looks like this after it was run several times