This post lists the data access class that the code-behind uses to talk to the database. In this class we have our SQL strings and the Dapper methods that use those strings.

We’ve got a few static methods here that we can use to work with the SQLite database with the help of the micro-ORM Dapper. Here we get back to our SQL code and our CRUD database manipulations. You can see in the list of methods below that the first four represent Read, Create, Update and Delete (of CRUD). The last one gets the connection string. The first four are employing the C# using keyword so that the memory will automatically be released during garbage collection.

• public static List LoadToDo()
• public static void SaveToDo(ToDo todo)
• public static void UpdateToDo(ToDo todo)
• public static void DeleteToDo(int todo)
• private static string LoadConnectionString(string id = “Default”)

Here is the SQLiteDataAccess.cs file listing.

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Data.SQLite;
namespace ToDoList
{
    class SQLiteDataAccess
    {
        public static List<ToDo> LoadToDo()
        {
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {   // using Dapper
                var output = conn.Query<ToDo>("select * from ToDos order by Id desc", new DynamicParameters());
                return output.ToList();
            }
        }
        public static void SaveToDo(ToDo todo)
        {
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                conn.Execute("insert into ToDos (Name, Status) values (@Name, @Status)", todo);
            }
        }
        public static void UpdateToDo(ToDo todo)
        {
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                conn.Execute("update ToDos set Name = @Name, Status = @Status where Id = @Id", todo);
            }
        }
        public static void DeleteToDo(int todo)
        {
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                conn.Execute("delete from ToDos where id = @Id", new { Id = todo } );
            }
        }
        private static string LoadConnectionString(string id = "Default")
        {
            // go to App.config and return the string called "Default"
            // need to add Reference: ConfigurationManager and using System.Configuration
            return ConfigurationManager.ConnectionStrings[id].ConnectionString;
        }
    }
}