Basic sample for Dapper/SqlLite

[kareninstructor]

Dapper NuGet is performic package for those working with a data provider. No need to be concerned about opening and closing a database connection.

Data operations work off a connection, no command object required.

Presented, using SqlLite data provider and Dapper.

• Create a new database and a table with several records.
• Read records
• Insert several records with a single command and note internally a loop is used. There are additional packages for real bulk operation.
• Perform a SELECT-WHERE
• Perform edit

using System.Data.SQLite;
using Dapper;

namespace SqlLiteSample1;

internal partial class Program
{
    private static string ConnectionString() 
        => "Data Source=sample1.db";

    static void Main(string[] args)
    {
        CreateDatabase();
        CreateTableWithTwoRecords();
        GetPeople();

        List<Person> people =
        [
            new Person { FirstName = "John", LastName = "Doe", Pin = 12334 },
            new Person { FirstName = "Mary", LastName = "Adams", Pin = 55555 },
            new Person { FirstName = "Karen", LastName = "Payne", Pin = 77334 },
            new Person { FirstName = "Jane", LastName = "Doe", Pin = 56778 }
        ];

        AddRange(people);

        GetPeople();

        var pin = GetPin(1);
        Console.WriteLine($"Pin {pin}");
        
        SetPin(1, 44444);

        pin = GetPin(1);
        Console.WriteLine($"Pin {pin}");

        Console.WriteLine("Done");
        Console.ReadLine();
    }

    // get all records (in this demo there are 3 records already
    private static void GetPeople()
    {
        var cn = new SQLiteConnection(ConnectionString());

        var people = cn.Query<Person>(
            """
            SELECT Id, 
                   FirstName, 
                   LastName, 
                   Pin 
            FROM Person 
            ORDER BY LastName
            """)
            .AsList();

        Console.WriteLine($"People count {people.Count}");

        foreach (var person in people)
        {
            Console.WriteLine($"{person.Id,-4}" +
                              $"{person.FirstName,-15}{person.LastName,-15}" +
                              $"{person.Pin}");
        }

        Console.WriteLine();
    }

    public static int GetPin(int id)
    {
        var cn = new SQLiteConnection(ConnectionString());
        return cn.QueryFirstOrDefault<int>(
            """
            SELECT Pin 
            FROM Person 
            WHERE Id = @Id
            """, 
            new { Id = id });
    }

    public static void SetPin(int id, int value)
    {
        var cn = new SQLiteConnection(ConnectionString());

        cn.Execute(
            """
            UPDATE Person
            SET Pin = @Pin
            WHERE Id = @Id
            """, 
            new { Id = id, Pin = value });

    }
    // bulk insert
    private static void AddRange(List<Person> list)
    {
        var cn = new SQLiteConnection(ConnectionString());
        cn.Execute(
            """
            INSERT INTO Person (FirstName,LastName,Pin) 
            VALUES (@FirstName, @LastName, @Pin);
            """, list);
    }

    private static void CreateDatabase()
    {
        SQLiteConnection.CreateFile("sample1.db");
    }

    private static void CreateTableWithTwoRecords()
    {
        using var cn = new SQLiteConnection("Data Source=sample1.db");
        cn.Execute(
            """
               CREATE TABLE IF NOT EXISTS Person
               (
                   Id INTEGER PRIMARY KEY AUTOINCREMENT,
                   FirstName TEXT NOT NULL,
                   LastName TEXT NOT NULL,
                   Pin INTEGER NOT NULL
                )
        """);

        List<Person> people =
        [
            new Person { FirstName = "Henry", LastName = "White", Pin = 98762 },
            new Person { FirstName = "Jim", LastName = "Beam", Pin =  89896 }
        ];

        AddRange(people);
    }
}

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Pin { get; set; }
}

See the following project on GitHub for a properly structured project.